April 7, 2015 at 4:45 pm
HI Experts
I have written a custom script to rebuild index. But if users choose wrong choice, it is supposed to throw a specific error msg which it does not do.
Please help me suggest what went wrong here.
Also, can I use/save the records for any future purpose to take any proactive call in updating the users if any/few of the indexes are consistently fragmented. Please suggest that too.
-- EXEC USP_REBUILDINDEX @choice = 'onlinexxx'
ALTER PROC USP_REBUILDINDEX @choice char(10) = 'ONLINE'
as
BEGIN
BEGIN TRY
SET NOCOUNT ON;
/*
if Not Exists (Select * from SysMessages where error = 62000)
EXEC sp_addmessage @msgnum = 62000, @severity = 16,
@msgtext = N'WRONG Argument Entered In Rebuild Index Procedure.',
@lang = 'us_english'
*/
select ROW_NUMBER() OVER(ORDER BY name) Seq, name AS DBNAME
INTO #Databases
from sys.databases where database_id in (
SELECT dbid FROM sys.sysdatabases d
EXCEPT
SELECT database_id FROM msdb..suspect_pages)
AND name NOT IN ('master','msdb','tempdb','model','distribution')
--AND compatibility_level > 80
AND state_desc = 'ONLINE'
select * from #Databases;
DECLARE
@Loop INT = 1,
@QT INT = (SELECT COUNT(1) FROM #Databases),
@DBNAME VARCHAR(50)
WHILE @Loop <= @QT
BEGIN
SET @DBNAME = (SELECT DBNAME FROM #Databases WHERE Seq = @Loop);
IF (@choice = 'ONLINE')
BEGIN
PRINT '*** Running Rebuild Index in Online Mode. ****'
EXEC(
'USE ' + @DBNAME + '; ' +
'PRINT ''Database in USE: '' + db_name();
SELECT
ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,
t.name TableName, h.name SchemaName,
i.name IndexName, p.avg_fragmentation_in_percent Frag
INTO #consultation
FROM
sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
where p.avg_fragmentation_in_percent > 10.0
and p.index_id > 0
and p.page_count >= 10
ORDER BY SchemaName, TableName;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #consultation),
@Command VARCHAR(500)
WHILE @Loop <= @Total
BEGIN
SELECT @Command = ''ALTER INDEX '' + IndexName +
'' ON '' + SchemaName + ''.'' + TableName +
( CASE WHEN Frag > 30.0 THEN '' REBUILD PARTITION = ALL WITH (ALLOW_PAGE_LOCKS = ON, ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ))'' ELSE '' REORGANIZE'' END)
FROM #consultation
WHERE Seq = @Loop;
EXEC(@Command);
PRINT ''Executed: '' + @Command;
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '' Qty affected indexes: '' + CONVERT(VARCHAR(5),@Total);
PRINT ''--------------------------------------------------------------'';
DROP TABLE #consultation;');
PRINT @Loop
SET @Loop = @Loop + 1;
END;
ELSE IF (@choice = 'OFFLINE')
BEGIN
PRINT '*** Running Rebuild Index in Offline Mode. ****'
EXEC(
'USE ' + @DBNAME + '; ' +
'PRINT ''Database in USE: '' + db_name();
SELECT
ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,
t.name TableName, h.name SchemaName,
i.name IndexName, p.avg_fragmentation_in_percent Frag
INTO #consultation
FROM
sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
where p.avg_fragmentation_in_percent > 10.0
and p.index_id > 0
and p.page_count >= 10
ORDER BY SchemaName, TableName;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #consultation),
@Command VARCHAR(500)
WHILE @Loop <= @Total
BEGIN
SELECT @Command = ''ALTER INDEX '' + IndexName +
'' ON '' + SchemaName + ''.'' + TableName +
( CASE WHEN Frag > 30.0 THEN '' REBUILD PARTITION = ALL WITH (ALLOW_PAGE_LOCKS = ON)'' ELSE '' REORGANIZE'' END)
FROM #consultation
WHERE Seq = @Loop;
EXEC(@Command);
PRINT ''Executed: '' + @Command;
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '' Qty affected indexes: '' + CONVERT(VARCHAR(5),@Total);
PRINT ''--------------------------------------------------------------'';
DROP TABLE #consultation;');
PRINT @Loop
SET @Loop = @Loop + 1;
END
ELSE
BEGIN
PRINT 'WRONG CHOICE'
RAISERROR (62000,16,1,'Error raised as wrong choice entered. USe either Online/Offline/Leave it blank.')
END
END
DROP TABLE #Databases;
END TRY
BEGIN CATCH
DECLARE @ERRORNUMBER INT;
DECLARE @ERRORSEVERITY INT;
DECLARE @ERRORSTATE INT;
DECLARE @ERRORLINE INT;
--DECLARE @ERRORPROCEDURE INT;
DECLARE @ERRORMESSAGE NVARCHAR(4000);
SELECT @ERRORNUMBER = Error_number(),
@ERRORSEVERITY = Error_severity(),
@ERRORSTATE = Error_state(),
@ERRORLINE = Error_line()
SELECT @ERRORMESSAGE = N'ERROR %D, LEVEL %D, STATE %D, PROCEDURE %S, LINE %D, ' + 'MESSAGE: ' + Error_message();
END CATCH
END
Thanks.
April 7, 2015 at 8:39 pm
I have written a custom script to rebuild index. But if users choose wrong choice, it is supposed to throw a specific error msg which it does not do.
Please help me suggest what went wrong here.
What error do you getting?
Are you expecting the error from the CATCH block to fire?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 7, 2015 at 8:45 pm
No Error.
I was expecting the raise error function to be thrown if the option was not provided correctly. It only printed the message in the ELSE block.
Can you help me understand how would I be able to throw alert both from ELSE block and also from CATCH block?
Thanks
Thanks.
April 7, 2015 at 9:15 pm
I was expecting the raise error function to be thrown if the option was not provided correctly. It only printed the message in the ELSE block.
Can you help me understand how would I be able to throw alert both from ELSE block and also from CATCH block?
You're not actually returning the @ERRORMESSAGE variable, you're only setting it...
Add a SELECT or PRINT statement to see the error (like this) SELECT @ERRORMESSAGE = N'ERROR %D, LEVEL %D, STATE %D, PROCEDURE %S, LINE %D, ' + 'MESSAGE: ' + Error_message();
SELECT @ERRORMESSAGE
END CATCH
Or remove the set altogether, like so:
SELECT N'ERROR %D, LEVEL %D, STATE %D, PROCEDURE %S, LINE %D, ' + 'MESSAGE: ' + Error_message();
END CATCH
You're not seeing it because your're not forcing the output
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 7, 2015 at 9:18 pm
Ah...got it. you're right. Many thanks. I will try tomorrow and will let you know how it goes. But what you said is right for sure.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply