SQL Server: Unable to shrink the tlog file?

Who is online?  148 guests and 0 members
home  »  blogs  »  Parikshit Savjani  »  SQL Server: Unable to shrink the tlog file?
  Rate This Blog Entry:  register  or  login

Author

Parikshit Parikshit Savjani (Member since: 1/15/2012 6:38:05 AM)

View Parikshit Savjani 's profile

Comments (7)

Naginder
3/15/2012 6:54:56 AM Naginder said:

This is a wonderful post, and i have been doing this whenever i face this problem of "unable to shrink the tlog file". Just one addition to this, instead of changing to simple recovery, we can take a tlog backup and shrinking the tlog file using dbcc shrinkfile in a Batch. DOing this is in a batch is very important else by the time we do dbcc shrinkfile, there may be transactions being posted which will update the last VLF and hence shrinking wont be possible.

 

Pls correct me if i am wrong...

by
Naginder
3/15/2012 6:56:52 AM Naginder said:

also the command is dbcc loginfo and not logfile as specified in the post

by
Parikshit
3/16/2012 4:05:27 AM Parikshit Savjani said:

Thanks Naginder for correcting the post. I have included your method in the blog post alongwith my comments.

 

by
Naginder
3/27/2012 3:10:16 PM Naginder singh virdi said:

gr8

by
Praveen Barath
3/29/2012 7:42:02 PM Praveen Barath  said:

 

Nice Topic…….

Very well said Parkshit, As you covered almost everything including the link of Too Many and Too few VLF could be bad…Appending and adding to the post.

 “LOG MANAGER” in SQL Server 2005 is the Brain for Log files which is smarter, faster than SQL 2000, So. YES, You can perform one DBCC SHRINKFILE statement to shrink the transaction log file immediately to 2 virtual log files. You can do this because the SQL Server 2005 log manager creates 2 virtual log files by following the physical disk storage order. Both of these virtual log files are at the start of the transaction log file.

When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size.

For more details (Ref) - http://support.microsoft.com/kb/907511

One more link I wrote long time back for SQLAUTHORITY.com

http://blog.sqlauthority.com/2007/11/22/sql-server-shrinking-truncate-log-file-log-full-part-2/

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.

 

by
Parikshit
3/30/2012 6:31:39 AM Parikshit Savjani said:

Praveen,

Good to see your comment :) ... its long time we had a call Smile

The only challenge here is, it is not a supported method of Microsoft and it may not work in future versions and hence I would not like to add it to the Blog Post.

If we can achieve the same results by using one of the supported Method then we dont want to promote unsupported methods

 

by
Praveen Barath
3/30/2012 1:39:46 PM Praveen Barath said:

Yupe

 Agreed ….As Its important to know what is ‘not supported’ …Ha ha ha ha.

Cheers mate

 

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Parikshit Savjani's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Hi Friends, In my earlier blog, I just explain about the importance of filtered indexes. Link for that blog is mention below: http://sqlservergeeks.com/blogs/princerastogi/personal/599/filtered-indexe...
  • A few words about the technology of our site. It's easy to take technology for granted, to focus on content and pay no attention to how it is being delivered. Typically we would encourage this focus. ...
  • Hello, Constraints can be defined as rules that govern the values that are inserted into a column. These rules help in enforcing data integrity. For example if a NOT NULL constraint is defined over a ...