Skip to Content

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. sivaprasad kandukuri

    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.

            

    Ur’s

    siva reddy

    (0) 
    1. 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

      SELECT

      result.session_id,

      result.command,

      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]

      ,

      CONVERT(VARCHAR(100),

        (

         SELECT SUBSTRING(text,result.statement_start_offset/2,

         CASE

         WHEN result.statement_end_offset = -1 THEN 1000

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

         END)

         FROM sys.dm_exec_sql_text(sql_handle)

        )

      )

      FROM sys.dm_exec_requests result

      WHERE command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’,’RESTORE LOG’,’BACKUP LOG’)

      (0) 

Leave a Reply