September 20, 2012 at 11:50 pm
Comments posted to this topic are about the item SQL Server Index Defragmentation (Updated)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
September 21, 2012 at 8:03 am
It says it supports SQL 2005 or greater but we don't have a DBMonitor database. After searching it looks like this is a 3rd party tool from Devart?
Perhaps this should be mentioned and a discussion of the pros/cons of installing this tool on your database should be included instead of just a teaser of Defrags all indexes.... with no mention of the need to install this.
September 21, 2012 at 8:05 am
DBMonitor is just the name of our administrative database, it does not belong to a third party tool. You can put the SP in any databasse (master, msdb, etc)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
September 21, 2012 at 8:21 am
AAAH.....Sorry I was confused 😛
Updated it to use our Utilities db and modified name to match our naming standards....
It's so big going through it to make sure I understand which parameters to run is taking some time.
October 4, 2012 at 5:13 am
Nice script 🙂
Just a heads up - found a missing QUOTENAME here: (around @DBName)
(only applies if you have underscores and/or hyphens and/or dashes in your database names) 😉
--Get rid of the brackets :<-->: [IndexName] --> IndexName
set @IndexNameExists = substring(@IndexName, 2, len(@IndexName) - 2)
set @SQLCmdExist = N'select @IndexExists = name from ' + @DBName + '.sys.indexes where name = ''' + @IndexNameExists + ''''
set @PDExist = N'@IndexExists varchar(128) output'
exec sp_executesql @SQLCmdExist, @PDExist, @IndexExists output
Changed to:
set @SQLCmdExist = N'select @IndexExists = name from ' + QUOTENAME(@DBName) + '.sys.indexes where name = ''' + @IndexNameExists + ''''
and now works without any errors on my servers.
Thanks
gsc_dba
October 4, 2012 at 5:19 am
I think you may have just solved my riddle of why it fails on SharePoint DB's that contain GUID's in their names. Thanks!
Just to confirm, I ran it against a test server (with the ISGUID functionality commented out) , and like you said, it now handles _'s and -'s. I've been spending months trying to figure this out, thanks again!
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
February 7, 2014 at 8:54 am
A nice script!
Thank gcs_dba. You fix my problem too.
February 7, 2014 at 12:17 pm
Interesting idea. Why is this better than using the "Reorganize Index Task" in the Maintenance Plan?
February 7, 2014 at 1:16 pm
I concur with the previous question... I regularly run reorg_re-index on all tables and trying to work out what exactly this script is doing. The only clue is in the title as far as I can tell.
Perhaps it would be worth explaining in a little more detail why this is needed and what it does differently. How regular it is suggested to be run.
T
August 21, 2015 at 4:03 am
Is there a specific reason why the indexname must be larger than 2 characters for the script to work?
I have a system where I can't change indexes as they are created (and maintained) by a client program (NAVISION) and are named $1, $2, etc...
I can see that your script conditions on the size, but why? Is it safe just to lower the requirement to 1?
May 9, 2016 at 1:30 pm
Thanks for the script.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply