• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Unable to shrink SQL log file

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
Damian_Gardner
Asked:
Damian_Gardner
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RiteshShahCommented:
0
Independent Software Vendors: 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!

 
Damian_GardnerAuthor Commented:
Thanks to everyone for all of your info.  How do I tell if we're in FULL or SIMPLE mode?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's in the database properties (advanced tab)
0
 
Damian_GardnerAuthor Commented:
Great. So looks like I'm in FULL mode.

Thanks to all again.  I'll split up the points.
0
 
RiteshShahCommented:
no problem. :) good luck
0
 
Damian_GardnerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Damian_GardnerAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now