|
I have data file with 52GB and it have free space of 5GB ... After I ran dbcc shrinkfile('databfilename',0)... It is taking long time to shrink (about 7min) and still running so I stopped it.... So please let me know how to shrink small sizes relaseing 500MB space ang again 500MB space... May be it could be speed up?
(comments are locked)
|
|
Did you checked your shrink process is blocked by any other session? DBCC OPENTRAN or run sys.dm_exec_requests monitor the columns percent_complete,wait_type,blocking_session_ID columns.
(comments are locked)
|
|
If you really need to shrink.... Check for any heaps, generally when you deletes rows from heap, pages freed remains allotted to the heaps, they are not released (but Truncate and DELETE WITH(TABLOCK) does). The option is to create a clustered index on them and then shrink. To find heaps
(comments are locked)
|
|
Also remember, that you must rebuild all your indexes and statistics after shrinking as the process caused fragmentation.
(comments are locked)
|
Are you running low on space? If you have plenty left when I would advise you leave the space, especially if you are still feeding the DB.
However, if you are sure. Then check what activity is currently on the system using something like sp_who2 as it is possible there is some transaction still active.
Don't forget - every time someone shrinks a database, God (or was it Paul Randal? tricky to tell the difference sometimes) kills a kitten.
Are you comparing Paul Randal to God?!
We are having low disk space and the data base is using for reporting purpose... So there is now problem if we shrink also as of now the data file having 36GB of unused space and the database is option set to "Autoshrink"... And I have takem Full backup and it completed successfully... I have checked the details by using sys.dm_exec_requests there Command Column have AutoShrink and Percent_complete is 40%... But still the data file 36GB of unused space no released...
Note: Database is in SIMPLE RECOVERY so there is no problem we have with log file and it is fine.
Just I want to relase that 36GB of unused space.... AutoShrink enabled....
The way to deal with this problem is not to shrink your databases over and over. That causes severe fragmentation of your database files and will seriously impact performance. Instead, you need to manage the space you have available, add additional drives, rearrange the storage of your databases. It's hard work, but that's why it's called work.