| 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 |
 |
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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
|
|
|
|
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"? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. USELESSA 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Dhudson29
Starting Member
23 Posts |
Posted - 2014-09-19 : 08:28:43
|
| Thanks for the tips. Will take the advice and read these articles. |
|
|
|
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. |
|
|
|
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 masterGOSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMBFROM sys.master_filesWHERE DB_NAME(database_id) = 'DatabaseNameGoesHere' AND type_desc = 'LOG'GOTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Log Backups Failing