Transaction log 50GB

[aosborn]

I'm using SQL Server 2000 and for a while my database's transaction log was not being backed up (low disk space), which I assume was making it not truncate. It showed 47GB used space and about 4GB free space.

I set a maintenance plan to backup/reindex/shrink/optimize and all that. It all completed without errors and now the transaction log file is 5GB used space (8GB DB so I think that's normal) but it still has 48GB of free space.

I manually ran a shrink database in Enterprise Manager and that didn't work. I ran a shrink file on the log file and that didn't work. I specified a specific filesize (Used Space+10%) and that didn't work. I'm getting no errors, it just isn't shrinking.

What else can be done?

Thanks!

 

[MikeBatters]

Have you checked the option to move pages to the begining of the file before shrinking?

Mike

*************************************

Remember - There is always another way..........I just haven't found out what it is yet!

 

[aosborn]

That option isn't in the maintenance plan but I can when manually shrinking the database. It does say it may hinder performance though, is this the only way to get it to shrink?

Thanks!

 

[MikeBatters]

It is indeed the only way.

It will have to move the 5GB of data to the begining of the file and then it will have 43GB of whitespace at the end of the file which can then be released by to file system.

Mike

*************************************

Remember - There is always another way..........I just haven't found out what it is yet!

 

[Infinity306]

Make sure to schedule transaction log backups, and not just database backups if you use anything other then the simple recovery model.. else Transaction logs will get huge. I had that problem.. with Deltek accounting system as major database role here.. the database itself was 100 megs and the transaction log grew to over 100GB.. I decided to change to simple recovery model here.. most we will lose is a day of data in the database in worst case.