October 10, 2005 at 10:05 am
I have a situation that appeared over the last week or so...
I have a job that backs up the transaction log of my database and ships the log off to the redundant server.
After the transaction log backs up, it should decrease in file size significantly. And it was doing this in the past.
For some reason today when the transaction log backs up, it still has a large transaction file.
Is there any way I can force it to shrink? What would prevent it from shrinking?
October 10, 2005 at 10:12 am
See books online about shrinking databases
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_462b.asp
look in books online for
dbcc shrinkdatabase
or
dbcc shrinkfile
October 10, 2005 at 1:52 pm
This is the transaction log file, not the actual database files.
I know the syntext to shrink a file. I need to know what would prevent the log file from shrinking?
October 10, 2005 at 3:06 pm
About the only thing I can think of is an open transaction.
shrinkfile pertains to the log also.
the shrinking of the log file usually only takes the "Free" contiguous pages at the end of the file.
Sometimes there are not many "Free" pages , so you need to run shrinkfile to force sql to reorganize the pages.
October 11, 2005 at 3:54 am
I recently had a similar experience where I had made a copy of an 8Gb database for a demonstration and needed to anonymise the data. After a heavy session of updates, I ended up with an 8Gb log file! This wouldn't shrink no matter what I tried, whether using dbcc shrinkfile or shrinkdb. I tried checkpointing, dumping the log but nothing worked. I even detached the database and reattached it to roll back any open transactions that I hadn't detected. Nothing worked.
I ended up by starting over with a fresh copy, and the problem didn't occur second time around!
Tony
October 11, 2005 at 5:40 am
I have also had isues with this. In a pinch I have backedup the database then chenge the database to simple recovery mode then back to full and it cleared the log. THis is not a recomended practise but I was on a test server and in a hurry.
Stacey W. A. Gregerson
October 11, 2005 at 5:59 am
I couldn't get it to shrink even after playing around with different settings of the recovery mode!
Tony
October 11, 2005 at 6:23 am
Hi,
try:
sp_helpfile
CHECKPOINT
--declare @cmd varchar(999) select @cmd= 'backup log '+DB_NAME()+' with truncate_only' exec (@cmd)
dbcc shrinkfile (DB_Log, 3072)
if that does not work uncomment the backup-line and try again.
if you need all transactions do a "backup with truncate" instead of a "backup with truncate_only"...
If that does not work you might use the script in
http://qa.sqlservercentral.com/scripts/contributions/26.asp
regards karl
Best regards
karl
October 11, 2005 at 7:29 am
I tried backup with truncate_only and backup with nolog (which I think does the same anyway) but neither helped!
Thanks for the suggestion though. I will go and checkout that script now.
Tony
October 11, 2005 at 11:15 am
Sometimes you might need to insert few dummy records in your DB before shrinking....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply