Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2008-01-10 : 07:24:50
|
We have a server that is running low on disk space. I set up a job that runs:DBCC SHRINKDATABASE ( 'Reports' ) 3 times a week in an attempt to keep disk space low. This worked fine for the first couple of weeks and released a fair amount of disc space back to the OS.Lately though, this has been taking a long, long time to complete and usually is terminated as the victim of a deadlock. When it does complete, it doesn't free up that much disc space anymore although the disc space is still being consumed at the same rate on a daily basis.Can anyone suggest why this would suddenly not be as effective as it used to be? |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-01-10 : 11:34:21
|
| Is any data being deleted from the Reports database, or is it just filling up with more and more data?Also, it is probably time to rebuild all the indexes, as the data has likely gotten very sparse with all the shrinking. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-10 : 16:45:34
|
| You can only shrink the database if there is actually empty space. You might also find that you don’t really need to shrink the database. If it just grows again, you should leave it alone after it gets to a stable size.The script on the link below will show you the amount of unused space in each database data file.Get Server Database File Informationhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress. You can use the script on the link below to do that.Shrink DB File by Increment to Target Free Spacehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355CODO ERGO SUM |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2008-01-11 : 02:41:39
|
| Thanks guys. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2008-01-11 : 07:53:24
|
| Just to say "great script Michael". The incremental shrink works a treat.Many thanks. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2008-01-14 : 09:49:50
|
| One more. Would it be advisable to run the shrink script on a live reporting db? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-14 : 10:53:41
|
| You should only shrink a DB when you have a good reason to do it. If you know it is going to grow again, you should just leave it alone.There is no reason why you cannot shrink a database that is in use. You may sometimes run into a problem with the script failing on deadlocks, but you can usually just wait a few minutes and restart the shrink. If possible, it is best to run the shrink at times of least activity.CODO ERGO SUM |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2008-01-14 : 14:42:07
|
| Thanks. I understand your concern but this is a bit of an emergency as this server is very low on disc space. The server is a business critical system for both MI and client reporting. It is highly transactional on a nightly basis and I have seen 20gb of disc consumed after one night. When this happens, the server is in danger of completely running out the following night if action is not taken. While we wait for the replacement to come online, I am monitoring the disc space daily in an attempt to preserve/free up as much space as possible to avoid a catastrophe. The shrinks do the job very well and keep enough space to ensure that there are no problems the following night. If you can think of a better alternative I would be very happy to listen. |
|
|
|
|
|
|
|
|
DBCC SHRINKDATABASE stopped working