0] Administration
0.1 Description & History
--
Maintenance Plan description
SELECT * FROM msdb.dbo.sysmaintplan_plans p
INNER JOIN msdb.dbo.sysmaintplan_subplans sp ON p.id =
sp.plan_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules j ON
j.schedule_id
= sp.schedule_id
-- Plan
overall duration & Next Run
SELECT name ,
start_time , end_time , next_run_date
, next_run_time , DateDiff(second, start_time
, end_time)/60 TotalMinutes
FROM msdb.dbo.sysmaintplan_plans
p
INNER JOIN msdb.dbo.sysmaintplan_subplans sp ON
p.id
= sp.plan_id
INNER JOIN msdb.dbo.sysmaintplan_log spl ON spl.subplan_id=sp.subplan_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules j ON
j.schedule_id
= sp.schedule_id
where name like
'%Hebdo%'
|
Server_name
|
Mntce_Plan
|
start
|
end
|
next_run_date
|
next_run_time
|
TotalMinutes
|
|
W-INSTANCE-1\NAMED
|
MaintenancePlanHebdo
|
2012-09-08
20:00:01.800
|
2012-09-08
20:09:06.193
|
20120915
|
200000
|
9
|
|
W-INSTANCE-1\NAMED1
|
MaintenancePlanHebdo
|
2012-09-08
22:00:15.707
|
2012-09-09
22:46:29.327
|
20120915
|
220000
|
1486
|
|
W-INSTANCE-1\NAMED2
|
MaintenancePlanHebdo
|
2012-09-08
20:00:01.160
|
2012-09-09
05:05:51.273
|
20120915
|
200000
|
545
|
-- History
of executions
SELECT
ld.server_name AS [ServerName],
ld.start_time AS [StartTime],
ld.end_time AS [EndTime],
ld.error_number AS [ErrorNo],
ld.error_message AS [ErrorMessage],
ld.command AS [Command],
ld.succeeded AS [Succeeded]
FROM msdb.dbo.sysmaintplan_plans
AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON
sp.plan_id=s.id
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON
spl.subplan_id=sp.subplan_id
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS ld ON
ld.task_detail_id=spl.task_detail_id
ORDER BY [StartTime]
DESC
|
ServerName
|
StartTime
|
EndTime
|
ErrorNo
|
ErrorMessage
|
Command
|
Succeeded
|
|
SRV
|
2015-04-07 20:00:36.000
|
2015-04-07 20:00:36.000
|
NULL
|
|
|
1
|
|
SRV
|
2015-04-07 20:00:36.000
|
2015-04-07 20:00:36.000
|
NULL
|
|
|
1
|
|
SRV
|
2015-04-07 20:00:02.000
|
2015-04-07 20:00:36.000
|
NULL
|
|
|
1
|
|
SRV
|
2015-04-06 20:00:36.000
|
2015-04-06 20:00:36.000
|
NULL
|
|
|
1
|
|
SRV
|
2015-04-06 20:00:36.000
|
2015-04-06 20:00:37.000
|
NULL
|
|
|
1
|
BTW Job
execution History, thanks to
http://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/
I just added
the « run status »
-- Job History
of executions
select
j.name as
'JobName',
s.step_id as 'Step',
s.step_name as 'StepName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 )
/ 60)
as 'RunDurationMinutes',
case run_status when 1 then 'OK'
when 0 then 'KO' end
status
From msdb.dbo.sysjobs
j
INNER JOIN msdb.dbo.sysjobsteps s
ON
j.job_id
= s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON
s.job_id
= h.job_id
AND
s.step_id
= h.step_id
AND
h.step_id
<> 0
where j.enabled = 1 --Only Enabled Jobs
and j.name = 'ETL_OLAP'
/*
and
msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN
'12/08/2012' and '12/10/2012'
--Uncomment for date range queries
*/
order by JobName, RunDateTime desc
|
JobName
|
Step
|
StepName
|
RunDateTime
|
RunDurationMinutes
|
status
|
|
ETL_OLAP
|
2
|
2_OLAP
|
2015-04-08 00:08:31.000
|
1
|
OK
|
|
ETL_OLAP
|
1
|
1_ETL
|
2015-04-07 23:30:00.000
|
39
|
OK
|
|
ETL_OLAP
|
2
|
2_OLAP
|
2015-04-06 23:57:36.000
|
1
|
OK
|
|
ETL_OLAP
|
1
|
1_ETL
|
2015-04-06 23:30:00.000
|
28
|
OK
|
|
ETL_OLAP
|
2
|
2_OLAP
|
2015-04-04 00:08:12.000
|
1
|
OK
|
|
ETL_OLAP
|
1
|
1_ETL
|
2015-04-03 23:30:00.000
|
38
|
OK
|
|
ETL_OLAP
|
2
|
2_OLAP
|
2015-04-03 00:07:43.000
|
1
|
OK
|
0.2 Notification Operator
Management
USE master
GO
--Enable database mail
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
--create database mail account
EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'Job
Alerts Account',
@email_address = 'SQL.Alerts@comp_name',
@replyto_address = 'mail_1@comp_name',
@description = 'SQL
Server Job Alerts Account',
@mailserver_name = 'sr-smtp'
GO
--create database mail profile
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'Job
Alerts Profile',
@description=N'SQL Server Job Alerts Profile'
GO
--add account to profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'Job
Alerts Profile',
@account_name=N'Job Alerts Account', @sequence_number=1
GO
--make profile public, default
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'Job
Alerts Profile',
@is_default=1
GO
--Create mail operator account
EXEC msdb.dbo.sp_add_operator @name=N'JobsAlertsEmailOperator', @enabled=1,
@pager_days=0,
@email_address=N'mail_1@comp_name;mail_2@comp_name'
GO
use msdb
go
EXECUTE sp_help_operator
go
0.3 sql server disable maintenance
plan using tsql
USE msdb ;
SELECT name FROM sysjobs WHERE enabled = 1
EXEC dbo.sp_update_job
@job_name = N'MP_INST1_JOUR_PRO_2.Subplan_1',
@enabled = 0;
GO
1] Index Maintenance
|
Goal
|
Index Reorganize or Rebuild
based on % Fragmentation threshold
|
|
Version
|
2008 R2
|
|
Views
Function
|
sys.databases sys.indexes sys.partitions sys.objects sys.schemas
sys.dm_db_index_physical_stats
|
|
Log as
|
SYSADMIN
|
Should be run under a Weekly basis. This is much better than
the default Index Rebuild provided by SSIS box ( very Transaction Log consuming
).
1.1 Create Procedure in MASTER DB
This procedure was adapted from MS :
-
First Argument : Reorganization
threshold % ( usually 10 )
-
Second Argument : Rebuild threshold
% ( usually 30 )
It
checks if the Index is big enough to benefit of a maintenance ( > 1 MB )
& if allow_page_locks = 1
exec sp_adb_IndexMntc '10','30'
USE master
IF EXISTS (SELECT name FROM sysobjects
WHERE name
= N'sp_adb_IndexMntc ' AND type
= 'P')
DROP PROCEDURE sp_adb_IndexMntc
GO
CREATE PROCEDURE [dbo].[sp_adb_IndexMntc] ( @FragmentationLevel1
int , @FragmentationLevel2
int )
--@FragmentationLevel1
int = 10,
--@FragmentationLevel2
int = 30
AS
BEGIN
-- Ensure a
USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @command2 nvarchar(4000);
DECLARE @dbname nvarchar(64);
DECLARE @IndexSizeKB int;
DECLARE @IndexSizeKB2 varchar(50);
DECLARE @ALLOW_PAGE_LOCK int;
DECLARE @txtbody nvarchar(512);
select @dbname = name
from sys.databases where database_id =
DB_ID() ;
--
Conditionally select tables and indexes from the sys.dm_db_index_physical_stats
function
-- and
convert object and index IDs to names.
-- ensure
the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#work_to_do')) IS NOT
NULL
DROP TABLE #work_to_do;
-- ensure
the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#TempSp_spaceused')) IS NOT
NULL
DROP TABLE #TempSp_spaceused;
-- A
procedure level temp table to store the resulting Index Size After
CREATE TABLE #TempSp_spaceused
(
tableName varchar(100), numberofRows
integer, reservedSize
varchar(50),
dataSize varchar(50), indexSize
varchar(50), unusedSize varchar(50)
)
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number
AS partitionnum,
avg_fragmentation_in_percent
AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL ,
NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @FragmentationLevel1
AND index_id > 0 ;
-- Declare
the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT
* FROM #work_to_do;
-- Open the
cursor.
OPEN partitions;
-- Loop
through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname
= QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s
ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id
= @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid
AND index_id = @indexid;
-- is the index big enough ?
or row_lock_page accepted when reorganizing allow_page_locks=1 ?
SET @command2
= N'INSERT #TempSp_spaceused EXEC sp_spaceused "'
+ @schemaname + N'.'
+ @objectname + N'"'
;
exec (@command2);
select @IndexSizeKB = replace(indexSize,' KB','')
from #TempSp_spaceused ;
select @ALLOW_PAGE_LOCK = allow_page_locks
from sys.indexes where object_id = @objectid
and index_id = @indexid
;
IF @IndexSizeKB
> 1024 AND @ALLOW_PAGE_LOCK = 1
BEGIN
-- 30 is an
arbitrary decision point at which to switch between reorganizing and
rebuilding.
IF @frag
< @FragmentationLevel2
SET
@command = N'ALTER INDEX ' + @indexname
+ N' ON ' + @schemaname
+ N'.' +
@objectname + N' REORGANIZE';
IF @frag
>= @FragmentationLevel2
SET @command
= N'ALTER INDEX ' + @indexname
+ N' ON ' + @schemaname
+ N'.' +
@objectname + N' REBUILD';
IF @partitioncount
> 1
SET @command
= @command + N'
PARTITION=' +
CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
-- get new Index size
-- schemaname name is needed to take care of Msg 15009, The
object xxx does not exist in database xxx or is invalid for this operation.
SET @command2
= N'INSERT #TempSp_spaceused EXEC sp_spaceused "'
+ @schemaname + N'.'
+ @objectname + N'"'
;
exec (@command2);
select @IndexSizeKB2 = indexSize
from #TempSp_spaceused ;
SELECT @txtBody = 'Executed:
' + @dbname + '
Frag ' + cast(ceiling(@frag)
as nvarchar(10))
+ ' Idx Size KB before ' + cast(ceiling(@IndexSizeKB) as nvarchar(10)) +
' ' + '
Idx Size KB after ' +
@IndexSizeKB2 + ' ' +
@command ;
print @txtBody
-- PRINT N'Executed: ' + @dbname + ' Frag ' +
cast(ceiling(@frag) as nvarchar(10)) + ' Idx Size KB before ' +
cast(ceiling(@IndexSizeKB) as nvarchar(10)) + ' ' + @command;
exec master..xp_logevent 50001, @txtBody, 'Informational'
END;
END;
-- Close
and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the
temporary tables.
DROP TABLE #work_to_do;
DROP TABLE #TempSp_spaceused;
END
GO
1.2 Make the Procedure visible
from every Databases
-- SS2k5
provides a stored procedure to mark the object as system
USE master
go
EXEC sys.sp_MS_marksystemobject sp_adb_IndexMntc
GO
--test
USE DB_NAME
go
EXEC sp_adb_IndexMntc '10','30'
go
1.3 Run it in every Databases
EXECUTE master.sys.sp_MSforeachdb 'USE
[?]; exec sp_adb_IndexMntc ''10'',''30'''
·
Task T-SQL
EXEC
sys.sp_MSforeachdb 'USE ?;DECLARE @name VARCHAR(50)
SELECT
@name=DB_NAME() ; if @name != ''TEMPDB'' begin
USE
[?]; exec sp_adb_IndexMntc ''30'',''60''
end'
or For Sharepoint :
EXEC master..sp_MSForeachdb '
USE [?]
if ''?'' in
(''master'',''model'',''msdb'',''tempdb'') return
BEGIN
IF
NOT EXISTS (SELECT name FROM sysobjects WHERE name =
N''Proc_DefragmentIndices'' AND type = ''P'')
-- SELECT ''?''
exec sp_adb_IndexMntc ''10'',''30''
END
'
It may then be executed in a T-SQL
Maintenance Task scheduled every week.
1st Run
Executed: DB_NAME Frag 63 Idx Size KB
before 13536 Idx Size KB after 11464 KB
ALTER INDEX [IX_TAB_ASSET] ON [SCHEMA].[TAB_ASSET] REBUILD
Executed: DB_NAME Frag 78 Idx Size KB
before 66672 Idx Size KB after 66120 KB
ALTER INDEX [PK_TABLE_SD] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 97 Idx Size KB
before 66120 Idx Size KB after 60744 KB
ALTER INDEX [FK_TABLE_SD_CATALOG] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 95 Idx Size KB
before 60744 Idx Size KB after 56680 KB
ALTER INDEX [FK_TABLE_SD_EMPLOYEE] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 97 Idx Size KB
before 56680 Idx Size KB after 51040 KB
ALTER INDEX [FK_TABLE_SD_LOCATION] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 80 Idx Size KB
before 51040 Idx Size KB after 46992 KB
ALTER INDEX [FK_TABLE_SD_PROJECT] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 80 Idx Size KB
before 46992 Idx Size KB after 42944 KB
ALTER INDEX [FK_TABLE_SD_EP_BUDGET] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 80 Idx Size KB
before 42944 Idx Size KB after 38896 KB
ALTER INDEX [FK_TABLE_SD_EP_ORDER] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 80 Idx Size KB
before 38896 Idx Size KB after 34848 KB
ALTER INDEX [FK_TABLE_SD_SD_KBASE] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 93 Idx Size KB
before 2080 Idx Size KB after 2080 KB
ALTER INDEX [PK_DEPARTMENT] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 47 Idx Size KB
before 2080 Idx Size KB after 2024 KB
ALTER INDEX [DEPARTMENT_IDX1] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 89 Idx Size KB
before 2024 Idx Size KB after 1984 KB
ALTER INDEX [DEPARTMENT_IDX10] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1984 Idx Size KB after 1944 KB
ALTER INDEX [DEPARTMENT_IDX11] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 1944 Idx Size KB after 1888 KB ALTER
INDEX [DEPARTMENT_IDX12] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 85 Idx Size KB
before 1888 Idx Size KB after 1784 KB
ALTER INDEX [DEPARTMENT_IDX2] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 55 Idx Size KB
before 1784 Idx Size KB after 1720 KB
ALTER INDEX [DEPARTMENT_IDX3] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 75 Idx Size KB
before 1720 Idx Size KB after 1680 KB
ALTER INDEX [DEPARTMENT_IDX4] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1680 Idx Size KB after 1640 KB
ALTER INDEX [DEPARTMENT_IDX5] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1640 Idx Size KB after 1600 KB
ALTER INDEX [DEPARTMENT_IDX6] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 75 Idx Size KB
before 1600 Idx Size KB after 1568 KB
ALTER INDEX [DEPARTMENT_IDX7] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1568 Idx Size KB after 1536 KB
ALTER INDEX [DEPARTMENT_IDX8] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 1536 Idx Size KB after 1480 KB
ALTER INDEX [DEPARTMENT_IDX9] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 90 Idx Size KB
before 1480 Idx Size KB after 1424 KB
ALTER INDEX [FK_DEPARTMENT_COST_CENTER] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1424 Idx Size KB after 1384 KB
ALTER INDEX [FK_DEPARTMENT_DEPARTMENT] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1384 Idx Size KB after 1344 KB
ALTER INDEX [FK_DEPARTMENT_EMPLOYEE] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 2736 Idx Size KB after 2720 KB
ALTER INDEX [PK_EMPLOYEE] ON [SCHEMA].[EMPLOYEE] REBUILD
Executed: DB_NAME Frag 99 Idx Size KB
before 2720 Idx Size KB after 2360 KB
ALTER INDEX [FK_EMPLOYEE_DEPARTMENT] ON [SCHEMA].[EMPLOYEE] REBUILD
Executed: DB_NAME Frag 86 Idx Size KB
before 2360 Idx Size KB after 1928 KB
ALTER INDEX [FK_EMPLOYEE_LOCATION] ON [SCHEMA].[EMPLOYEE] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 1928 Idx Size KB after 1512 KB
ALTER INDEX [FK_EMPLOYEE_SCHEDULE] ON [SCHEMA].[EMPLOYEE] REBUILD
Executed: DB_NAME Frag 95 Idx Size KB
before 1512 Idx Size KB after 1064 KB
ALTER INDEX [IX_EMPLOYEE] ON [SCHEMA].[EMPLOYEE] REBUILD
Executed: DB_NAME Frag 86 Idx Size KB
before 15232 Idx Size KB after 15216 KB
ALTER INDEX [PK_METRIC_RESULTS] ON [SCHEMA].[METRIC_RESULTS] REBUILD
Executed: DB_NAME Frag 82 Idx Size KB
before 58224 Idx Size KB after 57496 KB
ALTER INDEX [PK_HISTORY] ON [SCHEMA].[HISTORY] REBUILD
Executed: DB_NAME Frag 95 Idx Size KB
before 57496 Idx Size KB after 14320 KB
ALTER INDEX [FK_HISTORY_REFERENCE_HISTORY_PARAM] ON [SCHEMA].[HISTORY] REBUILD
Executed: DB_NAME Frag 49 Idx Size KB
before 7280 Idx Size KB after 7280 KB
ALTER INDEX [PK_TABLE_SD] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 56 Idx Size KB
before 7280 Idx Size KB after 7104 KB
ALTER INDEX [TABLE_SD_IDX1] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 89 Idx Size KB
before 7104 Idx Size KB after 6880 KB
ALTER INDEX [TABLE_SD_IDX10] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 6880 Idx Size KB after 6672 KB
ALTER INDEX [TABLE_SD_IDX11] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 6672 Idx Size KB after 6464 KB
ALTER INDEX [TABLE_SD_IDX12] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 6464 Idx Size KB after 6256 KB
ALTER INDEX [TABLE_SD_IDX13] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 6256 Idx Size KB after 6048 KB
ALTER INDEX [TABLE_SD_IDX14] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 90 Idx Size KB
before 6048 Idx Size KB after 5904 KB
ALTER INDEX [TABLE_SD_IDX15] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 5904 Idx Size KB after 5768 KB
ALTER INDEX [TABLE_SD_IDX16] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 82 Idx Size KB
before 5768 Idx Size KB after 5376 KB
ALTER INDEX [TABLE_SD_IDX2] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 53 Idx Size KB
before 5376 Idx Size KB after 5224 KB
ALTER INDEX [TABLE_SD_IDX3] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 79 Idx Size KB
before 5224 Idx Size KB after 5128 KB
ALTER INDEX [TABLE_SD_IDX4] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 79 Idx Size KB
before 5128 Idx Size KB after 5032 KB
ALTER INDEX [TABLE_SD_IDX5] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 88 Idx Size KB
before 5032 Idx Size KB after 4896 KB
ALTER INDEX [TABLE_SD_IDX6] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 81 Idx Size KB
before 4896 Idx Size KB after 4760 KB
ALTER INDEX [TABLE_SD_IDX7] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 89 Idx Size KB
before 4760 Idx Size KB after 4616 KB
ALTER INDEX [TABLE_SD_IDX8] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 87 Idx Size KB
before 4616 Idx Size KB after 4408 KB
ALTER INDEX [TABLE_SD_IDX9] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 40 Idx Size KB
before 4408 Idx Size KB after 4408 KB
ALTER INDEX [Missing_IDX_TABLE_SD1] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME Frag 95 Idx Size KB
before 10480 Idx Size KB after 10464 KB
ALTER INDEX [PK_LOCATION] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 72 Idx Size KB
before 10464 Idx Size KB after 10016 KB
ALTER INDEX [LOCATION_IDX1] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 91 Idx Size KB
before 10016 Idx Size KB after 9728 KB
ALTER INDEX [LOCATION_IDX10] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 95 Idx Size KB
before 9728 Idx Size KB after 9424 KB
ALTER INDEX [LOCATION_IDX11] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 90 Idx Size KB
before 9424 Idx Size KB after 8672 KB
ALTER INDEX [LOCATION_IDX2] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 73 Idx Size KB
before 8672 Idx Size KB after 8264 KB
ALTER INDEX [LOCATION_IDX3] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 75 Idx Size KB
before 8264 Idx Size KB after 8016 KB
ALTER INDEX [LOCATION_IDX4] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 75 Idx Size KB
before 8016 Idx Size KB after 7768 KB
ALTER INDEX [LOCATION_IDX5] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 95 Idx Size KB
before 7768 Idx Size KB after 7464 KB ALTER
INDEX [LOCATION_IDX6] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 93 Idx Size KB
before 7464 Idx Size KB after 7200 KB
ALTER INDEX [LOCATION_IDX7] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 89 Idx Size KB
before 7200 Idx Size KB after 6944 KB
ALTER INDEX [LOCATION_IDX8] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 94 Idx Size KB
before 6944 Idx Size KB after 5536 KB
ALTER INDEX [LOCATION_IDX9] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 75 Idx Size KB
before 5536 Idx Size KB after 5288 KB
ALTER INDEX [FK_LOCATION_EMPLOYEE] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 73 Idx Size KB
before 5288 Idx Size KB after 5040 KB
ALTER INDEX [FK_LOCATION_LOCATION] ON [SCHEMA].[LOCATION] REBUILD
Executed: DB_NAME Frag 13 Idx Size KB
before 5040 Idx Size KB after 5040 KB
ALTER INDEX [Missing_IDX_LOCATION1] ON [SCHEMA].[LOCATION] REORGANIZE
Executed: DB_NAME Frag 89 Idx Size KB
before 3408 Idx Size KB after 3408 KB
ALTER INDEX [PK_SHARED_USE] ON [SCHEMA].[SHARED_USE] REBUILD
Executed: DB_NAME Frag 97 Idx Size KB
before 3408 Idx Size KB after 2936 KB
ALTER INDEX [FK_SHARED_USE_TAB_ASSET] ON [SCHEMA].[SHARED_USE] REBUILD
Executed: DB_NAME Frag 96 Idx Size KB
before 2936 Idx Size KB after 2328 KB
ALTER INDEX [FK_SHARED_USE_COST_CENTER] ON [SCHEMA].[SHARED_USE] REBUILD
Executed: DB_NAME Frag 97 Idx Size KB
before 2328 Idx Size KB after 1800 KB
ALTER INDEX [FK_SHARED_USE_DEPARTMENT] ON [SCHEMA].[SHARED_USE] REBUILD
Executed: DB_NAME Frag 91 Idx Size KB
before 1800 Idx Size KB after 1288 KB
ALTER INDEX [FK_SHARED_USE_EMPLOYEE] ON [SCHEMA].[SHARED_USE] REBUILD
2nd Run
Executed: DB_NAME
Frag 75 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX10] ON [SCHEMA].[DEPARTMENT]
REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX11] ON [SCHEMA].[DEPARTMENT]
REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX4] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX5] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX6] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME
Frag 50 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX7] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME
Frag 50 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [DEPARTMENT_IDX8] ON [SCHEMA].[DEPARTMENT] REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [FK_DEPARTMENT_COST_CENTER] ON [SCHEMA].[DEPARTMENT]
REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [FK_DEPARTMENT_DEPARTMENT] ON [SCHEMA].[DEPARTMENT]
REBUILD
Executed: DB_NAME
Frag 67 Idx Size KB before 1344 Idx Size
KB after 1344 KB ALTER INDEX [FK_DEPARTMENT_EMPLOYEE] ON [SCHEMA].[DEPARTMENT]
REBUILD
Executed: DB_NAME
Frag 12 Idx Size KB before 1064 Idx Size
KB after 1064 KB ALTER INDEX [FK_EMPLOYEE_DEPARTMENT] ON [SCHEMA].[EMPLOYEE]
REORGANIZE
Executed: DB_NAME
Frag 12 Idx Size KB before 1064 Idx Size
KB after 1064 KB ALTER INDEX [FK_EMPLOYEE_LOCATION] ON [SCHEMA].[EMPLOYEE]
REORGANIZE
Executed: DB_NAME
Frag 12 Idx Size KB before 1064 Idx Size
KB after 1064 KB ALTER INDEX [FK_EMPLOYEE_SCHEDULE] ON [SCHEMA].[EMPLOYEE]
REORGANIZE
Executed: DB_NAME
Frag 28 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX15] ON [SCHEMA].[TABLE_SD] REORGANIZE
Executed: DB_NAME
Frag 43 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX16] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME
Frag 86 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX4] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME
Frag 58 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX5] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME
Frag 58 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX6] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME
Frag 45 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX7] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME
Frag 34 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [TABLE_SD_IDX8] ON [SCHEMA].[TABLE_SD] REBUILD
Executed: DB_NAME
Frag 60 Idx Size KB before 4408 Idx Size
KB after 4408 KB ALTER INDEX [Missing_IDX_TABLE_SD1] ON [SCHEMA].[TABLE_SD]
REBUILD
Executed: DB_NAME
Frag 24 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [LOCATION_IDX11] ON [SCHEMA].[LOCATION] REORGANIZE
Executed: DB_NAME
Frag 18 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [LOCATION_IDX4] ON [SCHEMA].[LOCATION] REORGANIZE
Executed: DB_NAME
Frag 30 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [LOCATION_IDX5] ON [SCHEMA].[LOCATION] REORGANIZE
Executed: DB_NAME
Frag 30 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [LOCATION_IDX6] ON [SCHEMA].[LOCATION] REORGANIZE
Executed: DB_NAME
Frag 14 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [LOCATION_IDX8] ON [SCHEMA].[LOCATION] REORGANIZE
Executed: DB_NAME
Frag 18 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [FK_LOCATION_EMPLOYEE] ON [SCHEMA].[LOCATION]
REORGANIZE
Executed: DB_NAME
Frag 30 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [FK_LOCATION_LOCATION] ON [SCHEMA].[LOCATION]
REORGANIZE
Executed: DB_NAME
Frag 13 Idx Size KB before 5040 Idx Size
KB after 5040 KB ALTER INDEX [Missing_IDX_LOCATION1] ON [SCHEMA].[LOCATION]
REORGANIZE
1.4 Run it in some Databases
DECLARE @dbName
VARCHAR(50) --
database name
DECLARE @sql
NVARCHAR(MAX)
DECLARE db_cursor
CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases WHERE name NOT
IN ('CM_001','tempdb','model')
OPEN db_cursor
FETCH NEXT
FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS
= 0
BEGIN
SET @sql
= N'USE ' + QUOTENAME(@dbName) +
' exec sp_adb_IndexMntc ''10'',''30'''
EXEC sp_ExecuteSql @sql
FETCH
NEXT FROM db_cursor
INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
