Are remote databases fragmented? Let's Defrag.

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

SET QUOTED_IDENTIFIER ON

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

-- 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

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

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

Are remote databases fragmented? Let's Defrag.

Step One: Create the Tables

Step 2: Create the stored procedures

Step Three - Jobs

Conclusion

Resources

Rate

Share

Categories

Share

Rate

Are remote databases fragmented? Let's Defrag.

Step One: Create the Tables

Step 2: Create the stored procedures

Step Three - Jobs

Conclusion

Resources

Rate

Share

Categories

Share

Rate

Related content

The Real World: Rebuilding Index - 1 Instance, 106 Databases

Index Utilization Trending

Indexes, Indexes, Indexes

The Ultimate Duplicate Index Finder

The Ultimate Index Usage Reporter