backup up sql db and logs question
I recently took charge of a database on sql server 2005 at a new company and I see this in the Event Viewer:
Event Id 8309
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
I looked at the job that is running and its:
USE [master]
GO
BACKUP LOG [CompanyDB] WITH TRUNCATE_ONLY
GO
USE [CompanyDB]
GO
DBCC SHRINKFILE (N'CompanySQL_log' , 0, TRUNCATEONLY)
GO
This database is in SIMPLE RECOVERY MODE which is fine since its a data warehouse and not transactional.
So my question is: I was going to disable this sql job but wanted to double check and make sure I don't need it and I am not going to mess anything up. any input or thoughts, thanks.
Event Id 8309
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
I looked at the job that is running and its:
USE [master]
GO
BACKUP LOG [CompanyDB] WITH TRUNCATE_ONLY
GO
USE [CompanyDB]
GO
DBCC SHRINKFILE (N'CompanySQL_log' , 0, TRUNCATEONLY)
GO
This database is in SIMPLE RECOVERY MODE which is fine since its a data warehouse and not transactional.
So my question is: I was going to disable this sql job but wanted to double check and make sure I don't need it and I am not going to mess anything up. any input or thoughts, thanks.
If I may add: shrinking log files (or datafiles) will have a negative performance impact if your processes keep regrowing your files. If free disk space availability is not an issue for you, preallocating the correct space amount (or not shrinking) will result in less disk fragmentation, less file movements overhead and better performance.
Never, ever include a SHRINKFILE or SHRINKDATABASE in a scheduled job. Period.
ASKER
ok, I am trying to understand this to the best of my ability. CYBERKIWI says: A weekly job without the TRUNCATEONLY option to DBCC SHRINKFILE may help more. , but ACPERKINS says NEVER.
which should I do?
we daily load data feeds, probably about 500k records a day, spread out over 20 different jobs.
which should I do?
we daily load data feeds, probably about 500k records a day, spread out over 20 different jobs.
I use TruncateOnly all the time, but only because I like to expend idle CPU time to minimize disk footprint. Idle CPU time is just wasted doing nothing, so might as well get it to do something.
The other reason for truncating LOG files is for use in development environments, where devs keep testing large queries for a day in a year against gigantic databases. No reason to leave the logs at gigantic sizes all other times.
However, if your log file size is reasonably stable and your input (daily feed) is regular, you can truncate it to a size about 1.5x-2x the size required* to cater for the daily load. This achieves the trimming of the log after one-off large operations, but also does not unnecessarily cause the log to grow/shrink all the time.
* keep truncating (to 0) for a week, and monitor the size of the log file for a while, then change the parameter to truncate to just above the median size x 1.5 or 2.
The other reason for truncating LOG files is for use in development environments, where devs keep testing large queries for a day in a year against gigantic databases. No reason to leave the logs at gigantic sizes all other times.
However, if your log file size is reasonably stable and your input (daily feed) is regular, you can truncate it to a size about 1.5x-2x the size required* to cater for the daily load. This achieves the trimming of the log after one-off large operations, but also does not unnecessarily cause the log to grow/shrink all the time.
* keep truncating (to 0) for a week, and monitor the size of the log file for a while, then change the parameter to truncate to just above the median size x 1.5 or 2.
Don't take my word for it, but rather read what Paul Randall has to say about it:
A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx
A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx
And it is Paul Randal and not Paul Randall. In case you have not been using SQL Server long and not familiar with the name he wrote the code for DBCC INDEXDEFRAG and DBCC SHOWCONTIG as well as large part of DBCC CHECKDB.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then I agree with you 100%, if performance is not a problem and all you are worried about is disk space then you should schedule a SHRINKFILE as often as possible, even better just enable Auto Shrink and you will not even have to shedule the SHRINFILE.
:)
:)
Simple recovery model keeps only the very current transaction (implicit or explicit) in the log.
As soon as any are completed for ACID control, the log records are closed/deleted, so the log never grows by much. However, if you deleted a million records in one go, it will still expand the log file for a while - so the 2nd part of that script can still be useful to shrink it.
A weekly job without the TRUNCATEONLY option to DBCC SHRINKFILE may help more.