question

DataJoe avatar image
DataJoe asked

How can I reindex all within a db

I have tried to use scripts within SSC and part of this due to my level of SQL experience. No matter what script I used it does not appear to reduce fragmentation. The below script runs but does not affect percentages. if you could direct me to script within SSC with be greatly appreciated. One script I ran from SSC was for A day of week online/offline ended with message: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '_AssignmentID'. Runs but no Joy: Use ePO_MCAFEE Go Declare @TBname nvarchar(255), @SQL nvarchar(max) select @TBname = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' while @TBname is not null BEGIN set @SQL='ALTER INDEX ALL ON [' + @TBname + '] REBUILD;' --print @SQL EXEC SP_EXECUTESQL @SQL select @TBname = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME > @TBname END Select * From INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' Order By TABLE_NAME
indexing
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would suggest using Ola Hallengren's index maintenance scripts instead of re-inventing the wheel. https://ola.hallengren.com/
1 Like 1 ·
We don't have enough information to determine the why your fragmentation isn't reducing. * What query are you using to measure fragmentation? * Do all of your tables have clustered indexes? * How large are your tables/how many pages do your tables have? The reason I ask is that very small tables and heaps don't de-fragment well. You could be chasing a ghost. Not to oversimplify, but for a generic setup I generally apply Ola's scripts, as mentioned by @JohnM, add in the parameters to also perform statistics updates, schedule the associated job and walk away (unless there are fragmentation based performance issues, which you haven't mentioned).
1 Like 1 ·
John I don't like reinventing the wheel as well. The script I pulled up is close to 5000 lines of code. I realize this must do more than a reindex all table within specified db but just what does this bad boy do. I have requested a SNAP backup of this server but wow. I really Trust SSC but just what else will this do for me?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.