Truncating Log file - SQL

[#1]

Hi All,

We need some help in reducing the size of our log files.

Our database has grown to 17gig and our log file is just over 12gig. Our database recovery model is "full".

We are running complete backups once a night, and also doing an hourly transaction log backup. I have tried right clicking the database in EM->all tasks->shrink database and specifically selected the log file and clicked ok, as well as just clicking ok, but there seems to be no effect.

Can anyone outline how to do this? From what I can tell, we are only using around 50Mb of the logfile and the rest is just empty space!

Thanks

Rajiv

[Former Member]

Hi Rajiv

TransactionLog file of SQL Server 2005 can be truncated by following T-SQL.

USE DatabaseName

GO

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

Please keep in mind:

1.a full backup should be done BEFORE and AFTER this operation.

2.It only works on SQL Server 2005.The TRUNCATE_ONLY has been

removed on SQL Server 2008.

The SQL Server 2008 is not supported for SBO yet though.

Regards,

Syn Qin

SAP Business One Forums Team

[Former Member]

Thank you both for your answers (can't award 10pts to both of you for some reason). Have manually truncated the log file.

Hopefully this won't happen again... DBCC OPENTRAN showed nothing. Hmmm.... will wait to see if it happens again - for now it seems to be ok

Thanks again

Raj

[Former Member]

10 point can only assign once. What I suggest you is to completely refresh the log file. Just by truncating may not be good enough. You can do this refresh periodically. In the meantime, the manual truncate can be done in between to extend the period to keep reducing the log.