Are remote databases fragmented? Let's Defrag.

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    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

  • Justin-281851

    Old Hand

    Points: 334

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

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    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,

    @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

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • sknox

    SSChampion

    Points: 12227

    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.

  • gregg.maahs

    Grasshopper

    Points: 20

    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

    '.

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    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

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply