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 12345»»»

INDEX DEFRAGMENTATION SCRIPT Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 9:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
Comments posted to this topic are about the item INDEX DEFRAGMENTATION SCRIPT

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #821729
Posted Tuesday, December 8, 2009 5:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
Very nice work, thank you. I would have 5-starred it, but I don't like the script writing to the master database. I realize others might disagree with me, but I'd rather the script writes to the database being maintained, or optimally, have it's own, dedicated database to write to.
Post #830576
Posted Tuesday, December 8, 2009 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 1:22 AM
Points: 90, Visits: 402
Very nice script indeed.
Just a question, isn’t there some misinformation in this part?

BEGIN
--IF PAGE LEVEL LOCKING IS DISABLED THEN REBUILD
BEGIN TRY
EXEC ('USE ['+@DB_NAME+'];ALTER INDEX ['+@IND_NAME+'] ON ['+@SCHEMA_NAME+'].['+@TAB_NAME +'] REORGANIZE ')
END TRY
BEGIN CATCH
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@DB_NAME+'];ALTER INDEX ['+@IND_NAME+'] ON ['+@SCHEMA_NAME+'].['+@TAB_NAME +'] REBUILD ')
END CATCH
EXEC ('USE ['+@DB_NAME+'];UPDATE STATISTICS ['+@SCHEMA_NAME+'].['+@TAB_NAME+'] (['+@IND_NAME+']) ' )
INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY
VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,'REORGANIZE & UPDATESTATS',GETDATE())
END



If the TRY fail it goes into the CATCH, it does a rebuild.
But it still updates the history table with “REORGANIZE & UPDATESTATS”
Post #830644
Posted Tuesday, December 8, 2009 8:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
If the TRY fail it goes into the CATCH, it does a rebuild.
But it still updates the history table with “REORGANIZE & UPDATESTATS”


Hi F.L
Thanks for your information.I ll change this ASAP.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #830714
Posted Thursday, December 10, 2009 3:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:04 AM
Points: 202, Visits: 231
OFFTOPIC: MUTHUKKUMARAN, you have the coolest name, I've ever heard!

I guess it's not the first time you hear this, isn't it?
Post #832085
Posted Friday, January 22, 2010 3:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:48 AM
Points: 484, Visits: 832
Can I ask how you came to the decision to only do if @Page>1000?

Thanks
Post #851879
Posted Friday, January 22, 2010 3:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 143, Visits: 227
Quick bug fix.
The line "EXEC ('USE '+@DB_NAME+';"

Should be "EXEC ('USE ['+@DB_NAME+'];"
otherwise it fails if you have a database name with spaces in.
Not that any of us would do this, but third parties who don't know any better do/did.

Otherwise a very useful script.
Post #851884
Posted Friday, January 22, 2010 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 23, 2014 9:24 AM
Points: 2, Visits: 39
I see in the SQL that this is for SQL 2005 +.

Is this possible on SQL 2000?

Thanks!
Post #852038
Posted Sunday, January 24, 2010 4:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
Alex Gay (1/22/2010)
Quick bug fix.
The line "EXEC ('USE '+@DB_NAME+';"

Should be "EXEC ('USE ['+@DB_NAME+'];"
otherwise it fails if you have a database name with spaces in.
Not that any of us would do this, but third parties who don't know any better do/did.

Otherwise a very useful script.


S i agreed.I missed that one.Thanks


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #852709
Posted Sunday, January 24, 2010 4:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:16 AM
Points: 1,159, Visits: 4,656
D Rose (1/22/2010)
I see in the SQL that this is for SQL 2005 +.

Is this possible on SQL 2000?

Thanks!


Defragmentation script only works for sql server 2005/2008.If u want u can search here for 2000.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #852710
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse