Solved

SQL2000 Transaction Log not shrinking

Posted on 2010-09-09
9
557 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
[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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

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!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

622 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