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 |
|
spock
Starting Member
35 Posts |
 Posted - 2002-02-02 : 03:54:39
|
| Hi I happened to come across an interview question on transactions. the interviewer told he had a table containing 3 mil records. He wanted to delete a sizeable portion (85%) of records using a delete statement. His question was would sql server 7.0 be able to handle that amount of transactions and what would happen to the transaction log ? would it become full and if yes what would then be have to done ? I dont have any clue to this question.can some one please clarify?thanks in advancekaushik |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-02 : 07:40:30
|
| IMHO if Transaction log is Set to Auto Grow then i dont think it should create any problems.A easier way of deleting that no. of records is creating a temperary table and copying the 15% records in it and dropping the original table and renaming the temperoary table to its orignal name. ofcourse, if there are any dependencies involved you have to take care of them.There are couple of good articles on Transaction log . Read them.http://www.sqlteam.com/SearchResults.asp?SearchTerms=transaction+log--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-02 : 18:08:56
|
| Yes sql server can handle it if it has the resources - but may take a long time.It will create a lot of entries in the tr log.If there is not room in the tr log or it runs out of room on the disc or locks then the delete will fail - and you may have problems with the database recovery on restart. Running out of room in the tr log is never a good idea.To execute this safely just put it in a loop with a rowcount set until no records are updated and make sure that the tr log is being cleared.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
transaction question