We have a database that is backed up every night (full backup). The database size is around 5GB and the transaciton log is about 3GB. And it's 3GB after the backup too. I thought the log size should shrink after each full backup. I can't imagine what data the log needs to maintain after a full backup is complete.
What can be done about this large transaction log?
TIA,
barkindog
you still have to explicitly
shrink file or shrink database after the fulldbbackup
The transaction log database file does not shrink after a full backup. It only marks the transactions as inactive. You need to:
Review your backup strategies if you intend to use transaction log backups. Then at the end of your backups, you can choose to shrink the database (or the transaction log file)
It's not quite clear if you mean the sizing of the physical file or the contents of the logfile.
In general, the size of the transaction file(s) should be left as is when you have 'normal' activities in your db. The log will grow to the size it needs in order to accomodate your largest transactions, and assuming no abnormal peaks, that's where the 'normal' size is for the given db. This assusumes a few things, though.
If the recocvery model is simple, then the log will be truncated from it's inactive parts at every checkpoint, and you just make full db backups at your appropriate interval. The log will be as large as it needs for the largest transaction, and this is where it likes to be.
If, on the other hand, your log has gradually grown, and is showing as almost 100% full, even after a full db backup, and keeps on growing...
Then the recovery model is probably 'full', and the only backups taken are full backups.
In order to truncate the log, and release the space from committed transactions, a full backup isn't enough. You have to do BACKUP LOG in order to 'clear' the log and keep it from ever growing larger and larger.
If this is the case and it's found out at a late time, then the logfile may be shrunk as a one-time operation to it's approximate 'proper' size.
/Kenneth
I was referring to the size of the log file on disk. For one database it is over 4GB! We do a nightly full backup of all our databases. Consider the database "Claims" which has a trans log file size of about 2.34GB. Here's what i did:
BACKUP
LOG ClaimsTO disk = 'D:\backups\ClaimsLogBackup.log'
Next I ran
DBCC
SHRINKFILE('Claims_Log')The final phsyical size of the log was still over 1.6GB!
I can't imagine what is in the log to account for 1.6GB. (Are these uncommitted transactions?) How can I tell if the trans log file can or cannot be shrunk further?
TIA,
Barkingdog
"If, on the other hand, your log has gradually grown, and is showing as almost 100% full, "
How can I tell how full a log file is?
Barkingdog
"You have to do BACKUP LOG in order to 'clear' the log and keep it from ever growing larger and larger.
"
I also noted that we do an hourly transaciton log backup. Yet the logs are still gigantic.
Barkingdog.
DBCC SQLPERF(logspace) will tell you how full your logs are.
By doing a log backup you'll lower the 'Log space used %' from the above DBCC command, however, it won't affect the actual size of the physical file.
Keep in mind that the 'correct' size for you is somewhere abouts the size needed to accomodate for your collected load of transactions between the longest interval between your log backups. (inlcuding night time if you have batches etc running off-hours) Sometimes 'gigantic' is what it takes =;o)
/Kenneth
Found huge log file just like you have had and tried many shrink routines (as found in first few comment lines).
Found that none worked except for the following script. Try at your own risk.
good luck.
--BACKUP LOG mydatabase WITH TRUNCATE_ONLY
--DBCC SHRINKFILE (mydatabase_log, 7, TRUNCATEONLY)
--DBCC SQLPERF (LOGSPACE)
--DBCC OPENTRAN (mydatabase)
--DBCC LOGINFO('mydatabase')
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [mydatabasefile] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'mydatabase_log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 45, -- Limit on time allowed to wrap log.
@NewSize = 300 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Same problem we saw - backups, truncation etc all failed
The SQL posted was the only thing that managed to drop a 45GB log file back down to 10MB !
An excelent post and another cherished nugget to help keep things ticking over.....
Mnn, just noticed the SQL above came from the MS KB "INF: How to Shrink the SQL Server 7.0 Transaction Log"
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
We're running into problem now as well, log is 12GB (with 0% free space) while db is only 10GB
Hi,
Ensure Autoshrink is not disable. also refer
Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=317375
How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/?kbid=873235
and threads:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/391579b8149281a4/b40ed8742075475d%23b40ed8742075475d?sa=X&oi=groupsr&start=1&num=3
http://groups.google.com/group/comp.databases.sybase/browse_thread/thread/4ad5e6818968385c/fac9ed81dd813cd9%23fac9ed81dd813cd9?sa=X&oi=groupsr&start=0&num=3
http://www.dbazine.com/sql/sql-articles/mullins-sqlserver
Hemantgiri S. Goswami
