DBCC CHECKIDENT within Transaction

Hi,
I am getting problem when I use DBCC CHECKIDENT with in a Transaction.
Please advice on this.
--
Regards
SivakumarHi
Most DBCC commands must be in their own batches and can not be in User
Transactions.
Please post error messages as "I am getting problem" does not give us much
to help you with.
Regards
Mike
"Subramaniam Sivakumar" wrote:

> Hi,
> I am getting problem when I use DBCC CHECKIDENT with in a Transaction.
> Please advice on this.
> --
> Regards
> Sivakumar|||Thanks for your reply.
I am calling some SPs from one master SP. That master SP begins a
transaction. After successful execution of other SP calls then the master SP
commits the transaction. If any of the SPs fails then the master SP rollback
s
the transaction. The other SPs have some EXEC (string) commands and call som
e
SPs. Actually these SPs do some migration from one database to another
database. Some times the source database will be in linked server.
In the master SP, after the migration finished then I reset the IDENTITY
values for all tables which having the IDENTITY column.
When I executing the master SP the control is stopped when the control comes
to the command DBCC CHECKIDENT.
The code sample as like this…
CREATE MASTER_SP
( @.SRC_DB, @.TGT_DB)
AS
BEGIN
DECLARE @.a INT
SET @.a = 0
BEGIN TRANSACTION
EXEC @.a = SP1 @.SRC_DB, @.TGT_DB
-- Reset IDENTITY values using DBCC CHECKIDENT
-- ……..
IF @.a <> 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN 1 -- ERROR
END
RETURN 0 -- SUCCESS
END
Regards
Sivakumar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Most DBCC commands must be in their own batches and can not be in User
> Transactions.
> Please post error messages as "I am getting problem" does not give us much
> to help you with.
> Regards
> Mike
> "Subramaniam Sivakumar" wrote:
>

DBCC CHECKDB

Please Advice.
I run a SQL 2K SP3 on WIN 2K SP4 Advanced in an Active/Passive Cluster confi
guration.
I notice when I ran DBCC CHECKDB on one of my Production databases on the th
e primary node the available RAM Memory decreases command (DBCC CHCKDB) on t
he secondary node (same database) memory decreased slightly.
Please help.DBCC takes memory - so the SQL Server process takes memory from the system
on the primary node. It will not give the memory back until there is memory
pressure. This is perfectly normal. The secondary node has nothing to do
with the SQL Server process running on the primary node - something else
must be running on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timi" <anonymous@.discussions.microsoft.com> wrote in message
news:993FAEF2-A3FF-4D22-938E-055C6FD46372@.microsoft.com...
> Please Advice.
> I run a SQL 2K SP3 on WIN 2K SP4 Advanced in an Active/Passive Cluster
configuration.
> I notice when I ran DBCC CHECKDB on one of my Production databases on the
the primary node the available RAM Memory decreases command (DBCC CHCKDB) on
the secondary node (same database) memory decreased slightly.
> Please help.

DBCC CHECKDB

Please Advice.
I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
Active/Passive Cluster configuration.
I notice when I ran DBCC CheckDB on one of my Production
databases the available RAM Memory decreases and fails to
return on completion of the command.
Please help.Hi
BOL says:
DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that
requires checking must first be read from disk into memory. In addition,
DBCC CHECKDB uses tempdb to do sorting.
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.
It is recommended that DBCC CHECKDB be run during hours when the load is
light on the server. If DBCC CHECKDB is run during heavy peak usage time,
expect a performance hit on the transaction throughput as well as DBCC
CHECKDB completion time.
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> Please Advice.
> I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> Active/Passive Cluster configuration.
> I notice when I ran DBCC CheckDB on one of my Production
> databases the available RAM Memory decreases and fails to
> return on completion of the command.
> Please help.
>|||Given that CHECKDB reads every allocated page in the database, I'm guessing
the transaction load was such that the buffer pool grew while CHECKDB was
running (i.e. decreasing available memory) and has not shrunk again since.
--
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:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
> Hi
> BOL says:
> DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that
> requires checking must first be read from disk into memory. In addition,
> DBCC CHECKDB uses tempdb to do sorting.
> 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.
> It is recommended that DBCC CHECKDB be run during hours when the load is
> light on the server. If DBCC CHECKDB is run during heavy peak usage time,
> expect a performance hit on the transaction throughput as well as DBCC
> CHECKDB completion time.
>
> "Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> > Please Advice.
> >
> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> > Active/Passive Cluster configuration.
> > I notice when I ran DBCC CheckDB on one of my Production
> > databases the available RAM Memory decreases and fails to
> > return on completion of the command.
> >
> > Please help.
> >
>|||Thanks Paul for your advice.
When running the DBCC CHECKDB statement there were no
open transaction. I ran the same statement on other
production databses one reported a similar behaviour i.e
the amount of memory available decreased but not much and
the others had little or no impact. What could be
responsible for this.
Your kind help appreciated.
>--Original Message--
>Given that CHECKDB reads every allocated page in the
database, I'm guessing
>the transaction load was such that the buffer pool grew
while CHECKDB was
>running (i.e. decreasing available memory) and has not
shrunk again since.
>--
>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:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
>> Hi
>> BOL says:
>> DBCC CHECKDB is a CPU- and disk-intensive operation.
Each data page that
>> requires checking must first be read from disk into
memory. In addition,
>> DBCC CHECKDB uses tempdb to do sorting.
>> 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.
>> It is recommended that DBCC CHECKDB be run during
hours when the load is
>> light on the server. If DBCC CHECKDB is run during
heavy peak usage time,
>> expect a performance hit on the transaction throughput
as well as DBCC
>> CHECKDB completion time.
>>
>> "Olutimi" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
>> > Please Advice.
>> >
>> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
>> > Active/Passive Cluster configuration.
>> > I notice when I ran DBCC CheckDB on one of my
Production
>> > databases the available RAM Memory decreases and
fails to
>> > return on completion of the command.
>> >
>> > Please help.
>> >
>>
>
>.
>|||I think its still the buffer pool growing in response to CHECKDB reading all
the pages. The algorithms involved are beyond the scope of this forum to
explain. Try looking at Kalen's Inside SQL Server 2000 for details.
What's the actual difference in mem used by SQL Server before and after
running CHECKDB?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:047901c3d11c$dcdf0a40$a501280a@.phx.gbl...
> Thanks Paul for your advice.
> When running the DBCC CHECKDB statement there were no
> open transaction. I ran the same statement on other
> production databses one reported a similar behaviour i.e
> the amount of memory available decreased but not much and
> the others had little or no impact. What could be
> responsible for this.
> Your kind help appreciated.
> >--Original Message--
> >Given that CHECKDB reads every allocated page in the
> database, I'm guessing
> >the transaction load was such that the buffer pool grew
> while CHECKDB was
> >running (i.e. decreasing available memory) and has not
> shrunk again since.
> >
> >--
> >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:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
> >> Hi
> >> BOL says:
> >> DBCC CHECKDB is a CPU- and disk-intensive operation.
> Each data page that
> >> requires checking must first be read from disk into
> memory. In addition,
> >> DBCC CHECKDB uses tempdb to do sorting.
> >>
> >> 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.
> >>
> >> It is recommended that DBCC CHECKDB be run during
> hours when the load is
> >> light on the server. If DBCC CHECKDB is run during
> heavy peak usage time,
> >> expect a performance hit on the transaction throughput
> as well as DBCC
> >> CHECKDB completion time.
> >>
> >>
> >>
> >> "Olutimi" <anonymous@.discussions.microsoft.com> wrote
> in message
> >> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> >> > Please Advice.
> >> >
> >> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> >> > Active/Passive Cluster configuration.
> >> > I notice when I ran DBCC CheckDB on one of my
> Production
> >> > databases the available RAM Memory decreases and
> fails to
> >> > return on completion of the command.
> >> >
> >> > Please help.
> >> >
> >>
> >>
> >
> >
> >.
> >