Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://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.

share|improve this question
    
Did you already attempt to shrink the data file(s)? –  Jon Seigel Aug 2 '12 at 0:59

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
But have a look at this and this first –  dezso Aug 1 '12 at 12:16
1  
DBCC SHRINKDATABASE and DBCC SHRINKFILE is redundant. The former data and log files. –  Thomas Stringer Aug 1 '12 at 12:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.