Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 Log Backups Failing

Author  Topic 

Dhudson29
Starting Member

23 Posts

Posted - 2014-09-18 : 07:51:24
My Log Backups fail from time to time with the following error:
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023). The step failed.

It is a 3-step process: (1) Delete Old Files (2) Backup Transaction Logs (3) Shrinkfile for Logs Only. It keeps failing on the 3rd Step. This seems to happen weekly. Any ideas??

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-18 : 12:23:36
Do you have a Full or Differential backup in process when this occurs?



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-09-18 : 14:08:47
Stop shrinking the log files - let the files grow to the expected size and then leave them alone.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-18 : 14:09:31
Why are you shrinking the log? Remove that step as you are creating massive fragmentation and a performance problem when it needs to expand out again. Log files can't be expanded using instant file initialization.

You should never shrink database files as part of a job. Shrinks should be done manually and for very specific reasons, such as a huge amount of data being deleted and not needing the space for a very long time.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Dhudson29
Starting Member

23 Posts

Posted - 2014-09-18 : 15:14:43
quote:
Originally posted by Bustaz Kool

Do you have a Full or Differential backup in process when this occurs?



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Go to Top of Page

Dhudson29
Starting Member

23 Posts

Posted - 2014-09-18 : 15:25:20
I'm a fairly new SQL DBA. I just took over this position. I see on most of their backup jobs, they are deleting old jobs, backing up, and then doing a Shrinkfile.

There is not full or diff backup running at this time.

So you believe (tkizer) the constant shrinking is "creating massive fragmentation and a performance problem"?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-18 : 17:23:55
quote:
Originally posted by Dhudson29


So you believe (tkizer) the constant shrinking is "creating massive fragmentation and a performance problem"?


Yes. For sure.

Start reading all the articles by Paul Randal on this subject: http://www.sqlskills.com/blogs/paul/category/shrink/

And if you are a DBA, read all of his articles on every subject. Plus Kimberly Tripp's. Subscribe to their RSS feeds. Check out Brent Ozar's stuff too. There are many others that you need to read also.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-18 : 17:26:14
quote:
Originally posted by Dhudson29


There is not full or diff backup running at this time.



You need to get a full backup schedule ASAP. Unless you have a full backup and the ENTIRE transaction log chain up to the point you need to recover to, your log backups are useless. USELESS

A diff backup is a luxury at this point. Get the full backup scheduled now. Don't wait. It's the difference between keeping your DBA job and being fired.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Dhudson29
Starting Member

23 Posts

Posted - 2014-09-19 : 08:28:43
Thanks for the tips. Will take the advice and read these articles.
Go to Top of Page

Dhudson29
Starting Member

23 Posts

Posted - 2014-09-29 : 14:03:49
First I know the advice about shrinking the database. Point well taken. But business insist. So here is my new question. There is a script that runs the dbcc shrinkfile command for each log file. Question: Is it possible to insert a statement in the query to tell me the size of the log before it runs the shrinkfile command? I've determined why the jobs failed intermittently, but just wondering if I can find out the size of the log before it runs the dbcc command.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-29 : 14:10:33
Here you go, but I would instead educate the business on this topic. Performance is typically a very important thing on a system, and the autogrow event for a log file is a huge performance issue. Test it to see what I mean and then show the business the results.

USE master
GO
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DatabaseNameGoesHere' AND type_desc = 'LOG'
GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -