Could not start tuning advisor. Getting the following error
"Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)"
Then you have to run the following script.
USE [msdb]
GO/****** Object: StoredProcedure [dbo].[sp_DTA_start_xmlprefix] Script Date: 12/31/2008 10:56:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_start_xmlprefix]asbegin declare @startTags nvarchar(128) set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>' select @startTagsendGO/****** Object: Table [dbo].[DTA_input] Script Date: 12/31/2008 10:56:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_input]( [SessionName] [sysname] NOT NULL, [SessionID] [int] IDENTITY(1,1) NOT NULL, [TuningOptions] [ntext] NOT NULL, [CreationTime] [datetime] NOT NULL DEFAULT (getdate()), [ScheduledStartTime] [datetime] NOT NULL DEFAULT (getdate()), [ScheduledJobName] [sysname] NOT NULL DEFAULT (''), [InteractiveStatus] [tinyint] NOT NULL DEFAULT ((0)), [LogTableName] [nvarchar](1280) NOT NULL DEFAULT (''), [GlobalSessionID] [uniqueidentifier] NULL DEFAULT (newid()),PRIMARY KEY CLUSTERED ( [SessionID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_progress] Script Date: 12/31/2008 10:57:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_progress]( [ProgressEventID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NULL, [TuningStage] [tinyint] NOT NULL DEFAULT ((0)), [WorkloadConsumption] [tinyint] NOT NULL, [EstImprovement] [int] NOT NULL DEFAULT ((0)), [ProgressEventTime] [datetime] NOT NULL DEFAULT (getdate()), [ConsumingWorkLoadMessage] [nvarchar](256) NULL, [PerformingAnalysisMessage] [nvarchar](256) NULL, [GeneratingReportsMessage] [nvarchar](256) NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_output] Script Date: 12/31/2008 10:57:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_output]( [SessionID] [int] NOT NULL, [TuningResults] [ntext] NOT NULL, [StopTime] [datetime] NOT NULL DEFAULT (getdate()), [FinishStatus] [tinyint] NOT NULL DEFAULT ((0)),PRIMARY KEY CLUSTERED ( [SessionID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_tuninglog] Script Date: 12/31/2008 11:01:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_tuninglog]( [SessionID] [int] NOT NULL, [RowID] [int] NOT NULL, [CategoryID] [nvarchar](4) NOT NULL, [Event] [ntext] NULL, [Statement] [ntext] NULL, [Frequency] [int] NOT NULL, [Reason] [ntext] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_database] Script Date: 12/31/2008 10:57:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_database]( [DatabaseID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NOT NULL, [DatabaseName] [sysname] NOT NULL, [IsDatabaseSelectedToTune] [int] NULL,PRIMARY KEY CLUSTERED ( [DatabaseID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_query] Script Date: 12/31/2008 10:59:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_query]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [StatementType] [smallint] NOT NULL, [StatementString] [ntext] NOT NULL, [CurrentCost] [float] NOT NULL, [RecommendedCost] [float] NOT NULL, [Weight] [float] NOT NULL, [EventString] [ntext] NULL, [EventWeight] [float] NOT NULL,CONSTRAINT [DTA_reports_query_pk] PRIMARY KEY CLUSTERED ( [SessionID] ASC, [QueryID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_querydatabase] Script Date: 12/31/2008 11:00:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_querydatabase]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [DatabaseID] [int] NOT NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_table] Script Date: 12/31/2008 11:00:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_table]( [TableID] [int] IDENTITY(1,1) NOT NULL, [DatabaseID] [int] NOT NULL, [SchemaName] [sysname] NOT NULL, [TableName] [sysname] NOT NULL, [IsView] [bit] NOT NULL DEFAULT ((0)),PRIMARY KEY CLUSTERED ( [TableID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_partitionfunction] Script Date: 12/31/2008 10:58:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_partitionfunction]( [PartitionFunctionID] [int] IDENTITY(1,1) NOT NULL, [DatabaseID] [int] NOT NULL, [PartitionFunctionName] [sysname] NOT NULL, [PartitionFunctionDefinition] [ntext] NOT NULL,PRIMARY KEY CLUSTERED ( [PartitionFunctionID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_partitionscheme] Script Date: 12/31/2008 10:59:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_partitionscheme]( [PartitionSchemeID] [int] IDENTITY(1,1) NOT NULL, [PartitionFunctionID] [int] NOT NULL, [PartitionSchemeName] [sysname] NOT NULL, [PartitionSchemeDefinition] [ntext] NOT NULL,PRIMARY KEY CLUSTERED ( [PartitionSchemeID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_tableview] Script Date: 12/31/2008 11:01:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_tableview]( [TableID] [int] NOT NULL, [ViewID] [int] NOT NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_querytable] Script Date: 12/31/2008 11:00:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_querytable]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [TableID] [int] NOT NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_index] Script Date: 12/31/2008 10:58:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_index]( [IndexID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [IndexName] [sysname] NOT NULL, [IsClustered] [bit] NOT NULL DEFAULT ((0)), [IsUnique] [bit] NOT NULL DEFAULT ((0)), [IsHeap] [bit] NOT NULL DEFAULT ((1)), [IsExisting] [bit] NOT NULL DEFAULT ((1)), [Storage] [float] NOT NULL, [NumRows] [int] NOT NULL, [IsRecommended] [bit] NOT NULL DEFAULT ((0)), [RecommendedStorage] [float] NOT NULL, [PartitionSchemeID] [int] NULL, [SessionUniquefier] [int] NULL,PRIMARY KEY CLUSTERED ( [IndexID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_column] Script Date: 12/31/2008 10:57:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_column]( [ColumnID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [ColumnName] [sysname] NOT NULL,PRIMARY KEY CLUSTERED ( [ColumnID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_queryindex] Script Date: 12/31/2008 11:00:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_queryindex]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [IndexID] [int] NOT NULL, [IsRecommendedConfiguration] [bit] NOT NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_querycolumn] Script Date: 12/31/2008 10:59:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_querycolumn]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [ColumnID] [int] NOT NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[DTA_reports_indexcolumn] Script Date: 12/31/2008 10:58:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DTA_reports_indexcolumn]( [IndexID] [int] NOT NULL, [ColumnID] [int] NOT NULL, [ColumnOrder] [int] NULL, [PartitionColumnOrder] [int] NOT NULL DEFAULT ((0)), [IsKeyColumn] [bit] NOT NULL DEFAULT ((1)), [IsDescendingColumn] [bit] NOT NULL DEFAULT ((1))) ON [PRIMARY]GO/****** Object: StoredProcedure [dbo].[sp_DTA_check_permission] Script Date: 12/31/2008 10:53:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_check_permission] @SessionID intas begin declare @retcode int declare @dbname nvarchar(128) declare @sql nvarchar(256) declare @dbid intset nocount on
-- Check if SA if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) begin return(0) end
-- if not yukon return if (patindex('%9.00.%',@@version) = 0) begin return (1) end -- declare and open a cursor and get all the databases specified in the input declare db_cursor cursor for select DatabaseName from DTA_reports_database where SessionID = @SessionID and IsDatabaseSelectedToTune = 1 -- open open db_cursor -- fetch first db name fetch next from db_cursor into @dbname -- loop and get all the databases selected to tune while @@fetch_status = 0 begin -- build use db string select @dbid = DB_ID(@dbname)
-- In Yukon this masks the error messages set @sql = N'begin try dbcc autopilot(5,@dbid) WITH NO_INFOMSGS end try begin catch set @retcode = 1 end catch'
execute sp_executesql @sql , N'@dbid int output, @retcode int OUTPUT' , @dbid output , @retcode output
-- if caller is not member of dbo if (@retcode = 1) begin -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(1) end
fetch from db_cursor into @dbname end -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(0) endGO/****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml] Script Date: 12/31/2008 10:54:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_index_usage_helper_xml] @SessionID int, @IsRecommended intas beginselect 1 as Tag, NULL as Parent, '' as [IndexUsageReport!1!!ELEMENT], case when @IsRecommended = 1 then 'false' else 'true' end as [IndexUsageReport!1!Current], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] union allselect 2 as Tag, 1 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID) union allselect 3 as Tag, 2 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @IsRecommended and D.SessionID = @SessionID GROUP BY D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseIDunion all
select 4 as Tag, 3 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , R.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT]
from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID,T.SchemaName, T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID
union allselect 5 as Tag, 4 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D1.DatabaseID as [Database!2!DatabaseID!hide], D1.DatabaseName as [Database!2!Name!ELEMENT] , T1.SchemaName as [Schema!3!Name!ELEMENT] , T1.TableID as [Table!4!TableID!hide], T1.TableName as [Table!4!Name!ELEMENT], I1.IndexID as [Index!5!IndexID!hide], I1.IndexName as [Index!5!Name!ELEMENT], R.Count as [Index!5!NumberOfReferences!ELEMENT], CAST(R.Usage as decimal(38,2)) as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_index] as I1, [msdb].[dbo].[DTA_reports_table] as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end
from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide], [Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide]
FOR XML EXPLICITendGO/****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_relational] Script Date: 12/31/2008 10:54:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_index_usage_helper_relational] @SessionID int, @IsRecommended int as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' from DTA_reports_database as D1 , DTA_reports_index as I1, DTA_reports_table as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end
from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID
GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc endGO/****** Object: StoredProcedure [dbo].[sp_DTA_database_access_helper_xml] Script Date: 12/31/2008 10:53:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[sp_DTA_database_access_helper_xml] @SessionID intasbegin select 1 as Tag, NULL as Parent, '' as [DatabaseAccessReport!1!!ELEMENT], NULL as [Database!2!Name!ELEMENT] , NULL as [Database!2!NumberOfReferences!ELEMENT], NULL as [Database!2!PercentUsage!ELEMENT] union all
select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName , R.Count , CAST(R.Usage as decimal(38,2)) from [msdb].[dbo].[DTA_reports_database] as D1 , ( select D.DatabaseID,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end
from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_database] as D LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID and Q.SessionID = QD.SessionID and Q.SessionID = @SessionID GROUP BY D.DatabaseID ) as R where R.DatabaseID = D1.DatabaseID and D1.SessionID = @SessionID and R.Count > 0 order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc FOR XML EXPLICITendGO
