August 20, 2010 at 9:49 am
Hi everyone.
Have been lurking off and on for a few months. Lots of great info. I'm not a DBA, but I'm having to play that role for my team.
I had a good reason to shrink a database (will give details if requested). I decided to use the GUI (2005) to do it. It started at 8:42 am two days ago and it's now 8:01 am. The data and log files still have the same size and the disk looks like it's being maxed out. CPU usage is very low.
The db file is 575 GB. The GUI tells me that currently allocated space is 771 GB and available space is 205 GB (26%). The drive has a total capacity of 774 GB with Used space of 589 GB.
It is in multiuser mode, but I am the only user logged in and there are no other processes running.
This server is not critical, but I would appreciate comments on two issues:
1 - Would it be normal for this process to take that long?
2 - How can you stop a shrink process from the GUI? The cancel button is grayed out. If I were using the command line in a query window, I could cancel the 'query'.
Again, I'm using this server as a test right now. I'm torn between letting it run longer to see how long it takes and what the shrink process did, and stopping it so I can continue with my testing. I'm more curious than anything else.
I would appreciate anyone taking a stab at educating me on this issue. In the meantime I will continue browsing through the posts; I feel like I have found the 'Cave of Wonders'.
Ricardo
August 20, 2010 at 11:20 am
No way from the gui. Open a query window, find the session id that the shrink is running from and KILL it.
Regarding the shrink...
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
and http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ and everything it links to.
If you're shrinking to reclaim space that will not be reused for a long time, make sure this is a once-off operation, and rebuild all your indexes afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 1:46 pm
Gail, thanks for the response. I will try your suggestion to kill the process.
This is a monster production db that we are restoring to this server to be used to develop new code against it. It has several years' worth of data. Some tables more than others. I'm planning to delete all but two years' worth of data from those tables. We also have some tables that will not be used at all. I will truncate those. I expect this will save about 150 GB.
So, this will be a one-time shrink. I naively decided to do a test shrink before I deleted anything to see if it would shrink some, and now I have a runaway shrink process.
A year ago we moved our production DW from this server to a much bigger server with about 30 drives. Db, log, and tempdb files are all separate. Since our budget is super tight, we're trying to enable the old server as our development server. I need to move about 4 dbs in there. I started with the biggest one first, with the idea of reducing the size as described above and restore the next one, reduce the size, restore the next one. The other 3 dbs are much smaller.
Anyway, I'm now learning new stuff. I appreciate the help you and the others provide here. Hopefully in the near future I can return the favor.
August 20, 2010 at 2:36 pm
try a shrink with the truncateonly option first to get back some space with minimum resource usage (might not get any back but worth a try) and then do the rest of the shrinking in chunks to keep the process under control.
---------------------------------------------------------------------
August 20, 2010 at 2:43 pm
You say 'The db file is 575 GB. The GUI tells me that currently allocated space is 771 GB and available space is 205 GB (26%). The drive has a total capacity of 774 GB with Used space of 589 GB.'
those figures do not add up. pls confirm file sizes and run command sp_spaceused whilst connected to the database (unless you have started shrink off)
Set this database to simple recovery mode as well before you do any deletes\shrinks
---------------------------------------------------------------------
August 20, 2010 at 3:31 pm
Hi George, I was rounding up to GB units. Here are the actual numbers, with their actual units:
Windows File Manager:
mdf file 575,784,960 KB
SQL Server Shrink Database GUI:
Currently allocated space 771,292.00 MB
Available free space 205,764.20 MB (26%)
Drive Properties dialog box:
Capacity 832,143,319,040 bytes
Used space 589,717,852,160 bytes
Free space 242,425,466,880 bytes
The database is in simple recovery mode.
The results of sp_spaceused are:
database_size: 771,292 MB
unallocated space: 202,642.56 MB
reserved: 368,278,976 KB
data: 270,625,640 KB
index_size: 96,846,632 KB
unused: 806,704 KB
Am I reading this correctly, there are only 270 GB of real data in the database?
Thanks for your interest.
August 21, 2010 at 3:02 am
ricnav (8/20/2010)
Hi George, I was rounding up to GB units. Here are the actual numbers, with their actual units:Windows File Manager:
mdf file 575,784,960 KB
The results of sp_spaceused are:
database_size: 771,292 MB
unallocated space: 202,642.56 MB
reserved: 368,278,976 KB
data: 270,625,640 KB
index_size: 96,846,632 KB
unused: 806,704 KB
Am I reading this correctly, there are only 270 GB of real data in the database?
the important value is 'reserved' which is data + indexes. Indexes take up space so you have to include them as well. So you have about 360GB used space in your 570GB data file. (Hence unallocated of about 200GB)
database size is also 770GB but the data file is only 570GB, so that tells me you have a transaction log of about 200GB. It must have needed to be that size at some point but with the reduction in data and the fact it is in simple mode that amount of log space won't be required in the future, so I would reduce that right down to about 10GB. Take care how you coordinate this with your data deletes, they could cause log growth if not batched up (truncates not an issue with the log).
---------------------------------------------------------------------
August 23, 2010 at 10:08 am
Hi George.
You are correct. There was an issue not too long where the process had to delete about 100 million records from our biggest table. I'm pretty sure that's reflected in the log file. The log file is 204 gigs I think. It hasn't become critical in our production server because we have 450 gigs assigned to log files. On this dev server, of course, it caused us trouble.
I was able to stop the shrink process (thanks Gayle). My plan is now to start a shrink with the dbcc command since it looks like there is more flexibility that way. I will shrink the database by a small amount just to test and time it.
Thanks for your analysis. I really appreciate it.
August 23, 2010 at 10:09 am
Sorry Gail. I didn't mean to mess up your name 🙁
August 23, 2010 at 10:15 am
ricnav (8/23/2010)
Sorry Gail. I didn't mean to mess up your name 🙁
No worries, I get all sorts of misspellings/misidentifications here. Doesn't bother me any longer.
I recommend you use ShrinkFile, not ShrinkDB and do your shrinks file-by-file for aditional control.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 11:32 am
Thanks for being so understanding.
Your advice came too late 😀
I used ShrinkDatabase notruncate. The GUI churned for over two days before I killed it. The dbcc command took less than five minutes to take the db from 575 gigs to 444 gigs.
Lesson learned: don't use the GUI.
I did use ShrinkFile for the log file. It took it from 204 gigs to a couple of megs. I set the min size to 20 gigs since we do some heavy duty processing and I know even in this dev server we still will.
I haven't done any of the deletes and truncates I mentioned earlier. After I do I will have to do another db shrink.
After all that I will rebuild all the indexes. We have no clustered indexes so far.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply