| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2010-12-29 : 02:42:15
|
| Hi all, is there any way i can avoid specific DML operations not to be written into the transaction log e.g. Insert into testDB.dbo.tab1select * from tab2 -- returns 100+K rowsit should have no effect on the transaction log, basically i am dealing with manipulation of large data sets for reporting purposes and do not want them to have any impact over the transaction file size.Many thanks! MIK |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-29 : 02:50:05
|
| NO.AFAIK all the transaction are first written to log file and thereafter to mdf file and it cannot be bypassed. Still Would like to know from masters. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 03:17:40
|
| select *into testDB.dbo.tab1from tab2Is your database recovery model bulk logged or simple rather than full?Will have less effect - just log the extent allocationAnother similar option is to use bcp, SSIS or a bulk datasource but those all involve an extra connection. They will do a similar operation to a select * into but do not create the destination as part of the insert.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2010-12-29 : 04:16:39
|
| Many thanks! i have created a testDB and set the recovery model to Simple, to prevent the growth of the trn log file size. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2010-12-29 : 05:13:15
|
| @Nigelrivett thanks for sharing the information. I am unable to get your point of "might not help" from the given link. However it was useful for me for understanding the MINI LSN concept :) THANKS! (and if i am not wrong it is the pointer to the first in-use portion of the logical file of the most recent commited/uncommited transaction, in other words the changes that are not reflected into the Datafiles as yet are held by the transaction log with active status)By the way I have created the testDB with initial transaction log size of (say) around 100MB (which is more than enough for my one go DML (batch) operation) with simple recovery model. And as per my understanding in Simple recovery model, administration of the trn log file growth is auto handled by the DB engine via firing the Checkpoint as when required. As a result I will be having free spaces in the trn log file for re-use. Please feel free to let me know if my concepts are wrong :)Thanks! |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 05:25:56
|
| Yep.It won't help if you do a large update in a single statement or if something keeps a transaction open.It sounds though that your problem is not reducing the amount of data written to the tr log but allowing it to be released - in which case this should help if you don't have a tr log backup schedule that will cope.Note you will now have to disable tr log backups. If you didn't have them then probably a review of all your databases is in order and maybe a bit more reading.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2010-12-29 : 06:08:06
|
| Thanks!Well i have large data set DML operation in a single statement, which would be scheduled at daily basis. But all of them are implicit transaction, so no worry of open transaction. Yes, I am not having the problem of reducing the tr log file size. Actually i don't want it to grow. In other words I don't want to administer the change in the file size by the scheduled job. Also i don't need the backups of the this DB, since it is already stored in another database which is continuously being backed up.Actually my task is to produce some sort of scheduled reports from an existing database but need to show the data in different layout than the existing one. For which i need DML operations. And if i do it on the same DB it will increase the trn log file size and will reduce the total available free space on the disk (risk for other databases), therefore created a new one!! But still i need to freeze the file growth any way so that it does not have impact on the disk size.Thanks! |
|
|
|
|
explicitly avoiding DMLs impact onto the tr log