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
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