Who is accessing my backup file ?

I am writing this post single handedly, quite literally. I broke my left wrist 🙁  and the Doc has wrapped it for the next 3 weeks! May be it is an indication for me to take a break, but I want to have it my way. So here I am typing these words using the right hand which is shaking my body as I type !

Enough of non-sense Pradeep. Today’s post is based on what one of my team member had encountered this Wednesday.

Here is the story. One of the daily backups failed with the below.

**************

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘M:db_3.bak’.
Device error or device off-line. See the SQL Server error log for more details

**************

The DBA at work was sure that the backup job had failed since some other application was accessing that file. How he checked it? He tried to rename the file in Windows Explorer and it returned this error.

Since the Backup team does a nightly backup of all the backups to tape, he reached out to them to check if any of the Backup processes was accessing that file. May be the Backup guy was too tired he blindly replied “It wasn’t me” !

Now the DBA wanted to complete the Database backup but some process was not allowing him to do so. Who will help the DBA now? The cool sysinternals tool process explorer came to the rescue. I asked the DBA to open Process Explorer binary on that server and click on Find Handle or DLL (Ctrl + F). All he had to do was to enter the backup filename in question and hit Search. Here was the output.

Now it was clear that the UnivAgent.exe was using that file and process indeed belonged to the Backup team. The DBA informed the same to the Backup team and after a while they said “Please check now” !