Integrity Check and Transaction Log

Running the Integrity Check (I think this is DBCC CHECKDB)
as part of a Database Maintenance Plan creates a
transaction log that is roughly the size of the database.
This is causing a problem where a large database is
filling up the disk. The simple answer is to get a bigger
disk, but how can I run the Integrity Check and not have
my tlog grow so much? Is this possible?
SQL BOL says "If actively performing transactions while
DBCC CHECKDB is running, the transaction log continues to
grow because the DBCC command blocks log truncation until
it has finished reading the log." However, the integrity
check occurs early in the morning (1am) and there are no
users in the database.
Thanks,
DeanAfter you run the checkdb, you could kick off a full backup then
truncate the log if you do not have users connecting.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Ray,
Yes, I could do that, and effectively am, only the next
day as part of the DB Maintenance Plan. My question comes
because I can't reproduce this tlog growth on other
databases. I've got several situations where it is
happening, but I can't pin down exactly why it's
occuring. I'm just about sure the DB Maint. Plan is the
culprit because of the timing.
Dean
>--Original Message--
>After you run the checkdb, you could kick off a full
backup then
>truncate the log if you do not have users connecting.
>HTH
>Ray Higdon MCSE, MCDBA, CCNA
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>|||Sounds more like the log is filling up due to the optimization (DBREINDEX)
portion of the MP and not the DBCC CHECKDB.
--
Andrew J. Kelly
SQL Server MVP
"Dean Beckley" <dean@.omegagroup.com> wrote in message
news:02f801c34a46$b1aff210$a401280a@.phx.gbl...
> Ray,
> Yes, I could do that, and effectively am, only the next
> day as part of the DB Maintenance Plan. My question comes
> because I can't reproduce this tlog growth on other
> databases. I've got several situations where it is
> happening, but I can't pin down exactly why it's
> occuring. I'm just about sure the DB Maint. Plan is the
> culprit because of the timing.
> Dean
> >--Original Message--
> >After you run the checkdb, you could kick off a full
> backup then
> >truncate the log if you do not have users connecting.
> >
> >HTH
> >
> >Ray Higdon MCSE, MCDBA, CCNA
> >
> >*** Sent via Developersdex http://www.developersdex.com
> ***
> >Don't just participate in USENET...get rewarded for it!
> >.
> >|||I can guarantee you that CHECKDB itself does not generate log records
(unless you're running repair) so something else _must_ be running at the
same as the check.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkScA8mSDHA.3192@.tk2msftngp13.phx.gbl...
> Sounds more like the log is filling up due to the optimization (DBREINDEX)
> portion of the MP and not the DBCC CHECKDB.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Dean Beckley" <dean@.omegagroup.com> wrote in message
> news:02f801c34a46$b1aff210$a401280a@.phx.gbl...
> > Ray,
> >
> > Yes, I could do that, and effectively am, only the next
> > day as part of the DB Maintenance Plan. My question comes
> > because I can't reproduce this tlog growth on other
> > databases. I've got several situations where it is
> > happening, but I can't pin down exactly why it's
> > occuring. I'm just about sure the DB Maint. Plan is the
> > culprit because of the timing.
> >
> > Dean
> > >--Original Message--
> > >After you run the checkdb, you could kick off a full
> > backup then
> > >truncate the log if you do not have users connecting.
> > >
> > >HTH
> > >
> > >Ray Higdon MCSE, MCDBA, CCNA
> > >
> > >*** Sent via Developersdex http://www.developersdex.com
> > ***
> > >Don't just participate in USENET...get rewarded for it!
> > >.
> > >
>|||Yes, I guess I made it sound more like a question than a statement. DBCC
CHECKDB does not manipulate the data so there is no need to log.
--
Andrew J. Kelly
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:Oxzrk7uSDHA.2276@.TK2MSFTNGP10.phx.gbl...
> I can guarantee you that CHECKDB itself does not generate log records
> (unless you're running repair) so something else _must_ be running at the
> same as the check.
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkScA8mSDHA.3192@.tk2msftngp13.phx.gbl...
> > Sounds more like the log is filling up due to the optimization
(DBREINDEX)
> > portion of the MP and not the DBCC CHECKDB.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Dean Beckley" <dean@.omegagroup.com> wrote in message
> > news:02f801c34a46$b1aff210$a401280a@.phx.gbl...
> > > Ray,
> > >
> > > Yes, I could do that, and effectively am, only the next
> > > day as part of the DB Maintenance Plan. My question comes
> > > because I can't reproduce this tlog growth on other
> > > databases. I've got several situations where it is
> > > happening, but I can't pin down exactly why it's
> > > occuring. I'm just about sure the DB Maint. Plan is the
> > > culprit because of the timing.
> > >
> > > Dean
> > > >--Original Message--
> > > >After you run the checkdb, you could kick off a full
> > > backup then
> > > >truncate the log if you do not have users connecting.
> > > >
> > > >HTH
> > > >
> > > >Ray Higdon MCSE, MCDBA, CCNA
> > > >
> > > >*** Sent via Developersdex http://www.developersdex.com
> > > ***
> > > >Don't just participate in USENET...get rewarded for it!
> > > >.
> > > >
> >
> >
>