Solved

backup up sql db and logs question

Posted on 2010-11-20
9
259 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:purdyra1
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
 
LVL 4

Expert Comment

by:pbarry1
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Never, ever include a SHRINKFILE or SHRINKDATABASE in a scheduled job. Period.
0
 

Author Comment

by:purdyra1
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now