Let’s say…one of your DB log file got too large and u need to make some space in your hard disk very quickly. So, u like to delete a .ldf file/log file of a database of MS SQL server. First, you need to close any SQL server client tool that is running (i.e. SQL Server Management Studio), then go to “control panel -> Administratice tools -> Services. Then stop SQL Server. Now u can go to the data directory of your SQL server (default directory is C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA) and delete the desired .ldf file.
Now, if u restart the stopped service and start SQL server management studio, you will find that the affected DB is showing up in the list, but u cannot browse it or make any query against it. What you have to do now is,
SELECT DATABASEPROPERTY(‘your_db_name’, ‘isShutdown’) –if this returns 1, proceed to next steps
ALTER DATABASE your_db_name SET OFFLINE
Now, your affected database will be up and running again with a new log file.
4 thoughts on “Deleted database log(.ldf) file of SQL Server? Need to Restore database?”
I tried this USE master
SELECT DATABASEPROPERTY(‘your_db_name’, ‘isShutdown’) but i ma getting result as NULL.
Can you please suggest what to do next.
I tried this ,
SELECT DATABASEPROPERTY(‘your_db_name’, ‘isShutdown’) but i am getting result as NULL.
Can you please suggest what to do next
Check if you spelled your DB name correctly. See this msdn page http://msdn.microsoft.com/en-us/library/ms186823.aspx
It is written that “If the database is not started, properties that the SQL Server retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.
Great post thaank you