5

Is there a way to shrink mdf file of database in SQL Server 2008 without using additional space?

Total space in hard disk is 136 GB, the .MDF file size is 124 GB, and the log file size is 2 GB. I have only 12 GB free space and while running the shrink command on the .MDF file, logs are growing and consume the free space -- low disk spaces throws the shrinking into a no respond state.

Is there another method to shrink this file without using additional disk space?

2
  • 4
    What does USE mydatabase; GO; EXECUTE sp_spaceused return? Unless the unused column is some fantastic amount, shrink's not going to appreciably decrease the size of the mdf---even if you could get the operation to complete.
    – billinkc
    Dec 3, 2012 at 16:38
  • 2
    You need a bigger disk - that's all ....
    – marc_s
    Dec 3, 2012 at 18:20

5 Answers 5

5

To summarize the answers here and to inject my own advice:

1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.

2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.

3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.

4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.

If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.

3

I would avoid shrinking if possible. It will cause fragmentation issues with any indexes you have and thus performance issues. It can also cause fragmentation in the file system.

If your in a crunch, add a second datafile in the same filegroup to a different volume. SQL Server will start putting new data in that new file. It will do this until the files are of equal size and then basically round robin going forward.

This should allow you to focus on averting a full drive until you can get a bigger disk.

2

DBCC SHRINKDB causes a lot of logging, disk IO, and blocking issues. If you absolutely must shrink, have you tried shrinking it in smaller chunks? Maybe just shrinking it by 1 GB or even 500MB at a time. Also, are you OK with having your log files on the same drives as your data? Tail of the log backups won't be able to be performed in case of a corrupt drive array and performance could be greatly affected.

0

If you're looking for a GUI way to do it, set your DB to SIMPLE RECOVERY (right-click on DB in SSMS, go to properties, Options, Recovery Model -> Simple).

Then do the shrink of files - firstly your log file and then your data file. To do so, right-click on DB in SSMS, Tasks, Shrink, Files. Set FileType to log and and choose "Reorganise pages..." and set as low as you can go. Repeat but set FileType to Data.

Set your DB back to FULL RECOVERY if it was on there before you changed to SIMPLE RECOVERY above. If it was already set for SIMPLE RECOVERY then just leave as is.

This sort of shrinking creates a lot of index fragmentation and probably shouldn't be a routine thing, but it can get you out of a jam :)

-1

The documentation for this is here:

http://msdn.microsoft.com/en-us/library/ms190488(v=sql.100).aspx

Give the link a thorough read.

2
  • problem is i have only 12 gb free space and while running the shrink command of mdf file, logs are buliding up and consumes the free space and low disk spaces throws the shrinking in no respnd state
    – puneet
    Dec 3, 2012 at 12:38
  • truncate the logfile first before you do this? Or do you want to keep the log file?
    – Ric
    Dec 3, 2012 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.