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

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

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

Отлично!

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

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