January 22, 2019 at 9:50 am
The network guys need a SQL database for Symantec and they just asked me about turning on the Symantec maint job that will trunc the tran log every 4 hours. This will be setup as simple recovery model.
From Symantec:
After you install the management server, the space in the database grows continually. The management server slows down after a few weeks or months. To reduce the database size and to improve the response time with the database, the management server performs the following database maintenance tasks:
- Truncates the transaction log.
The transaction log records almost every change that takes place within the database. The management server removes unused data from the transaction log.
Why is Symantec saying this, according to their documentation the log will continue to grow forever... Unless they assume Full recovery but in that case you cant trunc the log anyway. What am i missing?
https://support.symantec.com/en_US/article.HOWTO81047.html
January 22, 2019 at 2:35 pm
It would only continue to grow forever if the database were in full recovery mode, no log backups were taken and was on one of those infinity disks whose disk size is measured in infinity bytes.
Not sure why they wrote that advice.
Nutz.
That’s on the scale of “yes our app needs to use the sa account”. Shudders.
If in simples, then set the size of the log file appropriately, by all means leave auto growth in for the get out of jail card. If you did have the database in full recovery then backup regularly to keep the log file in check (some servers I have hourly, others every 15 or even 5 minutes) but you know this already.
I would trust Brent.
https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
January 22, 2019 at 3:25 pm
Thanks i just wanted to verify it wasn't just me scratching my head.
And yeah i know what Brent would say, i didn't even need to open that link, but i did.
January 22, 2019 at 3:40 pm
AlphaTangoWhiskey - Tuesday, January 22, 2019 3:25 PMThanks i just wanted to verify it wasn't just me scratching my head.
Sometimes (most of the time) that's the effect of dealing with vendors and their recommendations
Sue
January 22, 2019 at 4:22 pm
AlphaTangoWhiskey - Tuesday, January 22, 2019 9:50 AMThe network guys need a SQL database for Symantec and they just asked me about turning on the Symantec maint job that will trunc the tran log every 4 hours. This will be setup as simple recovery model.From Symantec:
After you install the management server, the space in the database grows continually. The management server slows down after a few weeks or months. To reduce the database size and to improve the response time with the database, the management server performs the following database maintenance tasks:
- Truncates the transaction log.
The transaction log records almost every change that takes place within the database. The management server removes unused data from the transaction log.
Why is Symantec saying this, according to their documentation the log will continue to grow forever... Unless they assume Full recovery but in that case you cant trunc the log anyway. What am i missing?
https://support.symantec.com/en_US/article.HOWTO81047.html
Take a look at what that maint job actually does - if they are using the truncate_only flag to backup the log, that won't work as it was deprecated and now fails. If they are actually truncating the log based on the recommended method - which is to alter the database to simple and back to full - then you definitely don't want it running as that causes many more issues than it ever solves.
If they insist because the vendor says it must be done - modify the job so it doesn't do anything and tell them it has been done 😉
I have also run into vendors that have really bad advice, for instance - one vendor insists that switching the recovery model from full to simple makes the application upgrade go faster. They insist on doing this for the databases even when those databases are part of an availability group that is used for both HA and DR and wonder why I absolutely refuse.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply