DB Mnt plan issues

Hi,
We have a 40GB OLTP system, 24x7.
We've setup and schld DB mnt plans to optimize the user
databases at Sunday 3AM where there is less activity on
the system.
The Optimization tab is set up to reorg data and index
pages and change the free space per page percentage to 10%.
When the job ran yesterday, it started blocking clients
and locked up around 90% of the resources on the server.
No one could access the server. The transaction log grew
to 9GB. We had to cold start the windows server.
Question is why did this happen and what can I do to
prevent this ?
TIA
MOis the maintenance plan doing dbcc dbreindex ? or is it using dbcc
indexdefrag ?
for 24x7 ops, you'll want to use dbcc indexdefrag.
hope this helps,
Greg Jackson
PDX, Oregon|||Hi,
Thx for your reply.
How do I tell if it is running dbreindex or indexdefrag ?
I run sp_who and it says dbcc.
MO
>--Original Message--
>is the maintenance plan doing dbcc dbreindex ? or is it
using dbcc
>indexdefrag ?
>for 24x7 ops, you'll want to use dbcc indexdefrag.
>
>hope this helps,
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||The maintenance plans generated by enterprise manager use DBCC DBREINDEX.
Because this completely rebuilds all the indexes on a table it can cause
serious blocking. If you want to use DBCC INDEXDEFRAG you will have to write
your own task to do the reindexing. There is an example of this in BOL on
the DBCC SHOWCONTIG page (example E)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"MO" <anonymous@.discussions.microsoft.com> wrote in message
news:521b01c3ffbd$6f1d2a30$a101280a@.phx.gbl...
> Hi,
> Thx for your reply.
> How do I tell if it is running dbreindex or indexdefrag ?
> I run sp_who and it says dbcc.
> MO
> >--Original Message--
> >is the maintenance plan doing dbcc dbreindex ? or is it
> using dbcc
> >indexdefrag ?
> >
> >for 24x7 ops, you'll want to use dbcc indexdefrag.
> >
> >
> >
> >hope this helps,
> >
> >
> >Greg Jackson
> >PDX, Oregon
> >
> >
> >.
> >|||Thx for your reply.
MO
>--Original Message--
>The maintenance plans generated by enterprise manager use
DBCC DBREINDEX.
>Because this completely rebuilds all the indexes on a
table it can cause
>serious blocking. If you want to use DBCC INDEXDEFRAG you
will have to write
>your own task to do the reindexing. There is an example
of this in BOL on
>the DBCC SHOWCONTIG page (example E)
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"MO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:521b01c3ffbd$6f1d2a30$a101280a@.phx.gbl...
>> Hi,
>> Thx for your reply.
>> How do I tell if it is running dbreindex or
indexdefrag ?
>> I run sp_who and it says dbcc.
>> MO
>> >--Original Message--
>> >is the maintenance plan doing dbcc dbreindex ? or is it
>> using dbcc
>> >indexdefrag ?
>> >
>> >for 24x7 ops, you'll want to use dbcc indexdefrag.
>> >
>> >
>> >
>> >hope this helps,
>> >
>> >
>> >Greg Jackson
>> >PDX, Oregon
>> >
>> >
>> >.
>> >
>
>.
>