January 4, 2009 at 12:06 pm
Hello all,
I run hourly T-log backups on a database and the .TRN files are usually around 1.5 GB. During the low traffic times between 2 AM and 6 AM in the morning the .TRN files are usually less than 1 GB, sometimes as low as 500 MB. Last night, the .TRN files that was created at 4 AM jumped up to 29 GBs. I started receiving failed T-log backup plan notifications and I noticed it was due to disk space usage. I deleted the log files, but at 10 AM when the job finally ran successfully, the .TRN was again 29 GBs in size. I deleted an old .TRN file that was 70 GBs, and the size of the .TRN files returned to about 1.5 GBs. My question is, how can I determine what caused the increase in the T-log file?
Any help is appreciated, thank you.
Peter
January 4, 2009 at 12:42 pm
It's very hard to tell why after the fact.
Were there any reindex jobs running during the period the log grew?
Having deleted tran log backups, you should do a full back as soon as possible. With log backups deleted, you won't be able to restore without data loss should the DB fail
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
January 4, 2009 at 1:03 pm
We take backups of our TRN files everyday. I did not delete the files that have not been backed up.
There has to be some way to read the TRN files to see what types of transactions were taking place.
January 4, 2009 at 1:23 pm
peter.chafin (1/4/2009)
There has to be some way to read the TRN files to see what types of transactions were taking place.
There are, but they are not cheap. SQL has undocumented functions that can read the active tran log, though it's not easy to understand. It's got no build in functions that I know of to read a backed up transaction log.
I believe Lumigent's Log Explorer and ApexSQL Log can read the trn files. Redgate's got a product too, but it's only for SQL 2000.
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
January 5, 2009 at 1:22 am
quest software has toad for sql server that has a log reader in it.
January 5, 2009 at 7:00 am
GilaMonster (1/4/2009)
Were there any reindex jobs running during the period the log grew?
You never answered Gail's question. In my experience, it has usually been a reindex operation that has caused unexpected log growth. The first place to start is to check jobs scheduled to run just before this log backup.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 10:58 am
You don't need a third party tool.
use DBNAME
SELECT * FROM ::fn_dblog(null, null)
GO
With some poking around in 'pre-emergency' time you should be able to figure out
what you're looking at. It's undocumented by Microsoft, but Google turns up some useful things.
~BOT
Craig Outcalt
January 5, 2009 at 11:13 am
SQLBOT (1/5/2009)
You don't need a third party tool.
use DBNAMESELECT * FROM ::fn_dblog(null, null)
GO
That only reads the active portion of the transaction log. It doesn't read a transaction log backup.
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
January 5, 2009 at 1:26 pm
Will a restore work for getting the backup into a readable active log?
~BOT
Craig Outcalt
January 5, 2009 at 1:45 pm
SQLBOT (1/5/2009)
Will a restore work for getting the backup into a readable active log?
No. I tried that once.
When SQL restores a log backup it replays the transactions recorded against the database. It doesn't re-log them.
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
January 5, 2009 at 1:47 pm
SQLBOT (1/5/2009)
Will a restore work for getting the backup into a readable active log?~BOT
Nope, a restore will restore the database and apply the changes stored in the transaction log backup. The existing transaction log will not be affected by the restore.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply