How often do you do transaction log backups?

A post appeared on SQL Server Central two days ago wherein the person asked why the transaction log backups were suddenly taking a long time, having gone from under 12 minutes to over two hours.

Subsequent questioning revealed that they do one transaction log backup daily.

Most experienced DBAs would be kind of aghast at the concept of doing transaction log backups once a day.  If the database is the least bit transactionally active, this would represent a potentially devastating data loss if the database fell in to the bit bucket.

Obviously any database will have different recovery objectives for time to recover and acceptable data loss depending on business needs and the size of the database.  Jeff Moden talks about his system where old data, which must be kept online, is kept in its own filegroups so that only active data is backed up, greatly reducing size and time required for data backups and making for much faster database recovery in the event of an emergency restore (restore the active data, they’re back in business and they can take time recovering the archived data).  The only scenario that I can (off-hand) envision where a once daily t-log backup would be acceptable is if the database does an ETL process and is then quiet transactionally thereafter.  But that’s my observation within my realms of experience.

Brent Ozar had a post two years ago advocating doing transaction log backups ONCE A MINUTE.  The reasoning is quite interesting, and can be summed up with the concept that a theoretical database produces 60 gig of transaction log changes every hour.  Would you rather back up 60 gig in one big chunk, every hour, or 1 gig every minute?  Which would be faster?  How big of a log would you need for one versus the other?  Hopefully you’d have your log pre-sized to handle 60 gig rather than having issues with it expanding as needed.

Good things to keep in mind when scheduling log backups.  Me, personally, my databases are typically fairly small.  At the end of the business day I would do a final log append backup, which would then be caught by the backup system.  First thing in the morning I’d do a log initialization backup and we’re back and running.

I know of a city government that installed an ERP system.  Pretty big install: a couple hundred or more users on at any given time doing all sorts of different jobs.  There might be utility billing jobs going on, lots of activity.  This would be a good installation for a t-log backup every minute.  Let’s say you do log backups every 15 minutes and your database crashes at 14 minutes since the last backup.  The users have to remember what they did in those 14 minutes and double-check their work to make sure everything is current.  At one minute intervals, there’s an excellent chance that almost no work was lost.

The backup interval is something that has to be negotiated with management as to how much loss of time and data is acceptable and how much they’re willing to spend to minimize those.  But once a day log backups?  I don’t think so.


Microsoft now recommends that ALL SQL Server Cumulative Updates be installed

Formerly the language around the CUs were fairly clear: CUs addressed specific issues, and if you weren’t experiencing that issue, then you shouldn’t install that CU.

Well, that was then and this is now.

Microsoft is now saying that you should install all CUs when they become available, and says that they undergo the same testing as complete Service Packs (SPs).  So now instead of waiting for an SP, even if you weren’t having problems, you should also keep current on CUs.

One good way of keeping up with the release of both CUs and SPs is Brent Ozar’s web site, SQL Server Updates., which tells me that SQL Server 2014 is up to CU5 for SP1.  On this site you can sign up for notification emails when new updates are available.  It lists versions going back to 2005, I expect that might fall off the list in three years when 2008 begins falling out of support.  (Microsoft says that when CU6 for 2014 SP1 comes out that CU5 will no longer be available for download)

So if you’ll excuse me, I have a couple of servers to patch.

How I Do SQL Server Database Maintenance

(One of the reasons for this blog is for me to document my processes for myself.  These are the ways that I do certain things.  In your career as a DBA, if that is your job, you’ll develop your own preferred techniques.  If these are useful for you, bonus!  Regardless, it gives me a source code repository that if I go somewhere to do some consulting to help them get their system maintaining itself properly, I can pull up posts like this and get them going quickly.)


The most critical thing as a SQL Server DBA is to ensure that your databases can be restored in the event of the loss of a server for whatever reason: disk crash, fire in the server room, tribble invasion, whatever.  To do this, not only do you have to back up your databases, you also have to test restores!  Create a database and restore the backups of your production DB to them.  It’s the safest way to make sure that everything works.  This test restore can be automated to run every night, but that’s outside the scope of what I want to talk about right now.

There are lots of places that problems can creep in, this is just one part of how you’ll need to monitor systems.  This is how I’ve done things for a number of years, and thus far it has served me well.

First and foremost, backups.  Your requirements will vary depending on your Recovery Point Objectives (RPO, how much data loss is acceptable) and Recovery Time Objectives (RTO, how long before the system is available).  If your normal business hours are Monday to Friday 8-5 with your largest database just a few gigabytes, you’ll have radically different requirements than someone with databases in the hundreds or thousands of gigabytes that require 24/7 uptime.

We’ll go with demonstrating a simple installation.

When your operation is 8-5 Mon-Fri, you have a huge maintenance window that you can get everything done in, so there’s no reason not to get everything done.

My personal method is to do my maintenance tasks at night.  These include DBCC CheckDB, Update Statistics, and checking the suspect pages table.  Note that I DO NOT USE MAINTENACE PLANS.  Gail Shaw recently described them as brain dead, doing far more than what is needed.  I particularly don’t like the miniscule amount that they report, so you’re never clear what was done.  So I do everything through SQL jobs.  And if I have a SQL Express installation, since everything is based on .CMD and .SQL files, it’s not a big deal to run everything through Window’s Task Scheduler.

Since my normal business hours normally see no activity at night, that’s when I do my maintenance.  I do a full backup of each database, followed by a full transaction log backup.  Those will be caught by the server backup later that night and written to tape, which will then be moved to another building the next work day.  I do differential database backups at noon, and transaction backups throughout the day, typically every 10 minutes.  As a rule you don’t want to start multiple backups at the exact same time, so stagger the start time slightly.  If you start the transaction log backups at 7:30 to capture people who come in early, fine.  I have the first transaction log backup initialize the log, then the appending t-log backups would actually begin at 7:40.  Have the noon differential fire at 12:02pm.  Stop the t-log backups at 6pm, start your nightly maintenance at 7pm.  You will develop a feel for how long each task takes based on the size of the database.

On to the job specifics.

My first step calls a file called NightlyDBCC.cmd, which is this:

osql -S"[servername]" -E -id:\dbccs\nightlydbcc.sql -od:\dbccs\dbccresult.txt -w200
osql -S"[servername]" -E -id:\dbccs\nightlysuspectpages.sql -od:\dbccs\suspectpagesresult.txt -w200

This .CMD file is two lines, each starting with osql -S.

The OSQL program is a SQL Server command line tool that lets you run T-SQL commands from the command line.  The switches that I use are:

  • –S: the name of your SQL Server
  • -E: use the current user’s credentials for authentication
  • -I: the input file that contains the commands to be run
  • -O where to write the output
  • -W: column width

Like most Windows utilities, you can get a full list of options by typing OSQL /? in a command prompt window.

The nightlydbcc.sql file contains the following:

use master
exec sp_MSforeachdb @command1="print getdate() print '? checkdb' DBCC CHECKDB(?)"

The stored procedure, sp_MSforeachdb, is an undocumented system stored procedure that runs a command string against every database on a server.  It accepts a question mark as a wildcard representing each database name.  So the string being run might be better visualized as

For Every Database:
print getdate()      --print the date for the output log
print '? checkdb'    --print header record of the database name
DBCC CHECKDB(?)      --run DBCC CHECKDB against every database

And the output is routed to dbccresult.txt.  Inspect that file every morning and you’ll have a good idea if your system is clean or if something nasty is lurking inside waiting to leap out, like the chestburster from the movie Alien.

NightlySuspectPages.sql has a little bit more going on.

SELECT 'Run date:', GETDATE();
--Server and SQL uptime
SELECT DATEADD(SECOND, -1 * (ms_ticks / 1000), GETDATE()) AS ServerStartDate,
    sqlserver_start_time AS SQLStartDate
FROM sys.dm_os_sys_info;
SELECT 'Suspect Pages' AS SuspectPages, *
FROM msdb.dbo.suspect_pages;
SELECT GETDATE() AS RunTime, LEFT(name, 15) AS DBName,
    LEFT(filename, 60) AS Filename, LEFT(status ,6) AS Status
FROM sysdatabases
WHERE dbid > 4
--    AND status <> 528
ORDER BY name;

The first code selects the time stamp of when the code ran in to the output file.  The second block prints when the server was last started and when SQL Server started up.  They should be fairly close, but could vary if you’d installed some SQL service packs or updates that could have restarted the SQL services.

The third select looks for any pages in msdb.dbo.suspect_pages.  These pages represent an I/O error, which could be a transient glitch or a warning of a problem in your disk system: a SAN or disk, whatever, it’s something to look at.  Take a look at Books Online for more information, just enter suspect_pages in to the search box.  Hopefully, normally, there won’t be any records here and you can ignore it.  But if something ever appears here, you have a problem!

And finally, the fourth select lists every database on that instance and shows the Status field.  It will be a number like 65536 or 65544.  That number will change depending on what options are selected for the database, like file recovery mode, etc.  The thing that I watch for is if it changes — if someone changes a database from full recovery to simple, I want to know that!

More on this soon.

Some useful SQL Server installation/configuration advice

I tended to do most of this automatically, but a reminder is good.

One thing that I love in recent versions of SQL Server, I think it started in the ’08 release, is the ability to use non-Exchange email accounts.  My current project is a hosted server that is not part of our domain, and we didn’t want to install Exchange on it just so it could send me emails: too many resources consumed in terms of disk and CPU.  Conveniently I could set it up with its own Gmail account and then configure the operators to send alerts to both my work email and my personal email along with the DBCC summary.

Here’s the article that I used that explains the process of setting up non-Exchange email for SQL Server:

The Importance of Running and Checking Backups and DBCCs

Most DBAs of any vendor’s system know how critical it is to back up your systems.  User databases, system databases, etc.  But do you ever restore them?  That’s the only way to know if your backup is good.  Of course, this represents a lot of difficulty if you’re managing a multi-terabyte system.  I personally don’t have any experience with such, the largest that I’ve worked with was on the order of 150 gig.

Recently there was a post on SQL Server Central by a poor and most unfortunate soul who discovered that his system had found corruption on a production database.  He had no backups, and apparently did not run DBCCs, and the error was one of those where your only options are to either restore from backup or to repair allowing dataloss.  And without having a working backup, well….  Not a good position to be in, having to tell your bosses that when you get the database running again that you might lose information and have inconsistent data.  Not good at all.

The sad part is that it’s not difficult to prevent.  In SSMS expand the Management tree (I’m using 2014, but it’s been around for a long time), right click on Maintenance Plans, click on Maintenance Plan Wizard, and you’re on your way to getting automatic database consistency checks (DBCCs).  AT THE LEAST you want to check Check Database Integrity, Update Statistics, Back Up Database (Full), and Back Up Database (Transaction Log).  Clicking on Next lets you change the order that the tasks are run.  Clicking on Next lets you select what databases this process will be run against.  THIS IS IMPORTANT: you should probably check All Databases.  System databases need love, too, particularly Master and MSDB.  Master contains the metadata that defines your installation, MSDB contains your jobs and maintenance plans, along with information on backups, log shipping, sysmail, syspolicy, SSIS, etc.  Model is important and should be checked, but it can be recreated from another installation, though you’ll lose customizations that you’ve done to it, if any.  But it isn’t as likely to get corrupted since it is not normally transactionally active.

(you don’t have to worry about TempDB: it’s dropped and recreated every time SQL Server starts)

After you’ve selected the databases, Next will let you select where the report on all this activity goes, likewise you can have it mailed if you have the Database Mail services configured and running on the server.

And now you’re done.  You’ve just taken some important steps towards protecting your data.  But you’ve only taken first steps: you still have to review the reports and verify that the backups can be restored!  If you don’t review the reports, how will you know your databases are clean and working properly?  If you don’t restore your backups, how will you know if you can if you have a serious server crash or corruption issue?


Here’s the kicker: I don’t do maintenance plans, particularly for DBCCs.  I’ll show you my personal method tomorrow.