Solved

Unable to shrink SQL log file

Posted on 2009-05-07
10
337 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 142

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 41
how to eliminate duplicates in a string variable in t-sql? 30 69
SQL query with cast 38 43
How can i use WITH CTE for checking exist value? 3 34
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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