| Author |
Topic |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2012-02-23 : 17:22:50
|
| Good afternon:Montly there is database backup and was running OK, but since aproximately 3 months ago whenever there is a database backup, the transaction log gets full, and therefore sql server running processes start failing.Its likely the database backup "eats up" the transaction log.The DB backup last aproximately 20 hours.The bottle line is when it occurs, we'got to call our DBA to free up the transation log, but he cannot do anything until the db backup finishes.Is it premature to figure out what the problem is ? and Is there any Hint or measure to avoid that the transaction logs gets full and the ongoing process starts failing ? Id appreciate your help |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-23 : 17:36:18
|
| It's not that the backup 'eats' the transaction log, it's that the log cannot be cleared or reused until the backup is complete. This is because the full backup needs to read over the log at the end.It's not a problem, it's expected behaviour and you'll need to make sure that the log file is large enough to hold all the transactions that occur during a full backup. Other options could be to back up at a quieter time (less transactions, so less log space) or to consider ways to backup faster - 3rd party products that compress the backup, backup compression in SQL 2008, backing up to multiple devices, increasing throughput/speed of the drives that the backup is written to, etc.--Gail ShawSQL Server MVP |
 |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2012-02-24 : 11:46:05
|
| OK, Thank you very much for your help and support and ive got to take to into account these recommendations, however, and it comes up a doubt, because Im not a DBA but where and how can i make sure that log file is large enough to hold all the transactions ? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-24 : 12:38:13
|
| Test, see how large it gets, then add some extra space as overhead.--Gail ShawSQL Server MVP |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-02-24 : 14:29:25
|
| 20 hours is a very long time for a backup to run.How large is the database? Are you backing up to a local drive on the SQL Server or over the network to a file share? What version and Edition of SQL Server are you using?CODO ERGO SUM |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-24 : 16:25:08
|
| I'll make a guess that you aren't doing transaction log backups either. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-24 : 16:53:06
|
| Log backups won't help while a full backup is running, the log can't be marked reusable during a full backup, the log 'clearing' that a transaction log backup does gets deferred to the end of the full backup.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-24 : 18:54:54
|
Right, but I'll still bet they are in Full, and not taking 'em  |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2012-02-25 : 19:43:54
|
| Good evening Answering yoor questions:1 Management STudio says the database size is:DB SIZE 6187673.06 MBTransaction log Size 146 GBInitial Size 144 GB2 every day at nights we backup the transaction logs with truncate only3 the datbase backup is stored in tapes by means of some libraries or called "robots"the specific eror that yields is: Error string: The transaction log for database 'TRAFICO1' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasesId appreciate your help |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-26 : 02:45:06
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url] (or ask your 'DBA' to read it)If all you're doing is truncating the log nightly then it's clear you don't care about point-in-time recovery and should just switch the database to simple recovery model. The log bay still grow during full backups as the log cannot be marked reusable until the backup completes, but you won't have to worry about manually truncating the log as that happens automatically in simple recovery.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-26 : 10:18:45
|
quote:
Originally posted by russell
Right, but I'll still bet they are in Full, and not taking 'em
What do I win?  |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-26 : 11:32:24
|
| You don't. They do have a scheduled BACKUP LOG. You didn't specify in the bet that the backup wrote a file out.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-26 : 11:35:59
|
LOL. Good point  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-27 : 16:57:37
|
quote:
Originally posted by GilaMonster
Log backups won't help while a full backup is running, the log can't be marked reusable during a full backup, the log 'clearing' that a transaction log backup does gets deferred to the end of the full backup.
We take a LOG backup right before the FULL backup, to reduce this risk [that the LDF fills up]. That's a reasonable idea, right? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-27 : 17:06:29
|
| Yes and no. It won't stop the log from filling up during a full backup because from the point the full backup starts to the point it completes, the log cannot be cleared, so if your log file is 1GB and the log records generated while the backup is running > 1GB, your log fills up.--Gail ShawSQL Server MVP |
|
|
|
|
Transaction log gets full in a database backup