SQL Server Performance Forum – Threads Archive
Database disappeared from Enterprise ManagerThe other night, we experienced a series of crashes on one of our SQL Servers. The cause has so far gone unexplained. The SQL Service stopped on it’s own a number of times. The interesting thing is that in the error log there is a message indicating that the service was shutting down due to a request from the service control manager…weird. This happened about 6 times and then stabilized. We have since rebooted the server and have not had any similar issues since…until this morning when a database was reported as missing. One of our databases it not showing up in the EM tree but when I look for the files they are still on the drive. I tried to attatch them and got this message: Error 5123: CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘the name of my database file’. Is it possible that SQL Server still has a lock on the .mdf file for my database even though it doesn’t show in EM? Is there a way to check this and to free it up? Any help would be appreciated. Thanks, Chris
"The interesting thing is that in the error log there is a message indicating that the service was shutting down due to a request from the service control manager" >check event viewer and findout from which computer the request for shutdown came from.Make sure it ws not by somone else remotly >Try from Query analyzer to see if the DB is there.If yes, just refresh the
>check SQLserver log and see if the databas was up after reserver restart
Or just find out any error in that log . -Rajiv
Hi,<br />* To stop SQL Server remotely <br />SHUTDOWN [ WITH NOWAIT ] <br />net stop mssqlserver <br />or via Remote Administration software / Terminal Service<br />or read * <b>SHUTDOWN</b> * for methods to stop sql server <br /><br />ERROR 5123 Indicates if <br />* <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">CREATE FILE encountered operating system error %ls while attempting to open or create the physical file ‘%.*ls’.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><blockquote>Is it possible that SQL Server still has a lock on the .mdf file for my database even though it doesn’t show in EM? Is there a way to check this and to free it up?<br />quote]<br /><br />* Untill your database is dettach (via EM or sp_detach_db ) it is their .<br /><br />Check your SQL and Windows Event Log to find what was happened ?!<br />if this happens to you really you have to be carefull ,<br /><br />1). Strong Password Mechanism<br />2). set NTFS Permission<br />3). Create Role with Appropriate Permissions<br />4). Defined TCP/IP port otherthan default <br />5). Set Audit On<br /><br />A Must Read Article to achive this :<br /><br />1). <a href=’http://vyaskn.tripod.com/sql_server_security_best_practices.htm’ target=’_blank’ title=’http://vyaskn.tripod.com/sql_server_security_best_practices.htm'<a target="_blank" href=http://vyaskn.tripod.com/sql_server_security_best_practices.htm>http://vyaskn.tripod.com/sql_server_security_best_practices.htm</a></a><br />2). <a href=’msdn.microsoft.com/library/en-us/adminsql/ad_config_09yd.asp’ target=’_blank’ title=’msdn.microsoft.com/library/en-us/adminsql/ad_config_09yd.asp’>msdn.microsoft.com/library/en-us/adminsql/ad_config_09yd.asp</a> <br /><br /><br />HTH<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
Alongwith above points also confirm the service pack level on SQL server. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.