Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.
Reference : Pinal Dave (https://blog.sqlauthority.com)
101 Comments. Leave new
how will i know the percentage of my fragmented database?
when i was rebuilding my database i had this error number -1073548784..
Executing the query “ALTER INDEX [KBA_ODPV_PR] ON [dbo].[@BA_ODPV] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
” failed with the following error: “The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
The statement has been terminated.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
@Joshua
I strongly suggest, you take a look at BOL about Indexes.
A clustered index (leaf level page) contains real data of that object.
In simple words, when you Rebuild Clustered Index it will put a lock on the table, because Index has to be rebuild meaning data was stored in one fashion earlier but by issuing Rebuild Index (for Clustered Index) command, you are asking SQL Server to again rebuild whole index i.e. store data in another fashion that is easily accessible . When data is being organized in another fashion, no user can access that object (Tables) until rebuild index process completes.
Long Story short, you cannot rebuild Clustered indexes when users are connected to database and accessing that particular table/ object.
Error message you posted is self explanatory, explanation is, Rebuild Index command is trying to put a lock on that table, but that table is being used, that is why sql server is not able to put a lock on that table. Until SQL Server dont put a lock on that table, it cannot actually rebuild clustered Index.
For Non-Clustered Index, there is no such restriction. You can rebuild Non-Clustered Indexes when users are connected to the database. Reason behind this is, Clustered Index ( Leaf level Pages) do not real data, but contains pointer to Clustered Index.
Solution:
1. Either Reorganize index instead of Rebuilding Index.
2. If you have SQL Server 2005 Enterprise Edition, then you can rebuild index on a table when user is accessing that object, all you have to do is, set ONLINE = ON ( in your rebuild command, right now it is ONLINE = is set to OFF). This would increase Rebuild Index time significantly ( Which is not good). Make sure you test this in Dev/ Test Environment before implementing this in PROD.
3. Try rebuilding indexes when no or very less users are accessing database. If not, rebuild indexes in maintenance window.
~ IM.
@ Imran Mohammed
sir thanks for your reply actually here’s what i did,
i had the maintenance last May 2 @7PM up to May 3 @midnight, the error occured at around 5PM on May 3, i had 5 Maintenance,
Check Database Integrity Done 8sec
Shrink Database Error 27sec
Reorganize Index Done 15:14:27
Rebuild Index Error 0sec
Update Statistics Done 6:23:46
“Long Story short, you cannot rebuild Clustered indexes when users are connected to database and accessing that particular table/ object. ”
“Try rebuilding indexes when no or very less users are accessing database. If not, rebuild indexes in maintenance window.”
– NO USERS CONNECTED DURING THE MAINTENANCE. all users are locked and cannot access the database.
“If you have SQL Server 2005 Enterprise Edition, then you can rebuild index on a table when user is accessing that object, all you have to do is, set ONLINE = ON ( in your rebuild command, right now it is ONLINE = is set to OFF). This would increase Rebuild Index time significantly ( Which is not good). Make sure you test this in Dev/ Test Environment before implementing this in PROD.”
– I ALSO SET ONLINE = ON, but with different error message, and still was not able to rebuild the index.
here are the actual errors for SHRINK DATABASE AND REBUILD INDEX:
REBUILD INDEX ERROR
ERROR NUMBER: -1073548784
ERROR MESSAGE:
Executing the query “ALTER INDEX [KBA_CCLSDPA_PR] ON [dbo].[@BA_CCLSDPA] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON ) ” failed with the following error: “Online index operation cannot be performed for index ‘KBA_CCLSDPA_PR’ because the index contains column ‘U_DprTypID’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
SHRINK DATABASE ERROR
ERROR NUMBER: -1073548784
ERROR MESSAGE:
Executing the query “DBCC SHRINKDATABASE(N’ALC’, 10, TRUNCATEONLY) ” failed with the following error: “A severe error occurred on the current command. The results, if any, should be discarded.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
i appreciate the solution sir, but i already did it but still it didn’t work.
thank you so much again…
@Joshua
As per BOL,while rebuilding index ,option
ONLINE=ON will fai if there is XML index or index consist of Large object data type columns inclyding image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml.
Check your indexes to figure out if you really need ONLINE=ON option
@Imran Mohammed
@Sandy
Thanks for your responses, i did everything you said but unfortunately, it didn’t, do u think there is an issue with MS SQL Server 2005? do i need to upgrade to SP3 since i am only using SP2?
thanks…
I clearly read the dis-advantages of REBUILD – and MS says Reorganize if between 5-40% and rebuild if higher.
1. What are the disadvantage(s) of REORGANIZE
2. Why wouldn’t you REORGANIZE if the fragmentation was over 50% (I assume the answer is poor use of resources, but I don’t see that written anywhere)
3. Wouldn’t a REORGANIZE of a Clustered Index also take a table lock?
Other index question – we have a (PK) Clustered Index on table that is 24×7 – we start from scratch (empty) every quarter – and by week 4 the fragmentation is over 80% (yes, i know its a poorly designed PK and Clustered Key – its a vendor product). The kicker is they don’t want me take the app offline for a short maint window – is there any way to define the index with 4 leaf levels – since thats how many it will grow to?
Hello,
My question is as follows: I have created a script to list all indexes with a fragmentation percent greater than 10. Next, this script will rebuild or reorganize these indexes based upon the fragmentation percent. However, the same indexes keep showing up in this report. So, I tried to manually rebuild with the same results, the framentation percent remain the same. These indexes are not clustered indexes. Would you be able to explain why this may be happening?
Herb
Can you share the script with us?
Need help with regards to this REBUILD ISSUE AGAIN…
@Imran Mohammed
@Sandy
Executing the query “ALTER INDEX [KBA_CCLSDPA_PR] ON [dbo].[@BA_CCLSDPA] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
” failed with the following error: “Online index operation cannot be performed for index ‘KBA_CCLSDPA_PR’ because the index contains column ‘U_DprTypID’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Note:
I tried to do the maintenance in our:
TEST SERVER – NO ERROR FOUND
PRODUCTION SERVER – and i found the error above
TEST SERVER – NO ONE IS CONNECTED ON IT
PRODUCTION SERVER – there are 3-5 users connected but less transaction because i did it at night…
hope you can help me with this problem…
i been experiencing this problem since i started posting here…
my last option is: TO DISCONNECT ALL USERS AND RUN THE REBUILD_INDEX for 3 hours…
SHRINK_DATABASE ISSUE HAS BEEN RESOLVED…
thanks…
I’ve got a new variation on this particular error (on SQL2008)
Executing the query “ALTER INDEX [PageUrlID_FK] ON [dbo].[WebParts] REO…” failed with the following error: “A severe error occurred on the current command. The results, if any, should be discarded.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
It’s a SharepointDB, and this is about 1/3 way down the T-SQL that the job generates automatically.
ALLOW_PAGE_LOCKS is on.
@jane
good day, if i am not mistaken this kind of error usually happens when the table is locked or being used by certain user or users… i’ll try to disconnect all the users tonight and test the rebuild index without any users accessing the database… if it works i’ll post the result here as soon as possible…
to: Pinal, Imran Mohammed, Sandy,
please correct me if i am wrong…
thanks…
@joshua
Any joy? It failed again this week. That said, it succeeded once after I’d rebuilt the index…
It’s a reorganize index that’s failing, which shouldn’t be affected by users being logged in.
Thanks
Hi Penal
i am getting the following error at one of my sql server databases
“[298] SQLServer Error: 1204, The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. [SQLSTATE HY000]”
while finding out the solutions on internet I found that by changing the locks configuration through sp_configure locks this problem can be resolved. but in one of the MS notes it is said that it should be set to 0. and I found it is already set to 0. what should I do in this case
is this rebuilding or reorganising indexes affect sizes of differential backups?? because i read some where if database if too huge (like 7gb) and if we donot consider rebuilding/reorganising indexes into consideration while taking diff bkps, resultant diff bkps are huge in size?? is that correct?
Simple, Straightforward and brilliant! Thanks for not only explaining the difference but giving recommendations as to when each option should be used.
God bless.
Hi Dave.
How does SQL 2005 use ram when performing a DBCC DBREINDEX? I have a 300GB database that I moved over from SQL 2000 to SQL 2005 SP3 (64 bit) on a test server. Nearly all 12GB on the test server was consumed by SQL 2005 during the reindex. It takes 22 hours to completely reindex the entire database. I have split the trans log off to a seperate array than the db file is on and that did improve performance some. I am now curious if extra memory will speed up the process. Thanks in advance.
Hi its very useful for me,thanx
Hello Dave,
I have a 90 Gb database. The rebuild index is run as a weekly job. Whenever the Indexes are rebuilt the database size goes from 90 Gb to 170 GB, leaving with very less free disk space and leaving more free space in the database. Is there any option where I can avoid the increase in size of the mdf file ? I use the option sort_in_tempdb ON, but that doesnt help much either.
Thanks,
Deepti
Hi Derek,
To find which are all the Indexes are used and unused guess this might help you out.
Please check it out and let me know your comments.
USE [YOURDATABASENAME]
GO
SELECT O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME,
I.TYPE_DESC, U.USER_SEEKS, U.USER_SCANS,
U.USER_LOOKUPS, U.USER_UPDATES
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS U ON I.OBJECT_ID = U.OBJECT_ID
AND I.INDEX_ID = U.INDEX_ID
AND U.DATABASE_ID = DB_ID()
WHERE O.TYPE ‘S’ — NO SYSTEM TABLES!
ORDER BY (ISNULL(U.USER_SEEKS, 0) + ISNULL(U.USER_SCANS, 0) + ISNULL(U.USER_LOOKUPS, 0) + ISNULL(U.USER_UPDATES, 0)), O.NAME, I.NAME
Thanks,
Manjunath
Hey dude, you are such nice guy and help a looooott!!
God Bless you!!!
Thanks!