Solved

SQL2000 Transaction Log not shrinking

Posted on 2010-09-09
9
515 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 142

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 142

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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

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 142

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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now