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

Clear database - delete all records from a database Expand / Collapse
Author
Message
Posted Saturday, February 2, 2008 3:35 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
Comments posted to this topic are about the item Clear database - delete all records from a database
Post #450857
Posted Monday, April 14, 2008 5:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 6:41 AM
Points: 907, Visits: 892
Good script! This would be perfect for me if it included one additional item: Re-initializing all identity fields to 1. I can do it table-by-table but if there's a way to do it globally, even better.

Ron Moses
ConEst Software Systems


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #484322
Posted Monday, April 14, 2008 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:40 PM
Points: 5, Visits: 201
Good Script.
My question is I have some reference tables in my Database. All other databases can be cleared with the exception of the reference tables.
Any help on that.

Thank you.
KP
Post #484530
Posted Monday, April 14, 2008 11:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
Unless the database is very small, this script will blow up the tran log.

Things I would change or add to this script:

1. Abort the script if the specified/current database is mirrored or replicated.
2. Abort the script if a user tried to run it on a system database.
3. Use Truncate Table instead of Delete From for improved performance, to prevent tran log bloat, and to reset identity values.
4. Get rid of cursors (not needed).




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #484574
Posted Monday, April 14, 2008 3:05 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
I have already tried to use 'truncate table' but it raises an error if there are any foreign key constraints. I would have to drop them before truncating table unless I find some way to disable and enable them after cleaning the database.
By the way this script will not work if relationships are guarded by triggers (some old database may still use this solution).
And You are right: I should not have used cursors.
Post #484657
Posted Monday, April 14, 2008 3:08 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
And good point about transaction logs. Mayby the script should check the recovery mode and set it to simple before cleaning the db and restore settings after the work is finished.
Post #484658
Posted Wednesday, April 16, 2008 1:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
I have just submitted a new version of the procedure:
- without cursors
- with reseeding identities
- with changing (and restoring) recovery mode
Now it is waiting for approval.
Post #485454
Posted Monday, February 16, 2009 5:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 9, 2009 4:57 AM
Points: 58, Visits: 32
Actually i m new to this, so i had a problem understanding this code. it would be really helpfull if u could help me getting it more clear. Thanks anyways.
Post #657621
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse