Skip to Content
Author's profile photo Former Member

Database backup error : System.Data.SqlClient.SqlError

Hi All,

Sometimes database backup triggered from MS SQL studio fails with below error

7-4-2012 10-55-58 PM.jpg

This document discusses how to identify those sessions which are not allowing you to access this backup file.

So let us start.

Go to MS SQL studio and open the Window to run a query and run the below query,

SELECT session_id, TEXT

FROM sys.dm_exec_requests

cross apply sys.dm_exec_sql_text(sql_handle)

Now execute this query

This give you the results which contain the sessions in the results section that are currently blocking our current request for DB backup.

7-5-2012 12-20-15 AM.jpg

Now you can understand if this is an critical task or a blocking task and decide to wait till its completion or kill the session.

To kill the session use the query “KILL <SESSION_ID>” (see below screenshot)

7-5-2012 12-22-40 AM.jpg

To confirm if this session was killed or not you can run the above query again.

And now you can trigger the backup again and this time its runs successfully.

Hope this document helps you.

Note : Screens shown in this document are for Microsoft SQL Server 2008 R2 – 10.50.1804.0 version

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Pradeep,

      I got many times the same error which you posted ,

      Ans : 1 There is no enough space .

               2 Storage location is not in network.

               3 Same time some other task to worked like deleting old files, or moving etc.



      siva reddy

      Author's profile photo Dennis Lee
      Dennis Lee

      Hi Pradeep,

        Based on my experience and according to your error message: " backup ... must be serialized" "after current backup is completed", another backup task is currently running. You must wait and submit your backup after the current backup db or log is finished.

      try TSQL below to check out SPID:

      USE master




      CONVERT(NUMERIC(6,2),result.percent_complete) AS [Percent Completed],

      CONVERT(VARCHAR(20),DATEADD(ms,result.estimated_completion_time,GetDate()),20) AS [Estimated Completion Time],

      -- CONVERT(NUMERIC(6,2),result.total_elapsed_time/1000.0/60.0) AS [Elapsed Minute],

      CONVERT(NUMERIC(6,2),result.estimated_completion_time/1000.0/60.0) AS [Estimated Minutes],

      CONVERT(NUMERIC(6,2),result.estimated_completion_time/1000.0/60.0/60.0) AS [Estimated Hours]




         SELECT SUBSTRING(text,result.statement_start_offset/2,


         WHEN result.statement_end_offset = -1 THEN 1000

         ELSE (result.statement_end_offset-result.statement_start_offset)/2


         FROM sys.dm_exec_sql_text(sql_handle)



      FROM sys.dm_exec_requests result