September 24, 2008 at 6:20 am
All,
A few months ago we had a problem with our backups and transaction logs which was fixed. We do a full backup daily and transaction logs every two hours and this is working fine.
However since the problem the ldf file has remained around 35GB and I can't work out why. I expected it to shrink back down after the first full backup because all the data is in the backup and therefore the transaction log is not needed? It's probably a simple answer but I've been through BOL and I can't work it out.
Can anyone advise the best way to fix this? I'm assuming I'm going to have to do some kind of truncate?
Thanks
Andrew
September 24, 2008 at 6:29 am
What is the recovery mode? If it is not simple put in simple recovery mode and check,
September 24, 2008 at 7:13 am
the backup will not shrink the log.
You will have to do this yourself - dbcc shrinkfile or use ssms.
make sure to leave space in the log for normal growth
September 24, 2008 at 7:16 am
Thank you for the reply. Sorry I should have stated that. It's in full mode. I assume your suggesting simple mode to check whether it's old transactions that were never backed up? Presumably by switching to simple and doing a backup that will remove any outstanding old transactions from the ldf file?
Thanks
Andrew
September 24, 2008 at 7:21 am
you can search books online for DBCC shrinkile or truncate log. For the time being use this which is very effective
USE DatabaseName
GO
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogNameFileName)
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 24, 2008 at 7:25 am
Andrew Smith (9/24/2008)
Thank you for the reply. Sorry I should have stated that. It's in full mode. I assume your suggesting simple mode to check whether it's old transactions that were never backed up? Presumably by switching to simple and doing a backup that will remove any outstanding old transactions from the ldf file?
Log backups backup and truncate all inactive entries in the transaction log. Don't switch to simple and back, it will break the log chain ans leave you unable to take more log backups until another full.
Backups don't shrink the file. They just remove backed up entries within the file leaving the space available for reuse.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2008 at 7:28 am
backup with truncate will break your restore chain - probably not what you should do?
September 24, 2008 at 7:36 am
The TRUNCATE_ONLY clause of the DUMP statement removes the inactive part of the log without making a backup copy of it. I am not sure if LSN's change unless you specify INIT and FORMAT in your statement.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 24, 2008 at 7:45 am
The NO_LOG and TRUNCATE_ONLY options break the backup chain. IF you run these, you need to do a full backup right away.
However, a log backup removes the inactive transactions, or marks that space as free, so really if you are doing log backups, you should be OK.
IF you really need to shrink the physical file, use DBCC SHRINKFILE
September 24, 2008 at 8:13 am
All,
"Backups don't shrink the file. They just remove backed up entries within the file leaving the space available for reuse. "
That's the bit I hadn't understood. Sorry for being dumb. I'll do some database shrinking through EM.
Thanks to all for your replies and advice.
Andrew
September 24, 2008 at 10:01 am
TRUNCATE_ONLY will be gone soon too
It is gone in SQL 2008
September 24, 2008 at 1:08 pm
Andrew Smith (9/24/2008)
That's the bit I hadn't understood. Sorry for being dumb. I'll do some database shrinking through EM.
Don't shrink the database. Shrink just the log file and shrink it to a reasonable size based on how the DB is used. If you shrink the data files, you'll be shuffling index pages causing severe index fragmentation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2008 at 2:24 pm
The_SQL_DBA (9/24/2008)
you can search books online for DBCC shrinkile or truncate log. For the time being use this which is very effectiveUSE DatabaseName
GO
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogNameFileName)
Please do not do this as this will break your log chain. If you do this, you will need to perform a full backup before you can run another transaction log backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2008 at 2:29 pm
All,
Thank you for your advice regarding breaking the log chain and shrinking the log file rather than the DB. I'd realised I would break the log chain if I truncated but the advice is very much appreciated. The log files are now more normal sizes and the problem is solved.
Thanks
Andrew
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply