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.
purdyra1Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Anthony, my beef is not about performance, never was.
I just don't like having space being used when it doesn't need to be.
Neither am I an extremist with always truncating to 0 bytes free, moving all pages to the beginning.
I don't believe I once mentioned performance in my above comments.
0
 
cyberkiwiCommented:
You can remove the job.
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.
0
 
pbarry1Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Anthony PerkinsCommented:
Never, ever include a SHRINKFILE or SHRINKDATABASE in a scheduled job. Period.
0
 
purdyra1Author Commented:
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.
0
 
cyberkiwiCommented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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.

:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.