SQL Transaction Log Filling
-
after running dbcc shrinkfiles and dbcc shrinkdatabase log files (gig) size still the same. can someone give me the right step how to perform or maintain growing log files
thank you
-
is the database in simple or full recovery mode ? if it's full then you need to set up a transaction log backup job.
then when the t-log has been trucated you can free up the space.
MVDBA
-
Try this one;
backup transaction with_truncate_only
-
This is a reply of Jeremy (Member of SQL Magazine Forum) whuch works best with me when I want to shrink a DB (I suugest you to Make Log Backup first):
SQL Server sometimes has a problem with shrinking the TLog. If you are not backing up the transaction log as a part of your data recovery plan, I recommend having truncate log on checkpoint enabled. This will help ease further issues.
For unruly database transaction logs, I use the following methodology:
1. First try to shrink the tlog using DBCC Shrinkfile (tlog_filename). After this has completed, run DBCC loginfo on the database of interest. If the last record returned has a status = 2 (active) and there are several entries before it with a status = 0 (inactive) you will need to run some transactions to push the active entry to the beginning of the log (step 2).
2. Run the following script
USE your database_name
GO
CREATE TABLE log_shrinker (char1 char(4000))
GO
DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT log_shrinker VALUES ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE log_shrinker
BACKUP LOG database_name WITH TRUNCATE_ONLY
END
This is a script which contains an infinite loop, which will need to be stopped manually. It will push the active entry to the beginning of the log so that the rest of the log can be truncated. I recommend running it for a minimum of 40 minutes for an 8GM Tlog and then stop it manually.
3. Try running BACKUP LOG database_name WITH TRUNCATE_ONLY
4. Now run DBCC Shrinkfile (tlog_filename) again, if the tlog hasn't already shrunk
5. If the Tlog is still huge, run the script in step (2) again. This should do the trick.
Note: This is a manual process which requires you to stop the infinite loop query. I have used it many times to shrink both live(production) databases as well as development databases which have grown too large. Sometimes the script in step (2) needs to be run for a longer time. You can periodically check the tlog using DBCC loginfo to ensure that the active entry in the log is moving to the beginning of the log file.
Hope this helps!
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply