Question

best practice for workflow transaction logs

Workflow

Updated August 23, 2018

asked on August 22, 2018

I am not a SQL person at all (just a heads up)

One of our customers has a LDF file (Workflow database) on their SQL server that has reached 273GBs. I know it is ultimately up to their SQL DBA to determine how they wish to maintain their SQL environment based on their procedures and goals, but what are some best practice to give to the customer?

They are SQL Server 2008 running on Full recovery model.

0 0

Replies

replied on August 22, 2018 • Show version history

Are they backing up the transaction logs? That should keep the log size down. We back up ours every half hour.

2 0

replied on August 22, 2018

No response yet but we did recommend doing daily intervals and full weekly backups.

0 0

replied on August 23, 2018

What SKU of SQL Server are they on? Compression can do wonders.

0 0

replied on August 22, 2018

Best way to reduce the log size is to shrink them, but note that I said shrink logs not the db. There are 2 ways to do it - through GUI or through TSQL, this is a helpful article: https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/

If this keeps happening, you can setup a SQL job that runs daily to keep the logs small:

ALTER DATABASE [yourDBname] SET RECOVERY SIMPLE
DBCC SHRINKFILE("yourLogName")
ALTER DATABASE [yourDBname] SET RECOVERY FULL

2 0

replied on August 22, 2018

There's a lot of previous discussion here if you know the right terms to search, I found these with "recovery model": https://answers.laserfiche.com/questions/115761/SQL-Maintenance-best-practices https://answers.laserfiche.com/questions/48435/SQL-Transaction-Log-is-taking-up-all-drive-space

1 0

You are not allowed to follow up in this post.

Question

Question

best practice for workflow transaction logs

Replies

Sign in to reply to this post.