INDEX DEFRAGMENTATION SCRIPT

Question

INDEX DEFRAGMENTATION SCRIPT

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply

vanceprice Grasshopper Points: 16 More actions · Answer 1

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?

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

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
https://www.sqlserverblogforum.com/

pavankvnv Newbie Points: 3 More actions · Answer 3

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

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
https://www.sqlserverblogforum.com/

sboerde Newbie Points: 5 More actions · Answer 5

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

Thanks Siebe. Will change it.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Henrik Staun Poulsen SSCertifiable Points: 6581 More actions · Answer 7

Hi Muthukkumaran,

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

IMHO it works better on larger databases.

Best regards,

Henrik Staun Poulsen

Stovi Software

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

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
https://www.sqlserverblogforum.com/

Paul Randal One Orange Chip Points: 29544 More actions · Answer 9

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 10

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
https://www.sqlserverblogforum.com/

Brian Seib SSChasing Mays Points: 605 More actions · Answer 11

Good afternoon -- I have an odd question - I have been using this scipt on a few of my servers for a while now, and just recentyl I have been getting an error when the job runs some days and not other days. The error is as follows:

Incorrect syntax near '('. [SQLSTATE 42000] (Error 102). The step failed.

I would say it is just a syntax error, but it runs one day and not another - so I do not really understand what parameter is changing on my database some days to make this error out..

Any ideas??

Thanks,

Brian

Paul Randal One Orange Chip Points: 29544 More actions · Answer 12

Is someone changing the compatibility level/mode of the database?

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

Balance SSCrazy Points: 2636 More actions · Answer 13

I've had errors like this and have always found that the original code I've worked from overlooked something causing the error. I would check the database is online, you are correctly handling schemas, names that need enclosing in [ ] etc.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

If that's case you can add additional condition within the script. Run a select statement one by one and check which is causing an issue.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Brian Seib SSChasing Mays Points: 605 More actions · Answer 15

So here is an odd update. It fails when it is running at night (normal time), but just for kicks I ran it this morning and it acually ran fine..? Very strange behavior - could it be that it is butting heads with a nightly process? I do not know this script that well, so I am not too sure.

Thanks,

Brian