Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is there any database setting that affects CHECKIDENT(RESEED) Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 2:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
Greetings, all.
I am using CHECKIDENT(RESEED) in a Visual Studio 2013 script that clears numerous test data tables & repopulates them afresh, whenever we deploy a revised build of the database project. The proper syntax (as per Microsoft) to restart the key-count at #1, is RESEED,1 for truncated tables & RESEED,0 for tables from which rows were deleted (due to foreign keys) rather than truncated.
When the TFSBuild process is run on my PC (SQL Server 2012), all behaves as expected. The same is true when another developer runs the build to his PC. However, when we run the (very same) build process to our Dev (network) server, the RESEED,0 populates those tables starting with key #0, rather than key #1. B/c some of the test data tables are related or populate specifically to certain key #'s, this causes problems for us. I have to continually change the SQL script mentioned, to use RESEED,1 for the deleted-from tables, to get the test data on the Dev server correct, then change it back for anyone else's build.
All SQL Servers appear to be running the same SQL Server version & service pack levels. Is there any setting, that anyone is aware of, that could cause such behavior? It is 100% consistent, every time the build process is run. (Or do you think this is somehow some setting related to Visual Studio?)
Thanks,
Randy
Post #1568174
Posted Tuesday, May 6, 2014 2:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 2,027, Visits: 3,024
No, don't think any other settings affect that.

Is there maybe something in the process where that table is truncating rather than deleting? Perhaps a missing foreign key constraint causing the process to do something different on that instance?


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1568191
Posted Tuesday, May 6, 2014 3:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 1,549, Visits: 4,281
Start by checking out this article Context Is Everything - Check Your Settings
Post #1568204
Posted Tuesday, May 6, 2014 3:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
Thanks. I installed the script you suggested, on both my PC & the network server. There were only three settings that were different, none of which seems relevant:

My PC = IsFullTextInstalled = 1, IsIntegratedSecurityOnly = 1, IsFullTextEnabled = 0
Network server = IsFullTextInstalled = 0, IsIntegratedSecurityOnly = 0, IsFullTextEnabled = 1
Post #1568214
Posted Wednesday, May 7, 2014 12:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 1,549, Visits: 4,281
Quick question, is the recovery model the same and is there any difference in the connections, ie. isolation?
Post #1568289
Posted Wednesday, May 7, 2014 8:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
Recovery model for both is "Full". Not quite sure what you mean on the other setting.
Post #1568493
Posted Wednesday, May 7, 2014 9:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 1,549, Visits: 4,281
rwitt 95744 (5/7/2014)
Recovery model for both is "Full". Not quite sure what you mean on the other setting.


That is the transaction isolation level, configurable ie. within the connection parameters. Simplest way to check is to run
DBCC USEROPTIONS
GO

on each system and check the output.
Post #1568526
Posted Wednesday, May 7, 2014 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
Running the above command, all 13 rows of settings are identical between the two servers.
I'm starting to think the issue is not in SQL Server but that somehow the Visual Studio build process being used on the two servers isn't the same, even if theoretically it's supposed to be.
Post #1568529
Posted Wednesday, May 7, 2014 9:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 1,549, Visits: 4,281
Can you run the DBCC statement from Visual Studio?
Post #1568532
Posted Wednesday, May 7, 2014 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
I think placing this in my test data script will resolve the issue (or rather, cover it, for both situations):

DECLARE @DELETEDFROM_<tablename> bit = 1
IF (SELECT IDENT_CURRENT('<tablename>') = 1 AND (SELECT COUNT(*) FROM <tablename>) = 0
SET @DELETEDFROM_<tablename> = 0 -- New table, has never contained any rows
-- Need to run the above prior to clearing the table

-- Run this after clearing the table
IF @DELETEDFROM_<tablename> = 0
BEGIN
-- New table, has never contained rows; CHECKIDENT(RESEED) treats it as a TRUNCATEd table
DBCC CHECKIDENT ('<tablename>', reseed, 1)
END
ELSE
BEGIN
-- Table exists & was DELETEd FROM; need to use different RESEED value
DBCC CHECKIDENT ('<tablename>', reseed, 0) -- Needed b/c DELETEd rather than TRUNCATEd this table
END
Post #1568550
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse