SQL 7 Can not truncate Transaction Log

Question

Post reply

SQL 7 Can not truncate Transaction Log

Steve Doyle

Valued Member

Points: 54
More actions
November 28, 2006 at 2:34 am

#173615

I have a database which has the following size properties;
Data Space : 79.63Mb (79.13Mb used)
Transaction Log Space: 1,330.24Mb (100.24Mb used)
I have tried "truncate transaction log" and "shrink database", but neither made a significat difference. I also performed these two actions with the database in single user mode.
Any help appreciated.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 1

Simply backup the transaction log to truncate it.

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

Steve Doyle Valued Member Points: 54 More actions · Answer 2

I have tried as you suggested, but it only appeared to remeove a small percentage of the log file (maybe todays transactions ?).

The log file still remains abnormally large.

Thanks for your help.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

if only 100mb of the 1.3gb is used you don't need to truncate the log - you need to shrink it.....

trunctate log means to remove the inactive entries from within the log (clearing space within the file)

what you need to do is either a dbcc shrinkfile or use enterprise manaager and right click on the database and choose the shrink option from the all tasks sub menu

MVDBA

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 4

Can you tell me the recovery model set for the database, the initial size of the log file and its growth settings?

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

rcovery model is down to what recovery options you need.

there are 2 basic modes in sql 2000/7

simple mode (or in sql 7 truncate log on checkpoint on)

in this mode you simply back up your database using the maintenance plan or a backup database command on a nightly ? basis - no srinking/truncating of the log files are needed unless you perform some extremely large operation

FULL mode (truncate log on checkpoint off in sql 7) in this mode you backup nightly?? and then also shedule a backup log command every hour or so(can be done through maintenance plan wizard!!!)

the benefit of full mode is that if you have a disaster you can restore the database and then restore the T-log files up to the point just before your disatser - you should really practice this though before assuming your DR strategy works

initial size of the log is fairly easy - idepends on the size of the database, but for your database it's say set it to 100Mb and leave it at 10% growth - it might grow once or twice during defrags but that should be it - if the log has to grow then your database runs slow so don't bother reclaiming space that the log will grow into agian

MVDBA

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 6

Also, please run the command:

DBCC SQLPERF(LOGSPACE)

and let me know the %used value for the database in question.

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

Steve Doyle Valued Member Points: 54 More actions · Answer 7

Pankaj;

Reccovery Model - Full

Initial size - not to sure how to identify this

Growth - Automatic by 10% unrestricted,

dbcc sqlperf(logspace)

Database Name Log Size (MB) Log Space Used (%) Status

BondTracker 1253.9922 1.7391721 0

Mike;

I think the log file grew initially due to a number of data load operations prior to use, these will not happen again in the day to day operation of this application.

Thanks for your interest and help.

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 8

Thanks for providing all the information.

Please carry out a few checks:

1) Check whether there is any open transaction on the database or not. To check this, use the following commands:

use <database-name>

go

DBCC OPENTRAN

If it returns any row, please check the SPID value, and see what that SPID is doing (for this, the command is :

DBCC INPUTBUFFER(<SPID&gt

However, if you don't see any output of DBCC OPENTRAN command, then procede as below.

2) Issue a manual checkpoint. Simply type CHECKPOINT in the query analyser and press F5. It should give you a message "The command is successfully completed"

3) Then take a backup of the transaction log.

4) Issue DBCC SHRINKFILE (<fileID of the log file&gt

I hope this will help.

Good Luck

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

Johan Bijnens SSC Guru Points: 135286 More actions · Answer 9

maybe this will get you on track ...

INF: How to Shrink the SQL Server Transaction Log

http://www.support.microsoft.com/kb/256650

and yes, you may have to repeat this action a couple of times

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dan Murray Right there with Babe Points: 779 More actions · Answer 10

I know this is going to cause 'Howls' of disbelief but if you are NOT using the transaction logs at this point to rollback transactions by the hour or whatever and find that they are just growing and getting in your way you can just delete them, here's how. Dettach the database, delete the log file and then reattach the database. A new empty log file will be created. Try it on a test database first to get the hang of it.

Guerrilla Sql.

Steve Doyle Valued Member Points: 54 More actions · Answer 11

Thanks to everyone who replied.

Using a combination of the suggestions, the log file has been successfully truncated to a more appropriate size.

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 12

In the future:

I have dealt with this in many ways.

I am about 99.9% positive this will work.

Backup Log File - Transaction Log
Change recovery model to simple
change recovery model back to Full
Run DBCC SHRINKFILE

This is a problem with SQL server. I have never had this method fail

EXAMPLE:

USE PUBS

GO

BACKUP LOG PUBS

TO DISK = 'C:\Pubs.TRN'

GO

ALTER DATABASE PUBS

SET RECOVERY SIMPLE

GO

ALTER DATABASE PUBS

SET RECOVERY FULL

GO

DBCC SHRINKFILE (pubs_log, 0, TRUNCATEONLY)

GO

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 13

what on earth are you doing jdixon?

if you set recovery simple you risk breaking the LSN chain so that reverting back to recovery mode full will be useless

you don't need to set recovery simple and then back to full in order to do a shinkfile

if you want to clear the log then issue a checkpoint and packup the log. then do the shrinkile.

never swap your recovery modes like that

MVDBA

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 14

Then Backup the database when you are done. You have to do what you have to do.