DBCC Commands

Hi

Can we use

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0)

Command in Stored procedure. Which executes daily.

Thanks

Sridhar K

yes u can very well do. You can create a sp and then schedule the sp though Job. else u can include the script directly in Job

what is the probelm u faced when u tried?

Madhu

|||

Thanks Madhu

I'm not having any problem. But I would like to know and as per my knowledge am not sure that these statements can execute daily on DB. Somebody said we have to use it periodically in maintenance.

Thanks

Sridhar K

|||

Yes there are dbcc commands which used for database maintenance. Those are generally used periodically like DBCC DBREINDEX/IndexDefrag/Checkalloc/CheckDB etc etc...

Madhu

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 Checkident Reseed

On some tables when I execute the following statement the identity value will start at 1 other times 0. Has anyone experienced this inconsistency before?
DBCC CHECKIDENT ('TABLE_NAME', RESEED, 0)Is the miracle effect?

Can you post some code where this happens?

Please make sure to post the DDL, any DML and sample data.

Thanks|||Here ya go. Most of the tables are very simplistic. I start by deleting all of the records then executing the DBCC CHECKIDENT ('table_name', RESEED, 0) in query analyzer against the table. Here's the ddl for one of the tables. 90% of the time the identity value starts at 1 the other 10% start at 0.

t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)

/*================================================= =============*/
/* Table: producer_type */
/*================================================= =============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go

alter table dbo.producer_type
add constraint pk_producer_type primary key (producer_type_id)
with
fillfactor= 90
go

/*================================================= =============*/
/* Index: ux_producer_type_01 */
/*================================================= =============*/
create unique index ux_producer_type_01 on dbo.producer_type (
producer_type_name
)
with
fillfactor= 90
go|||I'll take a look, but why don't you specify the seed an increment in the DDL?

I'm sure there's a default...but...

I've always set IDENTITY(1,1) (that is when I have to use it...I try to avoid it)|||Just out of curiosity...how are you building this table?

You can't do what you're doing unless you use dynamic sql...unless you're building from the application layer, and if you are, what's with the GO?

Also out of curiosity, it sounds like whatever process your doing is placing an undue amount of meaning to the identity column.

Why does it need to be reset to 1 (or 0)|||Sorry, these are in no particular order:

First, I'm building the tables with ddl and executing with query builder. I didn't want to post all of the ddl for the user defined data types, fks, etc... The 'GO' is used as a terminator for the SQL. I'm building over 150 tables, pk's, fk's, indexes, etc.. in one script. It helps me if I encounter an error. The script will stop at that point and allow me to correct the problem and continue running.

Second, the default on the identity is (1,1)

Third, we are placing extra burden on the identity value because we are trying to migrate data into the new system from an existing system and are heavily coding at the same time. The developers need to know the primary key values for reference or metadata for codiing purposes. I know the order of the values going into the tables and as a standard told all of the developers the pk would start at 1. This hurts if it periodically starts at 0 because we are constantly rebuilding the database from the ddl.

Finally, I'm regretting the decision not to turn the identity insert on and specify the values on insert. I could change my scripts but it would take some time to do. I didn't know if there was something simple I was missing with the DBCC CHECKIDENT and RESEED.

Hope this helps, sorry for the long reply.|||Are you not using Query Analyzer?

And if you are, then how does this work?

t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)

/ *=================================================
=============*/
/* Table: producer_type */
/ *=================================================
=============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go

You can't assign the datatype to a variable like that...unless it's a different interface/language...

I'm amazed at a 10% occurance...it's not often that the same process behave differently.

Since you're blowing the data away anyway, can you just do a drop, then recreate the table?

It will save on logging.

Why not create a shell db and recreate it when you need. Then have load scripts...or better just dump a "clean slate" db, and restore over...

and I'm sure NONE of these suggestion will work for you because of either RI, developer data, yada yada yada...

And in lieu of the fact that you're having these problems...why not give it a try and build the table with IDENTITY(1,1).

My Own Opinion (MOO)

Good Luck

DBCC CheckIdent message value to application

Hi,
I'm trying to get the value that DBCC CheckIdent is returning, I could
see the message in the query analizer, but I'm unable to get this value into
my application since its not a results set or return value its just a
message, is there a way to accomplish it?, I want to give a way in my
application for the end user to view the next "Identity value" and they
should be able to change it, but I can't get that value.
Thanks in advance
Shloma Baum| I'm trying to get the value that DBCC CheckIdent is returning, I could
| see the message in the query analizer, but I'm unable to get this value
into
| my application since its not a results set or return value its just a
| message, is there a way to accomplish it?, I want to give a way in my
| application for the end user to view the next "Identity value" and they
| should be able to change it, but I can't get that value.
--
A workaround would be to use OSQL to pipe the result of DBCC CheckIdent
into a textfile and then get the application to read that file.
Another workaround is to select the @.@.identity into a variable immediately
after an insert operation.
But what you're trying to accomplish does not scale too well. In a busy
data entry environment, there's a high probability that you will insert
duplicate values.
Hope this helps,
--
Eric Cárdenas
SQL Server support

DBCC CHECKIDENT call never returns

I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
returning, even after a day. If I do the same call through Query Analyser it
takes about a minute to run.
Can anyone think of anything that might affect the use of DBCC CHECKIDENT. I
cant check for deadlocks, because the Current Activity tab in Enterprise
Manager is locked up, presumably because of a deadlock of some kind...
Thanks in advance,
Fred Forsyth.
Development Manager
The reason EM is "locked up" is certainly not due to a deadlock. SQL Server
automatically resolves deadlocks in a few seconds. It might be due to
blocking though. What permissions does the vb app have? To run DBCC
CHECKIDENT you must be sa or dbo.
Andrew J. Kelly SQL MVP
"Fred Forsyth" <fred.forsyth.nospam@.nospam.rcp.co.uk> wrote in message
news:%23nkjGb6BGHA.4076@.TK2MSFTNGP14.phx.gbl...
>I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
>returning, even after a day. If I do the same call through Query Analyser
>it takes about a minute to run.
> Can anyone think of anything that might affect the use of DBCC CHECKIDENT.
> I cant check for deadlocks, because the Current Activity tab in Enterprise
> Manager is locked up, presumably because of a deadlock of some kind...
> Thanks in advance,
> Fred Forsyth.
> Development Manager
>
|||Fred Forsyth (fred.forsyth.nospam@.nospam.rcp.co.uk) writes:
> I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
> returning, even after a day. If I do the same call through Query
> Analyser it takes about a minute to run.
> Can anyone think of anything that might affect the use of DBCC
> CHECKIDENT. I cant check for deadlocks, because the Current Activity tab
> in Enterprise Manager is locked up, presumably because of a deadlock of
> some kind...
Current Activity gets block if some process has create tables within a
transaction that still is open.
Use sp_who or sp_who2 to check for blocking. Check the Blk or BlkBy column.
If this column has a non-zero value, the spid on this row, is blocked by
spid in the Blk column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Some further investigation has shown that the VB process is blocked
waiting on a checkpoint to complete, and that there is a system process
that is attempting to checkpoint the database but is blocked by the VB
process. (I can this through select * from sysprocesses).
I suspect that the reason for the need to checkpoint is that the I
removed the log files from the databases, and then run the dbcc command
on them, and that this is what is causing the problems. I think my test
case is at fault, because in the real world the original log files
would be there (they are very big, hence the reason I removed them).
|||The log files are pretty important and it is never a good idea to remove
them.
Andrew J. Kelly SQL MVP
<fredforsyth@.gmail.com> wrote in message
news:1135870204.499678.310830@.f14g2000cwb.googlegr oups.com...
> Some further investigation has shown that the VB process is blocked
> waiting on a checkpoint to complete, and that there is a system process
> that is attempting to checkpoint the database but is blocked by the VB
> process. (I can this through select * from sysprocesses).
> I suspect that the reason for the need to checkpoint is that the I
> removed the log files from the databases, and then run the dbcc command
> on them, and that this is what is causing the problems. I think my test
> case is at fault, because in the real world the original log files
> would be there (they are very big, hence the reason I removed them).
>
|||(fredforsyth@.gmail.com) writes:
> Some further investigation has shown that the VB process is blocked
> waiting on a checkpoint to complete, and that there is a system process
> that is attempting to checkpoint the database but is blocked by the VB
> process. (I can this through select * from sysprocesses).
> I suspect that the reason for the need to checkpoint is that the I
> removed the log files from the databases,
You did what? Never, never do that again! If you remove the log file,
you are gambling with your database. There is a fair chance that you
will have to remove the data file as well, because it will not be
accessibe.

> I think my test case is at fault, because in the real world the original
> log files would be there (they are very big, hence the reason I removed
> them).
You can shrink a log file with DBCC SHRINKFILE, but don't do this with a
production database, unless the log file has grown because of some
one-off operation.
For a production database, you should also backup the transaction log
regulary. If you don't care about up-to-the-point recovery, you can set
the database in simple recovery mode.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

DBCC CHECKIDENT Atomically Change Identity Seed?

I have a set of staging tables that need to be used to update a hierarchy of tables with foreign keys between them, and identity columns for the primary keys. One way I'm thinking of doing this is to reset the identity seed on the target tables based on the number of rows I have in the staging tables, then to update the staging tables keys to match the vacated range of identity values. I'd insert them with SET IDENTITY_INSERT ON.

The question is: can this be done atomically? It seems that DBCC CHECKIDENT will return the current identity value, but can only change the seed to an absolute value. That would require that I get the current value, add "n" to it, then set the seed value. This would seem to be non-atomic, in that a new row could be inserted between the time I find the "current" value and the time I set the new value.

Does anyone know of a way to pre-allocate a block of identity values atomically? This has to be done in a live OLTB database.

Further research shows that a fundamental assumtion I was maing is incorrect. DBCC CHECKIDENT does not return the current identity seed. I was depending on this to allow me to increment the seed value, but it appears that cannot be done.

It looks like replication might help here, but since this project must be finished by September 1, we don't have time to learn anything else new. It looks like I'll be stuck using cursors and doing these inserts one at a time.

|||

You can use IDENT_CURRENT() function to return the last used value and IDENT_SEED() to find the created seed.

e.g.

Code Snippet

SELECT TABLE_SCHEMA, TABLE_NAME,
IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS [IDENT_SEED],
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS [IDENT_CURRENT]
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;

Then use dbcc checkident() to reseed as needed.

|||

Thanks. This should do it for me. I can check after the DBCC CHECKIDENT to see whether any rows were created within the block that I "reserved", and try again until I get all the values I need.

DBCC CHECKIDENT (jobs, RESEED, 30) does not work

Hi,

I'm trying to set up merge replication and when I try to synchrinize with the subscriber I get the error "invalid column name ROWGUIDCOL". Following an article it recomended to reseed the identity columns on the subscriber so that there is no conflict. Now the tables with the identity column have the "Not for replication "option on. Using DBCC CHECKIDENT (table_name, RESEED, some_value) does not change the SEED when I check. SQL server help file remarks on DBCC CHECKIDENT say that if the column was created with the "Not for replication " option on it cannot change the value.

From sql server help file:

Remarks

If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Any way around it or any help so I dont get that "invalid column name ROWGUIDCOL" error?

Thanks allot. Looking forword

George

I had a similar problem before, but my problem was that I add a column with the name ROWGUID but I did forget to mark this field as ROWGUIDCOL, then the replication process make another ROWGUID field but with other name. Check that this is not the case.

I hope this can help you.

|||

Can you turn on profiler and see if a query failed due to invalid column name ROWGUIDCOL?

Thanks.

|||

Thanks for the advice

Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes

This happens on the subscriber when I start the merge agent from the publisher.

George

|||

Hello George,

More questions:

1. Before you setup merge replication between 2 servers, does users table already available on both servers? Can I assume they have the same schema?

2. If tables available on both servers already, did you setup merge replication subscription using "NOSYNC" (do sp_addmergesubscription, and let @.sync_type = 'NONE')?

3. Do both tables, non of them has rowguidcol column, one has, both have?

4. Do you have a long table name?

5. I assume both publisher and subscriber are SQL server 2000.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hello George,

Please try this, running the follow DDL on the subscriber database.

ALTER TABLE [table_need_to_be_merged] ADD rowguid uniqueidentifier ROWGUIDCOL

Drop the subscription and re-create it with "NOSYNC" option. (SELECT "No, the Subscriber already has the schema and data" from the "Push Subscription Wizard" on the "Initialize Subscription" Page).

The subscription is created and the error message "Invalid column name 'rowguidcol'" should be disappeared.

If this method is still not working for you, I may need to look at your table schema.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hello Zhou,

Answers to your questions

1. Both databases are available on both servers with the same schema

2. I used NOSYNC frof the wizard

3. One of them (the publisher ) has rowguid column filled. On the subscriber I used your script to created it but is not filled. The merge agent says "No data needed to be merged"

4. The longest table name has 56 characters.

5. Both publisher and subscriber are SQL server 2000.

Thanks

|||

1. So you can create the merge sync by publication and subscription.

2. After you set up the sync and there is no data merged between the publisher and subscriber.

3. Try to update all your rowguid column on the subscriber with newid() and try to sync.

4. if no data sync from step3, try to do a dummy update such as update [table name] set column1 = column1 and do sync.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

Dbcc Checkident

Dear All,

I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.

Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.

However I get the following:

User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

All users get the same message. This is an ASP.NET web app.

Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.

A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident