May 8, 2006 at 9:41 am
Hi,
I have a transaction LOG file that has grown unexpectedly to 35gb...
I have changed to recovery model to SIMPLE from BULK-LOGGED...
I tried shrinking the file to 700MB by using Enterprise Manager and Also QA...
But the filesize is unaffected...The current size of the LOG file has created some issues already...
How can I shrink it...
Any help will be highly appreciated...
Thanks
Jol
Joel
May 8, 2006 at 9:54 am
Hi Joel,
First think you need to check if you have still some open transactions and close them.
If the situation gets really critical consider to restart the SQL Server service, that will close all open transactions. (Sql Server will make a rollback on startup).
Then Backup the log with truncate only example:
backup log pubs with truncate_only
and then try to shrink the file again with the shrinkfile command.
regards,
Holger
May 8, 2006 at 12:13 pm
Shrinking a database or file doesn't always happen immediately, especially the log file.
If you do truncate it, do a full backup as soon as you can. Truncating the log 'breaks' the backup chain, so that later tlog backups can not be applied to a full backup done prior to the truncate.
That all said, you need to figure out how the log got so full.
1. the db is in FULL Recovery mode and you aren't doing Full backups and log backups.
2. you had a huge transaction happen. Are you doing Reindexing? Or Index Defragging? Both will cause the log file to grow very large (1.5 times the size of the database).
-SQLBill
May 8, 2006 at 1:22 pm
check this out... i've used it before, and works great!
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=46806&enterthread=y
_________________________
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply