Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 deleating 8 million rows
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kshahzad
Starting Member

45 Posts

Posted - 07/16/2013 :  01:14:25  Show Profile  Reply with Quote
i am deleating 8 Million rows from my database,

I am wondering how to control T-Log,

also I heard something about row lock and table lock

how is it related in this context?

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/16/2013 :  01:20:26  Show Profile  Reply with Quote
You need to break them up into batches and do delete for each batch. Do you've a unique valued column (primary key/identity) in your table? If yes, you can implement a looping logic around it for batch by batch deletion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 07/16/2013 :  06:22:01  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

You need to break them up into batches and do delete for each batch. Do you've a unique valued column (primary key/identity) in your table? If yes, you can implement a looping logic around it for batch by batch deletion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


row lock and table lock describe the way in which SQL Server acquires the necessary locks when it wants to update/delete/insert rows into a table.

Normally, you shouldn't need to explicitly specify what kind of locks you want to acquire. SQL Server will intelligently manage that. For example, if you were to update one row in a table that has a million rows, SQL Server would acquire a row level lock. But if you were to update 300,000 rows in that same table, SQL Server would acquire a table level lock. It does so to manage the number of locks to a reasonable level, thus improving efficiency.
Go to Top of Page

shaggy
Posting Yak Master

India
248 Posts

Posted - 07/16/2013 :  06:35:25  Show Profile  Reply with Quote
Do you have partition implementation in table if yes you can use switch partitiojn to delete the records in no time.

eg: if your table partiontion is (date)year wise if i want only current year (2013) records i can switch all the partion records to another table (that is trucation to particular partiion record) leaving alone 2013 partion.


Edited by - shaggy on 07/16/2013 06:36:07
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/16/2013 :  10:27:30  Show Profile  Reply with Quote
Do you think that deleting 8 million will be a problem? That is a pretty trivial amount of data by most standards. As others have mentioned if you are concerned, you can delete in bataches. Here is one of many posts on the topic:
http://www.sqlservercentral.com/Forums/Topic1304765-392-1.aspx

Are we also to assume that you need to delete rather than truncate because 8 Mil is not the entire table?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000