September 12, 2011 at 12:11 am
Comments posted to this topic are about the item Are remote databases fragmented? Let's Defrag.
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
September 12, 2011 at 6:44 am
I get the following error when I try and run the first part of step 2
Msg 102, Level 15, State 1, Procedure SelectAllServersFragmentedTables, Line 167
Incorrect syntax near '
exec (@cmd)
select @err = @@error
IF @err <> 0
begin
select '.
September 12, 2011 at 7:12 am
Looks like there was a typo in Step 2. Here's the new script.
/****** Object: StoredProcedure [dbo].[SelectAllServersFragmentedTables] Script Date: 09/12/2011 09:07:05 ******/
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,
@cmdmailvarchar(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
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
September 12, 2011 at 7:25 am
Interesting article. Since you are logging errors, do you have any statistics on how often laptop users disconnect even though they've been sent the e-mail?
In my workplace that would be more frequent than a successful run.
September 12, 2011 at 7:35 am
I get this error in step 3
Msg 102, Level 15, State 1, Line 167
Incorrect syntax near 'schedule'.
Msg 105, Level 15, State 1, Line 181
Unclosed quotation mark after the character string '
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
'.
September 12, 2011 at 7:36 am
Unfortunately, I have not collected the stats on how often my folks disconnect. As of now, it has not become a big problem for me, however if you have larger group than I do, I can see the potential problem.
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
September 12, 2011 at 7:45 am
Hmmm, looks like this didn't copy/paste over correctly. Try this to create the job:
USE [msdb]
GO
/****** Object: Job [DBA_Reports - Laptop Fragmentation] Script Date: 09/12/2011 09:40:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [DBA Monitoring] Script Date: 09/12/2011 09:40:57 ******/
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 4 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>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Clean Up Temp tables] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clean Up Temp tables',
@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'TRUNCATE TABLE FragmentedTables
TRUNCATE Table dbo.FragmentedTablesAfterDefragRun',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Call Fragmentation SPROC] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call Fragmentation SPROC',
@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 SelectAllServersFragmentedTables
',
@database_name=N'DBA_Reports',
@output_file_name=N'E:\Morning Reports\FragmentationOutputReport.txt',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send email of who was defragged today] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send email of who was defragged today',
@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 Server Fragmentaion 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 4 weeks] Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email for Laptops not Defragged for at least 4 weeks',
@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'EXEC msdb.dbo.sp_start_job ''DBA_Reports - Report Laptops not Defragged for at least 4 weeks''',
@database_name=N'DBA_Reports',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step Script Date: 09/12/2011 09:40:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'email what was defragged and initial numbers',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@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 Defragged Today''',
@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=131500,
@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
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply