Shrinking the transaction log in SQL 7 1

[OldSkoolSkater]

Hello

We run SQL 7 and backup transaction logs daily with autoshrink set.
We also have alerts set so that if the trans log exceeds 50% of its max size (max is 100MB) the transaction log backup job is kicked off to reduce the size of it.

This normally works fine but we had a load of infomation dumped into a db which caused the trans log to shoot up to 75MB. The backup job was invoked and it reduced the size of the log to 8 MB, but there is 67MB of free space left which I cannot get rid of.
Because of this free space, the trans log is still classed as 75MB and the backup is being kicked in to reduce it (but it doesn't)

How can I get rid of this free space?
I have tried a DBCC shrinkfile to no avail.


Cheers

Steve - Network Coordinating in the UK

 

[oublie]

Hi,

Have you tried using the TRUNCATEONLY option? This will Cause any unused space in the files to be released to the operating system, target_size is ignored when TRUNCATEONLY is used.

 

[OldSkoolSkater]

Hello oublie

Have just tried this to no avail - is this normal?

Cheers

Steve - Network Coordinating in the UK

 

[SQLBill]

The actual command is:

BACKUP LOG databasename WITH TRUNCATE_ONLY

(Replace databasename with the name of your database).

-SQLBill

 

[OldSkoolSkater]

As a further note:

I increased the space allocated up to 200MB for the trans log through the db properties in Enterprise Manager.
This naturally increased the free space up to 192Mb (from the original 67MB)
I then ran the backup job for the log and checked the free space - it had dropped down from 192MB to the original 67MB, and it just will not reduce any further no matter how often the back up job is run!

Cheers

Steve - Network Coordinating in the UK

 

[SQLBill]

What was the original size of the transaction log when it was first created?

If you created it as 67MB, you can't reduce it below that amount. SQL Server will not allow you to reduce a file below: 1. It's original size or 2. the size of the data currently in it.

You MIGHT be able to get rid of the extra space by detaching the database, deleting the log file, and reattaching the database. But I've never tried that and don't know if it will really work.

-SQLBill

 

[OldSkoolSkater]

I set my log files in all databases to have a maximum size of 100MB which is generally more than enough, and of course they are initially empty.
The problem arose when the db had 0.5GB of data thrown at it and as a result the transaction log went daft.

Since the db is not a live production one, just an archive that gets updated monthly, I might try detaching and reattaching as you suggest.

Cheers

Steve - Network Coordinating in the UK

 

[OldSkoolSkater]

SQLBill

You are indeed a star!
I have just done the detach/reattach db, and the trans log was recreated when reattached to SQL.
It was just a case of then setting the properties of the trans log from grow automatically to a fixed limit of 100MB.
Ta!

Cheers

Steve - Network Coordinating in the UK

 

[Corran007]

The reason it likely was not shrinking was because it was at the end of that 67 mb file for writing to the transaction log. Once it would have looped around to the beginning, it would have shrunk down after the shrink db command is run.