Truncating Log file - SQL

[Former Member]

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.

[Former Member]

The simple solution is changing Full to Simple as SAP suggested.

Thanks,

Gordon

[Former Member]

We want to be able to do a point in time restore - to the nearest hour - hence the use of the different recovery model.

From my understanding, the SIMPLE recovery model allows restore till the last full/differential backup. I'd be very surprised if most people were using this model over the FULL recovery model.

Typically, isn't the transaction log supposed to be purged of old entries once a backup of the logs is performed? In theory there should be no need to manually truncate the log...?

[Former Member]

I am not sure what your business is. However I am surprised by your understanding of the recovery model. Most people selected simple have a good reason. The performance plays more roles in this decision. Have you ever tried to recover your database to the last hour? B1 is for SME business. Because of current hardware and software reliability, the risk for the total failure is far less than some years ago. There is no need to put more weight on DRP than performance.

You have 29 GB database now. It will grow up even faster from now on. Do you have enough resource to keep your system running as you expected?

Thanks,

Gordon

[Former Member]

Ok, lets split this up into 2 issues to keep things simple:

1. In our present situation (using the FULL recovery model), we are performing a full backup every night. We are also backing up the transaction log every hour during working hours. In this scenario, from what I understand the transaction log file should be truncated to the point of backup. This is not happening on my server, so I am asking for guidance as to the best way to investigate why this is happening, and then of course the best way of truncating the file manually (T-SQL?).

2. In terms of the recovery model, in our business people phone in with orders, payments etc which are all input directly into B1. We physically wouldn't be able to recover back to the previous day/last full backup, as alot of the input data isn't on paper/email. I have done a test restore with backup + logs etc previously - it's not difficult per se, just lo-ong =).

The change in hardware works 2 ways... yes, you're right in that hardware may be more reliable, but the basic business need for recovery in the event of failure remains unchanged (in our specific case). In contrast however, as the hardware gets faster and faster, our 17gig DB + logfile (note, this should be nowhere near 12gig) will not be as incumbent as perhaps one might expect on our file server. Now if we can do full backups semi-transparently using the simple model every hour on newer hardware... that's one less thing to worry about! 😃

[Former Member]

In your case, Full mode is needed. Since your backup your log every hour, chances are: SQL server has no time to correct any errors so that the database is not in consistency anymore.

My suggestion: in your test database, change the mode from Full to Simple. Make sure the log size back to small then change it back to Full. When you feel it is ok, do it in your live database when you got enough time to do so.

You must have a clean database in order all functions work as they should be. You may need to extend your log backup interval to 3-6 hours if you can.

Sorry for the previous post because I do not know your specific requirement.

Thanks,

Gordon