Deleted database log(.ldf) file of SQL Server? Need to Restore database?

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,

USE master

 SELECT DATABASEPROPERTY(‘your_db_name’, ‘isShutdown’) –if this returns 1, proceed to next steps

 ALTER DATABASE your_db_name SET OFFLINE

EXEC sys.sp_detach_db your_db_name
 
 EXEC sys.sp_attach_db your_db_name,‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\your_db_name.mdf’

 

 Now, your affected database will be up and running again with a new log file.

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SQL, Database. Bookmark the permalink.

3 Responses to Deleted database log(.ldf) file of SQL Server? Need to Restore database?

  1. MRUNALI says:

    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.

  2. MRUNALI says:

    I tried this ,
    USE master
    SELECT DATABASEPROPERTY(‘your_db_name’, ‘isShutdown’) but i am getting result as NULL.
    Can you please suggest what to do next

    • Zahid says:

      Hi Mrunali

      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.
      “.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s