Transaction Logging Control (from Newbie)

Question

Transaction Logging Control (from Newbie)

tommill

SSC Journeyman

Points: 83
More actions
July 7, 2010 at 8:31 am

#141301

Hi - I hope this is the right forum - apologies if it isn't, but I assume this comes under SQL admin.
We have a manufacturing system using SQL Server as a back-end. When it was implemented, the systems guys decided to do FULL backups with transaction logging. The result is that we now have a database file of around 4Gb (no problem) and a log file of c22Gb (big problem). I am a general purpose IT Manager with little knowledge of SQL Server, other than writing basic queries for report purposes.
We think that what we ideally need is a transaction log that never contains more than seven days of data (the current one is getting on for two years). Can anyone advise how this can be achieved, and how it can be implemented, or of any suitable 'idiot guide' for SQL Admin?
Would a transaction log of this size have any effect on db performance? We have some serious performance issues, but the manufacturing software authors say we should just reboot once a month to keep it tidy.
Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergey Vavinskiy SSCarpal Tunnel Points: 4469 More actions · Answer 1

The growing transaction log can potentially create a space and data availability issue, so you have to handle this situation.

You have two different strategies how to handle it, and specific strategy depends on a recovery model of your database. Due to the certain signs I guess your database currently has the Full recovery model. You may want to keep it because this model gives you an ability of point-in-time recovery option. If you want to keep the Full recovery model, you have to handle transaction log issue by using a scheduled transaction log backup job.

If you don’t need point-in-time recovery option, it makes your life even easier. You just need to switch your database to the Simple recovery model, and SQL Server will take care of your transaction log. But before doing that you may want to backup transaction log and shrink a transaction log file.

steveb. SSC-Forever Points: 46733 More actions · Answer 2

You need to do transaction log backups to keep the transaction log maintainable

have a look at this article for some advice

http://www.sqlservercentral.com/articles/64582/"> http://www.sqlservercentral.com/articles/64582/

Also the size of the t-log should not affect performance.

but the manufacturing software authors say we should just reboot once a month to keep it tidy.

This is really bad advice, sql server does not need to be rebooted to keep it tidy, it just needs to be setup properly

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Take a look through this article - http://www.sqlservercentral.com/articles/64582/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tommill SSC Journeyman Points: 83 More actions · Answer 4

Thanks all of you for the replies. I have had a brief read of the article you recommended and that has clarified the function of the transaction log. What documentation would you recommend on how to actually configure it?

I am not surprised that you think the advice on rebooting once a month is no good. I feel the manufacturing sofware authors are not that specialised on SQL Server either (by their own confession).