Log is growing crazy when run DBCC INDEXDEFRAG or DBREINDEX

We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql

locks on object "xp_startmail"

Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the correct database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database
Escuse me, but I made a blunder, and without your help I couldn't get out of this error..