February 20, 2009 at 9:51 am
Hello all,
I have a sql script which analyses index fragmentation level and either starts a rebuild or a reorganize.
My idea is to store this script as a procedure so I can use it easily against every database.
E.g.:
I have this while loop to determine my databases in an instance
SET NOCOUNT ON
-- Get the name of all databases
DECLARE AllDatabases CURSOR FOR
SELECT name FROM master..sysdatabases
where name not in ('master','tempdb', 'model', 'msdb', 'Northwind','pubs')
-- Open Cursor
OPEN AllDatabases
-- Define variables needed
DECLARE @DB NVARCHAR(128)
DECLARE @COMMAND NVARCHAR(128)
-- Get First database
FETCH NEXT FROM AllDatabases INTO @DB
-- Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Processing ' + @db + 'now'
set @command ='EXEC msdb..usp_DefragRebuildIndexes'
print @command
EXEC (@command)
-- Get next database
FETCH NEXT FROM AllDatabases INTO @DB
END
-- Close and Deallocate Cursor
CLOSE AllDatabases
DEALLOCATE AllDatabases
And this is the script I want to place as a procedure:
/*Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#fraglist%' and type = 'U')
DROP TABLE #fraglist
DECLARE @tablename VARCHAR (128)
DECLARE @indexname VARCHAR (255)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @minfrag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @TrivPages INT
-- Decide on the maximum fragmentation to allow
SELECT @minfrag = 10.0
SELECT @maxfrag = 30.0
SELECT @TrivPages = 1000
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= @minfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
AND CountPages >= @TrivPages
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @frag >= @maxfrag
BEGIN
PRINT 'Executing DBCC DBREINDEX (''' + RTRIM(@tablename) + ''', ''' + RTRIM(@indexname) + ''') WITH NO_INFOMSGS - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC DBREINDEX (''' + RTRIM(@tablename) + ''', ''' + RTRIM(@indexname) + ''') WITH NO_INFOMSGS '
END
ELSE
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ''' + RTRIM(@tablename) + ''', ' + RTRIM(@indexid) + ') WITH NO_INFOMSGS - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ''' + RTRIM(@tablename) + ''', ' + RTRIM(@indexid) + ') WITH NO_INFOMSGS '
END
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
My problem now is that the 2nd script won´t run as a procedure and I have no idea why it does not work.
So, maybe someone can give me a hint of what I did wrong.
Thx a lot
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
February 20, 2009 at 10:37 am
ok, i copied and pasted your ocde, and found the #fraglist gets populated just fine.
the second cursor "index" never ran on my machine, because the condition below was never met: TrivPages = 1000, and i had nothing where where CountPages met that...when i changed it to 500 or lower, i got results on my sample database.
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= @minfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
AND CountPages >= @TrivPages
Lowell
February 20, 2009 at 10:49 am
Hi Lowell,
thanks for the quick reply.
Well, I have several databases where I have the condition TrivPages > 1000 so the script itself works fine.
The strange thing is that I cannot run that script as a procedure.
E.G.
use SampleDB
Go
EXEC msdb..usp_DefragRebuildIndexes
Here I get no result.
But if I set the focus on the sampledb and then run the script native, then there are a few indexes which are maintained.
Greetings
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
February 20, 2009 at 11:04 am
How are you compiling as a script? Perhaps there's something in the script that gets left out. For example, a GO might terminate a batch, and let the rest of the script run, but it would end the proc compilation at that point.
February 20, 2009 at 11:50 am
Hi Steve,
well, I can give it a try.
In the SQL statement you see above is one 'GO' at the end to drop the temporary table.
That´s the only one I have there.
So my thought was just to run
CREATE Procedure msdb.dbo.usp_RebuildDefragIndex AS
the 2nd statememt above
the 2nd statememt above
the 2nd statememt above
GO
If this won´t work at all, maybe there´s another way to execute this statement against multiple databases in an instance.
Background: I want to implement this statement on several instances with multiple, different databases (e.g. different names)
Due to the fact that I don´t want to write down the statements for each database, I´ll try to handle it dynamically as possible.
Greetings
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
February 20, 2009 at 1:35 pm
When you run this as a script - it will run in the context of the database you are using. When you create it as a stored procedure, it will run in the context of the database where the procedure exists.
In your case, it will always run in the context of the msdb database.
For this to work, you are going to need to do one of the following:
1) Move the procedure to the master database and mark it as a system object
2) Modify the procedure to use dynamic SQL - and in the dynamic SQL issue a use to the database
a) In this case, you would need a parameter for the database name
If you choose to go with marking this as a system object, be aware that future upgrades could potentially cause you problems. Be especially aware of the fact that upgrading to SQL Server 2005/2008 will leave your master database in 80 compatibility mode - which you will want to change to the correct version.
I would recommend modifying the procedure to use dynamic SQL instead - just my preference for this type of procedure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 20, 2009 at 1:47 pm
Hello Jeffrey,
now I begin to understand.
I will give it a try to put the dbname into the proc dynamically. I suppose here in this forum I'll find some good ideas.
Thanks for the help.
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
February 20, 2009 at 1:51 pm
Yes - there are probably several scripts already available on this site that do what you want.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2009 at 7:53 am
I am still confused, but on a much higher level right now....
It is not possible for me to just set a use @dbname inside the proc. The use database statement isn´t allowed inside.
I´m afraid that I have to reconsider the whole script. Also, I don´t want to store the statement in each database and run it from there except htere is no other way.
Greetings and nice weekend
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
February 21, 2009 at 8:46 am
to get a procedure to run under the context of the database it is called from, doesn't it HAVE to be in the MASTER database and also at least start with sp_procname?
you can also mark it as a system proc, but I've found that is not necessary as long as the other two rules above were followed.
When you put it in msdb, it would only run against the msdb database, right? same as if i put a copy in the BOB database; it would do the index thing only on BOB.
Lowell
February 21, 2009 at 9:14 am
Lowell (2/21/2009)
to get a procedure to run under the context of the database it is called from, doesn't it HAVE to be in the MASTER database and also at least start with sp_procname?you can also mark it as a system proc, but I've found that is not necessary as long as the other two rules above were followed.
When you put it in msdb, it would only run against the msdb database, right? same as if i put a copy in the BOB database; it would do the index thing only on BOB.
That may work on SQL Server 2000 and using the older objects, but it does not work using the new system views on 2005/2008. There could be other issues also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2009 at 9:17 am
Dirk Hondong (2/21/2009)
I am still confused, but on a much higher level right now....It is not possible for me to just set a use @dbname inside the proc. The use database statement isn´t allowed inside.
I´m afraid that I have to reconsider the whole script. Also, I don´t want to store the statement in each database and run it from there except htere is no other way.
Greetings and nice weekend
Dirk
To get this to work from msdb - you would have to use dynamic SQL in your procedure. You would pass the database name to the procedure, then build up the statements to be run in a variable and pass them to sp_executesql to run.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2009 at 9:56 am
Jeff it does still work; it depends on the commands you use; I've got an article coming out on a procedure i wrote that returns the DDL of any table;
your doesn't proc work in master because the DBCC command doesn't allow it self to change references.
Other objects, like referencing sysobjects,syscolumns, etc, have not issues when a stored proc in master gets called, and you expect it to change references to the calling database.
Jeffrey Williams (2/21/2009)
That may work on SQL Server 2000 and using the older objects, but it does not work using the new system views on 2005/2008. There could be other issues also.
Lowell
February 21, 2009 at 12:31 pm
Jeffrey Williams (2/21/2009)
To get this to work from msdb - you would have to use dynamic SQL in your procedure. You would pass the database name to the procedure, then build up the statements to be run in a variable and pass them to sp_executesql to run.
Since I use the dbcc showcontig command inside the script I have some difficulties to understand how I get this done in dynamic sql.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
Wouldn´t the statement still be processed in context of the msdb?
I think I will try tomorrow.
Nevertheless thank you for your advice.
Greetings and enjoy your weekend
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
February 21, 2009 at 5:55 pm
Lowell (2/21/2009)
Jeff it does still work; it depends on the commands you use; I've got an article coming out on a procedure i wrote that returns the DDL of any table;your doesn't proc work in master because the DBCC command doesn't allow it self to change references.
Other objects, like referencing sysobjects,syscolumns, etc, have not issues when a stored proc in master gets called, and you expect it to change references to the calling database.
Jeffrey Williams (2/21/2009)
That may work on SQL Server 2000 and using the older objects, but it does not work using the new system views on 2005/2008. There could be other issues also.
Lowell - what I was referring to was the new system views, as in:
sys.objects
sys.tables
sys.indexes
If you try to use those, they will not reference the object in the current database unless the procedure has been marked as a system object.
There are other problems as well - when you start to look at using the dynamic management views.
Either way, I don't suggest putting your procedures in the master database as it can just lead to a lot of issues when you upgrade.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply