DallasDBAs.com

SQL Server Database Consulting

SSMS

SQL Server Maintenance Plans

by Leave a Comment

If you’re a DBA, sysadmin, IT manager, or Accidental DBA, you’ve probably seen SQL Server’s built-in Maintenance Plans. They live right there in SSMS under the “Management” node, quietly offering to take care of your backups, index maintenance, integrity checks, random T-SQL tasks and more.

They look simple. They are simple. But that doesn’t mean they’re always the best solution.

 

What Maintenance Plans Can Do

Microsoft added Maintenance Plans to make basic tasks like backups accessible, especially in environments without a dedicated DBA.
The wizard-driven interface lets you:

  • Schedule Full, Differential, and Transaction Log backups
  • Perform index maintenance
  • Run DBCC CHECKDB
  • Execute basic cleanup tasks
  • Run T-SQL commands as part of the “flow”

And it all runs under SQL Server Agent so you can automate with just a few clicks.

 

What Maintenance Plans Can’t Do Well

Ease of use comes at the cost of flexibility.

Here’s where they fall short:

  • Limited control: You can’t fine-tune logic or dynamically skip steps based on conditions. Not without a lot of fiddling around in the SSIS canvas at least
  • LOTS of clicking, dragging, dropping, Googling, etc. if you are new to MPs. The Wizard will make some basic decisions for you.
  • Logging is basic: Failures often go unnoticed unless you’re checking manually. If a MP job fails, the reason is in the MP history, not the job history. Makes perfect sense.
  • Weird defaults: If you choose to create an index rebuild plan, it defaults to 30% or more fragmentation, and 1000 PAGES, that’s a LOT of time spent on teeny tiny 8MB indexes. Unless a page isn’t 8KB anymore.

If you’re working in a mission-critical or highly regulated environment, these gaps can cause trouble.

 

They’re Not Useless

Don’t get me wrong. Maintenance Plans have their place.

Especially if you’re:

  • Running one SQL Server instance with a couple of databases
  • Trying to get any backups in place after years of neglect. Any backup is better than no backup…but that’s a different post
  • Buying time until a better strategy is in place

 

Step-by-Step: How to Create a Full Backup Maintenance Plan

Let’s walk through the simplest case: backing up all user databases once a day.

  1. Launch the Wizard
  • In SSMS, expand Management
  • Right-click Maintenance Plans
  • Choose Maintenance Plan Wizard
  1. Name & Schedule the Plan
  • Click Next on the welcome screen
  • Name your plan (e.g., Nightly Full Backup)
  • Choose Single schedule for the entire plan
  • Click Change to set the schedule:
    • Frequency: Daily
    • Time: 2:00 AM (or another low-traffic time)
    • Recurs every: 1 day
  • Click OK, then Next
  1. Choose Task Type
  • Check only Back Up Database (Full) → Next
  1. Configure Backup Task
  • Databases: Select All user databases (or hand-pick)
  • Backup to: Disk → Choose or create a folder (e.g., D:\SQLBackups\)
    • URL is an option, for cloud storage.
  • Optional:
    • Create a sub-directory per database
    • Set backup expiration
    • Enable checksum
  • Click Next
  1. Reporting (Optional)
  • Save report to a text file or enable email notifications
    • The default is the same directory your SQL ERRORLOGs are living in.
  1. Finish
  • Review the summary
  • Click Finish to create and schedule the plan

Done. Backups will now run on schedule, and you’ve taken a first step.

But now you need to repeat that process for all the other maintenance tasks (Log backups, stats maintenance, CheckDB, etc.)

 

There’s a Better Way

Once you’re past the basics, most SQL Server professionals recommend moving on from Maintenance Plans. Here’s what they use:

Ola Hallengren’s Maintenance Solution

Free, flexible, and widely used in the SQL community.

  • Modular design
  • Intelligent scheduling
  • Excellent logging
  • Works with the SQL Agent
  • VERY simple setup. Please run this against a ‘DBA’ database, not master or msdb.

SQL Server Agent Jobs with Custom T-SQL

More setup time, but gives you full control over backup paths, logging, and error handling.

Third-Party Tools

If budget allows, options like Redgate SQL Backup or Idera SQL Safe Backup can offer robust UIs, centralized management, and alerts.

 

The Bottom Line

Maintenance Plans are training wheels.

They’ll get you moving, but they’re not built for high-speed, high-traffic, or high-stakes environments.

If you’re serious about protecting your data, build a better backup strategy. But if you’re just getting started and need a win?

SQL Server Errorlog: Not Just for Errors!

by Leave a Comment

A Goldmine of Clues You Might Be Ignoring

SQL Server’s Error Logs often get overlooked, until something breaks. But if you know how to use them, they can tell you what went wrong before you’re knee-deep in outage mode.

Whether you’re a sysadmin who inherited SQL or an MSP juggling multiple environments, understanding the ErrorLog is low effort, high return.

Let’s decode the basics.

What Is the SQL Server Error Log?

It’s not a crash dump. Its not even just errors. The errorlog is a plain text file that tracks:

  • Startup and shutdown events
  • Login successes and failures (settable)
  • Backup and restore activity
  • Deadlocks (if enabled)
  • Corruption errors
  • Memory dump ingo
  • Full data and log file warnings
  • And more!

 

It’s like a flight recorder for SQL Server. If something bad happens, it probably shows up here.

How Many Logs Are There? And Where Are They?

By default, SQL Server keeps:

  • 7 error logs (Errorlog (current) through Errorlog.6)
  • Each gets recycled when the SQL Server service restarts or the logs are manually cycled
  • Located in the same folder as your SQL Server binaries (or wherever SQL was installed)

 

You can view them in SQL Server Management Studio under Management > SQL Server Logs or directly from the file system.

Changing the Retention Settings

Seven logs may not be enough if you cycle the service frequently. Here’s how to increase retention:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 0
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 25
GO

If you don’t like registry edits, use SSMS:

Management>>SQL Server Logs>>Right-click: Configure. Check the “Limit the number of error log…” box, change the “Maximum number of error log files” to your preference and click OK

 

You can also cycle the log manually without restarting SQL Server:

EXEC sp_cycle_errorlog;

 

Do this regularly (e.g., weekly) via SQL Agent job so each log stays readable and relevant.

I like weekly cycling, 25 retention so I have 6 months of reasonably sized log files.

Finding the Signal in the Noise (Filtering)

The error log can be…wordy. Thankfully, SSMS lets you filter by multiple options:

 

For command-line lovers:

EXEC sp_readerrorlog 0, 1, 'Login failed';

This searches the current log for login failures.

Want to scan older logs? Change the first parameter:

EXEC sp_readerrorlog 3, 1, 'backup';  -- Searches the 4th oldest log

(xp_readerrolog exists, but is undocumented. Use sp_readerrorlog instead)

More Filtering:

If you can, log only failed Logins, not failed and successful. Your industry may require other setups.

Use trace flag 3226 to NOT log successful transaction log backups. Those are already being logged in the msdb tables and job history.

 

The Bottom Line: Set It and Watch It

  • Check retention: 7 logs may not be enough
  • Automate log cycling so logs don’t get so big they are unreadable
  • Use filters to quickly find useful info

Some monitoring tools read the logs for you, but you should still know how

 

 

Where Is My SQL Server Errorlog?

by Leave a Comment

If you need to find the SQL Server ErrorLog in a hurry and don’t want to spend 30 minutes drilling into every drive on the server:

“I don’t watch videos” version:

  • Right-click the instance
  • Choose ‘Facets’ from the shortcut menu
  • Look for ‘ErrorLogPath’:

 

 

 

That’s it…let me know if you already knew this or not!

Thanks for reading!

Kevin3NF

Server Review Essentials for Accidental and Junior DBAs—Client Onboarding (Part 3)

by Leave a Comment

The next post of this series is finally ready to fly. I apologize for the delay between posts, but between the rolling blackouts in Texas during the 2021 Snowpaclypse and a personal health issue, getting this written was a struggle. Let’s get back to it and take a look at the final steps in the client onboarding process.

(Also see Part 1 and Part 2 of this series)

Building a Maintenance Job List

Now that we’ve taken a look at getting the biographical data for your servers gathered, the next step is to take an inventory of the maintenance jobs that are already set up and running on your servers. We mainly want to see if proper backups are taking place, and whether or not common tasks such as integrity checks and index maintenance jobs exist. At this point we’re not concerned whether or not things are set up correctly, we only want to assess what is or is not happening so we can put together an effective plan for future data protection and preservation.

Gathering this information is easy, while deciding what to do with it will take some thought.

This can be somewhat tedious if you’re managing several instances of SQL Server, but here’s a quick way to find and chart the information you’re looking for.

In SSMS, open up your target instance in Object Explorer, then drill down to SQL Server Agent >> Jobs >> Job Activity Monitor.

This tutorial assumes the SQL Server Agent is running on your instance.

Double-click the Job Activity Monitor to open up a new window that should look something like this:

Now you can copy/paste this information into an Excel spreadsheet if you’d like to have a local copy of this information to work with. You’ll be limited to keyboard shortcuts to select all and copy—there’s no right-click “Copy with headers” available in this window.

You can see on my machine I have Ola Hallengren’s SQL Server Maintenance Solution installed to take care of all the basic maintenance tasks, as well as some addition useful maintenance jobs. In your situation, you may find similar results, a collection of Maintenance Plan jobs, or a different set of jobs altogether. Hopefully, you find something that’s already been set up to take care of backups, integrity checks, and index maintenance.

From here, you can see what jobs are active, when those jobs last executed successfully, and get a sense of the job’s schedule. Remember, at this stage of the onboarding process we’re more concerned with what jobs are set up rather than if they are set up properly.

Gathering this information will allow you to make informed recommendations and decisions going forward from this point. You’ll begin to be able to determine if the current job maintenance schedule meets your company’s Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). If so, great, you’re in good shape! If not, then you’ve got some work ahead of you to get your maintenance jobs in order.

Once you have this information gathered and collated, you’ll be ready to tackle the next step in the onboarding process, which is building a backup frequency list. We’ll take a look at that issue in the next post.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.



Home Blog About Privacy Policy