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 |
|
THNQdigital
Starting Member
15 Posts |
 Posted - 2004-03-09 : 08:04:25
|
| I have a question on Transaction commit/rollback.QuestionMy understanding is that untill a transaction is comitted, the modifications(inserts/updates/deleted) are not made on th disk ( datafiles).For Example if i am doing a bulk copy of millions of records from a flat file on to sql server table, untill all the records are bulkcopied and transaction completes successfully and a commit is issued explicitly and the data is NOT wriiten to disk, where is the data stored (FOR THAT TRANSACTION)till then?I mean where is the data stored till the transaction is completed and committed ? This could be really a large data can cache may not b ebig enough to hold this amount of data..?Please let me knowThanks,THNQdigital |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-09 : 14:03:59
|
| Well, data isn't necessarily committed to disk when the COMMIT occurs. It happens in memory. When a checkpoint occurs, that's when it'll move to disk. Have a look at CHECKPOINTS in SQL Server Books Online for more details.Tara |
 |
|
|
THNQdigital
Starting Member
15 Posts |
Posted - 2004-03-10 : 06:03:08
|
| Tara,When you say data is in memory, you mean buffer/cache memory. right?Do you mean however large the transaction is, till the transaction is comeplete and committed and till the check point occur's the data is held in memory ?. If you say yes, i would like to believe that..But i don't understand the concept, when some of the DBA's sayThe data is written on to disk even prior to commit and check pointand that data will be made permanent if it is a successfull transaction ( commit) else the data written on to disk will be (pages) marked as free to use for any other transaction.Because this does not explain well to me about checkpoint in sql server, as my understanding is that if data has to be written on to disk then, a CHECKPOINT should occur (explicit or by server settings) and data block having a commit issued against them will be flushed from cache memory that sql server would have built during the transaction and that amount of cache memoery is released back to OS by SQL ServerPlease let me know if i am wrongBest RegardsTHNQdigital |
|
|
|
|
|
|
|
|
Data untill transaction is comitted!