Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kshahzad
Starting Member
45 Posts |
 Posted - 2013-07-16 : 01:14:25
|
| 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
52326 Posts |
Posted - 2013-07-16 : 01:20:26
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-16 : 06:22:01
|
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 MVPhttp://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. |
|
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-07-16 : 06:35:25
|
| 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. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-16 : 10:27:30
|
| 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.aspxAre we also to assume that you need to delete rather than truncate because 8 Mil is not the entire table? |
|
|
|
|
|
|
|
|
deleating 8 million rows