July 20, 2009 at 1:52 am
Hi All,
I'm a rather new DBA, and I seem to have run into a problem I can't fully grasp. I hope you experts can help me. 🙂
I've made a maintenance plan that's supposed to reorganize all the indexes on the SQL server, but when the job runs, it fails with the error:
Failed: (-1073548784) Executing the query "ALTER INDEX [IX_asstaging_1] ON [dbo].[asstaging] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The indexes on table "asstaging" cannot be reorganized because there is already an online index build or rebuild in progress on the table.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Also when I try to reorganize or rebuild manually, using one of the following commands:
use DBNAME
GO
ALTER INDEX TableDetails_UK0 ON TableDetails REBUILD WITH ( PAD_INDEX=OFF, SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON )
GO
use DBNAME
GO
ALTER INDEX TableDetails_PK ON TableDetails REORGANIZE WITH ( LOB_COMPACTION=ON )
GO
The query runs successfully, but a check on the properties page of the index shows that nothing happend, they are still fragmented. I tried using the GUI for both rebuilding and reorganizing, but to no avail, I still have no effect on the index fragmentation.
I would really appreciate your input. Thanks alot.
Kind regards,
Rasmus
July 20, 2009 at 4:57 am
Hi
I understand the requirement is to avoid fragmentation , but lets takeit step by step.
Error
====
Failed: (-1073548784) Executing the query "ALTER INDEX [IX_asstaging_1] ON [dbo].[asstaging] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The indexes on table "asstaging" cannot be reorganized because there is already an online index build or rebuild in progress on the table.". [/u]Possible failure reasons: Problems with the query, "ResultSet" properly not set correctly, parameters not set correctly, or connection not established correctly.
=>To be on the safer side i would ensure that there 0 connections to the database when i amtrying to rebuild the index.
=>I would use dbcc showcontig against use management studio proerpty as there is a short delay in fetching the updates data . i would refresh the mangement studio several times to see whether it is getting reflested. using command is the best method.
please try to rebuild index with no other connections and the status through command.
dbcc indexdefrag ,will show the progress but will be droppped in near future.
Note: Alter index (or) dbcc indexdefrag will not work on disabled index.
please check whether the index is disabled (or)not.
July 20, 2009 at 5:02 am
rvje (7/20/2009)
The query runs successfully, but a check on the properties page of the index shows that nothing happend, they are still fragmented. I tried using the GUI for both rebuilding and reorganizing, but to no avail, I still have no effect on the index fragmentation.
How big are these indexes? How many pages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2009 at 1:22 pm
GilaMonster (7/20/2009)
rvje (7/20/2009)
The query runs successfully, but a check on the properties page of the index shows that nothing happend, they are still fragmented. I tried using the GUI for both rebuilding and reorganizing, but to no avail, I still have no effect on the index fragmentation.How big are these indexes? How many pages?
They seem rather small, about 2-60 pages.
/Rasmus
July 20, 2009 at 1:28 pm
MADHAN_PERS (7/20/2009)
HiI understand the requirement is to avoid fragmentation , but lets takeit step by step.
Error
====
Failed: (-1073548784) Executing the query "ALTER INDEX [IX_asstaging_1] ON [dbo].[asstaging] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The indexes on table "asstaging" cannot be reorganized because there is already an online index build or rebuild in progress on the table.". [/u]Possible failure reasons: Problems with the query, "ResultSet" properly not set correctly, parameters not set correctly, or connection not established correctly.
=>To be on the safer side i would ensure that there 0 connections to the database when i amtrying to rebuild the index.
=>I would use dbcc showcontig against use management studio proerpty as there is a short delay in fetching the updates data . i would refresh the mangement studio several times to see whether it is getting reflested. using command is the best method.
please try to rebuild index with no other connections and the status through command.
dbcc indexdefrag ,will show the progress but will be droppped in near future.
Note: Alter index (or) dbcc indexdefrag will not work on disabled index.
please check whether the index is disabled (or)not.
I'm trying to run dbcc showcontig on the index that fails with an error, its been running now for six hours now. I'll let it run through, and then I'll try dbcc indexdefrag. I'll get back with the result. 🙂
Many thanks to both of you for your help, I really appreciate it. 🙂
Kind regards,
Rasmus
July 20, 2009 at 1:42 pm
rvje (7/20/2009)
They seem rather small, about 2-60 pages.
Due to the way pages are allocated for small indexes/tables, very small indexes don't defrag properly. It's not a concern. Fragmentation only starts to become an issue and a concern when the index reaches around 1000 pages. I wouldn't even bother rebuilding anything much smaller than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2009 at 1:47 pm
GilaMonster (7/20/2009)
rvje (7/20/2009)
They seem rather small, about 2-60 pages.Due to the way pages are allocated for small indexes/tables, very small indexes don't defrag properly. It's not a concern. Fragmentation only starts to become an issue and a concern when the index reaches around 1000 pages. I wouldn't even bother rebuilding anything much smaller than that.
Okay, thanks alot for that information. Its really been bothering me. I'll see to it that my scripts are corrected.
Cheers, 🙂
Rasmus
July 21, 2009 at 12:02 am
MADHAN_PERS (7/20/2009)
HiI understand the requirement is to avoid fragmentation , but lets takeit step by step.
Error
====
Failed: (-1073548784) Executing the query "ALTER INDEX [IX_asstaging_1] ON [dbo].[asstaging] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The indexes on table "asstaging" cannot be reorganized because there is already an online index build or rebuild in progress on the table.". [/u]Possible failure reasons: Problems with the query, "ResultSet" properly not set correctly, parameters not set correctly, or connection not established correctly.
=>To be on the safer side i would ensure that there 0 connections to the database when i amtrying to rebuild the index.
=>I would use dbcc showcontig against use management studio proerpty as there is a short delay in fetching the updates data . i would refresh the mangement studio several times to see whether it is getting reflested. using command is the best method.
please try to rebuild index with no other connections and the status through command.
dbcc indexdefrag ,will show the progress but will be droppped in near future.
Note: Alter index (or) dbcc indexdefrag will not work on disabled index.
please check whether the index is disabled (or)not.
Hi again,
It finally finished, after only 7 hours and 18 minutes.
None of them seems to be disabled. I found another thing that strikes me as odd though. In the treeview (the left pane) in management studio, I see 10 indexes under this particularly table, however the DBCC SHOWCONTIG shows 11 indexes, one of which has no name, an indexid of 0 and also a LogicalFragmentation of 0. Mind you that the next index in the line, has an indexid of 2, there is no indexid 1. This nameless index, is also the biggeste one with 3.040.296 pages.
Could this not be the cause of my trouble?
I would like to start a dbcc indexdefrag now, but I fear that it's gonna take all day, and fail in the end. I would very much like to solve the issue with the rogue index, any idears?
Thanks alot for your help. 🙂
Cheers,
Rasmus
July 21, 2009 at 1:18 am
rvje (7/21/2009)
MADHAN_PERS (7/20/2009)
HiI understand the requirement is to avoid fragmentation , but lets takeit step by step.
Error
====
Failed: (-1073548784) Executing the query "ALTER INDEX [IX_asstaging_1] ON [dbo].[asstaging] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The indexes on table "asstaging" cannot be reorganized because there is already an online index build or rebuild in progress on the table.". [/u]Possible failure reasons: Problems with the query, "ResultSet" properly not set correctly, parameters not set correctly, or connection not established correctly.
=>To be on the safer side i would ensure that there 0 connections to the database when i amtrying to rebuild the index.
=>I would use dbcc showcontig against use management studio proerpty as there is a short delay in fetching the updates data . i would refresh the mangement studio several times to see whether it is getting reflested. using command is the best method.
please try to rebuild index with no other connections and the status through command.
dbcc indexdefrag ,will show the progress but will be droppped in near future.
Note: Alter index (or) dbcc indexdefrag will not work on disabled index.
please check whether the index is disabled (or)not.
Hi again,
It finally finished, after only 7 hours and 18 minutes.
None of them seems to be disabled. I found another thing that strikes me as odd though. In the treeview (the left pane) in management studio, I see 10 indexes under this particularly table, however the DBCC SHOWCONTIG shows 11 indexes, one of which has no name, an indexid of 0 and also a LogicalFragmentation of 0. Mind you that the next index in the line, has an indexid of 2, there is no indexid 1. This nameless index, is also the biggeste one with 3.040.296 pages.
Could this not be the cause of my trouble?
I would like to start a dbcc indexdefrag now, but I fear that it's gonna take all day, and fail in the end. I would very much like to solve the issue with the rogue index, any idears?
Thanks alot for your help. 🙂
Cheers,
Rasmus
A picture says more that a thousand words, so I attached a image of the dbcc showcontig result.
Hope it's usefull. 🙂
Cheers,
Rasmus
July 21, 2009 at 2:08 am
rvje (7/21/2009)
None of them seems to be disabled. I found another thing that strikes me as odd though. In the treeview (the left pane) in management studio, I see 10 indexes under this particularly table, however the DBCC SHOWCONTIG shows 11 indexes, one of which has no name, an indexid of 0 and also a LogicalFragmentation of 0. Mind you that the next index in the line, has an indexid of 2, there is no indexid 1. This nameless index, is also the biggeste one with 3.040.296 pages.
Index 0 is the heap, the table itself. That's why it's the biggest. It has 0 logical fragmentation because logical fragmentation is completely meaningless on a heap. Logical fragmentation is the % of pages that are out of order. A heap has no order.
If you had a clustered index on that table you would not see index id of 0, you would see index id of 1, that being the clustered index. It would be slightly larger than the heap, it's an index with the data pages at the leaf levels.
Not sure why you're getting the errors that you are. Are there any active connections to that database?
Can you run the following and see what it returns?
select session_id, DB_NAME(database_id) as DBName, start_time, wait_type, text
from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text (er.sql_handle) st
where database_id = DB_ID('Name of DB in question')
AND (st.text like '%DBCC%' OR st.text like '%INDEX%')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 4:56 am
GilaMonster (7/21/2009)
rvje (7/21/2009)
None of them seems to be disabled. I found another thing that strikes me as odd though. In the treeview (the left pane) in management studio, I see 10 indexes under this particularly table, however the DBCC SHOWCONTIG shows 11 indexes, one of which has no name, an indexid of 0 and also a LogicalFragmentation of 0. Mind you that the next index in the line, has an indexid of 2, there is no indexid 1. This nameless index, is also the biggeste one with 3.040.296 pages.Index 0 is the heap, the table itself. That's why it's the biggest. It has 0 logical fragmentation because logical fragmentation is completely meaningless on a heap. Logical fragmentation is the % of pages that are out of order. A heap has no order.
If you had a clustered index on that table you would not see index id of 0, you would see index id of 1, that being the clustered index. It would be slightly larger than the heap, it's an index with the data pages at the leaf levels.
Not sure why you're getting the errors that you are. Are there any active connections to that database?
Can you run the following and see what it returns?
select session_id, DB_NAME(database_id) as DBName, start_time, wait_type, textfrom sys.dm_exec_requests er cross apply sys.dm_exec_sql_text (er.sql_handle) st
where database_id = DB_ID('Name of DB in question')
AND (st.text like '%DBCC%' OR st.text like '%INDEX%')
Hi Gail,
Again thanks alot, this is fantastic info for me. 🙂
I ran the code you gave me, and apprently there is indeed active connections to the database. I attached the file "DBOpenSessions.jpg", which is the result of the code. I also attached "DBAMOpenSessions.jpg", which is the equivalent from Activity Monitor in management studio.
So would the best course of treatment be to go into Activity Monitor and kill the dbcc process?
Cheers,
Rasmus
July 21, 2009 at 5:54 am
You can kill the shrink, it's not harmful to do so and it'll kill fairly quickly. However we need to find what's running that shrink and remove it.
Do you have autoshrink enabled on that database? If so, turn it off. If you don't, check your maintenance plans and see if any are doing a shrink (removing unused space)
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 6:23 am
GilaMonster (7/21/2009)
You can kill the shrink, it's not harmful to do so and it'll kill fairly quickly. However we need to find what's running that shrink and remove it.Do you have autoshrink enabled on that database? If so, turn it off. If you don't, check your maintenance plans and see if any are doing a shrink (removing unused space)
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Autoshrink was enabled on that particularly database, I now killed the dbcc process and disabled autoshrink.
I'll run a dbcc checkdb and give the rebuild another go tonight, I'll post the result tomorrow morning. 🙂
Cheers,
Rasmus
July 22, 2009 at 1:56 am
rvje (7/21/2009)
GilaMonster (7/21/2009)
You can kill the shrink, it's not harmful to do so and it'll kill fairly quickly. However we need to find what's running that shrink and remove it.Do you have autoshrink enabled on that database? If so, turn it off. If you don't, check your maintenance plans and see if any are doing a shrink (removing unused space)
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Autoshrink was enabled on that particularly database, I now killed the dbcc process and disabled autoshrink.
I'll run a dbcc checkdb and give the rebuild another go tonight, I'll post the result tomorrow morning. 🙂
Cheers,
Rasmus
Hi,
It worked. 🙂 The rebuild completed successfully during the night.
Thank you so much for all your help. 🙂
Cheers,
Rasmus
July 22, 2009 at 2:24 am
Excellent!
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply