truncate still blocked after NOCHECK ALL

Please use SQL activity monitor or sp_who(sp_who2) stored procedures to see what locks the table.

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

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?

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.

ashilo, as per that link
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
did not disable everything as per the query in 37064027

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...

OK.. the logic I have to drop/truncate/create is from
https://www.experts-exchange.com/questions/26332893/delete-table-data.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
https://www.experts-exchange.com/questions/27425665/making-a-text-from-a-select-statement-to-execute.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.?

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

>>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..

>>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?

>>- 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?

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/

this won't take care of user's permissions, will it?

>>
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.

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

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