Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Are remote databases fragmented? Let's Defrag.

By Kimberly Killian,

At my new job, I found a need to improve upon our merge replicated laptop servers. To add a little background to my situation as I also stated in my "When Did Merge Replication Subscribers Last Sync?" article, I have approximately 20 laptops with SQL Express installed that use merge replication to sync back to a main publisher. These users are not always in the office and there are just too many servers to maintain individually when they are in the office. These users work off site/off network so they need performance and speed in their databases.

Because proficiency is a must, I needed to find a way to control table fragmentation without manual intervention on 20 laptops when they are back in the office, which is at the very least every couple weeks. When they are in the office, I have a job that will check the network and see who is online, notify the users that a job is about to begin, collect their fragmentation information, create either a reorganize or rebuild script based on the level of fragmentation (I use 5-30% reorganize and 31%+ rebuild), run the scripts, notify the user that the job has completed and finally write to a couple of tables what was run, maintain some history and send me emails.

This sounds simple and saves me tons of time babysitting the laptops. I have my job scheduled to run every day. If the user hasn't defragged in at least 7 days then my job will run on their machine.

I have created a database that I use for my DBA activities that I call DBA_Reports, however, you can call yours whatever you like just remember to change it in the scripts below. Please make sure that you have the following before you begin:

  • Ensure Database Mail is enabled on your server - this is absolutely key to this process
  • Enable xp_cmdShell on each remote server, so you will be able to see what servers are on line and load them into a table (this will be discussed and used in steps 2 and 3). This is also important to this working properly.
  • Depending how you are set up and what servers you are watching, you may need to create a linked server for each server. In my case, since my users are laptops with SQL Express installed on them, I had to create a linked server to each. You will need to be able to write to tempdb on each remote server from a job/stored procedure running from your DBA_Reports database.
  • Create yourself as an operator so you can receive notifications if your job fails. Personally I like to add a notification on every job I create in case it fails. This is optional.

I'm not going to go into details on how to set these up as they are readily available from other sources, however, just consider them as prerequisites.

Step One: Create the Tables

Once you set up the above prerequisites, it's time to create the tables we will use. If you've read and implemented my "When Did Merge Replication Subscribers Last Sync?" article, then this step is the same and you can skip it and move to step 2.

The "ServerList" table below will house the servers name and email address associated to it. This table will be used later for email notifications and also for verifying what servers are currently online. The server names will be populated by a job, however the email addresses will need to be manually added.

Although this takes some manual setup initially, once setup, only minimal maintenance will be required if new servers come online. I have a job that runs 3 times a day that will populate this table. If a new server is inserted, I receive an email notification so I know to go and add the email address for that server. The email address in my case belongs to the owner of the server, however it could be anyone supporting a server. This is the only manual intervention I have after initial setup. You will see this job later in this article. Copy/paste/run the script below to create the ServerList table.

use DBA_Reports
CREATE TABLE [dbo].[ServerList](
[Server] [varchar](128) NOT NULL,
[Server_email] [varchar](50) NULL,
CONSTRAINT [PK_ServerList] PRIMARY KEY CLUSTERED
(
[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
)
ON [PRIMARY]


The "DBA_Reports - Get New Servers" job will populate the ServerList_Stage and ServerList tables. This job uses xp_cmdshell to call the ''sqlcmd /Lc'' command. This command searches the network looking for servers where SQL Server is installed. Once it finds a server, it will insert the server name into a temp table called #Server, then as long as the servername is not null, it will create and insert into the ServerList_Stage table. The ServerList_Stage is just a staging table where we will use it to compare to our ServerList table later, so it is, as it says just a "staging" table. Lastly, the job will compare the server name from the ServerList_Stage table to the ServerList table. If the record does not already exist, then it will insert it. If a new record is inserted, it will send an email listing out the newly added records with the subject of ''SQL Server - New Servers Found Today".

After you create this job, schedule it to run every day. I have mine running 3 times a day because I have users in multiple timezones as well as logging in at all differnet times. Once this job is setup and an initial run has occurred edit the ServerList table and enter an email address for each server. This will be used later in the sendmail_Sync_Needed stored procedure in step 3. Make sure you change <ADD YOUR INFO HERE> to your information.


USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Get New Servers',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Gets the new servers that are not in the table and sends out an email for new servers',
@category_name=N'DBA Monitoring',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [DBA_Reports]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[ServerList_Stage]'') AND type in (N''U''))
DROP TABLE [dbo].[ServerList_Stage]
GO

USE [DBA_Reports]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerList_Stage](
[Server] [varchar](128) NOT NULL,

CONSTRAINT [PK_ServerList_Stage] PRIMARY KEY CLUSTERED
(
[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- Load table with server names
-- This will only work if XP_CMDSHELL is Enabled

USE DBA_Reports
GO
Create table #Server ( [Server] [varchar](128) )
Insert Into #Server
Exec xp_cmdshell ''sqlcmd /Lc''
Insert Into ServerList_Stage ([Server])
select [Server] from #Server where [Server] is not null
DROP Table #Server
GO

--Send email to with new servers found

SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - New Servers Found Today''

DECLARE @Count AS INT
SELECT @Count=COUNT(*) FROM DBA_Reports.dbo.ServerList_Stage where [Server] not in (select [Server] from [ServerList])
PRINT @Count

IF @Count > 0
BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>Server</th>'' +
N''</tr>'' +
CAST ( ( SELECT td=[server],''''
FROM [ServerList_Stage]
where [Server] not in (select [Server] from [ServerList])

FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;

END

INSERT INTO [DBA_Reports].[dbo].[ServerList]
([Server])
SELECT [Server]
FROM [DBA_Reports].[dbo].[ServerList_Stage]
where [Server] not in (select [Server] from [DBA_Reports].[dbo].[ServerList])
GO

',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 3 times',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=3,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110512,
@active_end_date=99991231,
@active_start_time=63000,
@active_end_time=220000

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The Fragmented_jobStartStopTime table is used to capture the start/end time of the job running so we can monitor how long the process is taking on each server since they will each be different.

CREATE TABLE [dbo].[Fragmented_jobStartStopTime](
[ServerName] [varchar](50) NOT NULL,
[StartJobTime] [datetime] NULL,
[FinishJobTime] [datetime] NULL,
[latest] [int] NULL
) ON [PRIMARY]

The Fragmented_reorg_queries table is used to hold the generated queries that will be run on each remote server.

CREATE TABLE [dbo].[Fragmented_reorg_queries](
[ServerName] [sysname] NULL,
[DatabaseName] [sysname] NULL,
[query] [varchar](537) NULL
) ON [PRIMARY]

The Fragmented_reorg_queries_history table is used to hold the generated queries that were run on each remote server along with a datetime stamp so we can use this table for reporting later. There is some redundancy here with the table layout, however you'll see later that we are truncating the fragmented_reorg_queries table between runs, therefore, we needed a static history table.

CREATE TABLE [dbo].[Fragmented_reorg_queries_history](
[ServerName] [sysname] NULL,
[DatabaseName] [sysname] NULL,
[LastChecked] [datetime] NOT NULL,
[Query] [nchar](1000) NULL
) ON [PRIMARY]

The FragmentedTables table is used to hold the initial (before) fragmentation levels prior to the reorg and reindex queries being run. We will use this later as well for reporting. This table acts like a temp table as it is truncated at the end of the process.

CREATE TABLE [dbo].[FragmentedTables](
[ServerName] [sysname] NULL,
[DatabaseName] [sysname] NULL,
[ObjectName] [char](255) NULL,
[ObjectId] [int] NULL,
[IndexName] [char](255) NULL,
[IndexId] [int] NULL,
[Lvl] [int] NULL,
[CountPages] [int] NULL,
[CountRows] [int] NULL,
[MinRecSize] [int] NULL,
[MaxRecSize] [int] NULL,
[AvgRecSize] [int] NULL,
[ForRecCount] [int] NULL,
[Extents] [int] NULL,
[ExtentSwitches] [int] NULL,
[AvgFreeBytes] [int] NULL,
[AvgPageDensity] [int] NULL,
[ScanDensity] [decimal](18, 0) NULL,
[BestCount] [int] NULL,
[ActualCount] [int] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ExtentFrag] [decimal](18, 0) NULL
) ON [PRIMARY]

The FragmentedTablesAfterDefragRun table isused to hold the post fragmentation levels after the reorg and reindex queries have run. This is also a little redundent, however we again are truncating at the end of our process so this is essentially working as a temp table.

CREATE TABLE [dbo].[FragmentedTablesAfterDefragRun](
[ServerName] [sysname] NULL,
[DatabaseName] [sysname] NULL,
[ObjectName] [char](255) NULL,
[ObjectId] [int] NULL,
[IndexName] [char](255) NULL,
[IndexId] [int] NULL,
[Lvl] [int] NULL,
[CountPages] [int] NULL,
[CountRows] [int] NULL,
[MinRecSize] [int] NULL,
[MaxRecSize] [int] NULL,
[AvgRecSize] [int] NULL,
[ForRecCount] [int] NULL,
[Extents] [int] NULL,
[ExtentSwitches] [int] NULL,
[AvgFreeBytes] [int] NULL,
[AvgPageDensity] [int] NULL,
[ScanDensity] [decimal](18, 0) NULL,
[BestCount] [int] NULL,
[ActualCount] [int] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ExtentFrag] [decimal](18, 0) NULL
) ON [PRIMARY]

Step 2: Create the stored procedures

The SelectAllServersFragmentedTables stored procedure will do the following:

  • Query the network to check what servers are online.
  • Query the fragmenation data into a table on tempdb for each remote server
  • Insert fragmentation information into a temporary table.
  • Insert fragmentation information including server name and database name into the remote table created in tempdb.
  • Create defrag/reindex queries and write to a table to be used later.
  • Write defrag/reindex queries to a history table to be used later

Copy/Paste/Run the scripts below to create the stored procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================================================================
-- Author: Kimberly Killian
-- Create date: 5/19/11
-- Description: Get the fragmented index's in each server/database (laptops)
-- ==========================================================================
CREATE procedure [dbo].[SelectAllServersFragmentedTables]
@p_FragmentedLimit decimal = 5.0
as
declare @err    int,
@rc    int, 
@cmd        varchar(8000),
@cmd2       varchar(8000),
@ServerName sysname,
@cmdmail varchar(50),
@cmdSprocDefrag varchar (50)
set nocount on
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServerList_frag]') AND type in (N'U'))
DROP TABLE [dbo].[ServerList_frag]
CREATE TABLE [dbo].[ServerList_frag](
[Server] [varchar](128) NOT NULL,
CONSTRAINT [PK_SSIS_ServerList_frag] PRIMARY KEY CLUSTERED 
( [Server] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
-- Load table with server names
-- This will only work if XP_CMDSHELL is Enabled
Create table #Server ( [Server] [varchar](128) )
Insert Into #Server  
    Exec xp_cmdshell 'sqlcmd /Lc' 
Insert Into SSIS_ServerList_frag  ([Server])
    select [Server] from #Server where SERVER in (select [SERVER] from dbo.ServerList where Skip_SQL_Overview = 'false')
DROP Table #Server 
---------------------------------------------------------------------------------------------------------
-- Truncate the central table with the fragmentation information     
---------------------------------------------------------------------------------------------------------
TRUNCATE TABLE Fragmented_reorg_queries
---------------------------------------------------------------------------------------------------------
-- Loop on Server list and find fragmented tables  where they haven't cleaned up for at least 14 days
---------------------------------------------------------------------------------------------------------
declare SrvCursB cursor for 
select Server from dbo.ServerList_frag where SERVER not in 
   (select distinct ServerName from dbo.Fragmented_reorg_queries_history group by servername having max(LastChecked) >= getdate()-14) 
     
open SrvCursB
fetch SrvCursB into @ServerName
 
while @@FETCH_STATUS = 0
BEGIN
--send warning email to remote servers
select @cmdmail = 'sendemail_do_not_disconnect ' +  @ServerName
exec (@cmdmail)
-- Create a table in tempdb that will be populated at the remote
             -- server and later be accessed from the central database
             -- (Command passed to osql should be in one line, no <CR>):
             --Check DB Consistency
select @cmd = 'DBCC CHECKDB WITH NO_INFOMSGS;' + 
'if exists (select 1 from ' + @servername + '.tempdb.dbo.sysobjects ' +
                            'where type = ''U'' AND NAME = ''fraglist'') ' +
                            'drop table tempdb.dbo.fraglist;' +
              'CREATE TABLE tempdb.dbo.fraglist (' +
                          'ServerName sysname,' +
                          'DatabaseName sysname,' +
                          'ObjectName CHAR (255),' +
                          'ObjectId INT,' +
                          'IndexName CHAR (255),' +
                          'IndexId INT,' +
                          'Lvl INT,' +
                          'CountPages INT,' +
                          'CountRows INT, ' +
                          'MinRecSize INT,' +
                          'MaxRecSize INT,' +
                          'AvgRecSize INT,' +
                          'ForRecCount INT,' +
                          'Extents INT,' +
                          'ExtentSwitches INT,' +
                          'AvgFreeBytes INT,' +
                          'AvgPageDensity INT,' +
                          'ScanDensity DECIMAL,' +
                          'BestCount INT,' +
                          'ActualCount INT,' +
                          'LogicalFrag DECIMAL,' +
                          'ExtentFrag DECIMAL) '
             
select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + 
                            @cmd + '"' 
exec master..xp_cmdshell @cmd2
select @err = @@error
IF @err <> 0 
begin
select 'ERROR Creating the table in tempdb for SERVER - '+
                           @ServerName
return -1
end
/*Query the fragmenation data into a table on tempdb for each remote server
        Insert fragmentation information into a temporary table.
        Insert fragmentation information including server name and database name into 
        the remote table created in tempdb. 
        Create defrag/reindex queries and write to a table to be used later.
        Write defrag/reindex queries to a history table to be used later*/
        
select @cmd = 
'exec [' + @ServerName + '].master.dbo.sp_msforeachdb ' +
'''use [?]; print ''''?''''; 
             if ''''?'''' in (''''tempdb'''',''''msdb'''',''''master'''',
                              ''''model'''',''''Northwind5'''',''''pubs'''',''''ReportServerTempDB'''',''''ReportServer'''')
                        return
create table #t (cmd varchar(max)); 
insert into #t select 
                     ''''SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON;
dbcc showcontig() with tableresults, all_indexes;''''
declare @cmd varchar(max) 
declare curs_tmp cursor for select cmd from #t
open curs_tmp
fetch curs_tmp into @cmd
while @@fetch_status = 0
begin
      select ObjectName, ObjectId, IndexName, IndexId, Lvl, 
                           CountPages, CountRows, MinRecSize, MaxRecSize, 
                           AvgRecSize, ForRecCount, Extents, ExtentSwitches,
                           AvgFreeBytes, AvgPageDensity, ScanDensity, 
                           BestCount, ActualCount, LogicalFrag, ExtentFrag
      into #fraglist from tempdb.dbo.fraglist where 1=2
insert into #fraglist exec (@cmd)
insert into tempdb.dbo.fraglist 
                          select ''''' +  @ServerName + ''''', 
                                 db_name(),* from #fraglist 
                          WHERE IndexID not in (0,255)
                            And LogicalFrag > ''''' + 
                                convert(varchar(10),@p_FragmentedLimit) +
                                '''''
      drop table #fraglist
fetch curs_tmp into @cmd
end
close curs_tmp
deallocate curs_tmp
drop table #t;
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF;'''
exec (@cmd)
select @err = @@error
IF @err <> 0 
begin
select 'ERROR Collecting fragmentation information for ' + 
                           'SERVER - ' + @ServerName
return -1
end
-- Insert the fragmentation details to the central (local) table
select @cmd = 'insert into FragmentedTables select * from ['+
                    @ServerName + '].tempdb.dbo.fraglist'
exec (@cmd)
select @err = @@error
IF @err <> 0 
begin
select 'ERROR selecting data from remote table for ' + 
                           'SERVER - ' + @ServerName
return -1
end
--get the fragmented tables and generate scripts  
 --save the history
select @cmd = 'INSERT INTO [SQL_Overview].[dbo].[Fragmented_reorg_queries]
 ([ServerName]   ,[DatabaseName]  ,[query]) 
SELECT ServerName,  DatabaseName,
''ALTER INDEX'' + '' '' + RTRIM(IndexName) + '' '' + ''ON '' + RTRIM([ObjectName]) + '' '' + ''REORGANIZE'' as query
FROM [SQL_Overview].[dbo].[FragmentedTables]
where LogicalFrag between 5 and 30
INSERT INTO [SQL_Overview].[dbo].[Fragmented_reorg_queries]
    ([ServerName]  ,[DatabaseName]  ,[query])
SELECT ServerName,  DatabaseName,
''ALTER INDEX'' + '' '' + RTRIM(IndexName) + '' '' + ''ON '' + RTRIM([ObjectName]) + '' '' + ''REBUILD'' as query
FROM [SQL_Overview].[dbo].[FragmentedTables]
where LogicalFrag >= 31
INSERT INTO [SQL_Overview].[dbo].[Fragmented_reorg_queries_history]
  ([ServerName] ,[DatabaseName] , [Query], [LastChecked])
SELECT [ServerName], [DatabaseName], [query], GETDATE() as LastChecked
FROM [SQL_Overview].[dbo].[Fragmented_reorg_queries]'
exec (@cmd)
--Run the queries generated for each server on each server 
select @cmdSprocDefrag = 'defrag_Fragmented_tables ' + @ServerName 
exec(@cmdSprocDefrag)
-- Drop the remote table in tempdb           
select @cmd = 
                  'if exists (select 1 from tempdb.dbo.sysobjects ' + 
                              'where type = ''U'' AND NAME = ''fraglist'') ' +
                                 'drop table tempdb.dbo.fraglist' 
select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + @cmd +
                            '"' 
exec master..xp_cmdshell @cmd2
select @err = @@error
IF @err <> 0 
begin
select 'ERROR dropping remote table for SERVER - ' +
                           @ServerName
return -1
end
--run query to check level of fragmentation after defrag
--same as above
select @cmd = 'if exists (select 1 from tempdb.dbo.sysobjects ' +
                            'where type = ''U'' AND NAME = ''fraglistafter'') ' +
                            'drop table tempdb.dbo.fraglistafter;' +
              'CREATE TABLE tempdb.dbo.fraglistafter (' +
                          'ServerName sysname,' +
                          'DatabaseName sysname,' +
                          'ObjectName CHAR (255),' +
                          'ObjectId INT,' +
                          'IndexName CHAR (255),' +
                          'IndexId INT,' +
                          'Lvl INT,' +
                          'CountPages INT,' +
                          'CountRows INT, ' +
                          'MinRecSize INT,' +
                          'MaxRecSize INT,' +
                          'AvgRecSize INT,' +
                          'ForRecCount INT,' +
                          'Extents INT,' +
                          'ExtentSwitches INT,' +
                          'AvgFreeBytes INT,' +
                          'AvgPageDensity INT,' +
                          'ScanDensity DECIMAL,' +
                          'BestCount INT,' +
                          'ActualCount INT,' +
                          'LogicalFrag DECIMAL,' +
                          'ExtentFrag DECIMAL) '
             
select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + 
                            @cmd + '"' 
exec master..xp_cmdshell @cmd2
select @err = @@error
IF @err <> 0 
begin
select 'ERROR Creating the table in tempdb for SERVER - '+
                           @ServerName
return -1
end
   /*Query the fragmenation data into a table on tempdb for each remote server
        Insert fragmentation information into a temporary table.
        Insert fragmentation information including server name and database name into 
        the remote table created in tempdb. */
     
select @cmd = 
'exec [' + @ServerName + '].master.dbo.sp_msforeachdb ' +
'''use [?]; print ''''?''''; 
             if ''''?'''' in (''''tempdb'''',''''msdb'''',''''master'''',
                              ''''model'''',''''Northwind5'''',''''pubs'''')
                        return
create table #a (cmd varchar(max)); 
insert into #a select 
                      ''''SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON;
dbcc showcontig() with tableresults, all_indexes;''''
declare @cmd varchar(max) 
declare curs_tmpafter cursor for select cmd from #a
open curs_tmpafter
fetch curs_tmpafter into @cmd
while @@fetch_status = 0
begin
      select ObjectName, ObjectId, IndexName, IndexId, Lvl, 
                           CountPages, CountRows, MinRecSize, MaxRecSize, 
                           AvgRecSize, ForRecCount, Extents, ExtentSwitches,
                           AvgFreeBytes, AvgPageDensity, ScanDensity, 
                           BestCount, ActualCount, LogicalFrag, ExtentFrag
      into #fraglistafter from tempdb.dbo.fraglistafter where 1=2
insert into #fraglistafter exec (@cmd)
insert into tempdb.dbo.fraglistafter 
                          select ''''' +  @ServerName + ''''', 
                                 db_name(),* from #fraglistafter 
                          WHERE IndexID not in (0,255)
                            And LogicalFrag > ''''' + 
                                convert(varchar(10),@p_FragmentedLimit) +
                                '''''
      drop table #fraglistafter
fetch curs_tmpafter into @cmd
end
close curs_tmpafter
deallocate curs_tmpafter
drop table #a;
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF'''
exec (@cmd)
select @err = @@error
IF @err <> 0 
begin
select 'ERROR Collecting fragmentation information for ' + 
                           'SERVER - ' + @ServerName
return -1
end
-- Insert the fragmentation details to the central (local) table
select @cmd = 'insert into FragmentedTablesAfterDefragRun select * from ['+
                    @ServerName + '].tempdb.dbo.fraglistafter'
exec (@cmd)
select @err = @@error
IF @err <> 0 
begin
select 'ERROR selecting data from remote table for ' + 
                           'SERVER - ' + @ServerName
return -1
end
-- Drop the remote table in tempdb, update system index's if needed then run update stats    
  
select @cmd = 
                  'if exists (select 1 from tempdb.dbo.sysobjects ' + 
                              'where type = ''U'' AND NAME = ''fraglistafter'') ' +
                                 'drop table tempdb.dbo.fraglistafter; EXEC sp_updatestats;' 
select @cmd2 = 'sqlcmd -E -S"' + @ServerName + '" -Q "' + @cmd +
                            '"' 
exec master..xp_cmdshell @cmd2
select @err = @@error
IF @err <> 0 
begin
select 'ERROR dropping remote table for SERVER - ' +
                           @ServerName
return -1
end
--send completion email to remote servers
select @cmdmail = 'sendemail_All_Clear ' +  @ServerName
exec (@cmdmail)
fetch SrvCursB into @ServerName
END
close SrvCursB
deallocate SrvCursB
GO

The defrag_Fragmented_tables stored procedure will run each generated query on each server. Also note that it will use sqlcmd so you must enable xp_cmdshell if you have not already. Copy/Paste/Run the script below.

USE [DBA_Reports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ======================================================================
-- Author: Kimberly Killian
-- Create date: 5/19/11
-- Description: Kick off the Defrag statements for each server/database
-- =========================================================================
CREATE PROCEDURE [dbo].[defrag_Fragmented_tables]
@ServerName varchar(50)
AS

SET NOCOUNT ON;
Declare @query varchar(8000)
Declare @DatabaseName varchar(128)

Declare query_cursor CURSOR FORWARD_ONLY FOR
select query + ';' from dbo.Fragmented_reorg_queries where [ServerName] = @ServerName

Open query_cursor
fetch Next from query_cursor into @query
While @@FETCH_STATUS = 0

Begin

select @DatabaseName = databasename from dbo.Fragmented_reorg_queries where [ServerName] = @ServerName
--use sqlcmd to run each query on each server

Declare @OpenServerRunQuery varchar(8000)
SET @OpenServerRunQuery = 'sqlcmd -E -S "' + @ServerName + '" -d "' + @DatabaseName + '" -q "' + @query + '"'

--exec queries for each server
exec master..xp_cmdshell @OpenServerRunQuery

fetch Next from query_cursor into @query

END

--clean up
close query_cursor
deallocate query_cursor

GO

The sendemail_do_not_disconnect stored procedure will kick off an email warning message to remote server users one at a time asking them not to disconnect from the network. It will also collect the job start time for later reporting. Make sure to change the <ADD YOUR INFO HERE> to your information.

USE [DBA_Reports]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kimberly Killian
-- Create date: 5/19/11
-- Description: send email to laptop users not to disconnect
-- =============================================
CREATE PROCEDURE [dbo].[sendemail_do_not_disconnect]
@ServerName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
Declare @email varchar(25)
select @email = Server_email from dbo.ServerList where Server = @ServerName

--log the start time of the job for this server
INSERT INTO [Fragmented_jobStartStopTime]
([ServerName],[StartJobTime], [latest])
VALUES (@ServerName, GETDATE(), 0);

--send the email

Declare @body varchar (1000)
SET @body = '<html><body><font face=verdana size=2>
<p><strong>Database Maintenance has started on ' + @ServerName + '. Please do not disconnect your machine from the network. This may take some time. </strong><br>
An email will be sent upon completion.</p>
<p><i>We apologize for any inconvenience this may cause. Please contact me if you experience any issues or need to disconnect.</i></p>
<p>Thank you,<br><br>
<strong><ADD YOUR INFO(name) HERE><br>
<ADD YOUR INFO (title) HERE><br>
<ADD YOUR INFO (company) HERE> </strong><br>
<ADD YOUR INFO (phone) HERE><br/>
<ADD YOUR INFO (email) HERE>
</p>
</body></html>'

--send warning email do not remove from network

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ADD YOUR INFO HERE',
@recipients =@email,
@copy_recipients = 'ADD YOUR INFO (title) HERE',
@subject ='AUTOMATED SEND: Database Maintenance Started Please Do not disconnect',
@body =@body,
@body_format ='HTML';

END

GO

The sendemail_All_Clear stored procedure is used to send the all clear email to the remote users once the queries have finished running as well as capture the finish timestamp of the job for later reporting. Remember to change the <ADD YOUR INFO (email) HERE> to your information.

USE [DBA_Reports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Kimberly Killian
-- Create date: 5/19/11
-- Description: send all clear email to laptop users
-- =============================================
CREATE PROCEDURE [dbo].[sendemail_All_Clear]
@ServerName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
Declare @email varchar(25)
select @email = Server_email from dbo.ServerList where Server = @ServerName

--set the finsih job time
UPDATE [DBA_Reports].[dbo].[Fragmented_jobStartStopTime]
SET [FinishJobTime] = GETDATE(), [latest] = 1
where servername = @ServerName and latest = 0;

Declare @body varchar (1000)
SET @body = '<html><body><font face=verdana size=2>
<p><strong>Database Maintenance has completed on ' + @ServerName + '. </strong><br></p>
<p><i>Thank you for your time and patience.</i></p>

<strong><ADD YOUR INFO(name) HERE><br>
<ADD YOUR INFO (title) HERE><br>
<ADD YOUR INFO (company) HERE> </strong><br>
<ADD YOUR INFO (phone) HERE><br/>
<ADD YOUR INFO (email) HERE>
</p>
</body></html>'

--send all clear email do not remove from network

EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<ADD YOUR INFO HERE>',
@recipients =@email,
@copy_recipients = '<ADD YOUR INFO HERE>',
@subject ='AUTOMATED SEND: Database Maintenance has completed',
@body =@body,
@body_format ='HTML';

END

GO

Step Three - Jobs

The DBA_Reports - Laptop Fragmentation calls DBA_Reports - "Report Laptops Defragged Today" and
"DBA_Reports - Report Laptops not Defragged for at least 2 weeks" which are reports. I have them separated out in the event that I'd like to run them without rerunning the entire job. These jobs will be used to call the stored procedures in step two. These jobs handle the following:

  • Notifies Users that db maintenance will begin
  • Runs SPROC to check level of fragmentation on each laptop
  • Runs SPROC to create scripts to either redinex or reorganize
  • Send mail with list of laptops with fragmentation
  • Send mail with scripts to run
  • Notifies users that db maintenance is completed
  • Sends admin email notifying who was defragged today
  • Sends admin email notifying who has not defragged for at least 2 weeks
  • Send admin email on which indexs were defragged',

Copy/Paste/Run the following scripts to create the jobs

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Laptop Fragmentation',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Notifies laptop user that db maintenance will begin
Checks level of fragmentation on each laptop
Creates scripts to either redinex or reorganize
Send mail with list of laptops with fragmentation
Send mail with scripts to run
Notifies users that db maintenance is completed
Sends admin email notifying who was defragged today
Sends admin email notifying who has not defragged for at least 2 weeks
Send admin email on which indexs were defragged',
@category_name=N'DBA Monitoring',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Call Fragmentation SPROC] Script Date: 06/14/2011 13:36:03 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call Fragmentation SPROC',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec SelectAllServersFragmentedTables



',
@database_name=N'DBA_Reports',
@output_file_name=N'<ADD YOUR INFO HERE>',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send email of who was defragged today',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Laptops Defragged Today''',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [send email for Laptops not Defragged for at least 2 weeks] Script Date: 06/14/2011 13:36:04 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email for Servers not Defragged for at least 2 weeks',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Servers not Defragged for at least 2 weeks''',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'email what was defragged and initial numbers',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - Indexs on Servers Defragged Today''

DECLARE @Count AS INT
SELECT distinct @Count=COUNT(ServerName) FROM [DBA_Reports].[dbo].[FragmentedTables]

PRINT @Count

IF @Count > 0
BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>Server Name</th>'' +
N''<th>Database Name</th>'' +
N''<th>Object Name</th>'' +
N''<th>Index Name</th>'' +
N''<th>Pages</th>'' +
N''<th>Fragmentation % Before</th>'' +
N''<th>Fragmentation % After</th>''+
N''</tr>'' +
CAST ( (SELECT td= dbo.FragmentedTablesAfterDefragRun.ServerName,''''
,td= dbo.FragmentedTablesAfterDefragRun.DatabaseName,''''
,td= dbo.FragmentedTablesAfterDefragRun.ObjectName, ''''
,td= dbo.FragmentedTablesAfterDefragRun.IndexName ,''''
,td= dbo.FragmentedTablesAfterDefragRun.CountPages, ''''
,td= dbo.FragmentedTables.LogicalFrag, ''''
,td= dbo.FragmentedTablesAfterDefragRun.LogicalFrag,''''

FROM dbo.FragmentedTables INNER JOIN dbo.FragmentedTablesAfterDefragRun
ON (dbo.FragmentedTablesAfterDefragRun.ObjectName = dbo.FragmentedTables.ObjectName)
AND (dbo.FragmentedTablesAfterDefragRun.DatabaseName = dbo.FragmentedTables.DatabaseName)
AND (dbo.FragmentedTables.ServerName = dbo.FragmentedTablesAfterDefragRun.ServerName)
AND (dbo.FragmentedTablesAfterDefragRun.IndexName = dbo.FragmentedTables.IndexName)
ORDER BY dbo.FragmentedTablesAfterDefragRun.ServerName, dbo.FragmentedTablesAfterDefragRun.DatabaseName, dbo.FragmentedTables.LogicalFrag, dbo.FragmentedTablesAfterDefragRun.IndexName
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;

END


',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clean Up Temp tables',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'TRUNCATE TABLE FragmentedTables

TRUNCATE Table dbo.FragmentedTablesAfterDefragRun',
@database_name=N'DBA_Reports,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'schedule',
@enabled=1,
@freq_type=8,
@freq_interval=62,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110523,
@active_end_date=99991231,
@active_start_time=140000,
@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The DBA_Reports - Report Laptops Defragged Today stored procedure generates a report that will list out the servers that were defregged today. Remember to change the <ADD YOUR INFO HERE> to your information.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Report Laptops Defragged Today',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Lists out the servers that have been defragged today',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send mail',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server - Laptops Defragged Today''

DECLARE @Count AS INT
SELECT distinct @Count=COUNT(ServerName) FROM [DBA_Reports].[dbo].[Fragmented_reorg_queries_history]
group by ServerName, DatabaseName
having datepart(month, max([LastChecked])) = datepart(month, GETDATE())
and datepart(day, max([LastChecked])) = datepart(day, GETDATE())
and datepart(year, max([LastChecked])) = datepart(year, GETDATE())

PRINT @Count

IF @Count > 0
BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>ServerName</th>'' +
N''<th>DatabaseName</th>'' +
N''<th>Last Checked</th>'' +
N''<th>Run Duration</th>'' +
N''</tr>'' +
CAST ( (SELECT td=[Fragmented_reorg_queries_history].[servername],''''
,td=[databasename],''''
,td=max([lastchecked]),''''
,td=right(convert(varchar(30), (max([FinishJobTime]) - max([StartJobTime])),121),12),''''

FROM [Fragmented_reorg_queries_history]
join [Fragmented_jobStartStopTime] on Fragmented_reorg_queries_history.servername = Fragmented_jobStartStopTime.servername
group by dbo.Fragmented_reorg_queries_history.ServerName, DatabaseName
having datepart(month, max([LastChecked])) = datepart(month, GETDATE())
and datepart(day, max([LastChecked])) = datepart(day, GETDATE())
and datepart(year, max([LastChecked])) = datepart(year, GETDATE())
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;

END',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The DBA_Reports - Report servers not Defragged for at least 2 weeks stored procedure generates a report that will list out the servers that were defregged today. Remember to change the <ADD YOUR INFO HERE> to your information.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Report servers not Defragged for at least 2 weeks',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'List of servers not defragged for at least 2 weeks',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<ADD YOUR INFO HERE>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SET nocount ON
--
DECLARE @Subject VARCHAR (100)
SET @Subject=''DBA Reports - Servers not defragged in the last 2 weeks''

DECLARE @Count AS INT
SELECT distinct @Count=COUNT(ServerName) FROM [DBA_Reports].[dbo].[Fragmented_reorg_queries_history]
group by ServerName, DatabaseName
having max([LastChecked]) <= GETDATE()-14

PRINT @Count

IF @Count > 0
BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<table border="1">'' +
N''<tr>'' +
N''<th>ServerName</th>'' +
N''<th>DatabaseName</th>'' +
N''<th>Last Checked</th>'' +
N''</tr>'' +
CAST ( ( SELECT td=[servername],''''
,td=[databasename],''''
,td=max([lastchecked]),''''
FROM [Fragmented_reorg_queries_history]
group by ServerName, DatabaseName
having max([LastChecked]) <= GETDATE()-14
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<ADD YOUR INFO HERE>'',
@recipients = ''<ADD YOUR INFO HERE>'',
@subject = @Subject,
@body = @tableHTML,
@body_format = ''HTML'' ;

END',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Conclusion

There you have it, a way to tell fragmentation levels before and after reorg/rebuild queries have run against remote servers (in my cast using merge replication). This job could also be adapted to run against regular servers.

Included in this package, the following will be created:
Tables:
- ServerList_frag
- ServerList
- Fragmented_job_startStopTime
- Fragmented_reorg_queries
- Fragmented_reorg_queries_history
- FragmentedTablesAfterDefragRun

Stored Procedures:
- defrag_Fragmented_tables
- SelectAllServersFragmentedTables
- sendemail_do_not_disconnect
- sendemail_All_Clear
Jobs
- DBA_Reports - Laptop Fragmentation
- DBA_Reports - Report Laptops Defragged Today
- DBA_Reports - Report Laptops not Defragged for at least 2 weeks

Resources:

FragScript_Updated9-12-11.zip
Total article views: 3405 | Views in the last 30 days: 3
 
Related Articles
FORUM

@@servername and serverproperty('servername') gives different values

select serverproperty('servername') and select @@servername shows different names

FORUM

@@Servername

SQL SERVER 2000

FORUM

Change @@servername in SQL 2005

@@servername does not reflect server properties name

FORUM

fix fragmentation sql server

fix fragmentation sql server

FORUM

SQLCLUSTER @@servername = NULL

SQLCLUSTER @@servername = NULL

Tags
administration    
indexing    
rebuild    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones