SQL Server Forums
Profile | Register | 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
 New Topic  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
52249 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

3323 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
245 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

4354 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000