SQL Server 2012 - Transaction log growth

Question

SQL Server 2012 - Transaction log growth

s*1918

Ten Centuries

Points: 1298
More actions
February 20, 2015 at 2:10 pm

#301418

I have DB which is set to Full recovery model and looks like the current transaction log file size is 330GB. The transaction log file is growth is increasing every day. How do I overcome this situation.
Help please!
Thanks,

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

JeepHound SSCrazy Points: 2963 More actions · Answer 1

You can back up and Truncate your transaction log. https://technet.microsoft.com/en-us/library/ms189085%28v=sql.105%29.aspx

e4d4 SSCertifiable Points: 5777 More actions · Answer 2

Start doing log backups, in any recovery mode different than simple this is a must. However if you are doing log backups then post result from this command:

select name,log_reuse_wait_desc from sys.databases

s*1918 Ten Centuries Points: 1298 More actions · Answer 3

s*1918

Ten Centuries

Points: 1298

February 20, 2015 at 3:31 pm

#1779077

Ok, I will try that!

s*1918 Ten Centuries Points: 1298 More actions · Answer 4

I have something around 52 DB's and out of 52 DB's nearly 25 DB has log_resue_wait_desc = 'LOG_BACKUP'

e4d4 SSCertifiable Points: 5777 More actions · Answer 5

So you must start making logs backup, or switch databases in simple recovery mode, all this depend on yours backup strategy.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 6

Here, read this.[/url]

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

s*1918 (2/20/2015)
I have DB which is set to Full recovery model and looks like the current transaction log file size is 330GB. The transaction log file is growth is increasing every day. How do I overcome this situation.
Help please!
Thanks,

Grant provided an excellent link on the subject.

Based on your post, however, no one at your company knows much about backups and probably even less about restores. Not trying to be nasty here but, if a database goes corrupt, you're all dead meat and will lose any and all data from the last time you did a full backup until whenever "now" is. That's provided that you're even doing full backups.

With that in mind, take the time to read "Books Online" (the help system for SQL Server) and learn how to do log backups for "Point-in-Time" restores and read about the "Recovery Model". Then, sit down and make a plan. Not a backup plan but a restore plan. That will guide you as to how the backups should be done to minimize data loss within acceptable limits.

You also need a plan for if the room where your equipment is located burns to the ground. It's called a "BCP" or "Business Continuity Plan".

Seriously. You folks need to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bouke Bruinsma SSCrazy Points: 2185 More actions · Answer 8

First of all, make sure you create full backups asap. Big tranlogs are usually a sign of absence of full backups altogether. After this, design a backup strategy/BCP.

e4d4 SSCertifiable Points: 5777 More actions · Answer 9

Bouke Bruinsma (2/22/2015)
First of all, make sure you create full backups asap. Big tranlogs are usually a sign of absence of full backups altogether. After this, design a backup strategy/BCP.

Big tran logs are a sign of absence of log backups not full backups, usually people don't understand difference between recovery mode and db/log backup.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Bouke Bruinsma (2/22/2015)
First of all, make sure you create full backups asap. Big tranlogs are usually a sign of absence of full backups altogether.

Absolutely not. You can have a DB in full recovery with full backup every hour and you'll have a large log file. Full backups don't truncate the log, and hence a large log says nothing at all about whether there are full backups scheduled. What it does say is that there are probably no log backups.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Grant Fritchey (2/21/2015)
Here, read this.[/url]

And this[/url]

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bouke Bruinsma SSCrazy Points: 2185 More actions · Answer 12

GilaMonster (2/23/2015)
Bouke Bruinsma (2/22/2015)
First of all, make sure you create full backups asap. Big tranlogs are usually a sign of absence of full backups altogether.
Absolutely not. You can have a DB in full recovery with full backup every hour and you'll have a large log file. Full backups don't truncate the log, and hence a large log says nothing at all about whether there are full backups scheduled. What it does say is that there are probably no log backups.

I understand that large logs in principle do not mean absence of full backups but in my experience I have seen too often that large log files correlate strongly to full backups not being done at all or the last one was a long time ago. So if there hasn't been any backup recently, get one asap.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 13

Bouke Bruinsma (2/23/2015)
GilaMonster (2/23/2015)
Bouke Bruinsma (2/22/2015)
First of all, make sure you create full backups asap. Big tranlogs are usually a sign of absence of full backups altogether.
Absolutely not. You can have a DB in full recovery with full backup every hour and you'll have a large log file. Full backups don't truncate the log, and hence a large log says nothing at all about whether there are full backups scheduled. What it does say is that there are probably no log backups.
I understand that large logs in principle do not mean absence of full backups but in my experience I have seen too often that large log files correlate strongly to full backups not being done at all or the last one was a long time ago. So if there hasn't been any backup recently, get one asap.

It's possible that they weren't doing FULL as well as LOG backups, but Gail is dead on accurate. A large or full log file isn't caused by FULL backups being done or not being done. It's caused by the database being in Full Recovery and LOG backups being taken. It's been that way since at least 6.5.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bouke Bruinsma SSCrazy Points: 2185 More actions · Answer 14

I wouldn't dare questioning your or Gails remarks. But I could give many examples where a database is in full recovery mode without any backup strategy in place. The log file grows and grows in time then. We are in the position where we do quite a bit of audits of SQL Server instances where we follow a list of checks that are comprised of known best practices and issues we have encountered more than once. Database backup strategy is one of them. Sometimes the follow up to these situations is not that transaction log backups are taken but the recovery model is changed to simple, the transaction log is reset to an appropriate size and a daily full backup is automated. This is very typical for situations where there is no DBA present. We of course advice to have full recovery with regular transaction log backups. But sometimes the customer accepts the risk of maximum a day loss of work.