Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Index Defragmentation (Updated) Expand / Collapse
Author
Message
Posted Thursday, September 20, 2012 11:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
Comments posted to this topic are about the item SQL Server Index Defragmentation (Updated)

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Post #1362428
Posted Friday, September 21, 2012 8:03 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 95, Visits: 219
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.
Post #1362682
Posted Friday, September 21, 2012 8:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #1362685
Posted Friday, September 21, 2012 8:21 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 95, Visits: 219
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.
Post #1362701
Posted Thursday, October 4, 2012 5:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:58 AM
Points: 1,270, Visits: 1,540
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
Post #1368304
Posted Thursday, October 4, 2012 5:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #1368308
Posted Friday, February 7, 2014 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:41 PM
Points: 47, Visits: 210
A nice script!

Thank gcs_dba. You fix my problem too.



Post #1539235
Posted Friday, February 7, 2014 12:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:21 AM
Points: 27, Visits: 183
Interesting idea. Why is this better than using the "Reorganize Index Task" in the Maintenance Plan?
Post #1539348
Posted Friday, February 7, 2014 1:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 27, 2014 2:47 PM
Points: 17, Visits: 64
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
Post #1539369
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse