Solved

Unable to shrink SQL log file

Posted on 2009-05-07
10
342 Views
Last Modified: 2012-05-06
Hello - we are running SQL Server 2005 on a 2003 Windows Server, and have a miantenance script in place to maintain the database.  Part of the script issues a DB shrink command:

[USE [LaCo_AX_Test]
GO
DBCC SHRINKDATABASE(N'LaCo_AX_Test', 10, TRUNCATEONLY)]

"LaCo_AX_Test" is the name of our database.  The log file is at 3.7tg, and has stayed that size for the past few shrink executions.  What are reasons that a log file would not shrink down in size?
0
Comment
Question by:Damian_Gardner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
ID: 24325644
if the database is in full recovery mode, while there are not regular transaction logs performed, that is normal.

note: if the database is in full recovery mode, you HAVE to run regular transaction log backups, and the .ldf file will NOT grow endlessy (except someone keeps a transaction open forever ...)
note: if the above is implemented, or the database is in simple recovery mode, you shall NOT need to run the shrink regulary. doing so just wastes I/O ....
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 167 total points
ID: 24325658
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 166 total points
ID: 24325705
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Damian_Gardner
ID: 24326704
Thanks to everyone for all of your info.  How do I tell if we're in FULL or SIMPLE mode?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24326816
it's in the database properties (advanced tab)
0
 

Author Comment

by:Damian_Gardner
ID: 24326986
Great. So looks like I'm in FULL mode.

Thanks to all again.  I'll split up the points.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24326999
no problem. :) good luck
0
 

Author Comment

by:Damian_Gardner
ID: 24327544
I know I closed the case, but I came up with another question.  I went ahead and created a backup of my logfile (which I hadn't done before), and then tried issuing a Shrink command through the SQL Mgmt interface.  It executes very quickly, and has no effect on the file size.  I'm reading about the Truncate Only command, and one of the gurus discourages using it.  If I've just made a backup of the log, should Truncate Only be ok to use?  This is an active production database, and active transactions are open against the log right now, BTW.

Thanks,
Damian
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24327590
the first log backup usually is not enough on it's own to make the shrink file effective.
after that first log backup, some transactions + another log backup will do the effect.
0
 

Author Comment

by:Damian_Gardner
ID: 24327757
Ah - ok.  I'll wait to see how the auto backup plan runs tonight then, and check it again in the morning.

Thanks again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question