July 17, 2012 at 1:33 pm
I have a 3 TB database, in which recently I relocated and dropped a huge table, probably about 1.2 TB big.
I am now trying to reclaim the space via either SHRINKDATABASE or SHRINKFILE. However, after about 20 hours of running with each option, nothing is happening. I have to stop things after 20 hours in order not to interfere with the backups. Why is the DB not shrinking? Furthermore, even if I stopped the command, shouldn't both commands be cumulative, i.e. if I run it for 20 hours, I should get some shrinkage, right?
This is a SQL Server 2008 R2 database, simple recovery model. Ordinarly in the past when I did a SHRINKFILE and had to recover say 500G, the operation took about 2 minutes and it was done.
July 17, 2012 at 1:52 pm
Do you have enough space in your log file? does the drive your log file is located have enough space for log file growth? shrinking logs a lot of things
Pooyan
July 17, 2012 at 2:19 pm
this is not the log I am trying to shrink, it's one of the data files I am trying to shrink.
July 17, 2012 at 2:21 pm
I know that .shrinking the data file uses a lot of log space.
Pooyan
July 17, 2012 at 2:42 pm
log space is not an issue.
I see the DBCC process running, but it almost looks like nothing is going on. I would expect to see some heavy I/O activity, but I don't see anything out of the ordinary.
July 18, 2012 at 2:16 am
Try shrinking in smaller chunks rather than one big shrink and keep your eye on blocking and percent_complete from sys.dm_exec_requests.
July 18, 2012 at 9:33 am
1. Shrinking the data file does not use a lot of log space.
2. Never use ShrinkDatabase. If you must shrink a file, use ShrinkFile. ShrinkDatabase basically calls ShrinkFile for every file in the database.
3. Shrinking a data file is a very slow procedd. It blindly moves the chunk of data closest to the end of the file to the spot closest to the beginning of the file where it will fit. It's very slow because it's doing a lot of very small actions in a loop. You can equate it to RBAR (row by agonizing row) except it's more like CBAC (chunk by agonizing chunk).
4. You may want to issue a shrinkfile command with the Truncate_Only option to drop whatever free space is at the end of the file (without moving any data) to see how much you have gained. You may even find out that you've gained enough and can stop.
5. For a 3 TB file, it will take a lot longer than 20 hours.
6. Shrink is performing physical movement of data that does not get rolled back. So each time you run it, whatever progress is made is retained. You can keep running it in small bunches until you're done if you must.
July 18, 2012 at 1:37 pm
For the record, shrinking data files is not a good idea in the first place.
My reasoning is based on the following blog by Paul Randall and we all know that Paul certainly knows SQL Server engine as well as storage inside out.
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
I am thinking that this would apply even more for a large database like the one in question.
July 18, 2012 at 2:16 pm
It is okay to shrink a file for a one-off situation like this. Not something you should do regularly. And you should plan for rebuilding the indexes afterwards because fragmentation will be horrible.
July 18, 2012 at 4:01 pm
vikrantspatil (7/18/2012)
For the record, shrinking data files is not a good idea in the first place.My reasoning is based on the following blog by Paul Randall and we all know that Paul certainly knows SQL Server engine as well as storage inside out.
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
I am thinking that this would apply even more for a large database like the one in question.
For those too lazy to cut and paste (or properly tag):
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
July 19, 2012 at 12:21 pm
so to test whether I'm crazy or not, I did a restore on the 3TB database onto a test server and deleted every table except for one table, which is around 1.2Tb. I then did a SHRINKFILE on the one filegroup that contains the table. Before I did the SHRINKFILE, I did a SHRINKFILE with the truncateonly option, which gave me back a measly 20G. I then ran the for 18 hours, DBCC SHRINKFILE(3,1), filegroup ID is 3 and put 1 in there knowing that it will simply go to whatever the actual size of the table is. I see I/O on the table, but the end result after 18 hours is that the size of the physical file is still exactly the same after 18 hours. I don't understand this. My expectation is that when I looked at it after 16 hours, I would have expected the filesize to be some number between 1.2Tb and 3.0 Tb, but it's still stuck at 3.0Tb.
I've got another issue, with the log, a more minor issue since I'm only talking about shrinking 50G. Ordinarly in the past I could issue a backup with no_log, then do a shrinkfile on the log, and the log would shrink. With 2008, since the DB is already in SIMPLE recovery mode, theoretically I should be able to shrink the log to next to nothing, i.e. DBCC SHRINKFILE(2,1) and things would shrink. I even ran the SHRINKFILE multiple times, in case there were funky issues with virtual boundaries as in past SQL Server versions. No shrinking. Once again, this is a standalone DB, no replication or anything else that would tie up the log, just a copy of the DB where I've deleted off all the tables and views, except for one table. (I don't think I have anything like code that would tie up the log right?)
July 19, 2012 at 1:56 pm
Make a full backup for this database and then shrink the database.
July 19, 2012 at 2:30 pm
SQL ORACLE (7/19/2012)
Make a full backup for this database and then shrink the database.
What difference will that make?
July 19, 2012 at 2:32 pm
cmoy, when you're shrinking the file, it won't resize the file until it's done moving chunks around. So you're not going to see it shrinking a little at a time. You have to let it complete or manually run shrink file with the truncate only option after you stop it.
July 19, 2012 at 2:39 pm
Robert Davis (7/19/2012)
SQL ORACLE (7/19/2012)
Make a full backup for this database and then shrink the database.What difference will that make?
We need to truncate the log to release the space from the database first.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply