Category Archives: SQL Maintenance

Find Out Who Changed the Database Recovery Model

— By Lori Brown @SQLSupahStah

I ran into a situation where we were working on a migration and had been directed to put all databases into FULL recovery model in anticipation of using log shipping to push databases to the new server. Once we are ready to go live on the new server the plan was to ship the last transaction logs and then restore them WITH RECOVERY in an effort to make the final cutover as quick as possible. Of course this means that we had to make sure that all databases were having regular log backups, which we did. Things were going along nicely until we started receiving log backup failure notifications.

Upon checking things, we found that one of the databases had been changed to SIMPLE recovery model. You can find this type of information in the default trace or you can simply scroll through the SQL error logs until you find the entry that you are looking for. If you have a busy instance that has a lot of entries in the error log, this can be a bit time consuming so I came up with a set of queries that will grab the error log entry and attempt to tie it to the info in the default trace so that it was easier to identify WHO was the culprit who made an unauthorized change to the database properties.


DECLARE @tracefile VARCHAR(500)



CREATE TABLE [dbo].[#SQLerrorlog](


[ProcessInfo] VARCHAR(10) NULL,





Valid parameters for sp_readerrorlog

1 – Error log: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…

2 – Log file type: 1 or NULL = error log, 2 = SQL Agent log

3 – Search string 1

4 – Search string 2


Change parameters to meet your needs


— Read error log looking for the words RECOVERY

–and either FULL, SIMPLE or BULK_LOGGED indicating a change from prior state


EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘FULL’



EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘SIMPLE’



EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘BULK_LOGGED’


UPDATE #SQLerrorlog

SET ProcessInfo = SUBSTRING(ProcessInfo,5,20)

FROM #SQLerrorlog

WHERE ProcessInfo LIKE ‘spid%’


— Get path of default trace file

SELECT @tracefile = CAST(value AS VARCHAR(500))

FROM sys.fn_trace_getinfo(DEFAULT)

WHERE traceid = 1

AND property = 2


— Get objects altered from the default trace

SELECT IDENTITY(int, 1, 1) AS RowNumber, *

INTO #temp_trc

FROM sys.fn_trace_gettable(@tracefile, default) g — default = read all trace files

WHERE g.EventClass = 164


SELECT t.DatabaseID, t.DatabaseName, t.NTUserName, t.NTDomainName,

t.HostName, t.ApplicationName, t.LoginName, t.SPID, t.StartTime, l.Text

FROM #temp_trc t

JOIN #SQLerrorlog l ON t.SPID = l.ProcessInfo

WHERE t.StartTime > GETDATE()-1 — filter by time within the last 24 hours



DROP TABLE #temp_trc




You can find more on the following:

sp_readerrorlog is an undocumented procedure that actually uses xp_readerrorlog –

sys.fn_trace_getinfo –

sys.fn_trace_gettable –

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Large MSDB From Hidden sysxmitqueue Table

— by Lori Brown @SQLSupahStah

Recently, I was checking on some of the servers that I manage and found that one had an 8GB msdb database. While that is not too bad it is kind of large so I figured that there must be something that needed to be cleaned up. I ran the Disk Usage by Top Tables report expecting to see a table that was huge to leap out at me. However, this time there was nothing. As a matter of fact, it looked like the largest table only had about 187MB of data in it. So, what in the world was going on?

I did some looking around and found that there is a hidden and undocumented table named sysxmitqueue that can sometimes become huge if you have event notifications set up. If there are errors in how the notification is set up, there will be lots of error messages in the hidden table and the transmission_queue view.

Run this query to find out of you have msdb bloat from the sysxmitqueue table:

USE msdb


— check for size of sysxmitqueue table

SELECT object_name(i.object_id) AS TableName,

i.[name] AS IndexName,

(sum(a.total_pages)*8)/1024 AS TotalSpaceMB,

(sum(a.used_pages)*8)/1024 AS UsedSpaceMB,

(sum(a.data_pages)*8)/1024 AS DataSpaceMB

FROM sys.indexes i

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

WHERE object_name(i.object_id) = ‘sysxmitqueue’

GROUP BY i.object_id, i.[name]


In my case, I found that this table had almost 8GB of data in it. Bingo!! Gotta clean it up now. Once again after doing some research, I found that lots of people have had problems with this table. Some say to set a new broker like so:



But that will cause problems if you do it in a production system because the service broker id will be different than is being used by anything else that has distributed conversations. If you do this, you could leave conversations stranded.

There is also a way to end conversations that takes a little time but will not cause issues if you are careful. I found that I could not query the sysxmitqueue at all but found that info from this table is also found in the sys.transmission_queue ( view. You can query it easily.

— get list of conversations

SELECT DISTINCT conversation_handle, to_service_name

FROM sys.transmission_queue

I used the above query to get a list of the conversations that needed to be cleaned up. The to_service_name column is the service name you created while setting up event notifications. You can use values from the conversation_handle column to tell an “END CONVERSATION…” statement ( which conversation exactly to end.

— end open conversations


If you have a lot of open conversations, running this manually can take a while but you won’t nuke anyone else’s conversations this way.

The last thing I need to do is figure out why my event notification is not ending conversations correctly. That will be another post later so….


For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

AlwaysOn 2016 – Maintenance

— by Ginger Keys

SQL Server AlwaysOn provides a fairly robust environment for disaster recovery; however you can never be too careful when it comes to technology and protecting your data. Regular maintenance routines (i.e. backups, etc.) need to be set up and performed on an ongoing basis, even when you have your databases in an AlwaysOn Availability Group.

AlwaysOn allows some maintenance routines to be performed on the secondary replicas, but determining which maintenance to perform on what replica can be somewhat confusing. We will discuss the following basic maintenance routines and where to execute them.

  • Backups
  • Integrity Checks
  • Index Maintenance
  • Update Statistics


Even though there is a copy of your database on the secondary replica(s), you should still continue to back up your databases and transaction logs on a regular basis.   Backups can be very resource intensive by putting strain on I/O and CPU especially when using compression. Because of this, offloading your backup operations to a secondary replica can greatly improve the performance of your primary production replica.

With an AlwaysOn group, you have several options of where to back up your databases as shown below. You can choose your backup preference when you set up your AlwaysOn group, or you can go back later and set your backup preferences through the AG properties.


Prefer Secondary – backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default behavior.

Secondary Only – backups will happen only on a replica that is designated as a secondary, and will execute in the order of the backup priority you set. Backups will not occur on the primary replica.

Primary – backups will only occur on the replica currently designated as primary.

Any Replica – backups will occur on the replica with the highest backup priority setting.

If you perform backups on your primary replica, Full, Differential, and Tlog backups can be run normally. If you perform backups on a secondary replica, there are some differences to be aware of:

  • You must configure your AlwaysOn group to specify where you want your backups to occur. You can do this through the New Availability Group setup GUI (as shown in picture above), or by using TSQL script (for more info click here These backup preferences will apply to both Full and Tlog backups.
  • The automated backup preference setting has no impact on ad-hoc backups. Manual ad-hoc backups will occur on whatever replica you perform the backup.
  • If you create your backup jobs through the Maintenance Plan, create the jobs on all replicas that have any intention of potentially performing backups. The job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function, and will not execute if the replica is not the preferred backup location. The job will not fail, it simply won’t execute. And in the event of failover the job will already exist on the replica the AlwaysOn Group has failed over to, and will execute accordingly.
  • Full Backups run on secondary replicas are copy only backups. Regular backups are not allowed. Copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not allowed on secondary replicas.
  • Tlog Backups only support regular backups on a secondary replica (copy only is not allowed). Tlog backups can be taken on any replica regardless of synchronization mode, and the log chain will remain consistent.
  • If databases exist in your SQL instance that are not part of the AlwaysOn Group, create separate maintenance jobs for those databases apart from the AlwaysOn databases so that the sys.fn_hadr_backup_is_preferred_replica function is not invoked. This way your non-AlwaysOn databases will not get lumped in with the AlwaysOn databases. Having separate maintenance routines for separate groups of databases can get a little hairy, as well as managing the jobs that need to run on each replica…for excellent guidance on this click here
  • Since the instances on the AlwaysOn replicas do not share an msdb database backups will not show up in the msdb backup tables.  This is important to know in the event you have to perform audits, or need to research backup history.


Integrity Checks

Perform Integrity Checks on all replicas if possible.

There are many differing opinions in the IT world on where to run the DBCC CHECKDB in an AlwaysOn Group.   Some believe integrity checks should be run on the secondary only, some believe it should be run wherever your database backups are happening, and some believe it should be run on all replicas.

SQL allows integrity checks to be offloaded to any of the secondary replicas in order to prevent resource contention on the primary replica, which keeps performance optimized. This is a good idea in theory, however if there is corruption in the data files of the primary database (assuming you have offloaded your integrity checks to a secondary) it will not be detected by the integrity check.   Even though data in the databases is the same across all replicas, the actual data files reside on the separate disk subsystems of each replica. The integrity check runs against the data files on each disk subsystem, and if your hardware or a file is corrupt on one of your replicas the integrity check run on another replica will not catch it.

Corruption on a primary does not propagate to the secondary because complete database pages are not sent, only the tlog records which only describe the physical changes made to the pages…they don’t contain the actual pages. Performing an integrity check on a primary replica doesn’t detect corruption on a secondary and vise-versa.   They are separate databases with their own data files kept in sync through transaction logs that describe the physical changes.

If your organization is extremely sensitive to performance for your AlwaysOn group, we recommend you restore a copy of your database backups to a server not in your AlwaysOn group (like a test server) and run the integrity checks there. This will provide a consistency check of your data, will ensure your backups are viable, and will eliminate overhead of running CHECKDB on the primary.

Index Maintenance

Perform Index Maintenance on primary replica

Because secondary databases are read-only, index maintenance must be performed on the primary replica. Index maintenance generates large amounts of logged changes by design and consequently will result in large transaction logs being replicated to the secondaries. Also index maintenance can cause the primary log files to grow large because the log cannot be truncated until redo has completed the changes in all secondary replicas.

Some ways to reduce resource contention for index maintenance is:

  1. Schedule during off peak times if possible
  2. Break up maintenance into smaller phases, spread out over time period
  3. Use MAXDOP to limit the number of processors used for the operation
  4. Rebuild/reorganize based upon specified criteria:
    1. Ignore heaps or small tables
    2. Rebuild/reorganize based upon fragmentation levels acceptable to your environment

If you are fortunate enough to have a large maintenance window to be able to rebuild your indexes on a daily basis, there is not much need to update statistics as rebuilding indexes does this by design. The frequency of your maintenance routines will depend on each individual SQL environment. As a general rule with our clients, we rebuild indexes once per week and update statistics every day…but again, your environment may require something different.

Update Statistics

Update Statistics on the primary replica.

SQL Server creates statistics on database tables and indexes that gives the query optimizer information about the distribution of data. This allows SQL to compile the most efficient query plan in order to improve query performance and run the queries as quickly as possible.

In an AlwaysOn Group of databases, statistics that are created and updated on the primary replica are persisted to the secondary replicas as part of the data replication of the transaction logs records.

But what if you’re using your secondaries for read-only operations and the secondary databases need different statistics than the primary? Databases on the secondary replicas are restricted to read-only access so statistics cannot be created on them! If the secondary replicas cannot create statistics, then performance of read-only workloads can suffer.

Thankfully SQL will create and maintain statistics on the secondary replicas in tempdb by using the suffix _readonly_database_statistic appended to the name of the temporary statistic. This keeps it separate from the permanent statistics that have been sent over from the primary. These temporary statistics can only be created by SQL Server, and you need to allow auto update and auto creation of statistics.

For more information on SQL statistics, click here


This article covers a high-level overview for performing some basic maintenance on your AlwaysOn databases. These are suggestions based on a simple AlwaysOn environment. There are always exceptions to these rules and circumstances where a system is set up in a more complex manner.   For example, you may want Tlog backups to occur on a different replica than the Full backups. You may have your AlwaysOn group also involved in log shipping or some other functionality that requires more or different maintenance routines.   There are methods to accommodate every environment, but is beyond the scope of this article. Hopefully these topics have given you a basic foundation for maintaining your AlwaysOn environment.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

Tip of the Month: WAIT_AT_LOW_PRIORITY option for Index Rebuilds

— By Lori Brown  @SQLSupahStah

Available in SQL Server 2014 Enterprise Edition since can only be done using ONLINE rebuilds.

Index rebuilds have been enhanced with the WAIT_AT_LOW_PRIORITY option. It is used to set the duration in minutes to wait as well as behavior when blocking happens. When setting WAIT_AT_LOW_PRIORITY you also must set MAX_DURATION and ABORT_AFTER_WAIT to tell SQL how long to wait for blocking and then what to do after that amount of time has expired and blocking is still happening.

ALTER INDEX ix_RoadRaces_State

on dbo.RoadRaces


  • NONE = Keep waiting
  • SELF = Give way to the user queries
  • BLOCKERS = Kill all user transactions that block the online index rebuild

Large MSDB Database From sysmaintplan_logdetail Table

— By Lori Brown  @SQLSupahStah

I recently received a panicked call from a client who had a SQL instance go down because the server’s C drive was full. As the guy looked he found that the msdb database file was 31 GB and was consuming all of the free space on the OS drive causing SQL to shut down. He cleaned up some other old files so that SQL would work again but did not know what to do about msdb.

As we looked at it together I found that the sysmaintplan_logdetail table was taking all the space in the database. The SQL Agent had been set to only keep about 10000 rows of history but for some unknown reason the table never removed history. After consulting MSDN I found this code did the trick for truncating this table.

USE msdb


ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];


ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];


TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail;


TRUNCATE TABLE msdb.dbo.sysmaintplan_log;


ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);


ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])

REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;


After the table was truncated we were able to shrink the database to about 1 GB. For the record – I hate, hate, hate to shrink databases but there were no other options left to us and we had to clear out some room on the drive.

Now with the crisis averted we checked the SQL Agent settings and found that the box to remove agent history was not checked.


We checked it, hit OK then opened the SQL Agent properties again only to find that the box was unchecked. After doing some research I found that this is a bug that has not been resolved even in SQL 2014. Awesome, huh?!

If you check the link there is a workaround posted. I have tested it and found that it takes a super long time to run sp_purge_jobhistory and my test server only has 2 jobs that would have any history at all. So, use the workaround if you feel brave. Hopefully Microsoft will actually fix this some time. Until then, keep an eye on your msdb database size.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Backup Database Commands

SQL Server Administration: Backup database to one file or multiple files. Use these commands to back your databases up without using a maintenance plan. Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. Good to use when large backups are hard to move across the network.

— Backup a database to a single backup file

DECLARE @Timestamp VARCHAR(50)
— create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-‘,”),’:’,”),’ ‘,”)
— create backup file string
SET @BackupFile = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
— backup the database
BACKUP DATABASE AdventureWorks2012
TO DISK = @BackupFile
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,

— Backup a database to multiple backup files

DECLARE @BackupFile1 NVARCHAR(200)
DECLARE @BackupFile2 NVARCHAR(200)
DECLARE @BackupFile3 NVARCHAR(200)
DECLARE @BackupFile4 NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
— create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-‘,”),’:’,”),’ ‘,”)
— create backup file strings
SET @BackupFile1 = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
SET @BackupFile2 = ‘D:\SQLBackups\AdventureWorks2012_Full2_’+@Timestamp+’.bak’
SET @BackupFile3 = ‘D:\SQLBackups\AdventureWorks2012_Full3_’+@Timestamp+’.bak’
SET @BackupFile4 = ‘D:\SQLBackups\AdventureWorks2012_Full4_’+@Timestamp+’.bak’
— backup the database
BACKUP DATABASE [AdventureWorks2012]
TO DISK = @BackupFile1,
DISK = @BackupFile2,
DISK = @BackupFile3,
DISK = @BackupFile4
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,

Remove maintenance plans left after a server rename.

Many times when upgrading SQL Server or migrating to new hardware, it is necessary to rename the server when it’s ready to be used as production.  However, if any maintenance plans created before the server was renamed are still present after the rename, you may find that trying to delete the old jobs results in error 547 (below).

Drop failed for Job ‘User Databases – Full Daily Backup.Subplan_1’. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.
The statement has been terminated.
The statement has been terminated. (Microsoft SQL Server, Error: 547)_________________________________________________________________________ 

 Here are the steps to correct the problem:

 — Find the maintenance plan name and id that you want to delete.

— Write down the id of the one you want to delete.

SELECT name, id FROM msdb.dbo.sysmaintplan_plans


 — Place the id of the maintenance plan you want to delete

— into the below query to delete the entry from the log table

DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ‘<<id from 1st query>>’

 — Place the id of the maintenance plan you want to delete

— into the below query and delete the entry from subplans table

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ‘<<id from 1st query>>’

 — Place the id of the maintenance plan you want to delete

— into the below query to delete the entry from the plans table

DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ‘<<id from 1st query>>’

 Now you can delete the jobs from Management Studio. 

What’s Causing Files to Automatically Grow

Every DBA has encountered a database that has a mystery statement that will unexpectedly cause either the transaction log or a table to grow out of control and fill the drive.  Here is a way to find out who is running what statement that is causing the problem.  Find out what statements are causing database files to automatically grow.

 /*  Step 1 – Capture a Profiler or server side trace with these events:

      Event 41 = SQL:StmtCompleted

      Event 45 = SP:StmtCompleted

      Event 92 = Data file auto growth event

      Event 93 = Log file auto growth event

* Make sure to include these columns:





—    Step 2 – Load the trace into a table:

SELECT*INTO TraceTable FROM::fn_trace_gettable(<<path to trace file>>,DEFAULT)


—    Step 3 – Make a column to hold part of the textdata for search purposes



UPDATE TraceTable SET QueryText =CONVERT(VARCHAR(MAX), TextData)


—    Step 4 – Get all spids and trasactionids that are associated with auto growth


INTO #GrowTrans

FROM TraceTable

WHERE EventClass IN(92, 93)


—    Step 5 – Get the info on statements called by the spid and transactionid associated with auto growth


FROM TraceTable t

JOIN #GrowTrans g ON (t.SPID = g.SPID AND t.TransactionID = g.TransactionID)



Attempt to Recover a Suspect Database

Databases will go into suspect state for a multitude of reasons. If you find your database in SUSPECT state, check all error logs and the Windows Event Log for any related errors, and try to resolve them. This is a disaster situation!  Refer to the script below for help.

SQL Server Administration:  You can try to save data by creating an empty database and copying tables to it after placing the database into EMERGENCY and then SINGLE_USER state. Most of the time, a suspect database has to be restored to be able to be used again. However, sometimes it is possible to repair a suspect database. I recently did just that for a client, and recovered 4 suspect databases.

Use the script below to check the integrity of the database or to repair it. Running an integrity check will tell you where the errors are and what the minimum repair you can do is. Always make sure you have database and transaction log backups so you can recover a failed database to point in time, if necessary. 

— Attempt repair suspect database

USE master;


— Set database in EMERGENCY read only state



— Set database in SINGLE_USER state





— DBCC CHECKDB (MyDB, REPAIR_REBUILD) WITH NO_INFOMSGS  — Repair database with no dataloss

— DBCC CHECKDB (MyDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS  — Repair database with possible dataloss


— Set database in MULTI_USER state



September 2010 Tip of the Month

Performance Monitor: Monitor the performance counters SQLServer:Buffer Manager (Page reads/sec) and SQLServer:Buffer Manager (Page writes/sec) to detect poor performance. These counters measure physical IOs and not logical IOs. Heavy activity can indicate lack of database memory, a poor table implementation or can show that applications are not accessing a database correctly. Monitoring these counters will give insight into changes in workload as well. Many times, alot of writes will occur at the same time that poor performance is noticed.

For both Page reads/sec and Page writes/sec performance thresholds are: 
> 1000 – Very High Activity 
> 500 – High Activity 
> 90 – Elevated Activity