We help IT Professionals succeed at work.

truncate still blocked after NOCHECK ALL

EXEC sp_Msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'Truncate table ?'
fails on a truncate.. what could be the reason?
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Please use SQL activity monitor or sp_who(sp_who2) stored procedures to see what locks the table.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
and btw - what error you get when "[...] fails on a truncate.."? Please post the error message.

Author

Commented:
the error message is

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.tblPASCode' because it is being referenced by a FOREIGN KEY constraint.

when i run the below

select * from master..sysprocesses where dbid = db_id ('RAISP') i am the only user in the database, so should not be any locks?

Author

Commented:
it seems like the ALTER TABLE Statement is not hitting all the constraints..


SELECT count(*),
CASE WHEN OBJECTPROPERTY(ID,'CnstIsDisabled') = 1 THEN 'Disabled' ELSE 'Enabled' END DisableStatus
FROM sysobjects
WHERE
xtype IN ('c','d','f','uq')
GROUP by CASE WHEN OBJECTPROPERTY(ID,'CnstIsDisabled') = 1 THEN 'Disabled' ELSE 'Enabled' END

still shows some as enabled.
Chief Database Architect
CERTIFIED EXPERT
Commented:

Author

Commented:
ashilo, as per that link
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
did not disable everything as per the query in 37064027
Aaron ShiloChief Database Architect
CERTIFIED EXPERT
Commented:
hi

you have some restrictions :

1. A PRIMARY KEY constraint cannot be dropped if an XML index exists on the table.
2. the NOCHECK option Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.
Commented:
It's the existance of a foreign key that prevents the truncate, other restrictions in http://msdn.microsoft.com/en-us/library/ms177570.aspx

With DELETE it works but loging and id-reset is not the same
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" 
 
EXEC sp_MSForEachTable "DELETE FROM ?" 
 
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Open in new window

Commented:
For the identity-init you also need this

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Another important lack with the delete is it won't free allocated pages.

Author

Commented:
wow.. delete is still going for a long time now.. truncate would have been a breeze..

if i have FK or indexed view or in replication, then there is no simple way to do a truncate on all tables, then, right?

before i was creating scripts for ALTER TABLE DROP CONSTRAINT... TRUNCATE... THEN ALTER TABLE ADD CONSTRAINT...

but that was lot of work, and hence wanted to try it this way...
Commented:
If it's a regular action. Isn't it possible to have an backup of the database with empty tables ready and restore that over the one with the one with filled database?
Raises possible other questions on how to deal with changes in table-definitions .... they can't be done in the live database and forget them in the standby-empty database.

Author

Commented:
OK.. the logic I have to drop/truncate/create is from
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26332893.html#33223317

the complication (tricky) part is.. there is a break between the drop/truncate and create part.. there comes the repopulate part which is
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27425665.html#37063985

so I manually copy the script to drop the constraints then truncate.. then repopulate with the separate code... and then finally copy the script to create..

compared to that,

EXEC sp_Msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_Msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
seemed like a possible solution to avoid the manual process.. but it takes times and more importantly it will break the log drive :)

it is effective, but manual process.. could you see a way to automate the above code without manual intervention.?
Commented:
Sorry I don't have the time now to check other questions.

But delete is a (logged -> slow) way.  But if you realy need an intial database you can also try to generate a 'create database' script and create the whole database from that (so no content).

If you realy need it on a regular base -> have a backup of the initialized database and a restore of a backup to replace an existing database is something that is not difficult to automate.
lcohanDatabase Analyst
CERTIFIED EXPERT
Commented:
I would rather save ALL foreign key definitions, drop them do the CHECK ALL then TRUNCATE and add them back WITH NOCHECK (or CHECK if you must enfoirce data integrity but slower) at the end after tables are populated.
DROP CONSTRAINT, TRUNCATE and ADD WITH NOCHECK would be very quick comparing to anything else in my opinion.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
You could also check out this article that does similar thing with TRUNCATE and DELETE:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Author

Commented:
>>If you realy need it on a regular base -> have a backup of the initialized database and a restore of a backup to replace an existing database is something that is not difficult to automate.

thanks- we only want to get new data in, but leave other objects intact.. but I could propose to the management that we maintain all the new schema changes on an empty database for this purpose..

Author

Commented:
>>DROP CONSTRAINT, TRUNCATE and ADD WITH NOCHECK would be very quick comparing to anything else in my opinion.

but can you automate it?

in the link you provided, they do some truncate and some deletes.. so still could bloat the log and be slow, right?
lcohanDatabase Analyst
CERTIFIED EXPERT
Commented:
Sure - there could be SQL stored procedures(SP) for instance to save all FKey definitions in a DB table, one SP to drop them all, then you do the step(s) with truncate/insert, then another SP to add them back based on the definition saved in the DB table.


"in the link you provided, they do some truncate and some deletes.. so still could bloat the log and be slow, right? " yeah the delets could still be slow depending on the volumes and that's not just due to the logging or DB recovery model for that matter.

BTW - if all the above are done off line (I mean not when you DB is accessed by users, jobs, etc) then you could change the recovery mode from FULL for SIMPLE  in order to reduce logging.
Commented:
Yes you can automate yourself the whole way
1. capture everything that should be removed in the form for recreation
2. remove those objects
3. initilise database
4. recreate objects captured in 1
    - hope you don't get any sequence-problem
    - hope you don't have any (new) option in syntax that you didn't automate yet
5. how to see if your db-schema is exactly the same after whole drop/recreation action?

What I suggest
1. Take a copy of your actual database (without content)
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
2. use this as your reference for initial db, any modification in your db-schema should be done also here
3. Your action 'empty it all' will be basicly changed to 'just put back the empty database'

PS: the create database script can also be added to a source control system beside your application code.  




Author

Commented:
>>- if all the above are done off line (I mean not when you DB is accessed by users, jobs, etc) then you could change the recovery mode from FULL for SIMPLE  in order to reduce logging.

no it has to be done online.. will, by any chance, 'Transfer SQL Server Objects Task' cause less logging than plain inserts?
lcohanDatabase Analyst
CERTIFIED EXPERT
Commented:
"will, by any chance, 'Transfer SQL Server Objects Task' cause less logging than plain inserts"
No - it's still an (batched) insert operation and it is logged.

On another note..."no it has to be done online.." why do you need to perform

""
EXEC sp_Msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'Truncate table ?'
""

on a live online database?????

Author

Commented:
>>
on a live online database?????

i understand.. the network team is fuzzy about putting the dbs on simple and then full and back and forth because it messes their backup sequence...

but i agree, it is trivial, and i should really address this point. thanks.
Commented:
Changing recovery model is indeed an enterprise decision.

'create db scrip'. I don't know if it's scripting permissions (and don't have sql here at the moment). But it was a second way to create an empty database. The first was: do one time a delete (+shrink? +maintenance?) and backup your empty database.
lcohanDatabase Analyst
CERTIFIED EXPERT
Commented:
Sorry my question was not very clear - what I wanted to ask is why would anyone TRUNCATE ALL tables in a LIVE database plus now after you provided more details...worry about backups which I agree they may be interrupted however....

In that case the process can be adjusted to take a FULL BACKUP just before the truncate and that would be your fail back/recovery path as your statem,ents would truncate ALL tables anyway - does this makes sense?

Author

Commented:
yes, makes good sense.. and had a good meeting here based on your pointers..

we are in good shape. thanks to both of you.