Как быстро отремонтировать базу 1С Предприятия в клиент-сервере

10 комментариев

1. 

   Андраник Арутюнян 1 августа 2017 Войдите, чтобы ответить

   Доброе время суток Богдан спасибо за стаю, xотя я это давним давно исползую но было приятно прочитатаь все еше раз. могу порекомендовать свой варянт решение такиx проблем глоболно без ручнин работ он в 99% помогает и даже влияет на бистродествие БД

   • 

     Богдан Кухар 1 августа 2017 Войдите, чтобы ответить

     Здравствуйте, Андраник.

     Делитесь конечно, публикуйте скрипт. Я и многие пользователи будут Вам только благодарны!

2. 

   Андраник Арутюнян 1 августа 2017 Войдите, чтобы ответить

   Use master

   /* Drop Table Scripts:
   Drop Table dbo.dba_indexDefragLog;
   Drop Table dbo.dba_indexDefragExclusion;
   */
   If Not Exists(Select [object_id] From sys.tables
   Where [name] In (N’dba_indexDefragLog’, ‘dba_indexDefragExclusion’))
   Begin

   Create Table dbo.dba_indexDefragLog
   (
   indexDefrag_id int identity(1,1) Not Null
   , databaseID int Not Null
   , databaseName nvarchar(128) Not Null
   , objectID int Not Null
   , objectName nvarchar(128) Not Null
   , indexID int Not Null
   , indexName nvarchar(128) Not Null
   , partitionNumber smallint Not Null
   , fragmentation float Not Null
   , page_count int Not Null
   , dateTimeStart datetime Not Null
   , dateTimeEnd datetime Null
   , durationSeconds int Null

   Constraint PK_indexDefragLog
   Primary Key Clustered (indexDefrag_id)
   );

   Print ‘dba_indexDefragLog Table Created’;

   Create Table dbo.dba_indexDefragExclusion
   (
   databaseID int Not Null
   , databaseName nvarchar(128) Not Null
   , objectID int Not Null
   , objectName nvarchar(128) Not Null
   , indexID int Not Null
   , indexName nvarchar(128) Not Null
   , exclusionMask int Not Null
   /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */

   Constraint PK_indexDefragExclusion
   Primary Key Clustered (databaseID, objectID, indexID)
   );

   Print ‘dba_indexDefragExclusion Table Created’;

   End
   Else
   RaisError(‘One or more tables already exist. Please drop or rename before proceeding.’, 16, 0);

   If ObjectProperty(Object_ID(‘dbo.dba_indexDefrag_sp’), N’IsProcedure’) = 1
   Begin
   Drop Procedure dbo.dba_indexDefrag_sp;
   Print ‘Procedure dba_indexDefrag_sp dropped’;
   End;
   Go

   Create Procedure dbo.dba_indexDefrag_sp

   /* Declare Parameters */
   @minFragmentation float = 5.0
   /* in percent, will not defrag if fragmentation less than specified */
   , @rebuildThreshold float = 30.0
   /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
   , @executeSQL bit = 1
   /* 1 = execute; 0 = print command only */
   , @database varchar(128) = Null
   /* Option to specify a database name; null will return all */
   , @tableName varchar(4000) = Null — databaseName.schema.tableName
   /* Option to specify a table name; null will return all */
   , @scanMode varchar(10) = N’LIMITED’
   /* Options are LIMITED, SAMPLED, and DETAILED */
   , @onlineRebuild bit = 1
   /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
   , @maxDopRestriction tinyint = Null
   /* Option to restrict the number of processors for the operation; only in Enterprise */
   , @printCommands bit = 0
   /* 1 = print commands; 0 = do not print commands */
   , @printFragmentation bit = 0
   /* 1 = print fragmentation prior to defrag;
   0 = do not print */
   , @defragDelay char(8) = ’00:00:05′
   /* time to wait between defrag commands */
   , @debugMode bit = 0
   /* display some useful comments to help determine if/where issues occur */
   , @rebuildStats bit = 1
   /* option to rebuild stats after completed index defrags */

   As
   /*********************************************************************************
   Name: dba_indexDefrag_sp

   Author: Michelle Ufford, http://sqlfool.com

   Purpose: Defrags all indexes for the current database

   Notes:

   CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.

   @minFragmentation defaulted to 10%, will not defrag if fragmentation
   is less than that

   @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL;
   greater than 30% will result in rebuild instead

   @executeSQL 1 = execute the SQL generated by this proc;
   0 = print command only

   @database Optional, specify specific database name to defrag;
   If not specified, all non-system databases will
   be defragged.

   @tableName Specify if you only want to defrag indexes for a
   specific table, format = databaseName.schema.tableName;
   if not specified, all tables will be defragged.

   @scanMode Specifies which scan mode to use to determine
   fragmentation levels. Options are:
   LIMITED – scans the parent level; quickest mode,
   recommended for most cases.
   SAMPLED – samples 1% of all data pages; if less than
   10k pages, performs a DETAILED scan.
   DETAILED – scans all data pages. Use great care with
   this mode, as it can cause performance issues.

   @onlineRebuild 1 = online rebuild;
   0 = offline rebuild

   @maxDopRestriction Option to specify a processor limit for index rebuilds

   @printCommands 1 = print commands to screen;
   0 = do not print commands

   @printFragmentation 1 = print fragmentation to screen;
   0 = do not print fragmentation

   @defragDelay Time to wait between defrag commands; gives the
   server a little time to catch up

   @debugMode 1 = display debug comments; helps with troubleshooting
   0 = do not display debug comments

   @rebuildStats Affects only statistics that need to be rebuilt
   1 = rebuild stats
   0 = do not rebuild stats

   Called by: SQL Agent Job or DBA

   Date Initials Version Description
   —————————————————————————-
   2007-12-18 MFU 1.0 Initial Release
   2008-10-17 MFU 1.1 Added @defragDelay, CIX_temp_indexDefragList
   2008-11-17 MFU 1.2 Added page_count to log table
   , added @printFragmentation option
   2009-03-17 MFU 2.0 Provided support for centralized execution
   , consolidated Enterprise & Standard versions
   , added @debugMode, @maxDopRestriction
   , modified LOB and partition logic
   2009-06-18 MFU 3.0 Fixed bug in LOB logic, added @scanMode option
   , added support for stat rebuilds (@rebuildStats)
   , support model and msdb defrag
   , added columns to the dba_indexDefragLog table
   , modified logging to show “in progress” defrags
   , added defrag exclusion list (scheduling)
   *********************************************************************************
   Exec dbo.dba_indexDefrag_sp
   @executeSQL = 0
   , @printCommands = 1
   , @debugMode = 1
   , @printFragmentation = 1;
   *********************************************************************************/

   Set NoCount On;
   Set XACT_Abort On;
   Set Ansi_Padding On;
   Set Ansi_Warnings On;
   Set ArithAbort On;
   Set Concat_Null_Yields_Null On;
   Set Numeric_RoundAbort Off;
   Set Quoted_Identifier On;

   Begin

   If @debugMode = 1 RaisError(‘Undusting the cogs and starting up…’, 0, 42) With NoWait;

   /* Declare our variables */
   Declare @objectID int
   , @databaseID int
   , @databaseName nvarchar(128)
   , @indexID int
   , @partitionCount bigint
   , @schemaName nvarchar(128)
   , @objectName nvarchar(128)
   , @indexName nvarchar(128)
   , @partitionNumber smallint
   , @fragmentation float
   , @pageCount int
   , @sqlCommand nvarchar(4000)
   , @rebuildCommand nvarchar(200)
   , @dateTimeStart datetime
   , @dateTimeEnd datetime
   , @containsLOB bit
   , @editionCheck bit
   , @debugMessage varchar(128)
   , @updateSQL nvarchar(4000)
   , @partitionSQL nvarchar(4000)
   , @partitionSQL_Param nvarchar(1000)
   , @LOB_SQL nvarchar(4000)
   , @LOB_SQL_Param nvarchar(1000)
   , @rebuildStatsID int
   , @rebuildStatsSQL nvarchar(1000)
   , @indexDefrag_id int;

   /* Create our temporary tables */
   Create Table #indexDefragList
   (
   databaseID int
   , databaseName nvarchar(128)
   , objectID int
   , indexID int
   , partitionNumber smallint
   , fragmentation float
   , page_count int
   , defragStatus bit
   , schemaName nvarchar(128) Null
   , objectName nvarchar(128) Null
   , indexName nvarchar(128) Null
   );

   Create Table #databaseList
   (
   databaseID int
   , databaseName varchar(128)
   , scanStatus bit
   , statsStatus bit
   );

   Create Table #processor
   (
   [index] int
   , Name varchar(128)
   , Internal_Value int
   , Character_Value int
   );

   If @debugMode = 1 RaisError(‘Beginning validation…’, 0, 42) With NoWait;

   /* Just a little validation… */
   If @minFragmentation Not Between 0.00 And 100.0
   Set @minFragmentation = 10.0;

   If @rebuildThreshold Not Between 0.00 And 100.0
   Set @rebuildThreshold = 30.0;

   If @defragDelay Not Like ’00:[0-5][0-9]:[0-5][0-9]’
   Set @defragDelay = ’00:00:05′;

   If @scanMode Not In (‘LIMITED’, ‘SAMPLED’, ‘DETAILED’)
   Set @scanMode = ‘LIMITED’;

   /* Make sure we’re not exceeding the number of processors we have available */
   Insert Into #processor
   Execute xp_msver ‘ProcessorCount’;

   If @maxDopRestriction Is Not Null And @maxDopRestriction > (Select Internal_Value From #processor)
   Select @maxDopRestriction = Internal_Value
   From #processor;

   /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
   If (Select ServerProperty(‘EditionID’)) In (1804890536, 610778273, -2117995310)
   Set @editionCheck = 1 — supports online rebuilds
   Else
   Set @editionCheck = 0; — does not support online rebuilds

   If @debugMode = 1 RaisError(‘Grabbing a list of our databases…’, 0, 42) With NoWait;

   /* Retrieve the list of databases to investigate */
   Insert Into #databaseList
   Select database_id
   , name
   , 0 — not scanned yet for fragmentation
   , 0 — statistics not yet updated
   From sys.databases
   Where name = IsNull(@database, name)
   And [name] Not In (‘master’, ‘tempdb’)– exclude system databases
   And [state] = 0; — state must be ONLINE

   If @debugMode = 1 RaisError(‘Looping through our list of databases and checking for fragmentation…’, 0, 42) With NoWait;

   /* Loop through our list of databases */
   While (Select Count(*) From #databaseList Where scanStatus = 0) > 0
   Begin

   Select Top 1 @databaseID = databaseID
   From #databaseList
   Where scanStatus = 0;

   Select @debugMessage = ‘ working on ‘ + DB_Name(@databaseID) + ‘…’;

   If @debugMode = 1
   RaisError(@debugMessage, 0, 42) With NoWait;

   /* Determine which indexes to defrag using our user-defined parameters */
   Insert Into #indexDefragList
   Select
   database_id As databaseID
   , QuoteName(DB_Name(database_id)) As ‘databaseName’
   , [object_id] As objectID
   , index_id As indexID
   , partition_number As partitionNumber
   , avg_fragmentation_in_percent As fragmentation
   , page_count
   , 0 As ‘defragStatus’ /* 0 = unprocessed, 1 = processed */
   , Null As ‘schemaName’
   , Null As ‘objectName’
   , Null As ‘indexName’
   From sys.dm_db_index_physical_stats (@databaseID, Object_Id(@tableName), Null , Null, @scanMode)
   Where avg_fragmentation_in_percent >= @minFragmentation
   And index_id > 0 — ignore heaps
   And page_count > 8 — ignore objects with less than 1 extent
   And index_level = 0 — leaf-level nodes only, supports @scanMode
   Option (MaxDop 2);

   /* Keep track of which databases have already been scanned */
   Update #databaseList
   Set scanStatus = 1
   Where databaseID = @databaseID;

   End

   Create Clustered Index CIX_temp_indexDefragList
   On #indexDefragList(databaseID, objectID, indexID, partitionNumber);

   /* Delete any indexes from our to-do that are also in our exclusion list for today */
   Delete idl
   From #indexDefragList As idl
   Join dbo.dba_indexDefragExclusion As ide
   On idl.databaseID = ide.databaseID
   And idl.objectID = ide.objectID
   And idl.indexID = ide.indexID
   Where exclusionMask & Power(2, DatePart(weekday, GetDate())-1) > 0;

   Select @debugMessage = ‘Looping through our list… there”s ‘ + Cast(Count(*) As varchar(10)) + ‘ indexes to defrag!’
   From #indexDefragList;

   If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;

   /* Begin our loop for defragging */
   While (Select Count(*) From #indexDefragList Where defragStatus = 0) > 0
   Begin

   If @debugMode = 1 RaisError(‘ Picking an index to beat into shape…’, 0, 42) With NoWait;

   /* Grab the most fragmented index first to defrag */
   Select Top 1
   @objectID = objectID
   , @indexID = indexID
   , @databaseID = databaseID
   , @databaseName = databaseName
   , @fragmentation = fragmentation
   , @partitionNumber = partitionNumber
   , @pageCount = page_count
   From #indexDefragList
   Where defragStatus = 0
   Order By fragmentation Desc;

   If @debugMode = 1 RaisError(‘ Looking up the specifics for our index…’, 0, 42) With NoWait;

   /* Look up index information */
   Select @updateSQL = N’Update idl
   Set schemaName = QuoteName(s.name)
   , objectName = QuoteName(o.name)
   , indexName = QuoteName(i.name)
   From #indexDefragList As idl
   Inner Join ‘ + @databaseName + ‘.sys.objects As o
   On idl.objectID = o.object_id
   Inner Join ‘ + @databaseName + ‘.sys.indexes As i
   On o.object_id = i.object_id
   Inner Join ‘ + @databaseName + ‘.sys.schemas As s
   On o.schema_id = s.schema_id
   Where o.object_id = ‘ + Cast(@objectID As varchar(10)) + ‘
   And i.index_id = ‘ + Cast(@indexID As varchar(10)) + ‘
   And i.type > 0
   And idl.databaseID = ‘ + Cast(@databaseID As varchar(10));

   Execute sp_executeSQL @updateSQL;

   /* Grab our object names */
   Select @objectName = objectName
   , @schemaName = schemaName
   , @indexName = indexName
   From #indexDefragList
   Where objectID = @objectID
   And indexID = @indexID
   And databaseID = @databaseID;

   If @debugMode = 1 RaisError(‘ Grabbing the partition count…’, 0, 42) With NoWait;

   /* Determine if the index is partitioned */
   Select @partitionSQL = ‘Select @partitionCount_OUT = Count(*)
   From ‘ + @databaseName + ‘.sys.partitions
   Where object_id = ‘ + Cast(@objectID As varchar(10)) + ‘
   And index_id = ‘ + Cast(@indexID As varchar(10)) + ‘;’
   , @partitionSQL_Param = ‘@partitionCount_OUT int OutPut’;

   Execute sp_executeSQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OutPut;

   If @debugMode = 1 RaisError(‘ Seeing if there”s any LOBs to be handled…’, 0, 42) With NoWait;

   /* Determine if the table contains LOBs */
   Select @LOB_SQL = ‘ Select @containsLOB_OUT = Count(*)
   From ‘ + @databaseName + ‘.sys.columns With (NoLock)
   Where [object_id] = ‘ + Cast(@objectID As varchar(10)) + ‘
   And (system_type_id In (34, 35, 99)
   Or max_length = -1);’
   /* system_type_id –> 34 = image, 35 = text, 99 = ntext
   max_length = -1 –> varbinary(max), varchar(max), nvarchar(max), xml */
   , @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;

   Execute sp_executeSQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OutPut;

   If @debugMode = 1 RaisError(‘ Building our SQL statements…’, 0, 42) With NoWait;

   /* If there’s not a lot of fragmentation, or if we have a LOB, we should reorganize */
   If @fragmentation = 1 Or @partitionCount > 1
   Begin

   Set @sqlCommand = N’Alter Index ‘ + @indexName + N’ On ‘ + @databaseName + N’.’
   + @schemaName + N’.’ + @objectName + N’ ReOrganize’;

   /* If our index is partitioned, we should always reorganize */
   If @partitionCount > 1
   Set @sqlCommand = @sqlCommand + N’ Partition = ‘
   + Cast(@partitionNumber As nvarchar(10));

   End;

   /* If the index is heavily fragmented and doesn’t contain any partitions or LOB’s, rebuild it */
   If @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
   Begin

   /* Set online rebuild options; requires Enterprise Edition */
   If @onlineRebuild = 1 And @editionCheck = 1
   Set @rebuildCommand = N' Rebuild With (Online = On';
   Else
   Set @rebuildCommand = N' Rebuild With (Online = Off';

   /* Set processor restriction options; requires Enterprise Edition */
   If @maxDopRestriction Is Not Null And @editionCheck = 1
   Set @rebuildCommand = @rebuildCommand + N', MaxDop = ' + Cast(@maxDopRestriction As varchar(2)) + N')';
   Else
   Set @rebuildCommand = @rebuildCommand + N')';

   Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
   + @schemaName + N'.' + @objectName + @rebuildCommand;

   End;

   /* Are we executing the SQL? If so, do it */
   If @executeSQL = 1
   Begin

   If @debugMode = 1 RaisError(' Executing SQL statements…', 0, 42) With NoWait;

   /* Grab the time for logging purposes */
   Set @dateTimeStart = GetDate();

   /* Log our actions */
   Insert Into dbo.dba_indexDefragLog
   (
   databaseID
   , databaseName
   , objectID
   , objectName
   , indexID
   , indexName
   , partitionNumber
   , fragmentation
   , page_count
   , dateTimeStart
   )
   Select
   @databaseID
   , @databaseName
   , @objectID
   , @objectName
   , @indexID
   , @indexName
   , @partitionNumber
   , @fragmentation
   , @pageCount
   , @dateTimeStart;

   Set @indexDefrag_id = Scope_Identity();

   /* Execute our defrag! */
   Execute sp_executeSQL @sqlCommand;
   Set @dateTimeEnd = GetDate();

   /* Update our log with our completion time */
   Update dbo.dba_indexDefragLog
   Set dateTimeEnd = @dateTimeEnd
   , durationSeconds = DateDiff(second, @dateTimeStart, @dateTimeEnd)
   Where indexDefrag_id = @indexDefrag_id;

   /* Just a little breather for the server */
   WaitFor Delay @defragDelay;

   /* Print if specified to do so */
   If @printCommands = 1
   Print N'Executed: ' + @sqlCommand;
   End
   Else
   /* Looks like we're not executing, just printing the commands */
   Begin
   If @debugMode = 1 RaisError(' Printing SQL statements…', 0, 42) With NoWait;

   If @printCommands = 1 Print IsNull(@sqlCommand, 'error!');
   End

   If @debugMode = 1 RaisError(' Updating our index defrag status…', 0, 42) With NoWait;

   /* Update our index defrag list so we know we've finished with that index */
   Update #indexDefragList
   Set defragStatus = 1
   Where databaseID = @databaseID
   And objectID = @objectID
   And indexID = @indexID
   And partitionNumber = @partitionNumber;

   End

   /* Do we want to output our fragmentation results? */
   If @printFragmentation = 1
   Begin

   If @debugMode = 1 RaisError(' Displaying fragmentation results…', 0, 42) With NoWait;

   Select databaseID
   , databaseName
   , objectID
   , objectName
   , indexID
   , indexName
   , fragmentation
   , page_count
   From #indexDefragList;

   End;

   /* Do we want to rebuild stats? */
   If @rebuildStats = 1
   Begin

   While Exists(Select Top 1 * From #databaseList Where statsStatus = 0)
   Begin

   /* Build our SQL statement to update stats */
   Select Top 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' +
   'Execute sp_updatestats;'
   , @rebuildStatsID = databaseID
   From #databaseList
   Where statsStatus = 0;

   Set @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL;

   If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;

   /* Execute our stats update! */
   Execute sp_executesql @rebuildStatsSQL;

   /* Keep track of which databases have been updated */
   Update #databaseList
   Set statsStatus = 1
   Where databaseID = @rebuildStatsID;

   End;
   End;

   /* When everything is said and done, make sure to get rid of our temp table */
   Drop Table #indexDefragList;
   Drop Table #databaseList;
   Drop Table #processor;

   If @debugMode = 1 RaisError('DONE! Thank you for taking care of your indexes! :)', 0, 42) With NoWait;

   Set NoCount Off;
   Return 0
   End
   Go

   Set Quoted_Identifier Off
   Set ANSI_Nulls On
   Go

   • 

     Богдан Кухар 1 августа 2017 Войдите, чтобы ответить

     Отлично!

     • 

       Андраник Арутюнян 1 августа 2017 Войдите, чтобы ответить

       скрипт проверяет индексы, делает реорганизацию бд, обновляет статистики, и проверяет бд

       • 

         Богдан Кухар 1 августа 2017 Войдите, чтобы ответить

         Да, я вижу, хорошая работа!

         • 

           Андраник Арутюнян 2 августа 2017 Войдите, чтобы ответить

           по любому не смотря на железо через тот скрипт можно 2 а то и 3 раза ускорить 1С

3. 

   Андраник Арутюнян 1 августа 2017 Войдите, чтобы ответить

   USE [msdb]
   GO

   /****** Object: Job [DynamicRebuild] Script Date: 11/17/2014 16:26:28 ******/
   BEGIN TRANSACTION
   DECLARE @ReturnCode INT
   SELECT @ReturnCode = 0
   /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 11/17/2014 16:26:28 ******/
   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’DynamicRebuild’,
   @enabled=1,
   @notify_level_eventlog=0,
   @notify_level_email=0,
   @notify_level_netsend=0,
   @notify_level_page=0,
   @delete_level=0,
   @description=N’No description available.’,
   @category_name=N'[Uncategorized (Local)]’,
   @owner_login_name=N’sa’, @job_id = @jobId OUTPUT
   IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
   /****** Object: Step [Step1-Rebuild] Script Date: 11/17/2014 16:26:28 ******/
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step1-Rebuild’,
   @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’USE [master]

   DECLARE @return_value int

   EXEC @return_value = [dbo].[dba_indexDefrag_sp]
   @database = N”KORP_SQL”

   EXEC @return_value = [dbo].[dba_indexDefrag_sp]
   @database = N”UPP_SQL”

   ‘,
   @database_name=N’master’,
   @flags=0
   IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
   /****** Object: Step [Step2-DeleteOldData] Script Date: 11/17/2014 16:26:28 ******/
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step2-DeleteOldData’,
   @step_id=2,
   @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 master
   delete from dba_indexDefragLog where dateTimeStart<GETDATE()-10
   ',
   @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_jobschedule @job_id=@jobId, @name=N’Daily’,
   @enabled=1,
   @freq_type=4,
   @freq_interval=1,
   @freq_subday_type=1,
   @freq_subday_interval=0,
   @freq_relative_interval=0,
   @freq_recurrence_factor=0,
   @active_start_date=20141117,
   @active_end_date=99991231,
   @active_start_time=0,
   @active_end_time=235959,
   @schedule_uid=N’f7c61767-5101-4082-bf12-733e4acc4784′
   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

4. 

   Андраник Арутюнян 1 августа 2017 Войдите, чтобы ответить

   Если кому то станет интересно или будут вопросы обращайтесь в любое время суток помогу чем смогу

5. 

   Алексей Нефедкин 5 августа 2017 Войдите, чтобы ответить

   Андраник, доброго времени суток, а по пунктам вы можете расписать скрипт( я имею в виду с комментариями).