0

I am a .net developer and not an expert database administrator.

I have an application running on SQL server 2008. Yesterday there was an issue with a certain query getting executed in an infinite loop which caused the database to grow from 500 MB to 380 GB. The log file itself was 300 GB and the mdf file 80 GB.

Today I stopped the query and truncated the log file to 12 MB. Then I figured out which tables had grown and truncated them, but still my database size is 67 GB!

I ran a query to determine the size of all tables https://stackoverflow.com/a/7892349/287100

However, the total size of all tables was less than 100 MB. I want to know why the database size is so big and how I can shrink it back to around 500MB.

Thanks.

1
  • Did you already attempt to shrink the data file(s)? Commented Aug 2, 2012 at 0:59

1 Answer 1

1

You can shrink an MDF file using:

USE [Databasename]
GO
DBCC SHRINKDATABASE(N'Databasename' )
GO

and you can shrink an LDF file using: 

USE Data base 
GO
DBCC SHRINKFILE(databsename_Log, 1)
GO
Hannah Vernon ♦
71.1k22 gold badges178 silver badges323 bronze badges
answered Aug 1, 2012 at 11:59
2
  • But have a look at this and this first Commented Aug 1, 2012 at 12:16
  • 1
    DBCC SHRINKDATABASE and DBCC SHRINKFILE is redundant. The former data and log files. Commented Aug 1, 2012 at 12:33

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.