SQL log file size not reducing after backing up log file in full rec.

[vickd]

Hello,

We are using a Full Database recovery model for our db, and our log file size was in control until recently when the server crashed and when it rebooted it rolled forward trasactions which were not commited to the database. Until this point whenever we did a backup of the transaction log it would reduce the space used size from the log file and thereby keeping the log file size in check as it kept using the same space over & over for newer logs. Now even though its backing up the trx log (and the remove inactive entries from the trx log file is checked) its no longer reducing this space. is the log file in any way not syncronized? we did not lose any data after the server crash. Any help is much appreciated.

Vick

 

[JayKusch]

What "probably" is occuring is that the TLog is being truncated BUT the physical file size (OS file size) has not been shrunk.

You would do a shrink file command on the TLog and then kick it out incrementally to it working size.

Thanks

J. Kusch

 

[vickd]

I think you are right..since its backing up the log..its just the fact that the space is not being released to the OS.

The only problem I think with the shrink file command is that if i run that statement it would reduce the Current Size of the log file to the file size specified and not the space used size. By doing this I will have to increase the Current Size of the log file again otherwise sql will increase it by the default 10% when the current size is reached by the log file. and ultimately our goal is to reduce the space used size.


I was planning to do this step ..

Run BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY
against the MASTER database in Query Analyzer.

Do a backup of the log file (with remove the inactive entries button checked) and see if it reduces the log file size.

If this did not do it then ..right click on the database in Enterprise Manager and click on shrink database with the truncate free space from the end of the file..

Is this the right solution?

Is this what you mean by do a shrink file command on the TLog.

Thanks

Vick

 

[jgoodman00]

Run BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY
against the MASTER database in Query Analyzer.

Do a backup of the log file (with remove the inactive entries button checked) and see if it reduces the log file size.

If this did not do it then ..right click on the database in Enterprise Manager and click on shrink database with the truncate free space from the end of the file..

Is this the right solution?

Yes. You could actually shrink the TLog from QA as well using DBCC SHRINKFILE.

DBCC SHRINKDATABASE will also shrink the mdf file which can lead to index fragmentation.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com