SQL Server Performance Forum – Threads Archive

Database disappeared from Enterprise Manager

The 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
[email protected]

"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
EM .
>check SQLserver log and see if the databas was up after reserver restart
Or just find out any error in that log . -Rajiv

Hi, * To stop SQL Server remotely SHUTDOWN [ WITH NOWAIT ] net stop mssqlserver or via Remote Administration software / Terminal Service or read * SHUTDOWN * for methods to stop sql server ERROR 5123 Indicates if * <blockquote 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"></blockquote id="quote"> <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? quote] * Untill your database is dettach (via EM or sp_detach_db ) it is their . Check your SQL and Windows Event Log to find what was happened ?! if this happens to you really you have to be carefull , 1). Strong Password Mechanism 2). set NTFS Permission 3). Create Role with Appropriate Permissions 4). Defined TCP/IP port otherthan default 5). Set Audit On A Must Read Article to achive this : 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> 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> HTH [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Alongwith above points also confirm the service pack level on SQL server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>