June 2, 2013 at 11:12 pm
The sequence of events went like this.
Transaction log disk ran out of space. I was able to add space to the drive. I then manually increased the space of the database log file that ran out of space through SSMS. That was successful. The file increased on the previously full disk.
I tried to run my transaction backup jobs but backup files were never written. I canceled this job.
I had previously canceled a scheduled transaction log backup job that was running since the disk filled up.
I tried to run a manual backup on a small database bit am getting the below error.
Msg 3023, Level 16, State 2, Line 11
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.
My server log file was huge, crashing SSMS or any text editor. I ran sp_cycle_errorlog and created a new log.
All I see now is: BACKUP failed to complete the command BACKUP DATABASE dba. Check the backup application log for detailed messages.
Any suggestions?
June 2, 2013 at 11:29 pm
Also the first occurrence of the 'Backup, file manipulation operations..' error was when the blasted maintenance plan attempted its weekly shrink.
Source: Shrink Database Task Executing query "USE [DATABASE1] ".: 50% complete End Progress Error: 2013-06-02 14:05:04.77 Code: 0xC002F210 Source: Shrink Database Task Execute SQL Task Description: Executing the query "DBCC SHRINKDATABASE(N'DATABASE1', 10, TRUNCATEONLY) ..." failed 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. DBCC SHRINKDATABASE: File ID 1 of database ID 7 was skipped because the file does not have enough free space to reclaim.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Progress: 2013-06-02 14:05:04.82 Source: Shrink Database Task Executing query "USE [Full_Database] ".: 50% complete End Progress Error: 2013-06-02 14:05:08.82 Code: 0xC002F210 Source: Shrink Database Task Execute SQL Task Description: Executing the query "DBCC SHRINKDATABASE(N'Full_Database', 10, TRUNCATEONL..." failed with the following error: "The transaction log for database 'Full_Database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
June 3, 2013 at 12:11 am
Restated the instance but the restart failed
32(The process cannot access the file because it is being used by another process.)
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
Restarted the server and I seem to be in business.
I am using hyperbac and this post seems to indicate a similar problem.
http://www.sqlservercentral.com/Forums/Topic9165-5-1.aspx
Sorry to keep answering my own post but maybe this will help someone.
June 3, 2013 at 4:01 am
Are you dumping log backups on the same disk that contains the actual log files?
If not, then why would you cancel the log backup? Allowing it to run might actually have freed up some space within your log files and allowed your DBs to operate normally in spite of the full disk. (SQL Server doesn't care if the disk is full as long as there is free space within already allocated DB files.)
It looks as though your backup originally failed either because there was already another backup running or because a file manipulation operation (add file, grow, shrink, etc.) was in progress at the time. That's really all I can say based on your original post, since you did not include any details on your backup software or log entries thereof.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
June 3, 2013 at 4:06 am
Chrissy321 (6/2/2013)
Also the first occurrence of the 'Backup, file manipulation operations..' error was when the blasted maintenance plan attempted its weekly shrink.
Yikes! Running weekly shrink as part of a maintenance plan is a TERRIBLE practice - This is highly destructive to the overall performance of your SQL Server!!
Why is this being done?
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
June 3, 2013 at 5:29 am
When using a third party backup application, an external process from this third party could lock files and/or processes. When cancelling a backup job within SQL, you'll need to manually cancel the external (to SQL) process also. Sometimes this has to be done by restarting the third party service.
If you query SYS.DATABASES you can see in column [log_reuse_waint_desc] what is preventing the log from releasing it's space (and why it could be growing).
And I agree with Vegard Hagen: it's not good practice to shrink a database!!
June 3, 2013 at 8:59 am
I agree on the database shrinking but I with limited DBA resources available the standard SQL server maintenance plans are in effect.
June 3, 2013 at 9:57 am
Well, I strongly suggest you reconsider this "standard" practice.
In many cases, having plans that involve weekly shrinks is inferior to not having DB maintenance plans at all.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
June 3, 2013 at 10:29 am
IIRC, you can configure the maintenance plans so that they don't shrink the database or log files.
It has been so long since I used them I can't actually remember.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply