Solved

SQL2000 Transaction Log not shrinking

Posted on 2010-09-09
9
541 Views
Last Modified: 2012-05-10
I have a SQL2000 DB (450GB) that currently has a transaction log (multiple files) of 110GB.  The DB was in simple mode and the last complete backup was completed 2 days ago.  The normal full backup runs one a week.  The logs have just recently started growing without shrinking after backup.  I have put the db in full and bulk mode and done a transaction log backup and stil no shrink???  I have looked on this sit, trying to research other possible methods for getting the log file size down, but nothing has worked thus far.  Here are some otf the actions taken:

(1) run DBCC SHRINKFILE ('filename',1)
(2) run DBCC SHRINKFILE ('filename',truncateonly)
(3) BACKUP LOG xxx_DatabaseName_xxx WITH TRUNCATE_ONLY

I would appreciate any assistance, I have very limited space on this server.


0
Comment
Question by:blberger
  • 5
  • 4
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33636358
please check the output of DBCC OPENTRAN
0
 

Author Comment

by:blberger
ID: 33636580
Here is the results of DBCC OPENTRAN:

Transaction information for database 'DBName'.

Oldest active transaction:
    SPID (server process ID) : 54
    UID (user ID) : 1
    Name          : ClearChangeBits
    LSN           : (24063464:12390:1)
    Start time    : Sep  9 2010  9:23:36:737AM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am currently performing a full backup of the DB in Simple mode to try and get a new starting point.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33636700
presuming that you have something like lunch time, that makes some hours ...
so, can  you check what that transaction (54) is doing ...
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:blberger
ID: 33636746
Well the backup is still progressing (15GB) completed, but the total size will be just over 400GB when completed.  By completing this full backup, my transaction log should automatically get truncated to it's smallest size when complete, correct?  (or is there another process that I will need to complete, such as a dbcc shrinkfile('filename',truncateonly))?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33636779
no. backup will NOT shrink the size. it will just backup.

only DBCC SHRINKFILE can do that.
for the transaction log file, it will only do that if there are no "active" sections at the end of the file.

0
 

Author Comment

by:blberger
ID: 33636909
Ok, so if the backup of the Db will not cause that to happen, then how can I get the log to commit all transactions so there will be no 'active' sections?
0
 

Author Comment

by:blberger
ID: 33637039
Ok, I resolved the issue, here is what I did:

(1) DBCC OPENTRAN --|  result showed no open transactions
(2) BACKUP LOG DATAMART WITH TRUNCATE_ONLY --|  ran successfully
(3) DBCC SHRINKFILE ('LogFileName',1)  --|  Ran for each log file
(4) SP_HELPFILE --|  Displayed the current file statistics
--|  result showed all log files resuced to 128K each

Also note:  Use DBCC OPENTRAN to determine whether an open transaction exists within the transaction log. When you use the BACKUP LOG statement, only the inactive part of the log can be truncated; an open transaction can prevent the log from truncating completely. In earlier versions of Microsoft SQL Server, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. To identify an open transaction, use sp_who to obtain the system process ID.


Your Assistance with this guided me to the resolution
0
 

Author Closing Comment

by:blberger
ID: 33637060
The Guidance to the resolution whether by accident or not was a good method for discovery.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33637076
>BACKUP LOG DATAMART WITH TRUNCATE_ONLY
note that this should not be needed, actually, if the db is in simple recovery mode ...
and if it were in full recovery mode: you should not do that neither ...
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
MS SQL Update query with connected table data 3 38
Stored Proc - Rewrite 42 59
Error when creating a table from a function 6 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

837 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