March 14, 2005 at 11:40 am
I have some production transactional databases running on SQL Server 2000. The transaction logs are growing bigger and bigger. It was created with an initial size of 300 MB. I have a daily full (nightly) and a daily(noon) transaction log back up set. I have the recovery model as FULL. The autoshrink option is not selected.
Is there a way I can shrink the transaction log as it has grown to 5GB? Is it possible to automate that? So that I don’t have to revisit this issue again and again in many databases?
Your help will be greatly appreciated.
Thanks,
Rajesh.
March 14, 2005 at 2:18 pm
Look at dbcc shrinkfile in BOL. If you do a search on transaction log shrink on this site a number of helpfull discussion threads appear.
Francis
March 14, 2005 at 2:50 pm
Here how I have mines automated...
DBCC SHRINKDATABASE (N'datbase', 0,TRUNCATEONLY) -- for SINGLE db
exec sp__msforeachdb "DBCC SHRINKDATABASE (N'?', 0,TRUNCATEONLY)" -- for ALL db on server
Please see BOL for future info about this command...
Hope this helps...Marsha
March 15, 2005 at 1:15 am
Do the shrinkfile as another step in your job after the full backup and the tran log backup.
March 15, 2005 at 1:45 am
Sorry, but i heard the first time from sp__msforeachdb.
I searched BOL but i cant find anything about this Stored procedure.
Kind Regards
Michael
March 15, 2005 at 2:34 am
not in the BOL ... but you can see sp_MSforeachdb in Master's Stored Procedure
March 15, 2005 at 11:46 am
Do that in 2 steps:
Backup LOG YourDB WITH NO_LOG
GO
DBCC SHRINKFILE ('YourDB_Log',TRUNCATEONLY)
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply