Category Archives: Maintenance

Asynchronous Transactions

Business Case: An organization frequently creates databases ad-hoc and wants them to be backed-up immediately upon creation. This reduces risk by automating what would otherwise be another step for the database creator to remember to perform. Additionally, the existing maintenance actions include a full backup daily and a log backup every fifteen minutes. The challenge is that a log backup will fail if a full backup has not already been performed on the database. Given the frequency and ad-hoc nature of database creation, it is important to ensure a full backup is taken as soon as a database is created.

Environment: On the surface, this business case seems like a good match for a server-level trigger on the CREATE_DATABASE statement, with the trigger action performing the backup. However, that will not work because a backup operation is not permitted within a transaction containing other statements; triggers are executed as part of the calling statement (because the calling transaction must be rolled-back if the trigger action fails). Thus, we will need to be able to perform a second action as reliably as if it were part of a database transaction, but it cannot actually exist within the same literal transaction.

Approach: To perform this sort of asynchronous transaction, we will use event notification and the service broker to perform a full backup whenever a database is created.

Solution: Let us start by creating a stored procedure that will read a yet-to-be-defined service broker queue whenever a message is placed in it. The procedure will consume the message, perform the database backup, and then end the service broker conversation.

You’ll notice that this stored procedure makes use of a procedure we created previously to perform our backup operations. It is available here.

Also note that this yet-to-be-defined queue will be created in the msdb database. We are creating it there because it roughly aligns with part of what msdb is intended to do (manage the SQL agent); also, because msdb is a system database, it is conceptually independent from the user databases for which this solution is intended to manage initial backup creation.

-- Create a procedure to consume service broker queued messages
CREATE PROCEDURE [dbo].[uspBrokerBackup]
AS
BEGIN
    -- Initialize variables for later use
    DECLARE
        @uiHandle UNIQUEIDENTIFIER
        , @strMsg NVARCHAR(MAX)
        , @sysMsgName SYSNAME
        , @sysDBName SYSNAME
    ;
    -- Consume a message from the queue, waiting no longer than one second
    WAITFOR (
        RECEIVE TOP(1)
            @uiHandle = [conversation_handle]
            , @strMsg = message_body
            , @sysMsgName = message_type_name
        FROM msdb.dbo.queBackupDB)
        , TIMEOUT 1000 -- in milliseconds
    ;
    -- Validate message type and contents
    IF @sysMsgName = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    AND CHARINDEX(N'<DatabaseName>', @strMsg) != 0
    AND CHARINDEX(N'</DatabaseName>', @strMsg) != 0
    BEGIN
        -- Extract the database name from the message body
        SET @sysDBName = SUBSTRING(
            @strMsg
            , CHARINDEX(N'<DatabaseName>', @strMsg)
                + LEN(N'<DatabaseName>')
            , CHARINDEX(N'</DatabaseName>', @strMsg)
                - CHARINDEX(N'<DatabaseName>', @strMsg)
                - LEN(N'<DatabaseName>'))
        ;
        -- Perform the full backup; click here for uspMxBackup
        EXECUTE master.dbo.uspMxBackup
            @strBackupType = 'FULL'
            , @sysServerName = NULL
            , @sysDatabaseName = @sysDBName
        ;
        -- End the service broker conversation
        END CONVERSATION @uiHandle;
    END
END

Now let’s setup the service broker. We only need to create a couple of the service broker objects; we’ll use some system objects for the rest. We’ll need a queue to store and process notifications of database creation, a service to receive notifications and put them in the queue, and an event notification to catch the create database actions and send them to the service. Don’t worry: it only sounds easy because it is. 🙂

-- If the service already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.services
    WHERE name = N'//SQLEXPRESS/InternalAction/BackupDB')
DROP SERVICE [//SQLEXPRESS/InternalAction/BackupDB]
;

-- If the queue already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.service_queues
    WHERE name = N'queBackupDB')
DROP QUEUE [queBackupDB]
;

-- Create the queue to store and process the notifications
CREATE QUEUE [queBackupDB]
WITH
    ACTIVATION (
        PROCEDURE_NAME = [master].[dbo].[uspBrokerBackup]
        , MAX_QUEUE_READERS = 1
        , EXECUTE AS N'dbo')
;

-- Create the service to send notifications to the queue
CREATE SERVICE [//SQLEXPRESS/InternalAction/BackupDB]
ON QUEUE queBackupDB ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
;

-- If the event notification already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.server_event_notifications
    WHERE name = N'evntCreateDatabase')
DROP EVENT NOTIFICATION evntCreateDatabase
ON SERVER
;

-- Get the msdb service broker id
DECLARE @uiBrokerID UNIQUEIDENTIFIER = (
    SELECT service_broker_guid
    FROM sys.databases
    WHERE name = N'msdb')
;

-- Build the SQL to create the event notification
DECLARE @strSQL NVARCHAR(MAX) =
N'CREATE EVENT NOTIFICATION evntCreateDatabase
ON SERVER
FOR CREATE_DATABASE
TO SERVICE
    ''//SQLEXPRESS/InternalAction/BackupDB'' -- name of service broker service
    , ''' + CONVERT(CHAR(36), @uiBrokerID) + N''' -- service_broker_guid for msdb db
;'
;

-- Create the event notification
EXECUTE sp_executesql @strSQL;

And, there you have it. When the create database event occurs, a notification message is sent via a service to a queue, which then uses a stored procedure to process each notification message. If useful in your environment, feel free to add defensive coding, such as ensuring notification messages are processed successfully, retrying if they are not, notifying an operator if any piece fails, etc. Enjoy!

Advertisements

Backup Databases on Remote Server

As the last in a three-post series on database maintenance stored procedures (the other two being CHECKDB and file clean-up), the following stored procedure extends a T-SQL script we discussed previously that backs-up all databases on a server. The procedure below adds the capability of specifying the backup type as either FULL or LOG, as well as specifying the backup directory and, optionally, a remote server on which to run the script.

This procedure makes use of linked server functionality, so be aware of security contexts and credentials. Specifically, ensure the account under which this procedure executes has the necessary permissions on the target server on which the backups will be performed, as well as write permissions to the file path to which the backup files will be saved.

We’ll create the procedure within our maintenance schema (mx).

CREATE PROCEDURE mx.uspBackup
    @strBackupType VARCHAR(4) = N'FULL' -- options: FULL, LOG
    , @strBackupDirectory VARCHAR(50) -- the local file path
    , @sysServerName SYSNAME = NULL -- optional remote server name
AS
BEGIN
    -- Create a linked server, if needed
    IF @sysServerName IS NOT NULL
    BEGIN
        -- Create an effectively-unique name for a linked server
        DECLARE @sysServer SYSNAME = @sysServerName
            + N'_'
            + CONVERT(CHAR(36), NEWID())
        ;

        -- If the linked server already exists, drop it
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id <> 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;

        -- Add the linked server
        EXECUTE master.dbo.sp_addlinkedserver
            @server = @sysServer
            , @srvproduct = N'SQL Server'
        ;

        -- Add login information to the linked server
        EXECUTE master.dbo.sp_addlinkedsrvlogin
            @rmtsrvname = @sysServer
            , @useself = N'True'
            , @locallogin = NULL
            , @rmtuser = NULL
            , @rmtpassword = NULL
        ;
    END

    -- Create the file directory
    EXECUTE master.dbo.xp_create_subdir@strBackupDirectory;

    -- Declare and initialize the backup SQL
    -- Exclude tempdb from all backup operations
    DECLARE @strSQL NVARCHAR(MAX) = N'
        IF ''?'' <> ''tempdb'' -- Do not execute against tempdb'
    ;

    -- If performing a log backup, only include simple recovery model dbs
    IF @strBackupType = N'LOG'
        SET @strSQL += N'
AND N''SIMPLE'' <> ( -- Confirm recovery model for log backups
      SELECT recovery_model_desc
      FROM sys.databases
      WHERE name = N''?'')'
        ;

    -- Define the backup path
    SET @strSQL += N'
BEGIN
      -- Define the backup path
      DECLARE @strPathFull NVARCHAR(128) =
            N''' + @strBackupDirectory + '\?\?''
            + N''_backup_''
            + CONVERT(CHAR(8), GETDATE(), 112)
            + N''_''
            + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 114), '':'', '''')'
    ;

    -- Add the backup type file extension and backuyp type command
    IF @strBackupType = N'FULL'
        SET @strSQL += N'
            + N''.bak''
;
      -- Backup the database
      BACKUP DATABASE [?]'
        ;
    ELSE IF @strBackupType = N'LOG'
        SET @strSQL += '
            + N''.trn''
      ;
      -- Backup the log
      BACKUP LOG [?]'
        ;

    -- Continue the backup T-SQL command
    SET @strSQL += N'
      TO DISK = @strPathFull
      WITH
            NOFORMAT
            , NOINIT'
    ;

    -- Set the backup name based on the backup type requested
    IF @strBackupType = N'FULL'
        SET @strSQL += N'
            , NAME = N''?-Full Database Backup'''
;
    ELSE IF @strBackupType = N'LOG'
        SET @strSQL += N'
            , NAME = N''?-Log Backup'''
        ;

    -- Continue the backup T-SQL command
    SET @strSQL += N'
            , SKIP
            , NOREWIND
            , NOUNLOAD
            , STATS = 10
      ;
     
      -- Declare a variable to retrieve backup position
      DECLARE @intPos INT;
     
      -- Initialize the backup position variable
      SELECT @intPos = position
      FROM msdb.dbo.backupset
      WHERE
            database_name = N''?''
            AND backup_set_id = (
                  SELECT MAX(backup_set_id)
                  FROM msdb.dbo.backupset
                  WHERE database_name = N''?'')
      ;
     
      -- Check for error in initializing the backup position variable
      IF @intPos IS NULL
            RAISERROR(N''Backup verification failed for database ''''?''''.'', 16, 1)
      ;
     
      -- Verify the backup was created successfully
      RESTORE VERIFYONLY
      FROM DISK = @strPathFull
      WITH
            FILE = @intPos
            , NOUNLOAD
            , NOREWIND
      ;
END'
    ;

    -- Execute the SQL against each database, remote if needed
    IF @sysServerName IS NULL
        -- Execute locally
        EXECUTE sp_MSforeachdb@strSQL;
    ELSE
    BEGIN
        -- Prepare SQL for remote execution
        DECLARE @strSQL2 NVARCHAR(MAX) =
            N'EXECUTE (N''EXECUTE sp_MSforeachdb N'''''
            + REPLACE(@strSQL, N'''', N'''''''''')
            + N''''''') AT '
            + N'[' + @sysServer + N']'
        ;

        -- Execute remotely
        EXECUTE sp_executesql@strSQL2;

        -- Drop the linked server
        IF EXISTS(SELECT name FROM sys.servers WHERE server_id <> 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
    END
END

CheckDB on Remote Server

While we are on the subject of maintenance scripts (see previous post on file clean-up), here is one to run a CHECKDB against all databases, even on a remote server. We’ll make use of the sp_MSforeachdb system stored procedure as well as linked servers.

Generally linked servers are unpopular as they can present a security risk due to embedded credentials; however, we will minimize that risk by specifying that credentials are simply passed through to the linked server rather than embedding alternate credentials within the linked server definition. Since we are passing through credentials, you should ensure that the credentials under which the CHECKDB procedure will execute are sufficient to access and run the commands on the linked server.

We’ll create this stored procedure within our maintenance schema (mx).

-- Create a stored procedure in our maintenance schema
CREATE PROCEDURE mx.uspCheckDB
    @sysServerName SYSNAME = NULL -- server name if to be executed remotely
AS
BEGIN
    -- Create a linked server, if needed
    IF @sysServerName IS NOT NULL
    BEGIN
        -- Create an effectively-unique name for a linked server
        DECLARE @sysServer SYSNAME =
            @sysServerName
            + N'_'
            + CONVERT(CHAR(36), NEWID())
        ;
        -- If the linked server already exists (however unlikely), drop it
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id != 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
        -- Add the linked server
        EXECUTE master.dbo.sp_addlinkedserver
            @server = @sysServer
            , @srvproduct = N'SQL Server'
        ;
        -- Add login information to the linked server
        EXECUTE master.dbo.sp_addlinkedsrvlogin
            @rmtsrvname = @sysServerName
            , @useself = N'True'
            , @locallogin = NULL
            , @rmtuser = NULL
            , @rmtpassword = NULL
        ;
    END
    -- Initialize variables
    DECLARE @strSQL NVARCHAR(MAX) = N'-- Set the database context
USE [?];
-- Run the CHECKDB command
DBCC CHECKDB(N''?'') WITH NO_INFOMSGS;'
    ;
    -- Execute the SQL against each database, remote if needed
    IF @sysServerName IS NULL
        -- Execute locally
        EXECUTE sp_MSforeachdb @strSQL;
    ELSE
    BEGIN
        -- Prepare SQL for remote execution
        -- Wrap the existing SQL within an EXECUTE command and specify
        -- to execute that command at the linked server; also need to
        -- replace single quotes with quadruple quotes as the command
        -- will execute at the third nested level
        DECLARE @strSQL2 NVARCHAR(MAX) =
            N'EXECUTE (N''EXECUTE sp_MSforeachdb '''''
            + REPLACE(@strSQL, '''', '''''''''')
            + N''''''') AT '
            + N'[' + @sysServer + N']'
        ;
        -- Execute remotely
        EXECUTE sp_executesql @strSQL2;
        -- Drop the linked server
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id != 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
    END
END

Backup File Clean-Up

Here is a handy little stored procedure to delete backup files from your file system. It makes use of the undocumented system stored procedure xp_delete_file; I’ve provided some information on the parameters the procedure accepts in the example below.

FYI, xp_delete_file will only delete SQL backup files or SQL maintenance plan reports, so don’t try to use it to delete non-SQL files on your file system. And it doesn’t simply look at the file extension either… it actually parses the file header information to determine the file type.

While this sample clean-up procedure is rather simple, it could easily be extended to accept additional parameters to calculate the date boundary, whether to delete the backup history as well as (or instead of) backup files, whether to traverse sub-folders, and so on.

For this example we’ll create the procedure in our maintenance schema (mx). Once created you can simply run it on-demand, through the SQL Agent, or via the windows task scheduler with the command-line SQL utility.

Enjoy!

CREATE PROCEDURE mx.uspCleanUp
    @strDirectory NVARCHAR(50)
    , @strFileExtention CHAR(3) = 'bak' -- options: bak, trn
    , @dtDeleteBefore DATE = NULL
AS
BEGIN

    -- Declare and initialize variables
    DECLARE @dt DATE =
        ISNULL(@dtDeleteBefore, DATEADD(WEEK, -2, GETDATE()));

    -- Delete the backup history older than the specified date
    EXECUTE msdb.dbo.sp_delete_backuphistory
        @oldest_date = @dt
    ;

    -- Delete the backup files older than the specified date
    EXECUTE master.dbo.xp_delete_file
        0 -- file type; 0 = backup, 1 = report
        , @strDirectory -- directory in which to look for files
        , @strFileExtension -- extension of files to look for (no period!)
        , @dt -- delete files older than this date
        , 1 -- traverse sub-folders; 0 = FALSE, 1 = TRUE
    ;

END

Backup All Databases

Here is a handy, little TSQL script that can be used to backup all databases on a server. Why would you need such a script? How about when you have an instance of Express Edition, which does not include support for maintenance plans or scheduled jobs? This script can be scheduled though the task scheduler and SQL command line or through other means.

The script starts by creating a suffix for the backup file, in the format of _backup_yyyymmdd_hhmissmmm.bak. The suffix will be appended to the database name to create the full backup file name. As an example, if the “DB” database was backed up on June 17, 2012, at 1:26:15.123 PM, the full file name would be: DB_backup_20120617_132615123.bak. (Happy Father’s Day, by the way! :-))

Furthermore, the example below assumes we will save the backup file to the B: drive, in a folder with the database name. Thus, the full path for our example database would be: B:\DB\DB_backup_20120617_132615123.bak. Both the path and file name can be easily modified as needed, obviously.

The script makes use of the sp_MSforeachdb system stored procedure in order to execute the backup operation against every database. Note that the tempdb system database is excluded as backup and restore operations are explicitly disallowed on the tempdb database.

-- Create the backup file name suffix
DECLARE @suffix CHAR(30) =
    '_backup_'
    + CONVERT(CHAR(8), GETDATE(), 112)
    + '_'
    + REPLACE(CONVERT(CHAR(12), GETDATE(), 114), ':', '')
    + '.bak'
;

-- Create the SQL string to execute for each database on the server
DECLARE @strSQL VARCHAR(255) = 'IF ''?'' <> ''tempdb''
    BACKUP DATABASE [?]
    TO DISK = N''B:\?\?' + @suffix + '''
    WITH
        NOFORMAT
        , NOINIT
        , NAME = N''?-Full Database Backup''
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , STATS = 10
;'
;

-- Execute the SQL string for each database on the server
EXECUTE sp_msforeachdb @strSQL
;

Create Severity Alerts

Sometimes it’s the simple things that are the easiest to forget, such as setting up automatic, server-level error alerting. It only takes a minute to create an operator (an individual, or, better yet, a group!) to receive notifications, define various alert events, and create a notification that will inform the operator when the alert event occurs.

The following script will do just that for database engine error severities 16 through 25.

-- Set context to the msdb database
USE msdb;
GO

-- Declare variables
--   @strOperator = operator name
--   @strOperatorEmail = operator's email address
--   @intSeverity = the lowest severity level to alert on
--   @intSeverityMax = the highest severity level to alert on
--   @strAlertName = a dynamic variable with which to name alerts
DECLARE
    @strOperator VARCHAR(50) = N'DBA'
    , @strOperatorEmail VARCHAR(50) = N'dba_group@yourdomain.com'
    , @intSeverity TINYINT = 16
    , @intSeverityMax TINYINT = 25
    , @strAlertName VARCHAR(50)
;

-- If the operator we want to create already exists, drop it
IF EXISTS (
    SELECT name
    FROM msdb.dbo.sysoperators
    WHERE name = @strOperator)
EXECUTE msdb.dbo.sp_delete_operator
    @name = @strOperator
;

-- Create a new operator
--   Best practice is to use a distribution list, not an individual
EXECUTE msdb.dbo.sp_add_operator
    @name = @strOperator
    , @email_address = @strOperatorEmail
;

-- Loop through the desired severity levels
WHILE @intSeverity <= @intSeverityMax
BEGIN

    -- Set the alert name
    SET @strAlertName = N'Severity 0' + CONVERT(CHAR(2), @intSeverity)
    ;

    -- If the alert we want to create already exists, drop it
    IF EXISTS (
        SELECT name
        FROM msdb.dbo.sysalerts
        WHERE name = @strAlertName)
    EXECUTE msdb.dbo.sp_delete_alert
        @name = @strAlertName
    ;

    -- Create the alert
    EXECUTE msdb.dbo.sp_add_alert
        @name = @strAlertName
        , @severity = @intSeverity
        , @delay_between_responses = 60 -- seconds
        , @include_event_description_in = 1 -- email only
    ;

    -- Add notification to the alert
    EXECUTE msdb.dbo.sp_add_notification
        @alert_name = @strAlertName
        , @operator_name = @strOperator
        , @notification_method = 1 -- send email only
    ;

    -- Increment the severity
    SET @intSeverity += 1
    ;

END

Defragment Indices

As records are added to or removed from a table, indices on the table will become fragmented, thus reducing their efficiency both in terms of disk usage and performance. This fragmentation is especially severe if a database is shrunk (one reason why a database should NOT be shrunk, barring exceptional circumstances). SQL Server ships with maintenance tasks that can be configured to defragment indices. However, there are situations when running a maintenance task is not practical, or a greater degree of customization is desired beyond what is configurable within the maintenance task. In such circumstances, a stored procedure can be used to manage the defragmentation.

The example procedure below is created within a maintenance schema named “mx”. Furthermore, it uses the system stored procedure sp_MSforeachdb to span all databases on a server when looking for indices to defragment; it only looks for user tables, even though it includes the system databases; it specifies a fragmentation threshold to qualify a table for defragmentation; and it uses the degree of fragmentation to decide whether to REORGANIZE or REBUILD the index.

-- Create a stored procedure to defragment indices
CREATE PROCEDURE [mx].[uspDefragmentIndices]
AS
BEGIN
    -- Declare variables
    DECLARE
        @DBName NVARCHAR(255)
        , @TableName NVARCHAR(255)
        , @SchemaName NVARCHAR(255)
        , @IndexName NVARCHAR(255)
        , @PctFrag DECIMAL
        , @Defrag NVARCHAR(MAX)
    ;
    -- If the temporary table already exists, drop it
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    ;
    -- Create the temporary table
    CREATE TABLE #FragTables (
        DBName NVARCHAR(255)
        , TableName NVARCHAR(255)
        , SchemaName NVARCHAR(255)
        , IndexName NVARCHAR(255)
        , AvgFragment TINYINT)
    ;
    -- Insert fragmented indices into the temporary table
    EXECUTE sp_MSforeachdb
        'INSERT INTO #FragTables (
            DBName
            , TableName
            , SchemaName
            , IndexName
            , AvgFragment)
        SELECT
            ''?'' AS DBName
            , t.Name AS TableName
            , sc.Name AS SchemaName
            , i.name AS IndexName
            , s.avg_fragmentation_in_percent
        FROM
            ?.sys.dm_db_index_physical_stats(
                DB_ID(''?'')
                , NULL
                , NULL
                , NULL
                , ''Sampled'') AS s
            INNER JOIN ?.sys.indexes AS i
                ON s.Object_Id = i.Object_id
                AND s.Index_id = i.Index_id
            INNER JOIN ?.sys.tables AS t
                ON i.Object_id = t.Object_Id
            INNER JOIN ?.sys.schemas AS sc
                ON t.schema_id = sc.SCHEMA_ID
        WHERE
            s.avg_fragmentation_in_percent > 20
            AND t.TYPE = ''U''
        ;'
    ;
    -- Declare a cursor to scroll through the fragmented indices
    DECLARE cIndices CURSOR
    FOR
    SELECT
        DBName
        , TableName
        , SchemaName
        , IndexName
        , AvgFragment
    FROM #FragTables
    ;
    -- Open the cursor
    OPEN cIndices;
    -- Populate the cursor
    FETCH NEXT FROM cIndices
    INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
    ;
    -- Scroll through the fragmented indices
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Begin building the TSQL statement to defragment the index
        SET @Defrag = N'ALTER INDEX [' + @IndexName
            + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] '
        ;
        -- Set the defrag type based on the degree of fragmentation
        IF @PctFrag <= 40.0
            SET @Defrag += 'REORGANIZE';
        ELSE IF @PctFrag > 40.0
            SET @Defrag += 'REBUILD';
        -- Display the TSQL statement that was built
        PRINT @Defrag;
        -- Execute the TSQL statement
        EXECUTE sp_executesql @Defrag;
        -- Populate the next record into the cursor
        FETCH NEXT FROM cIndices
        INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
        ;
    END
    -- Close the cursor
    CLOSE cIndices;
    -- Release the cursor
    DEALLOCATE cIndices;
    -- Drop the temporary table
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    ;
END

To execute this stored procedure, simply run the following TSQL command. Note that it does not matter from which database the stored procedure is run, as the procedure itself references all databases on the server. This can be modified, of course, by removing the code that captures the indices to be defragmented from within the sp_MSforeachdb stored procedure.

-- Defragment the indices
EXECUTE DB.mx.uspDefragmentIndices;

As this procedure runs, it will display a list of TSQL statements that were executed, giving you an overview of the state of indexes on the server prior to defragmentation. It is often worth saving this list along with other maintenance and change logs.

Maintenance Schema

As the first post in the “maintenance” section, why not start with an easy one: creation of a maintenance schema. Using a schema to group maintenance tasks allows you to easily assign elevated privileges to them via the controlling user(s) as well as simply ensuring they are displayed with like-purposed entities within SQL Server Management Studio (SSMS).

The basic syntax is simple: specify the database within which you would like to create the schema, then create the schema with the default owner. For this example, we will use a database named “DB” and assign the database owner “dbo” user as the owner.

-- Specify the database to use
USE [DB];
GO

-- Create the maintenance schema
CREATE SCHEMA [mx] AUTHORIZATION [dbo];
GO