Truncating transaction log

Question

Truncating transaction log

Ryan007

SSCrazy Eights

Points: 8275
More actions
July 14, 2011 at 2:26 am

#240810

I have created one job to truncate the transaction log of an user database which grows huge in a week. Traking transaction log backup every 2 hour does not solve the problem. The query being executed from the job is given below.
USE AdventureWorksLT
GO
DBCC SHRINKFILE(AdventureWorksLT_log, 100)
GO
BACKUP LOG AdventureWorksLT WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(AdventureWorksLT_log, 100)
GO
When I run the job manually from SSMS it runs successfully. But fails when it runs automatically as per the schedule. The error message from the job history is given below.
Executed as user: NT AUTHORITY\SYSTEM. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Any idea about the reason behind it?
Ryan
//All our dreams can come true, if we have the courage to pursue them//

Viewing 10 posts - 1 through 9 (of 9 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Could it be that the scheduled job ran when you were already running a backup on the log? In any case I have to admit that I don’t understand why you want to shrink the log in the first place. According to your description the log gets to its size because of the normal work on the database. This means that the log grows each time, then you shrink it and it grows again, so why shrink it? Let it use the amount of space that it needs without the ritual of shrinking it and then let it expend.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

That's terrible log mismanagement. You're breaking the log chain, you will not be able to do log backups, so no point in time recovery until, you take another full backup.

That is in no way a good solution to manage logs. Either switch to simple recovery completely (so no log backups, no point in time recovery) or run log backups regularly enough to keep the log the size you want. If it grows further analyse why, don't just truncate it (that will only help if the reason for the log growth was that the log backups were too infrequent)

Please read through this - Managing Transaction Logs[/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

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 3

Hi Gail,

I followed the same as described in your article.

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

We are taking log backup every two hour successfully. We can not take it to simple recovery mode because user wants point in time recovery. The log grows huge in one week and performance decreases. Thats why I have created the job just before full backup every weekend. So log chain is not interrupted.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

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

Seriously, it's still bad management. Increase the frequency of log backups or identify the reason why the log is growing.

Large log files don't cause bad performance. A badly configured one (from repeated shrinks and grows) can slow backups, DB recovery and similar, but still won't affect regular operation.

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

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 5

Try to change it like that:

USE AdventureWorksLT

GO

BACKUP LOG AdventureWorksLT WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE(AdventureWorksLT_log, 100)

GO

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 6

Thanks adi. Actually truncating log and log backup was taking place at the same time. Now it is running fine.

Hi Gail,

You are right. I am trying to figure it out what activities cause log files to grow huge. But sometimes we get incidents for the performance issue, once we truncate the transaction log database responds normally and user dont face any performance issue.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

TRUNCATE ONLy is no longer supported in sql 2008 btw

Jayanth Kurup[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

GilaMonster (7/14/2011)
so no point in time recovery until, you take another full backup.

or a differential 😉

If the 2 hourly backups are not managing the log sufficiently increase it to hourly or every 15 mins

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 9

This post will give you the clear picture.

http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/