SQL2000 Transaction Log not shrinking

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.


blbergerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please check the output of DBCC OPENTRAN
0
 
blbergerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
presuming that you have something like lunch time, that makes some hours ...
so, can  you check what that transaction (54) is doing ...
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
blbergerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
blbergerAuthor Commented:
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
 
blbergerAuthor Commented:
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
 
blbergerAuthor Commented:
The Guidance to the resolution whether by accident or not was a good method for discovery.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.