question

Sharma avatar image
Sharma asked

INDEX REBUILDING V/S DATABASE UN-ALLOCATED SIZE

Hi, In mine database maintence daily I do reorgnization on index which have logical fragmentation is >10% and on monthly basic I do rebuilding all indexes due to this reason after rebuilding process database unallocated size grow 40%. Can anyone please suggest any solution through this I can handle to stop increasing database unallocated size unnecessary on rebuilding process. I know index rebuilding is drop and recreate index process due to this reason they required space for sorting and creating index before drop old one but I feel that I rebuild indexes in single batch so they are increasing more size in database so how I can handle each index rebuild separately. Database details:- Recovery Mode:- SIMPLE ALTER INDEX ALL ON TABLE1 REBUILD WITH (PAD_INDEX = ON,FILLFACTOR = 90,SORT_IN_TEMPDB = ON); ALTER INDEX ALL ON TABLE2 REBUILD WITH (PAD_INDEX = ON,FILLFACTOR = 90,SORT_IN_TEMPDB = ON); CONT…
rebuild
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
You're using that much transaction space because that much is required. I don't know what script you use to rebuild your indexes, but the good ones shouldn't be running everything in a single transaction. If you think that's what's occurring, yes, change it.
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Sharma avatar image Sharma commented ·
Right now I generate INDEX REBUILD command through system tables and then run these through delphi code one by one. Should I use "GO" after every index rebuild command? Is command "ALTER INDEX ALL " required more space instead of "ALTER INDEX ?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
If the code is wrapped in a transaction, then it's going to be a single statement as far as SQL Server is concerned. You need individual statements, but "GO", I don't know how Delphi is constructing the statements. Maybe.
0 Likes 0 ·
Sharma avatar image Sharma commented ·
Is there any solution to run given below query result set queries with separate-2 transaction each? SELECT 'ALTER INDEX ' + Rtrim(si.name) + ' ON ' + Rtrim(so.name) + ' REBUILD WITH (PAD_INDEX = ON,FILLFACTOR = 90,SORT_IN_TEMPDB = ON);' QUERY FROM dbo.sysindexes si JOIN dbo.sysobjects so ON si.id = so.id WHERE si.indid > 0 AND si.indid < 255 AND Indexproperty( so.id, si.name, 'IsStatistics') = 0 AND so.TYPE = N'U'
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Yes, run them as two different statements.
0 Likes 0 ·
Sharma avatar image Sharma commented ·
If I run 100 ALTER INDEX commands using "GO" between each command then it will be different every statement or should I use different-2 begin and commit transaction for each.
0 Likes 0 ·
Show more comments

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.