June 11, 2009 at 9:10 am
Hi All,
Im trying to write a script to rebuild indexes who's average fragmentation has risen above 30%. I just cannot get it to work. There are no errors created it just doesnt work. My Code is as follows:
USE MyDatabase
GO
SET NOCOUNT ON
DECLARE @TableName NVARCHAR(128)
DECLARE @IndexName NVARCHAR(128)
DECLARE @cmd NVARCHAR(500)
DECLARE cIndexes CURSOR FAST_FORWARD
FOR SELECT OBJECT_NAME(a.object_id) as TableName, name AS IndexName
FROM sys.dm_db_index_physical_stats (DB_ID(N'MyDatabase'),
NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30.0 AND a.index_id >= 1
OPEN cIndexes
FETCH NEXT FROM cIndexes INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ['+@IndexName+'] ON [dbo].'+@TableName+'] REBUILD
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON,
ONLINE = OFF )'
EXEC (@cmd)
FETCH NEXT FROM cIndexes INTO @TableName, @IndexName
END
close cIndexes
deallocate cIndexes
GO
Does anyone have any thoughts? I usually manage to figure these things out only this time I'm stumped.....
June 11, 2009 at 9:37 am
see if this solves your purpose:
sp_msforeachdb @command1 = 'USE [?]; select ''ALTER INDEX ''+ss.name+ '' ON ''+ object_name(si.object_id,si.database_id)+'' REBUILD ;'', db_name(si.database_id),
ss.name,si.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (DB_id(),NULL,NULL,NULL,NULL) si
join sys.indexes ss on si.object_id=ss.object_id and si.index_id=ss.index_id
where si.database_id>4 and si.avg_fragmentation_in_percent> 30 and si.index_id>=1 '
Just copy and paste the first part from results and Execute.
Maninder
www.dbanation.com
June 12, 2009 at 4:39 am
Thanks for the suggestion but I only want to rebuild the indexes in one specific database. Is there any reason why my code wont work?
June 12, 2009 at 4:45 am
A good way to check the correctness of a SQL string before executing it, is top PRINT it. You will notice that you are missing an opening bracket before @TableName
SET @cmd = 'ALTER INDEX ['+@IndexName+'] ON [dbo].['+@TableName+'] REBUILD
That should do the trick.
Edit: Apparently I can't use the [ b ] tag inside a [ code ] tag.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2009 at 5:05 am
I had a similar issue with a script that checks frag and rebuilds.
Depending on how the index was created, or if it is a on a computed column you may need to force "set quoted_identifier on".
Run your script on my DB and looks to work fine.
Just a thought!
JL
June 12, 2009 at 5:19 am
Thanks for the tip. However, I printed the string and there were no errors:
ALTER INDEX [TestIndex] ON [dbo].[TestTable] REBUILD
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON,
ONLINE = OFF)
Excuse my stupidity but I still cant see where Im missing the bracket.
June 12, 2009 at 5:20 am
Just seen your post sql_lock. Ill give it a try.
June 12, 2009 at 5:35 am
Nope, still doesnt work. Im banging my head here.
June 12, 2009 at 5:45 am
What is the error that is returned?
If you try running one of the statements on its own does it work?
June 12, 2009 at 5:48 am
This line is in your script:
SET @cmd = 'ALTER INDEX ['+@IndexName+'] ON [dbo].'+@TableName+'] REBUILD
Fill that with life:
DECLARE @cmd varchar(200), @indexname varchar(200), @tablename varchar(200)
SELECT @indexname = '', @tablename = ''
SET @cmd = 'ALTER INDEX ['+@IndexName+'] ON [dbo].'+@TableName+'] REBUILD'
PRINT @cmd
gives
ALTER INDEX [] ON [dbo].] REBUILD
which is not right, methinks.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2009 at 5:56 am
There is no error returned. I added a print statement to the cursor and the indexes and tables are all correct.
Just tried the rebuild statement on its own sometimes it works and others it doesnt. I have no idea whats going wrong. It must be something fundamental.
June 12, 2009 at 5:58 am
Apologies Frank, this was an error on my part when I first added the script. I changed it and it still doesnt work.:ermm:
June 12, 2009 at 6:56 am
Following up on my previous thread:
you can restrict the query by database_id = with out the sp_msforeachdb query will look like this:
select 'ALTER INDEX '+ss.name+ ' ON '+ object_name(si.object_id,si.database_id)+' REBUILD ;', db_name(si.database_id),
ss.name,si.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL,NULL) si
join sys.indexes ss on si.object_id=ss.object_id and si.index_id=ss.index_id
where si.database_id = 23
and si.avg_fragmentation_in_percent> 30 and si.index_id>=1
-- you can even use the database_id here sys.dm_db_index_physical_stats (,NULL,NULL,NULL,NULL) without the where clause for database_id
Maninder
www.dbanation.com
June 12, 2009 at 7:39 am
Regarding your script:
Try to change the Database contect to the database you are trying to run the script in.
use then run your cursor.
Because the sys.indexes might be relating to the database sysindexes that you are running under and hopefully it is the master database...
So the solution is to change to use your database then execute your script...
See if this Helps/Works.
Maninder
www.dbanation.com
June 12, 2009 at 8:03 am
Hi
Pls look into the BOLD area which was newly added. Now your script will generate and execute the ALTER scripts...
USE MyDatabase
DECLARE @TableName NVARCHAR(128)
DECLARE @IndexName NVARCHAR(128)
DECLARE @cmd NVARCHAR(500)
DECLARE @DB_ID INT
SELECT @DB_ID = DB_ID()
DECLARE cIndexes CURSOR FAST_FORWARD
FOR SELECT OBJECT_NAME(a.object_id) as TableName, name AS IndexName
FROM sys.dm_db_index_physical_stats(@DB_ID,NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30.0 AND a.index_id >= 1
OPEN cIndexes
FETCH NEXT FROM cIndexes INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ['+@IndexName+'] ON [dbo].'+@TableName+'] REBUILD
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON,
ONLINE = OFF )'
EXEC (@cmd)
FETCH NEXT FROM cIndexes INTO @TableName, @IndexName
END
close cIndexes
deallocate cIndexes
Alternate wat to REBUILD / REORGANIZE :
SP_MsForEachTable 'DBCC DBReIndex(''?'')'
Regards
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply