SQL SERVER – Shrinking Truncate Log File – Log Full – Part 2

About a year ago, I wrote SQL SERVER – Shrinking Truncate Log File – Log Full. I was just going through some of the earlier posts and comments on this blog and one particular comment by Praveen Barath caught my eye. It is very good explanation. I am copying complete explanation here with full credit to original author.

How to truncate log file in SQL Server 2005

SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.

Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.

I. Shrink the log file size at the right time

I found out this trick:

Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files

set the file size, say, 1MB.

Then, click OK and you are done.

II. Eliminate the log file completely

Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

Let’s say, the database name is testDev. In the SQL Server Management Studio,

Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.

Reference : Pinal Dave (http://blog.SQLAuthority.com), Praveen Barath

21 thoughts on “SQL SERVER – Shrinking Truncate Log File – Log Full – Part 2

  1. I have noticed that you have not included a step of backing up the database first, and when doing so, clicking remove inactive entries will verify that all transactions have made it through the log before deleting it. I have avoided the dreaded “Unable to Attach” corruption error by doing this fashion. Thanks for having a great site, although I am disappointed in your decision to not write 2k tips anymore. I understand it, but still have clients from all over who are still running 2k and will for quite a while longer. Still, thanks for all you do!

    Shannon

    Like

  2. Pingback: SQL SERVER - Solution - Log File Very Large - Log Full Journey to SQL Authority with Pinal Dave

  3. Hi,

    i am using sql server 2005 express edition,
    I detach the database but i couldn’t find log folder anywhere. There is SQL serve logs folder under Management folder but it is not showing any rename option after right clicking on it, it just shows Refresh.
    How can bring back my detach database as i don’t have any backup for that.

    Like

  4. Hi Sukh,
    You need to find the Log file folder in your Windows folders by going to place where your SQL server is installed and has its data files. The path should be something like.. ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\’. There you will see your Log file and MDF file.

    – Gaurav

    Like

  5. We have an option with one of our server like disk space alert, when the usage os disk reaches 90-95% it wil create an alert and send an email to all DBA.

    All the time Critical suituation is due to growth of LOG files only, rarely on MDF files. Is there any query to find which file MDF/LDF is growing or which is adapting more space.

    Is there any way to find the file in short time period because we have to resolve it as quick as posible.

    Regards,

    Senthil kumar Rajendran.

    Like

  6. I am presently working on Upgradation project and the SQL Server is development phase n we are getting data from another database through SSIS. I am experiencing performance problem, where there are only 20-30 users who are using the SQL. There is a very slow running SSIS packages and sometimes they are stopping due to unkown reason n if they leave them..the next day its show tht the SSIS package ran successful. what are the things I am suppose to check for?..plz suggest me how to check the loading performance as its bulk loads n even mass updates are ran on the loaded tables.

    thanks and regards

    Like

  7. Hi Pinal, I am using SQL Server 2005 Management studio and connected to SQL Server 2000 instance and I am able to get rid of the promary log file completely.
    Thanks for posting the solution.

    Like

  8. Hi Pinal,

    Even after executing the above shrink query the size of the log file has not reduced. In my case the data file is around 180 mb, where as Log file is aroung 32 GB. Please let me know the log maintenance procedure to reduce the log file size.

    Regards,
    Uday

    Like

  9. Hi!
    What about removing the log file in Ms Sql 2008?
    I tried to Detach&Attach the database, however I can’t attach it without the log file..
    thank you,
    yasemin

    Like

  10. Hi Pinal,
    thank you for this post. I initially had the same problem as Yasemin but found if the reference to the log file is removed when re-attaching, your solution works perfectly.

    Like

  11. Hi,
    If the log file size is huge and you are not able to truncate it then you can try these steps as well.
    1. Goto to database properties.
    2. Goto options.
    3. Change the recovery model to simple and click over ok.
    4. Restart the database.
    5. Try shrinking the log file.

    Like

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  13. Hi

    I need one help very badly. My scenario is like this. I have1 DB on an Instance which is having a data of almost 50GB with around 20 million records. And we have one job after running which the data or records in the DB1 are pushed into an another DB which is running on another Instance. But the bad thing is that the Log file(on Instance 2) is getting filled and the job getting failed. I already kept the Log Drive size as 68GB and I can’t afford more than that. So is there any chance or settings by setting which the log file will be shrinked on fly and the transaction continues.

    Like

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