|
singlehouse03
Starting Member
2 Posts |
 Posted - 2014-02-15 : 17:15:09
|
| Here is error message:Job '_SQL_AutoIndexAll' : Step 2, 'SQLAutoIndexPrepDataCapture' : Began Executing 2014-02-15 11:16:27Msg 8115, Sev 16, State 2, Line 1 : Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003] Msg 3621, Sev 16, State 1, Line 1 : The statement has been terminated. [SQLSTATE 01000]here is all script file of 'SQLAutoIndexPrepDataCapture'--how to fix it?ThanksUSE [msdb] GO/****** Object: StoredProcedure [dbo].[SQLAutoIndexPrepDataCapture] Script Date: 02/15/2014 11:35:05 ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOCREATE PROC [dbo].[SQLAutoIndexPrepDataCapture] @ScanMode VARCHAR(15) = 'LIMITED' -- LIMITED, SAMPLED, or DETAILED; from fast / non-leaf scan to slow / full B-Tree ASSET NOCOUNT ON--DECLARE @db_id SMALLINT DECLARE @db_id BIGINT DECLARE @SQLString NVARCHAR(2500) DECLARE @InternalDBName NVARCHAR(128)IF (Object_Id('tempdb..#tbl_InstancePrepDatabases')) Is Not Null Drop Table #tbl_InstancePrepDatabases --the following table is used to hold the data relating to the Db state, this is also used to control the looping of databases. CREATE TABLE #tbl_InstancePrepDatabases (int_InstDb_RowRef bigint IDENTITY(1,1) PRIMARY KEY, --Constraint name not given as this can cause conflicts when used with local temp tables --int_DbId smallint, int_DbId bigint, nvchr_DbName nvarchar(125))CREATE INDEX [Ix_Table_#tbl_InstancePrepDatabases_Col_int_DbId] ON [dbo].[#tbl_InstancePrepDatabases](int_DbId) INCLUDE (nvchr_DbName)INSERT INTO #tbl_InstancePrepDatabases (nvchr_DbName, int_DbId) SELECT [name], [database_id] FROM sys.databases WHERE ([state] + is_in_standby + is_read_only) = 0 AND [name] NOT IN (SELECT DISTINCT [nvchr_DbName] FROM systems..SQLAutoIndex_Exclusions UNION SELECT 'master' UNION SELECT 'model' UNION SELECT 'msdb' UNION SELECT 'Mssqlresourcedb' --this should not even show up, but for completeness added it to system Db exculsions UNION SELECT 'tempdb' UNION SELECT 'systems')--DECLARE @intInstDbCounter smallint DECLARE @intInstDbCounter bigintSET @intInstDbCounter = (SELECT ISNULL(MAX(int_InstDb_RowRef),0) FROM #tbl_InstancePrepDatabases)IF @intInstDbCounter = 0 RETURN --This means that there are no databases in this instances which are in a state which allows them to be defraged--the databases contained within the temp table are looped through in reverese order WHILE @intInstDbCounter > 0 BEGIN --sets the two variables used throughout the loop to control the database being worked on SET @db_id = (SELECT int_DbId FROM #tbl_InstancePrepDatabases WHERE int_InstDb_RowRef = @intInstDbCounter) SET @InternalDBName = (SELECT nvchr_DbName FROM #tbl_InstancePrepDatabases WHERE int_InstDb_RowRef = @intInstDbCounter) --deletes any existing data held for the database about to be analysed SET @SQLString = 'DELETE FROM systems.dbo.tbl_INDEX_FragData_Detailed WHERE intDbId = ' + CAST(@db_id as varchar) EXEC (@SQLString) --updateds the summary table to signify that the data for the relevant database has been purged SET @SQLString = 'UPDATE systems.dbo.tbl_INDEX_FragData_DB_Summary SET dte_DataPurged = GETUTCDATE() WHERE dte_DataPurged is null AND ' SET @SQLString = @SQLString + 'dte_DataRfshStart IN (SELECT MAX(dte_DataRfshStart) FROM systems.dbo.tbl_INDEX_FragData_DB_Summary WHERE intDbId =' + CAST(@db_id as varchar) + ')' EXEC (@SQLString) --the following creates a new row in the summary table to signify that fragment data is beign captured SET @SQLString = 'INSERT INTO systems.dbo.tbl_INDEX_FragData_DB_Summary (intDbId, nvchr_DbName, dte_DataRfshStart) VALUES (' SET @SQLString = @SQLString + CAST(@db_id as varchar) + ', ' + CHAR(39) + @InternalDBName + CHAR(39) + ', GETUTCDATE())' EXEC (@SQLString) --the following inserts the output from the dmv "dm_db_index_physical_stats" into the detailed table INSERT INTO systems.dbo.tbl_INDEX_FragData_Detailed (intDbId, nvchr_DbName, intObject_Id, intIndex_Id, fltFragPrcnt, vchrIndexType, vchrAllocType, intUserSeeks, intUserScans, intUserLookups, intUserUpdates, sintPagelocks, sintDisabled, intPageCount) SELECT database_id, DB_NAME(database_id), DMV_IndexStat.[object_id], DMV_IndexStat.[index_id], DMV_IndexStat.[avg_fragmentation_in_percent], DMV_IndexStat.[index_type_desc], DMV_IndexStat.alloc_unit_type_desc, 0, 0, 0, 0, 0, 0, DMV_IndexStat.[page_count] FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , @ScanMode) AS DMV_IndexStat WHERE (DMV_IndexStat.[index_id] > 0) AND DMV_IndexStat.[avg_fragmentation_in_percent] > 0 --if no rows where inserted then code will "RETURN" out to the parent call or stored proc IF @@RowCount = 0 BEGIN --continues out to the nnext stage of the loop --the tables used are truncated & the loop counter is decremented SET @SQLString = 'UPDATE systems.dbo.tbl_INDEX_FragData_DB_Summary SET dte_DataRfshEnd = GETUTCDATE() WHERE dte_DataRfshEnd IS NULL ' SET @SQLString = @SQLString + 'and intDbId = ' + CAST(@db_id as varchar) EXEC (@SQLString) SET @intInstDbCounter = @intInstDbCounter - 1 --the continue command is requried to move to the next step ignoring code after this which makes no sesne if no data to process CONTINUE END --This next section retrieves the shcema & table names, also whether table has LOB data SET @SQLString = N'UPDATE systems.dbo.tbl_INDEX_FragData_Detailed SET vchr_OwnerName = SubFilt.SchmaName, vchr_TableName = SubFilt.TblName, sintHasLOB = ' + N'SubFilt.LobDt FROM systems.dbo.tbl_INDEX_FragData_Detailed JOIN (SELECT SysSchma.[name] SchmaName, SysTbls.[name] TblName, SysTbls.[object_id] ObjctId, ' + N'SysTbls.lob_data_space_id LobDt FROM [' + @InternalDBName + N'].sys.tables SysTbls JOIN ['+ @InternalDBName + N'].sys.schemas SysSchma On SysTbls.[schema_id]' + N' = SysSchma.[schema_id] WHERE [object_id] IN(SELECT DISTINCT intObject_Id FROM systems.dbo.tbl_INDEX_FragData_Detailed WHERE intDbId = ' + CAST(@db_id as varchar) + N')) SubFilt On systems.dbo.tbl_INDEX_FragData_Detailed.intObject_Id = SubFilt.ObjctId WHERE intDbId = ' + CAST(@db_id as varchar) EXEC (@SQLString) --This removes any system tables which may have been captured DELETE FROM systems.dbo.tbl_INDEX_FragData_Detailed WHERE UPPER(ISNULL(vchr_OwnerName, 'sys')) = 'SYS' --This next section retrieves the index name SET @SQLString = N'UPDATE systems.dbo.tbl_INDEX_FragData_Detailed SET vchrIndexName = Sys_Indx.[name], sintPagelocks = Sys_Indx.[allow_page_locks], sintDisabled = ' + 'Sys_Indx.[is_disabled] FROM systems.dbo.tbl_INDEX_FragData_Detailed JOIN [' + @InternalDBName + N'].sys.indexes As Sys_Indx WITH(NOLOCK) On ' + 'systems.dbo.tbl_INDEX_FragData_Detailed.intObject_Id = Sys_Indx.[Object_id] AND systems.dbo.tbl_INDEX_FragData_Detailed.intIndex_Id = Sys_Indx.Index_id ' + 'WHERE intDbId = ' + CAST(@db_id as varchar) EXEC (@SQLString) --This next section retrieves the Index useage figures SET @SQLString = N'UPDATE systems.dbo.tbl_INDEX_FragData_Detailed SET intUserSeeks = DMV_IndexUse.user_seeks, intUserScans = DMV_IndexUse.user_scans, intUserLookups = ' + N'DMV_IndexUse.user_lookups, intUserUpdates = DMV_IndexUse.user_seeks FROM systems.dbo.tbl_INDEX_FragData_Detailed JOIN ['+ @InternalDBName + N'].sys.dm_db_index_usage_stats ' + N' As DMV_IndexUse ON systems.dbo.tbl_INDEX_FragData_Detailed.intObject_Id = DMV_IndexUse.Object_id AND systems.dbo.tbl_INDEX_FragData_Detailed.intIndex_Id = ' + N'DMV_IndexUse.Index_id AND systems.dbo.tbl_INDEX_FragData_Detailed.intDbId = DMV_IndexUse.database_id' EXEC (@SQLString) SET @SQLString = 'UPDATE systems.dbo.tbl_INDEX_FragData_DB_Summary SET dte_DataRfshEnd = GETUTCDATE() WHERE dte_DataRfshEnd IS NULL ' SET @SQLString = @SQLString + 'and intDbId = ' + CAST(@db_id as varchar) EXEC (@SQLString) SET @intInstDbCounter = @intInstDbCounter - 1 ENDIF (Object_Id('tempdb..#tbl_InstancePrepDatabases')) Is Not Null Drop Table #tbl_InstancePrepDatabasesGO |
|
help with this complex t-sql script