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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
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.
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 :)
The documentation for this is here:
http://msdn.microsoft.com/en-us/library/ms190488(v=sql.100).aspx
Give the link a thorough read.
USE mydatabase; GO; EXECUTE sp_spaceusedreturn? 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.