|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123,
Visits: 4,423
|
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 43,
Visits: 120
|
|
| 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.
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:36 AM
Points: 76,
Visits: 392
|
|
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”
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123,
Visits: 4,423
|
|
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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, April 22, 2013 2:31 AM
Points: 199,
Visits: 205
|
|
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?
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Today @ 4:12 PM
Points: 463,
Visits: 644
|
|
Can I ask how you came to the decision to only do if @Page>1000?
Thanks
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 1:53 AM
Points: 29,
Visits: 115
|
|
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.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Tuesday, November 13, 2012 12:38 AM
Points: 2,
Visits: 30
|
|
I see in the SQL that this is for SQL 2005 +.
Is this possible on SQL 2000?
Thanks!
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123,
Visits: 4,423
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123,
Visits: 4,423
|
|
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
|
|
|
|