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, sometimes 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 a 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 test-drive. 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 (https://blog.sqlauthority.com), Praveen Barath
19 Comments. Leave new
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
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.
Sorry, i attached the detached file by right clicking on database->attach. But my questions of finding log folder is still unsolved.
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
Can one programmatically schedule this?
If yes, then what would the BAT file look like?
Please advise.
Thank you.
— Mark Kamoski
Hi Pinal ,
If i create second log file for the one database. so how it works ?
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.
Very nice and helpful article. Got a log file down from 524 meg to 500 k.
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
I just tried II. Eliminate the log file completely. The trick worked PERFECTLY!
Thank you for posting the solution!
this will not be done if database is mirrored., Whats procedure to shiriking mirror database.
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.
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
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
Hi pinal
how to create email alert for log is full in database.
please help me….
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.
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.
So easy i wish had thought of it. I’m on SQL Express 2008 r2 and it worked just fine.
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.