1

I've got this issue which has me baffled at the moment.

When I came in this morning there were 2 instances on a server with 4 SQL Server instances who had a backupthread process which got stuck when I killed this SQL Server process this stayed in the killed/rollback state. When trying to kill the process which was the culprit this showed to be a process which was non-existing. So after some googling and consulting with co workers we thought it best to restart the SQL Server instance.

enter image description here

So far so "good".

When stopping and then starting the SQL Server service again, the service refused to start. with the following error: The process cannot access a file because it used by another process.

enter image description here

And it was using the master.mdf file.

When checking which process was using the file it was the SQLservr.exe process which was using the file. (Using process explorer https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx)

When I looked over to services, the service of the instance was successfully stopped. The Process however was still running according to task manager and process explorer although it was not using any resources.

When trying to kill this process ID it gave me an access denied error, when trying to kill it through command line with using taskkill /pid XXXX /F it still gives me access denied error, even when running as administrator with local admin and domain admin account.

The only shortterm solution was rebooting the Server, this solved the issue. Until I tried to restart the service again. When we got the same problem again.

EDIT: They rebooted again without my knowledge, now the issue cannot be recreated

SQL Server is patched until the latest version, however the Windows server has not been patched since 2013.

We are running on SQL Server 2012 and Windows server 2012

Is this a known issue with windows server? Is this a SQL Server issue? Any guidance or information would be helpful.

Improve this question
6
  • try to start sql server with single user mode. You can find How to do it here
    – Racer SQL
    Commented Dec 19, 2016 at 10:55
  • @RafaelPiccinelli Apparantly they just rebooted the server again without my knowledge, and now the problem can't be recreated, so I cannot test that right now. However since this instance came up and running again I guess i'm not facing any master file corruption
    – Stijn Wynants
    Commented Dec 19, 2016 at 11:10
  • @Stijn Wynants, Did you check from your side may be the issue like that 1)The service encountered a login failure(service account password invalid or account locked or disabled). 2)Either the defined location for the error log files is not there, or the SQL account does not have permission on it.
    – Md Haidar Ali Khan
    Commented Dec 19, 2016 at 11:19
  • @MdHaidarAliKhan Checked for the failed logins/locked/disabled, this is not the case. Also the startup parameters are there, and because the instance starts when reboot happened I think it is not an issue of SQL account permissions/missing errorlog files.
    – Stijn Wynants
    Commented Dec 19, 2016 at 11:22
  • @StijnWynants,which account you are login into sql server?If you are running other account then try to run sql server through Log On As (Account).
    – Md Haidar Ali Khan
    Commented Dec 19, 2016 at 11:30

2 Answers 2

Reset to default
1

I have found the issue, it was something called a ghost process.

If you want then use the extra tool to kill the process SQL Server will start.

more information on http://amorvita.github.io/2013/08/finding-a-zombie-process/

Improve this answer
-1

Check the backup target location isn't on a windows compressed volume ! I've seen that causing backups to get stuck.

Improve this answer
3
  • Yeah the issue of the backup was due to some network hickups, these got fixed. However this did not explain the issue of the master.mdf file in use.
    – Stijn Wynants
    Commented Dec 19, 2016 at 12:59
  • @Stijn Wynants, You are accessing the (mater.mdf) file on the same location. which you have got error message related to location of (master.mdf) file.
    – Md Haidar Ali Khan
    Commented Dec 19, 2016 at 13:05
  • @MdHaidarAliKhan Indeed, It is the same location
    – Stijn Wynants
    Commented Dec 19, 2016 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.