Friday, January 29, 2010

SQL Server DELETE and TRUNCATE for ROW

I am currently working on a project, which requires to be synced with server database all the time, and there is lot of deletion which keeps happening, there are about 15,000+ records which gets deleted whenever synchronization is happening between server and client. I was performing DELETE statement to delete row from the table which was not required. Everything worked fine, the real problem started arrising when we were looking at the way database would act, we figured out that everytime we did deletion of 15,000+ rows, it will increase log file of database by 6.00MB. Which means within in a month database log file would increase upto 180MB, and 2190MB within in a year, and this is just regarding Deleting row.

I faced challange regarding how to make sure that the log file does not increase. While going through documentation, I found one keyword called TRUNCATE. When we used TRUNCATE keyword instead of DELETE, it worked perfectly fine, and log file was not increased, and we also found a good increase in performance.

Reason is that, when we use DELETE keyword it deletes row and also it keeps log of it in LOG file, but when we use TRUNCATE, it does not keep log of it, so nothing is recorded in LOG file, hence log file size does not increase. Which means that when we DELETE row(s) and we want to recover data back, we can use recover data back through log file, but when we use TRUNCATE keyword, we can not recover data back from log file.

But, here is the catch, if I use TRUNCATE keyword in TRANSACTION block, it allows us to recover data back, but there is no gurante of recovering data.

Sample Code:
BEGIN TRAN
SELECT * FROM MyTable
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable

No comments:

Post a Comment