August 23, 2012 at 10:19 am
I have a very odd problem with my backups on a remote 2005 server. Some of the databases, user and system, backup just fine while others fail to backup and report the following error the SQL Server Log
BACKUP failed to complete the command BACKUP DATABASE <DB5>. Check the backup application log for detailed messages.
Error: 3041, Severity: 16, State: 1.
Another user database that was just before it produced this result:
Database backed up. Database: <DB4>, creation date(time): 2010/01/10(13:16:52), pages dumped: 16562, first LSN: 35296:477:37, last LSN: 35296:493:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\SQLBackups\<drive>\<db4>.bak'}). This is an informational message only. No user action is required.
The setup I am using is a centralized SQL server running a powershell command that runs a sproc from SQL Agent. The SQL Server Agent service account has a login to the remote server with sysadmin privileges as well as permissions on the E:\SQLBACKUPS directory (and sub directories).
The powershell command is:
Invoke-Sqlcmd -ServerInstance "<DATABASE>" -Query "EXEC MASTER.dbo.sp_BackupDBs;"
The stored procedure is:
ALTER PROCEDURE [dbo].[sp_BackupDBs]
AS
BEGIN
SET NOCOUNT ON;
BACKUP DATABASE master
TO DISK = 'E:\SQLBackups\<drive>\Master.bak'
WITH INIT, NOUNLOAD, NAME = 'Master Backup', NOSKIP, NOFORMAT;
BACKUP DATABASE MSDB
TO DISK = 'E:\SQLBackups\<drive>\MSDB.bak'
WITH INIT, NOUNLOAD, NAME = 'MSDB Backup', NOSKIP, NOFORMAT;
BACKUP DATABASE <db3>
TO DISK = 'E:\SQLBackups\<drive>\<db3>.bak'
WITH INIT, NOUNLOAD, NAME = '<db3> Backup', NOSKIP, NOFORMAT;
BACKUP DATABASE <db4>
TO DISK = 'E:\SQLBackups\<drive>\<db4>.bak'
WITH INIT, NOUNLOAD, NAME = '<db4> Backup', NOSKIP, NOFORMAT;
BACKUP DATABASE <db5>
TO DISK = 'E:\SQLBackups\<drive>\<db5>.bak'
WITH INIT, NOUNLOAD, NAME = '<db5> Backup', NOSKIP, NOFORMAT;
I have never had a problem with the system databases (master and MSDB) but the last 2 user databases <db4> and <db5> for some reason fail most of the time. If I run the sproc manually on the remote SQL Server everything runs fine and all databases are backed up successfully. All databases are in SIMPLE recovery model and the sizes are similar (60MB - 400MB).
I ran a profiler trace during the running and here are the relevant rows from the trace. Note that the backup for <db5> says successful.
RowNumberEventClassTextDataApplicationNameNTUserNameLoginNameCPUReadsWritesDurationClientProcessIDSPIDStartTimeEndTimeBinaryDataDatabaseIDDatabaseNameEventSequenceEventSubClassHostNameIndexIDIntegerDataIsSystemLoginSidNTDomainNameObjectIDObjectID2ObjectNameObjectTypeRequestIDServerNameSessionLoginNameTransactionIDXactSequenceColumnPermissionsDBUserNameNestLevelOwnerNameRoleNameSuccessTargetLoginNameTargetLoginSidTargetUserNameLineNumberBigintData1ErrorGUIDSeverityStateFileNameProviderNamePermissionsParentNameLinkedServerName
10115BACKUP DATABASE master TO DISK = 'E:\SQLBackups\<DB\Master.bak' WITH INIT, NOUNLOAD, NAME = 'Master Backup', NOSKIP, NOFORMAT; .Net SqlClient Data Provider<ServiceAccount><COMPANY>\<ServiceAccount>NULLNULLNULLNULL112245807:11.2NULLNULL1master8991511<CentralSQLServer>NULLNULLNULL0x0105000000000005150000009B0760794743096AEF5C8F5CC1860000<COMPANY>NULLNULLmaster169640<remoteserver><COMPANY>\<ServiceAccount>NULL0NULLdbo1dboNULL1NULLNULLNULL19NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
11115BACKUP DATABASE MSDB TO DISK = 'E:\SQLBackups\<DB\MSDB.bak' WITH INIT, NOUNLOAD, NAME = 'MSDB Backup', NOSKIP, NOFORMAT; .Net SqlClient Data Provider<ServiceAccount><COMPANY>\<ServiceAccount>NULLNULLNULLNULL112245807:11.9NULLNULL4msdb8991531<CentralSQLServer>NULLNULLNULL0x0105000000000005150000009B0760794743096AEF5C8F5CC1860000<COMPANY>NULLNULLmsdb169640<remoteserver><COMPANY>\<ServiceAccount>NULL0NULLdbo1dboNULL1NULLNULLNULL23NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
191115BACKUP DATABASE <DB3> TO DISK = 'E:\SQLBackups\<DB\<DB3>.bak' WITH INIT, NOUNLOAD, NAME = '<DB3 Backup', NOSKIP, NOFORMAT; .Net SqlClient Data Provider<ServiceAccount><COMPANY>\<ServiceAccount>NULLNULLNULLNULL112245807:13.8NULLNULL5<DB3>8993351<CentralSQLServer>NULLNULLNULL0x0105000000000005150000009B0760794743096AEF5C8F5CC1860000<COMPANY>NULLNULL<DB3>169640<remoteserver><COMPANY>\<ServiceAccount>NULL0NULLdbo1dboNULL1NULLNULLNULL27NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
194115BACKUP DATABASE <DB4> TO DISK = 'E:\SQLBackups\<DB\<DB4>.bak' WITH INIT, NOUNLOAD, NAME = '<DB4 Backup', NOSKIP, NOFORMAT; .Net SqlClient Data Provider<ServiceAccount><COMPANY>\<ServiceAccount>NULLNULLNULLNULL112245807:20.2NULLNULL6<DB4>8993431<CentralSQLServer>NULLNULLNULL0x0105000000000005150000009B0760794743096AEF5C8F5CC1860000<COMPANY>NULLNULL<DB4>169640<remoteserver><COMPANY>\<ServiceAccount>NULL0NULLdbo1dboNULL1NULLNULLNULL31NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
197115BACKUP DATABASE <DB5> TO DISK = 'E:\SQLBackups\<DB\<DB5>.bak' WITH INIT, NOUNLOAD, NAME = '<DB45 Backup', NOSKIP, NOFORMAT; .Net SqlClient Data Provider<ServiceAccount><COMPANY>\<ServiceAccount>NULLNULLNULLNULL112245807:33.8NULLNULL7<DB5>8993471<CentralSQLServer>NULLNULLNULL0x0105000000000005150000009B0760794743096AEF5C8F5CC1860000<COMPANY>NULLNULL<DB45>169640<remoteserver><COMPANY>\<ServiceAccount>NULL0NULLdbo1dboNULL1NULLNULLNULL35NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
Any ideas? What could be causing the problem? Is there another tool I can diagnose with?
P.S. This is a direct copy of my original thread over on the msdn social forums. I haven't gotten any replies there so I wanted to post here to see if there were any ideas.
September 6, 2012 at 12:13 am
When a backup fails to complete, 2 error messages are returned. The one you posted above is the 2nd error message that gets returned. You need to look in the log and see what the first error message was. If the other error is somethign that isn't getting logged, then you need to capture that error via manual reproduction or by capturing all output to a log file.
September 6, 2012 at 1:23 am
Robert,
I figured this out a couple days ago and hadn't updated my post. You are correct in that the first error message wasn't getting logged but it took me a long time to figure out how to see the error message. When SQL Agent is running the powershell command, it wasn't reporting the error. I tried running the command in Powershell ISE but I didn't get the error message at first and I am unsure why. Eventually I re-installed the SQL libraries for Powershell and I was able to get the error message. It was a timeout problem.
After searching the net for a bit I found that there is a bug with invoke-sqlcmd. If you don't specify a -querytimeout value it should be infinite however it is defaulting to 30 seconds. The bug submission can be found here: http://connect.microsoft.com/PowerShell/feedback/details/523996/invoke-sqlcmd-querytimeout.
That explains why it would sometimes fail on one of the databases but work the next time it ran. The query time was hovering around 30 seconds.
In order to fix the problem I have specified a timeout but made it sufficiently large enough to handle the backup lengths.
Invoke-Sqlcmd -ServerInstance "<DATABASE>" -Query "EXEC MASTER.dbo.sp_BackupDBs;" -querytimeout 9999
Thanks for the reply!
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply