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 Monday, March 26, 2012 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:02 PM
Points: 2, Visits: 27
If we are running SQL 2008 and have "Auto Update Statistics" enabled, Do we need to update statistics in condition 2 or 3 of this script? Are there any down sides to doing both?
Post #1273036
Posted Tuesday, March 27, 2012 6:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 1,159, Visits: 4,658
vanceprice (3/26/2012)
If we are running SQL 2008 and have "Auto Update Statistics" enabled, Do we need to update statistics in condition 2 or 3 of this script? Are there any down sides to doing both?


It will not create and problem. if you don't want then you may remove it.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1273431
Posted Tuesday, April 17, 2012 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 2:00 AM
Points: 1, Visits: 10
HI Muthu,

I ran the script and it created those two tables in master and msdb and executed successfully.

But fragmentation is not reduced. Please let me know how to put this script in a job that should execute weekly and should run for all databases.

Thanks,
Pavan KVN
Post #1284721
Posted Wednesday, April 18, 2012 6:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 1,159, Visits: 4,658
pavankvnv (4/17/2012)
HI Muthu,

I ran the script and it created those two tables in master and msdb and executed successfully.

But fragmentation is not reduced. Please let me know how to put this script in a job that should execute weekly and should run for all databases.

Thanks,
Pavan KVN


It may be a small table.

You can do that using script. Write a SP loop the sys.databases.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1285575
Posted Tuesday, June 19, 2012 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 5, 2012 8:41 AM
Points: 3, Visits: 8
Hi Muthukkumaran,
The phrases '@frag BETWEEN 15 AND 29' and '@frag>=30' should be replaced by
'@frag BETWEEN 15 AND 30' and '@frag>30'.
With the current script you are missing the values of @frag greater than 29 and smaller than 30.
(also the comments should be adjusted likewise)
Regards,
Siebe
Post #1318080
Posted Wednesday, June 20, 2012 3:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 1,159, Visits: 4,658
Thanks Siebe. Will change it.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1318524
Posted Wednesday, September 26, 2012 12:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,418, Visits: 1,019
Hi Muthukkumaran,

Did you look at http://ola.hallengren.com/

IMHO it works better on larger databases.

Best regards,
Henrik Staun Poulsen
Stovi Software



Post #1364457
Posted Wednesday, September 26, 2012 1:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 1,159, Visits: 4,658
henrik staun poulsen (9/26/2012)
Hi Muthukkumaran,

Did you look at http://ola.hallengren.com/

IMHO it works better on larger databases.

Best regards,
Henrik Staun Poulsen
Stovi Software


Henrik,

Indeed! That's good for larger databases.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1364489
Posted Wednesday, September 26, 2012 10:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
Your script as such will suffer false positives from fragmented LOB_DATA and ROW_OVERFLOW_DATA allocation units - a common oversight. You need to filter on alloc_unit_type_desc = IN_ROW_DATA.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1364829
Posted Wednesday, September 26, 2012 10:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 1,159, Visits: 4,658
Paul Randal (9/26/2012)
Your script as such will suffer false positives from fragmented LOB_DATA and ROW_OVERFLOW_DATA allocation units - a common oversight. You need to filter on alloc_unit_type_desc = IN_ROW_DATA.


I have no idea on that.Thanks Paul.I will update it asap alloc_unit_type_desc = IN_ROW_DATA.


Muthukkumaran Kaliyamoorthy

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

Add to briefcase «««12345»»

Permissions Expand / Collapse