Solved

backup up sql db and logs question

Posted on 2010-11-20
9
263 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
ID: 34179867
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
ID: 34180244
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
ID: 34181538
Never, ever include a SHRINKFILE or SHRINKDATABASE in a scheduled job. Period.
0
 

Author Comment

by:purdyra1
ID: 34190623
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34190692
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
ID: 34210015
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
ID: 34210026
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
ID: 34210097
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
ID: 34210110
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

943 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

15 Experts available now in Live!

Get 1:1 Help Now