Enable those alerts!

Since SQL Server 7.0 Microsoft has implemented so called alerts in SQL Agent.
The main purpose of these alerts is that when specific events occur, the DBA automatically gets informed by mail / pager or other means of these event(s) and take corrective action.

There are a couple of basic event that should be implemented on an instance by default but I’ve haven’t seen that many servers were the actually were installed, used and monitored.

Here’s a T-SQL script that will create the right basic alerts that, after setup, will pop-up under the ‘Alert’ folder in SQL Agent. It’s an easy step to connect the alerts to an operator so they can be sent out. I’ll leave this challenge to you ;-)

USE [msdb]
GO
/****** Object:  Alert [019 - Fatal Error in Resource] 13:29:32 ******/
EXEC msdb.dbo.sp_add_alert @name=N'019 - Fatal Error in Resource',
        @message_id=0,
        @severity=19,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [020 - Fatal Error in Current Process] ******/
EXEC msdb.dbo.sp_add_alert @name=N'020 - Fatal Error in Current Process',
        @message_id=0,
        @severity=20,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [021 - Fatal Error in Database Process] ******/
EXEC msdb.dbo.sp_add_alert @name=N'021 - Fatal Error in Database Process',
        @message_id=0,
        @severity=21,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [022 - Fatal Error Table Integritiy Suspect] ******/
EXEC msdb.dbo.sp_add_alert @name=N'022 - Fatal Error Table Integritiy Suspect',
        @message_id=0,
        @severity=22,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [023 - Fatal Error Database Integrity Suspect] ******/
EXEC msdb.dbo.sp_add_alert @name=N'023 - Fatal Error Database Integrity Suspect',
        @message_id=0,
        @severity=23,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [024 - Fatal Error Hardware Error]  ******/
EXEC msdb.dbo.sp_add_alert @name=N'024 - Fatal Error Hardware Error',
        @message_id=0,
        @severity=24,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'


USE [msdb]
GO
/****** Object:  Alert [025 - Fatal Error] ******/
EXEC msdb.dbo.sp_add_alert @name=N'025 - Fatal Error',
        @message_id=0,
        @severity=25,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
 

Create multiple files per database and increase performance

In this post I want to explain why to create multiple data files per SQL Server database

Note: this is only relevant for data files, not for log files. Log files always have one file!

Microsoft documentation says:
• The number of data files within a single filegroup should equal to the number of physical CPU cores (hyper threading cores should not be counted)

This is also true for TempDB

So create all the files in your new database with the same initial size – make an estimate on how big you want the files to be- and set a reasonable growth factor. Do not set it to a percentage, but on the same fixed size in MB for all data files. Use the same growth factor for all the files you have created.

SQL Server will fill the data files in a round robin way, meaning that data is spread equally across all the files as the database grows.

Tip!
Another advantage from this approach is that if you have performance issues and you can pin this down to an disk I/O related problem
(see my http://sqlserveradvisor.blogspot.com/2009/02/microsoft-included-fnvirtualfilestats.html on fn_virtualfilestats() post for this), it is then an easy change to add a new raid disk configuration to your server and then spread the data files evenly over both the raid arrays.
Giving you an instant performance boost!!

fn_virtualfilestats: Measure the I/O load on your disk system

Microsoft included the fn_virtualfilestats function in SQL Server so you can monitor the I/O load the instance executes on the disk subsystem.

I use this function mainly to:
- Get the load on TempDB and check if it’s a good idea to move it to it’s own disks (if you have not already done so)
- Get the I/O profile (more read of write activity?) on the database level to help determine if it might be an idea to replace a raid-5 array to a raid-10 or to move datafiles to additional diskarrays (see my post http:// for this)
- Check if there are any disk related issues, in other words are there many stalled I/O’s?
(stalled I/O is an I/O waiting for another I/O)
- Check if the system can handle more db’s, eliminating additional servers

Real life example:
I managed to save big $$$ and could avoid buying new hardware by letting our vendor run this script in comaparable environent of another customer and so proving the load was not so big as they claimed, so we could run the new system on our existing hardware.

Here’s the T-SQL to get the I/O profile of all your database and data files:

DECLARE @TotalIO    BIGINT,
    @TotalBytes BIGINT,
    @TotalStall BIGINT

SELECT @TotalIO  = SUM(NumberReads + NumberWrites),
       @TotalBytes = SUM(BytesRead + BytesWritten),
       @TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)

SELECT [DbName] = DB_NAME([DbId]),
      (SELECT name FROM sys.master_files
        WHERE database_id = [DbId]
              and FILE_ID = [FileId]) filename,
    [%ReadWrites]       = (100 * (NumberReads + NumberWrites) / @TotalIO),
    [%Bytes]        = (100 * (BytesRead + BytesWritten) / @TotalBytes),
    [%Stall]        = (100 * IoStallMS / @TotalStall),
    [NumberReads],
    [NumberWrites],
    [TotalIO]       = CAST((NumberReads + NumberWrites) AS BIGINT),
    [MBsRead]       = [BytesRead] / (1024*1024),
    [MBsWritten]        = [BytesWritten] / (1024*1024),
    [TotalMBs]      = (BytesRead + BytesWritten) / (1024*1024),
    [IoStallMS],
    IoStallReadMS,
    IoStallWriteMS,
    [AvgStallPerIO]     = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
    [AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
    [AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),

    [AvgBytesPerRead]  = ((BytesRead) / (NumberReads + 1)),
    [AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname
 

Be ware: counters are re-set after the instance is restarted!

The result (not all columns shown here). Click on image to enlarge

SQL Server Day to day maintenance

Here’s a little SQL Server T-SQL script I wrote that will executes some day-to-day maintenance
It’ calls some sp’s in MSDB to remove old (as specified by the parameter in the dateadd() function) mail, job and backup history info that will normally accumulate in msdb making it grow bigger over time.

Also a DBCC CHECKDB statement is constructed for each on-line database. Microsoft recommends executing this command frequently. Especially before a full backup is made.

benefits of this script over a SSIS maintenance plan:
- newly created databases are automaticly included
- Databases can be set off-line without the job-step to fail
- Databases in standby mode are skipped
- minumum overhead, giving you precise control
- I've been running this script for more then 2 years now without any error(s)...

I normally schedule this script daily in SQL Server Agent as a separate job before the full backup kicks in, or include as a separate step in the backup process.

Here’s T-SQL Code (store in the CodeCatalog):

-- ====================================================================

-- Author: SQL Server Advisor
-- Create date: jan/2009
-- Description: execute maintenance tasks per database, deletes MSDB log info
-- ====================================================================
CREATE PROCEDURE [dbo].[usp_DatabaseMaintenance] AS
BEGIN
DECLARE @Histdate DATETIME
DECLARE @dbname VARCHAR(100), @command NVARCHAR(350)

  SELECT @Histdate= DATEADD(DAY, -28, GETDATE())

--sp’s stored in MSDB
  EXEC msdb..SP_DELETE_BACKUPHISTORY @Histdate -- delete old history info

  -- delete old mail items
  EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Histdate

  -- delete the log of the sent items
  EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @Histdate

  -- delete the job history log
  EXEC msdb.dbo.SP_PURGE_JOBHISTORY @oldest_date = @Histdate

  --get all the on-line databases
    DECLARE dbnames_cursor CURSOR FAST_FORWARD FOR
      SELECT name FROM sys.databases
     WHERE name NOT IN ('tempdb') -- skip the unwanted DBs
       AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
       AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
     ORDER BY name

   OPEN dbnames_cursor
   FETCH NEXT FROM dbnames_cursor INTO @dbname

   WHILE @@FETCH_STATUS = 0
   BEGIN
     SET @dbname = QUOTENAME(@dbname)

     SET @command = 'DBCC CHECKDB (' + @dbname + ') WITH NO_INFOMSGS'
     PRINT @command
     EXEC SP_EXECUTESQL @command

     FETCH NEXT FROM dbnames_cursor INTO @dbname
  END

  CLOSE dbnames_cursor
  DEALLOCATE dbnames_cursor

END  

Generate restore scripts from backup history

Over the years I’ found this to be a very hand stored proc to generate the required SQL Server
T-SQL RESTORE commands to recreate a recovered database out of the latest backup info:
dumps to disk (full/differerential/log) and the history tables from MSDB

I can not emphasize enough that the MSDB database has to be an integral part of you back strategy. I recommend configuring it in ‘FULL’ recovery mode and include it in your log backups

CREATE PROCEDURE [dbo].[usp_GenerateRestoreScripts] @DBname VARCHAR(100)
AS

SET NOCOUNT ON-- required because we're going to print T-SQL for the restores in the messages 'tab' of SSMS

/* 
Script creates the T-SQL to restore a database with info from MSDB
It helps by creating RESTORE command constructed from the last FULL backup, the last DIFFERENTIAL backup
and all the required TRANSACTION LOG backups after this.
Neat when you have a high frequency of differential or log backups

The variable @DBName should be set to the name of the database you want to restore.

!!! BE AWARE: include MSDB in your backup plan for this T-SQL script to work in all circumstances !!!
I usually include MSDB in the log backup schedule (set the db to full recovery mode)

*/
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
DECLARE @i INT, @logBackupPath VARCHAR(1000)

-- remove temp object that might exist
IF OBJECT_ID('tempdb..#MSDBBackupHistory') IS NOT NULL
    DROP TABLE #MSDBBackupHistory

CREATE TABLE #MSDBBackupHistory (
    id INT IDENTITY(1,1),
    backup_start_date DATETIME,
    backup_type CHAR(1),
    physical_device_name VARCHAR(1000))

INSERT INTO #MSDBBackupHistory (backup_start_date,  backup_type, physical_device_name)
    SELECT BS.backup_start_date, BS.type, RTRIM(BMF.physical_device_name)
    FROM msdb..backupset BS JOIN msdb..backupmediafamily BMF ON BMF.media_set_id=BS.media_set_id
    WHERE BS.database_name = @DBName
    ORDER BY BS.backup_start_date -- dump the last backup first in table

-- get the last Full backup info.
SET @lastFullBackup = (SELECT MAX(id) FROM #MSDBBackupHistory WHERE backup_type='D')
SET @lastFullBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)

-- Restore the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastFullBackupPath + ''''

-- IF it's there's no backup (differential or log) after it, we set to 'with recovery'
IF (@lastFullBackup = (SELECT MAX(id) FROM #MSDBBackupHistory))
    PRINT 'WITH RECOVERY'
ELSE PRINT 'WITH NORECOVERY'

PRINT 'GO'
PRINT ''

-- get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup = (SELECT MAX(id) FROM #MSDBBackupHistory WHERE backup_type='I' AND id > @lastFullBackup)
SET @lastDifferentialBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastDifferentialBackup)

-- when there's a differential backup after the last full backup create the restore T-SQL commands
IF (@lastDifferentialBackup IS NOT NULL)
BEGIN
    -- Restore last diff. backup
    PRINT 'RESTORE DATABASE ' + @DBName
    PRINT 'FROM DISK=''' + @lastDifferentialBackupPath + ''''

    -- If no backup made (differential or log) after it, set to 'with recovery'
    IF (@lastDifferentialBackup = (SELECT MAX(id) FROM #MSDBBackupHistory))
        PRINT 'WITH RECOVERY'
    ELSE PRINT 'WITH NORECOVERY'

    PRINT 'GO'
    PRINT '' -- new line for readability
END

-- construct the required TRANSACTION LOGs restores
IF (@lastDifferentialBackup IS NULL) -- no diff backup made?
    SET @i = @lastFullBackup + 1    -- search for log dumps after the last full
ELSE SET @i = @lastDifferentialBackup + 1 -- search for log dumps after the last diff

-- script T-SQL restore commands from the log backup history
WHILE (@i <= (SELECT MAX(id) FROM #MSDBBackupHistory))
BEGIN
    SET @logBackupPath = (SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i)
    PRINT 'RESTORE LOG ' + @DBName
    PRINT 'FROM DISK=''' + @logBackupPath + ''''

    -- it's the last transaction log, set to 'with recovery'
    IF (@i = (SELECT MAX(id) FROM #MSDBBackupHistory))
        PRINT 'WITH RECOVERY'
    ELSE PRINT 'WITH NORECOVERY'   

    PRINT 'GO'
    PRINT '' -- new line for readability

    SET @i = @i + 1 -- try to find the next log entry
END

-- remove temp objects that exist
IF OBJECT_ID('tempdb..#MSDBBackupHistory') IS NOT NULL
    DROP TABLE #MSDBBackupHistory

(re) Cycle the error logs

During my SQL Server career I’ve seen many sites where recycling the error-log(s) was not in place, making browsing of the log from very cumbersome to almost impossible due the large amount of history data that has become meaningless over time.

Recycling the logs can be done in two steps:

EXEC dbo.sp_cycle_agent_errorlog ;
EXEC dbo.sp_cycle_errorlog ;

I normally schedule these two stored-procs as a SQL Server Agent job once a week on a Sunday night, so the log is fresh at the beginning of the week.

According to books on-line:
SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension

This approach leaves me with of log history of seven weeks (instance restarts not counted of course as this also recycles the log), leaving me with enough info to browse at high speed.

Let SQL Server automatically suggest indexes!

I found this link on the Microsoft website
http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx
and ran the AutoIndex.sql from the attached AutoIndexBlog.zip in my code own CodeCatalog db

The two sp’s I use are ‘auto_create_index’ and ‘auto_drop_index’. Making use of the dynamic management views: dm_db_index_operational_stats, sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats all databases/tables are analyzed. The result with the suggested create and drop index statements are stored in the ‘recommendations’ table

I schedule the two mentioned sp’s in one SQL Server Agent job every night. The performance impact is low, they’re completed in less then a minute.

Let the sp’s run on a daily basis and monitor the suggestions over time.
It happened to me that indexes where suggested and later one dropped again,
usually when –new- tables change over time.


Results as found in the recommendations table (click to enlarge):

It’s a really great source when having discussions with vendors and finally pinpoint what they are doing wrong!

Reorganize fragmented indexes on database tables

Over time when data is added, changed or deleted from Microsoft SQL Server database tables the indexes on these tables become fragmented.
It is very important to keep your indexes in a healthy and defrage’d state to keep performance of the database to an optimum. In my experience this is an area that doesn’t get the attention as it should (honest I’ve seen tables fragmented for more then 99%)

Microsoft presents us with a maintenance-plan option to defrag the SQL Server tables, but the downside of their approach is that all tables within the db –including the big and not so defrag’ed ones are reorganized.For larger db’s this could lead to less availability of the db (processed tables can be locked), and unnecessary transaction log growth.

Why I think the code presented here is better:
- It only defrags indexes that suffer from fragmentation
- Small –base- tables (<8kb)>
- Databases that are not on-line, or are being used as fail-over db’s in transaction-log shipping or database mirroring are skipped
- System db’s (master, msdb, distribution) are included
- Not so heavily defrag’ed tables are reorganized instead of rebuild which takes less time to perform and improves uptime of the database
- Statistics are updated when required
- Newly created db’s are automatically included
- Through the usage off globally (##)temp-db tables the code can be stored as stored-procs in one central location per instance, I use a CodeCatalog db for this.

I normally schedule this script in SQL Server Agent once a day as a separate job.

Here’s T-SQL Code (store in CodeCatalog)

-- =========================================================================
-- Author:  SQL Server Advisor
-- Create date: jan/2009
-- Description: Loops through database collection and calls
--              script to de-fragment
-- =========================================================================
CREATE PROCEDURE [dbo].[usp_SetupReorgDatabase] AS

    DECLARE @dbname VARCHAR(100)

    DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
      SELECT name FROM sys.databases
       WHERE name NOT IN ('tempdb','Northwind','model') -- skip unwanted DBs
         AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
         AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
        ORDER BY name

    OPEN dbnames_cursor
    FETCH NEXT FROM dbnames_cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- actual reorganization goes here...
        EXEC usp_ReorgDatabase @dbname

        FETCH NEXT FROM dbnames_cursor INTO @dbname
    END

    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor

-- ==========================================================================
-- Author:   SQL Server Advisor
-- Create date: jan/2009
-- Description: Reorganizes fragmented tables
--       called from usp_SetupReorgDatabase
-- ==========================================================================

CREATE PROCEDURE [dbo].[usp_ReorgDatabase] @DBname AS VARCHAR(100)
AS

SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @DB_ID SMALLINT;

SET @DB_ID = DB_ID(@DBname)

-- drop any tempdb objects that might exist
    IF OBJECT_ID('tempdb..#ReorgCandidates') IS NOT NULL
        DROP TABLE #ReorgCandidates

    IF OBJECT_ID('tempdb..##DBobjects') IS NOT NULL
        DROP TABLE ##DBobjects

    IF OBJECT_ID('tempdb..##DBschemas') IS NOT NULL
        DROP TABLE ##DBschemas

    IF OBJECT_ID('tempdb..##DBindexes') IS NOT NULL
        DROP TABLE ##DBindexes

    IF OBJECT_ID('tempdb..##DBpartitions') IS NOT NULL
        DROP TABLE ##DBpartitions

    -- use temp global ## tables to get objects/schemas/indexes info for the requested db
    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBobjects FROM sys.objects'
    EXEC SP_EXECUTESQL @command

    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBschemas FROM sys.schemas'
    EXEC SP_EXECUTESQL @command

    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBindexes FROM sys.indexes'
    EXEC SP_EXECUTESQL @command

    SET @command = N'USE ' + @DBname + N' SELECT * INTO ##DBpartitions FROM sys.partitions'
    EXEC SP_EXECUTESQL @command

    SELECT  OBJECT_ID AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #ReorgCandidates
    FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL , NULL, 'DETAILED')-- LIMITED DETAILED
    WHERE index_id > 0--Only clustered and nonclustered indexes
      AND (avg_fragmentation_in_percent >= 10 OR avg_page_space_used_in_percent < 60)
      AND page_count > 8 -- We do not want indexes less than 1 extent in size

    -- Declare the cursor for the list of partitions to be processed.
    DECLARE ReIndexCursor CURSOR FAST_FORWARD FOR
        SELECT DISTINCT objectid,indexid,partitionnum – ONLY GET UNIQUE objects
            FROM #ReorgCandidates;

    -- Open the cursor.
    OPEN ReIndexCursor;

    -- Loop through the objects.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT FROM ReIndexCursor
                INTO @objectid, @indexid, @partitionnum;

            IF @@FETCH_STATUS < 0
                BREAK;

            SELECT @frag = MAX(frag)-- get the max fragmentation level of this table
                FROM #ReorgCandidates
                WHERE objectid = @objectid
                  AND indexid = @indexid

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM ##DBobjects AS o
            JOIN ##DBschemas AS s ON s.schema_id = o.schema_id
            WHERE o.OBJECT_ID = @objectid;

            SELECT @indexname = QUOTENAME(name)
            FROM ##DBindexes
            WHERE OBJECT_ID = @objectid AND index_id = @indexid;

            SELECT @partitioncount = COUNT (*)
            FROM ##DBpartitions
            WHERE OBJECT_ID = @objectid AND index_id = @indexid;

                        -- 20 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 20.0
            BEGIN
                SET @command = N'USE ' + @DBname + N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname;
                PRINT N'Executed: ' + @command

                EXEC SP_EXECUTESQL @command

                SET @command = N'USE ' + @DBname + N' ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            END

            IF @frag >= 20.0
            BEGIN
                SET @command = N'USE ' + @DBname + N' ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)';
            END

            IF @partitioncount > 1
            BEGIN
                SET @command = @command + N'USE ' + @DBname + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
            END

            EXEC SP_EXECUTESQL @command

            PRINT N'Executed: ' + @objectname + '-' + CAST(@objectid AS NVARCHAR(30)) + '-' + @indexname  + '-' + CAST(@frag AS NVARCHAR(30)) + '/' + @command;
        END;

    -- Close and deallocate the cursor.
    CLOSE ReIndexCursor;
    DEALLOCATE ReIndexCursor;

-- Drop the temporary tables.
    IF OBJECT_ID('tempdb..#ReorgCandidates') IS NOT NULL
        DROP TABLE #ReorgCandidates

    IF OBJECT_ID('tempdb..##DBobjects') IS NOT NULL
        DROP TABLE ##DBobjects

    IF OBJECT_ID('tempdb..##DBschemas') IS NOT NULL
        DROP TABLE ##DBschemas

    IF OBJECT_ID('tempdb..##DBindexes') IS NOT NULL
        DROP TABLE ##DBindexes

    IF OBJECT_ID('tempdb..##DBpartitions') IS NOT NULL
        DROP TABLE ##DBpartitions
 

CodeCatalog: a database with –almost- no data?

This blog is about Microsoft SQL Server and the T-SQL language used programming it.

Over the years I have seen code samples to demonstrate topics like backup/restore or re-indexing. Most of these samples (including the re-indexing sample in SQL Server Books On-line!), require to store code in each database running on the SQL Server instance.

As a programmer I want to try to avoid as much duplicate code as possible, so that is why I usually create a very small database per SQL Server instance containing all of the code I want to execute. No tables.

Think of it as a code catalog

I also include this database in my backups jobs to my work on coding is safe and secure

All of the T-SQL code I’m planning to present on this blog uses this approach.

Code for all sorts off backup

Backup is one off the most important things that need to be arranged after a SQL Server database is created, restored or attached.

The advantages of the T-SQL scripts presented here over a SSIS task:
1. Newly created db's are automatically included, no additional steps are required;
2. Databases that are in recovery mode 'Full' or 'Bulk Recovery' are automatically selected when transaction log backups are sheduled;
3. The checksum option that was introduced with SQL 2005 is included;
4. After a backup is completed a verify is executed;
5. Expired backups are automatically deleted;
6. Each set of dumps are stored in their own folder: less clutter
7. Databases can be set off-line without the job-step to fail
8. Databases in standby mode are skipped


The actual backup is made in the 'usp_MakeBackup' stored proc that is not called directly,
but is executed from the parent sp's: 'usp_MakeFullBackup', 'usp_MakeDifferentialBackup' and 'usp_MakeLogBackup' all scheduled from SQL Agent.


I usally make full backup around 2300hr and schedule log backups every 2hrs or so between 0800 until 2000hr.


The differential backup sp was only used in an OLTP config with a high volume of log backups (every 25 minutes), To avoid a very long list if individual log files we scheduled a differential backup every 4hrs. So the restore sequence was: full backup, last differential backup after the full backup and one or more transaction log backup that were made after the differential.

Code to initiate a full backup (store in CodeCatalog)

-- =========================================================================
-- Author:         SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a full backup
--                 The actual backup is made in sp 'usp_MakeBackup'
-- ========================================================================
CREATE PROCEDURE [dbo].[usp_MakeFullBackup]
AS
BEGIN
        SET NOCOUNT ON;

        DECLARE @DbName NVARCHAR(100)

        DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
           SELECT name FROM sys.databases
             WHERE name NOT IN ('tempdb','AdventureWorks') -- skip the unwanted DBs
               AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
               AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
            ORDER BY name

        OPEN dbnames_cursor
        FETCH NEXT FROM dbnames_cursor INTO @DbName

        WHILE @@FETCH_STATUS = 0
        BEGIN
             EXEC usp_MakeBackup @dbname, 'F' -- backup db here..

             FETCH NEXT FROM dbnames_cursor INTO @DbName
        END

        CLOSE dbnames_cursor
        DEALLOCATE dbnames_cursor


END
 

Code to initiate a differential backup (store in CodeCatalog)

-- ======================================================================
-- Author:    SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a differential backup
--              The actual backup is made in sp 'usp_MakeBackup'
-- ======================================================================
 
CREATE PROCEDURE [dbo].[usp_MakeDifferentialBackup]
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @DbName NVARCHAR(100)
 
--get all the on-line databases
       DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
           SELECT name FROM sys.databases-- skip unwanted DBs
            WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
              AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
              AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
           ORDER BY name
 
       OPEN dbnames_cursor
       FETCH NEXT FROM dbnames_cursor INTO @DbName
 
       WHILE @@FETCH_STATUS = 0
       BEGIN
              EXEC usp_MakeBackup @dbname, 'D' -- backup db here..
 
              FETCH NEXT FROM dbnames_cursor INTO @DbName
       END
 
       CLOSE dbnames_cursor
       DEALLOCATE dbnames_cursor
 
END
 

Code to initiate a transaction log backup (store in CodeCatalog)

-- ==============================================================
-- Author:    SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a log backup
--            The actual backup is made in sp 'usp_MakeBackup'
-- ==============================================================
CREATE PROCEDURE [dbo].[usp_MakeLogBackup]
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @DbName NVARCHAR(100)

 --get all the on-line databases
       DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
          SELECT name FROM sys.databases
              WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
                AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
                AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
                AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
           ORDER BY name

       OPEN dbnames_cursor
       FETCH NEXT FROM dbnames_cursor INTO @DbName

       WHILE @@FETCH_STATUS = 0
       BEGIN
              EXEC usp_MakeBackup @dbname, 'L' -- backup log here..

              FETCH NEXT FROM dbnames_cursor INTO @DbName
       END

       CLOSE dbnames_cursor
       DEALLOCATE dbnames_cursor
END
 

The actual backup to disk is made here (to store in CodeCatalog). Looks bit odd, still fighting the code editors:

-- ================================================================================
-- Author:        SQL Server advisor
-- Create date: jan/2009
-- Description:    makes full or logbackup based on passed parameter @BackupType
--            databases are selected in 'usp_MakeFullBackup',
--               'usp_MakeLogBackup' and usp_MakeDifferentialBackup
--=================================================================================

 --called from usp_MakeFullBackup, usp_MakeLogBackup or usp_MakeDifferentialBackup

CREATE PROCEDURE [dbo].[usp_MakeBackup] @DbName NVARCHAR(100), -- Database name
    @BackupType CHAR(1)
-- 'F' = full backup, 'L' = log backup, 'D' = differential backup
AS
BEGIN
    DECLARE @FileName NVARCHAR(300), @Directory NVARCHAR(256)
    DECLARE @BackupRetention SMALLINT   -- backup retention level, days
    DECLARE @DateToDelete DATETIME,@DeleteDateStr NVARCHAR(30),@FileExtension NCHAR(10)

    SET @Directory = 'D:\backup\' + @DbName -- set the backup path here

    SET @BackupRetention = 4 -- set retention &#8211;in days- of backup files here

    EXECUTE master.dbo.xp_create_subdir @Directory

    SET @FileName=@Directory + '\' + @DbName+'.'+Convert(nvarchar(10),GetDate(),104) + '_' +    LTrim(Str(DatePart(hour,GetDate())))+LTrim(Str(DatePart(minute,GetDate())))

    SET @DateToDelete=DateAdd(day,-ABS(@BackupRetention),GetDate())
    SET @DeleteDateStr=Convert(nvarchar(30),@DateToDelete,1) + ' 00:00'

    IF @BackupType = 'F' -- make a full backup
    BEGIN
        SET @FileExtension = 'BAK'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP DATABASE @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
    END

    IF @BackupType = 'D' -- make a differential  backup
    BEGIN
        SET @FileExtension = 'DIF'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP DATABASE @DbName TO DISK = @FileName WITH DIFFERENTIAL,
             
 CHECKSUM, INIT, STOP_ON_ERROR
    END

    IF @BackupType = 'L' -- make a log backup
    BEGIN
        SET @FileExtension = 'TRN'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP LOG @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
    END

--  check if backup was successfull
    RESTORE VERIFYONLY FROM DISK = @FileName WITH CHECKSUM, STOP_ON_ERROR

--  remove the backup files older then @BackupRetention days
    EXECUTE master.dbo.xp_delete_file 0,@Directory,@FileExtension,@DeleteDateStr,1;

END