Index Reorg Maintenance Plan after SP2 gets a System.OutOfMemoryException

Recently upgraded SQL Server 2005 x64 to SP2 and upto Build 3159. Since then the Maintenance Plan for Index Reorgs has failed with a System.OutOfMemoryException error. No other errors are logged anywhere. The plan report file has no information either.

Has anybody come across a similar issue?

Thank you.

Hi. Yes, we are getting the same error message. Anyone have any ideas on how to fix it?|||I haven't got around to debugging this but believe something in .NET of course is running into a memory limitation.

For now, I have stopped using the maintenance plan and have scripted the Index Reorg process.

Index Reorg Maintenance Plan after SP2 gets a System.OutOfMemoryException

Recently upgraded SQL Server 2005 x64 to SP2 and upto Build 3159. Since then the Maintenance Plan for Index Reorgs has failed with a System.OutOfMemoryException error. No other errors are logged anywhere. The plan report file has no information either.

Has anybody come across a similar issue?

Thank you.

Hi. Yes, we are getting the same error message. Anyone have any ideas on how to fix it?|||I haven't got around to debugging this but believe something in .NET of course is running into a memory limitation.

For now, I have stopped using the maintenance plan and have scripted the Index Reorg process.

Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.Depending on the database, I plan for the maintenance window to defragment the indexes with dbcc dbreindex and change the recovery model to bulk. When the this is complete I change the model to full and do a complete backup. This reduces the amount of log space needed and still maintains our recoverability.|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.
>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
>> Michelle,
>> Can you clarify why you have linked recoverability of
the databases with
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>> Thanks!
>> STurner
>
>.
>|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> > Depending on the database, I plan for the maintenance window to
defragment
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>

Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.
Michelle,
Can you clarify why you have linked recoverability of the databases with the reindexing of the databases. I'm wanting to verify that your not asking about availability of the databases instead of recoverablitity.
Thanks!
STurner
|||Depending on the database, I plan for the maintenance window to defragment the indexes with dbcc dbreindex and change the recovery model to bulk. When the this is complete I change the model to full and do a complete backup. This reduces the amount of l
og space needed and still maintains our recoverability.
|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.
|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner
|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.

>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
the databases with
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>
>.
>
|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
defragment
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>

Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.Michelle,
Can you clarify why you have linked recoverability of the databases with the
reindexing of the databases. I'm wanting to verify that your not asking abo
ut availability of the databases instead of recoverablitity.
Thanks!
STurner|||Depending on the database, I plan for the maintenance window to defragment t
he indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup. T
his reduces the amount of l
og space needed and still maintains our recoverability.|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.

>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
the databases with[vbcol=seagreen]
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>
>.
>|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
defragment[vbcol=seagreen]
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>

Index Error from Database Integrity Job

Listed below is an error that I received from my SQL Server 2000 database
maintenance plan from the database integrity check job.
After receiving this error, I applied the following parameters to rebuild
all of the table indexes.
I received the same error after rebuilding the table indexes from the
parameter listed below.
DBCC DBREINDEX (MarketDeals, '', 0)
Please help me resolve this error.
Thanks,
Error Messages:
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
3 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
4 but was not detected in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
errors and 0 consistency errors in table 'MarketDeals'You seem to have a corruption in your database.
If you are lucky, you can just drop the corrupted index and then find out why this happened in the
first place (HW errors probably, so you need to fix that).
Warm up the tape machines, since this might mean that you need to do a restore. First, run DBCC
CHECKDB using the NO_INFOMSGS option to get error numbers back that you can search for in Books
Online. Then search for those error numbers. Make sure that you have the most recent update of Books
Online as it contains specific recommendations for each corruption-type of error number. Also, see:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
> errors and 0 consistency errors in table 'MarketDeals'|||Joe
I suspect your database gets corrupted
Run DBCC CHECKDB to repair the data
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> Listed below is an error that I received from my SQL Server 2000 database
> maintenance plan from the database integrity check job.
> After receiving this error, I applied the following parameters to rebuild
> all of the table indexes.
> I received the same error after rebuilding the table indexes from the
> parameter listed below.
> DBCC DBREINDEX (MarketDeals, '', 0)
> Please help me resolve this error.
> Thanks,
>
> Error Messages:
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
to
> by the previous pointer of IAM page (1:8596228) object ID 781961862 index
ID
> 3 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
to
> by the previous pointer of IAM page (1:8596444) object ID 781961862 index
ID
> 4 but was not detected in the scan.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation
> errors and 0 consistency errors in table 'MarketDeals'|||You shouldn't ever run repair to correct corruptions without first working
out why the corruption occured and preferably restoring from a backup.
Repair fixes the database structures but will most likely break any
application logic inherent in the data stored in the database (as it may
have to delete records or pages to restore consistency).
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> Joe
> I suspect your database gets corrupted
> Run DBCC CHECKDB to repair the data
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> >
> > Listed below is an error that I received from my SQL Server 2000
database
> > maintenance plan from the database integrity check job.
> >
> > After receiving this error, I applied the following parameters to
rebuild
> > all of the table indexes.
> >
> > I received the same error after rebuilding the table indexes from the
> > parameter listed below.
> >
> > DBCC DBREINDEX (MarketDeals, '', 0)
> >
> > Please help me resolve this error.
> >
> > Thanks,
> >
> >
> >
> > Error Messages:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
> to
> > by the previous pointer of IAM page (1:8596228) object ID 781961862
index
> ID
> > 3 but was not detected in the scan.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is pointed
> to
> > by the previous pointer of IAM page (1:8596444) object ID 781961862
index
> ID
> > 4 but was not detected in the scan.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2
allocation
> > errors and 0 consistency errors in table 'MarketDeals'
>|||Paul
Yes, I agree that the OP should investigate why it has happened but this
commnad provides three methods of repair. My mistake was that I did not
mention to what repair to be more useful
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eeWU%23IPUFHA.544@.TK2MSFTNGP15.phx.gbl...
> You shouldn't ever run repair to correct corruptions without first working
> out why the corruption occured and preferably restoring from a backup.
> Repair fixes the database structures but will most likely break any
> application logic inherent in the data stored in the database (as it may
> have to delete records or pages to restore consistency).
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#pykSmLUFHA.2172@.tk2msftngp13.phx.gbl...
> > Joe
> > I suspect your database gets corrupted
> > Run DBCC CHECKDB to repair the data
> >
> >
> > "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> > news:E79AFF38-33A0-474B-A82D-F3FE62353ECC@.microsoft.com...
> > >
> > > Listed below is an error that I received from my SQL Server 2000
> database
> > > maintenance plan from the database integrity check job.
> > >
> > > After receiving this error, I applied the following parameters to
> rebuild
> > > all of the table indexes.
> > >
> > > I received the same error after rebuilding the table indexes from the
> > > parameter listed below.
> > >
> > > DBCC DBREINDEX (MarketDeals, '', 0)
> > >
> > > Please help me resolve this error.
> > >
> > > Thanks,
> > >
> > >
> > >
> > > Error Messages:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is
pointed
> > to
> > > by the previous pointer of IAM page (1:8596228) object ID 781961862
> index
> > ID
> > > 3 but was not detected in the scan.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]IAM page (0:0) is
pointed
> > to
> > > by the previous pointer of IAM page (1:8596444) object ID 781961862
> index
> > ID
> > > 4 but was not detected in the scan.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2
> allocation
> > > errors and 0 consistency errors in table 'MarketDeals'
> >
> >
>