Hi All,
Sometimes database backup triggered from MS SQL studio fails with below error
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.
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)
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
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
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’)