September 30, 2004 at 3:23 pm
Today I ran a program that deleted loads of data from a large sql2k database that I administer. Afterwards I ran the shrinkdatabase and shrinkfile utilities in an attempt to reduce the size of the database. It is currently about 50g.
We use the simple transaction log method. The partition has plenty of room.
Does anyone have a clue?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 30, 2004 at 10:33 pm
Did you use shrinkfile (file_name,size,truncateonly) then shrinkfile (file_name,size,notruncate)?
It happened to me once. I think it's waiting for the next checkpoint to occur before it does the shrink. Either try backing up the DB first then try again. Or manually back up the transaction log, which triggers a checkpoint then try again.
October 1, 2004 at 2:46 am
Yeah, I agree with Grasshopper,
I usually run the following:
DBCC SHRINKFILE(DBLOG)
BACKUP LOG DB_LOG
WITH TRUNCATE_ONLY
THEN DBCC SHRINKFILE(DBLOG) and his does the job
If you have replication running you may have to run sp_repldone with defaults on the db in question. (This will also prevent size reduction)
Graeme
October 1, 2004 at 6:24 am
There are times this doesn't work because of the virtual log files. If you can't get it to shrink, try this script:
http://www.sqlservercentral.com/scripts/contributions/26.asp
October 1, 2004 at 12:09 pm
I think he is asking not about the log file, but about a database files shrinking after deleting data from the database. You can not shrink the data ase below the original size by shrinkdatabase. You can do by shrinkfile. Please, post your statements and sized of a database and log files.
Yelena
Regards,Yelena Varsha
October 1, 2004 at 2:33 pm
I ran Graeme's routine and followed it by a shrinkfile command on the data file. It worked! The routine freed up about 10G. Thanks for all the great advice!
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply