Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL2000 Transaction Log not shrinking

Posted on 2010-09-09
9
Medium Priority
?
562 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

704 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