September 14, 2009 at 10:38 am
If anyone can shed some light in this issue..
We take a backup of our Production db, restore to dev server, its around 450GB. This is to create a new release seed for upgrade and UA testing. I have been doing this for years with no problem, in fact nothing has changed except the database becoming 50GB bigger than the last time I ran this procedure.
As we have to move this database around for various reasons, we have to make the db smaller. We truncate various large tables that are not required for testing like Audit etc.
We also have a Documents tables which stores scanned files in a Image field. This is approx 400GB of the datafile size. I use a 'WRITETEXT dbo.DOCUMENTS.IMAGE_DATA @ptrval NULL...' script that basically removes the image data.
The database is then a nice size used space is around 27GB.
Normally we run a couple of shrinks and all is fine. This time the Shrink failed, tried every way possible to shrink the data file. I have also tried all the below and prob more that I cant rememeber.. any help would be appreciated..
1. ShrinkFile...
2. SHRINKDATABASE...
3. I migrated all the data to a new datafile then tried to shrink it fails
4. Run updateusage - which did alter the pages used for LOB (see below)
DBCC UPDATEUSAGE: sysindexes row updated for table 'DOCUMENTS' (index 'DOCUMENTS_PK', partition 1):
USED pages (LOB Storage): changed from (4294943613) to (19388) pages.
RSVD pages (LOB Storage): changed from (0) to (38052) pages.
5. DBCC DROPCLEANBUFFERS & DBCC FREEPROCCACHE - Tried shrink again
6. restarted SQL Services - shrink still not worked
7. DBCC CLEANTABLE('ss_fwaslive', 'DOCUMENTS')
8. DBCC CHECKDB - no errors and still wont shrink
9. Thinking its still corruption of some kind and the only table it could be is 'Documents' as this was the table that help the 400GB of data, so I dropped all keys and FK's to this table. Created a new filegroup and file, then moved it.
ALTER TABLE Drop CON.... With (Move to 'NEW Filegroup) then recreated indexes, update usage, sp_help 'Documents' all moved to new file/fielgroup fine. Tried shrink still will not work!!!
When I run a shrink - its runs for about 20 mins then just returns the same info with no change in the reserved space on the os file.
111548398405120034823523481680
11231473 314733147231472
I am at a loss as to why the shrink wont work..when it always has in the past!
Tomorrow I am going to Service Pack this dev box, but i am not holding much hope!
Anyone seen this before, any Ideas?
Oraculum
September 14, 2009 at 12:53 pm
hmmm has your source database been restored lately? I've run into a similar problem before and it had to do with the source database files intial size. You can not shrink below the intial size of the files.
For some reason after I restored the source, the intial size of the files was the size of the file before the restore. Get it?
September 14, 2009 at 12:56 pm
The target size for data and log files as calculated by DBCC SHRINKDATABASE can never be smaller than the minimum size of a file. The minimum size of a file is the size specified when the file was originally created, or the last explicit size set with a file size changing operation, such as DBCC SHRINKFILE. For example, if all the data and log files of mydb were specified to be 10 MB at the time the database was created, the minimum size of each file is 10 MB. DBCC SHRINKDATABASE cannot shrink any of these files to a size smaller than 10 MB. To shrink a file to a size smaller than its minimum size, use DBCC SHRINKFILE and specify the new size. Executing DBCC SHRINKFILE changes the minimum file size to the new size specified.
September 15, 2009 at 2:40 am
I have specified new values to shrink the file to , tried just taking 1Gb off, then 20GB then 200Gb but still no difference..
As I mentioned I have been doing this process again and again over the years with absolutely no problems, its just on this last attempt it will not shrink.. The last time I believe i ran this new release seed refresh it was under the 400GB size, could it be this now the db is 450GB???
** Oh and no the Production database has never been restored, the last restore activity was 18 months ago when we migrated from 2000 to 2005, but I have run this process of copying and shrinking many times successfully since then..
Any other ideas?
Oraculum
September 15, 2009 at 3:02 am
It would also appear that the updateusage did work and released the reserved pages correctly.. so I know that they are free..
DBCC UPDATEUSAGE: sysindexes row updated for table 'DOCUMENTS' (index 'DOCUMENTS_PK', partition 1):
USED pages (LOB Storage): changed from (4294943613) to (19388) pages.
RSVD pages (LOB Storage): changed from (0) to (38052) pages.
When you use the SSMS GUI to perform a shrink it tells me that the minimum size is 26,700MB which again is correct - it just wont let me shrink it!
Ok I have backed up this db, and restored to a new db. Tried the shrink again and now the shrink status for the sessions flips between suspended, runnable and rollback!.. still no joy!
Oraculum
September 16, 2009 at 8:46 am
How, exactly, are you doing the shrink? Are you using the gui or the tsql directly?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 16, 2009 at 9:45 am
I have used both tsql and gui and there is no difference in the result- gui just disapears after few mins, and in query analyser its just returns the file info with no change as if it has worked, but it hasnt!
sp_spaceused - returns
db dbsize unallocated
ss_fwaslive 402681.88 MB 374745.48 MB
Oraculum
September 16, 2009 at 2:20 pm
I'm going to take a shot at this.... I believe you need to rebuild all your indexes first, then shrink. The reason being is because SQL Server tables are actually IOT's (Indexed Organized Tables). Though you are deleting rows (or truncating) the leaf nodes are still there, just empty (or mostly empty).
Give it a try and let us know....
Tim White
September 17, 2009 at 2:45 am
hmmm I had started to script out all the indexes to drop them, shrink db then re-create... but i'll prob end up trying both as I still dont hold out any hope!
I will try the reindex first and let you know..
thanks
:o)
btw- I have not deleting or truncated rows for this problem table. On this table that held the 400GB, I have just emptied the Image data from a column which contained scanned documents..
This table had data size 400MB, Index Size 229MB and Text Size 354,000 MB
Oraculum
September 17, 2009 at 6:35 am
The rebuild index did not work, but ....some good news... 😀 I have dropped all FK's, Indexes and PKs from every table, ran an update usage and have started a shrink to 20,000 MB..
It has been running for 3 hours so far! so fingers crossed!
Thanks for all your help - I could not imagine that it was this that was holding onto unallocated space from the removed data of an Image column! :ermm:
Oraculum
September 17, 2009 at 7:30 am
Ok its worked! One i wont forget!
So all I have to do now is re-instate all the FK, PK and Indexes and Move the documents table back from an ndf back to the Primary filegroup!
Oraculum
September 17, 2009 at 11:30 pm
oraculum (9/17/2009)
Ok its worked! One i wont forget!So all I have to do now is re-instate all the FK, PK and Indexes and Move the documents table back from an ndf back to the Primary filegroup!
I am trying to figure out how exactly dropping the FKs, PKs and indexes worked and not the Index rebuild?
September 19, 2009 at 4:23 am
I would be surprised if it was the FKs that made the difference. I suspect it was the clustered keys primary or otherwise.
I have run into this problem on an older database where the file growth was set to 10%. The number of growth incrrements that occurred was the problem. We deliberately increase our database and log file sizes to 2x predicted size for 2 reasons.
1. We have a large single growth.
2. Our systems will never have downtime caused by filegrowth issues.
September 21, 2009 at 2:13 am
Hi Everyone!
Just thought I would update you, I tried a clean restore of the database and ran a rebuild index job then a shrink, and blow me down with a feather it worked 1st time!
Maybe all the other attempts to shrink the previous version made things worse!
Thank you 🙂
Oraculum
September 21, 2009 at 2:46 am
.
Oraculum
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply