Hello all,
We are having a space issue at work and I am between a rock and a hard place. On our production machine the Trans logs are getting full really quickly (mainly due to poor programming). I was told that nothing is being allowed to move to production from UAT, so I need to come up with an alternative for getting my data out of prod.
I have 3 SP's that does a few SELECT statements, so no problem here. The logs should be ok but I do have a few INSERT statements that stores incremental data (records that has changed or that is new). What I am thinking is to utilize OPENROWSET. I would change my dev code to select my production data from an openrowset and then use BCP to write my results to a CSV or txt file.
I have a few questions.
- What affect does OPENROWSET have on the Trans log?
- Is there a better way of doing this?
- Without using DTS or SSIS is there a way to set the commit/batch size of an insert statement? (I'm thinking a loop)
- Would the above mention plan work?
Much Thanks
Well Done is better than well said
- Ben Franklin
We are having a space issue at work and I am between a rock and a hard place. On our production machine the Trans logs are getting full really quickly (mainly due to poor programming). I was told that nothing is being allowed to move to production from UAT, so I need to come up with an alternative for getting my data out of prod.
I have 3 SP's that does a few SELECT statements, so no problem here. The logs should be ok but I do have a few INSERT statements that stores incremental data (records that has changed or that is new). What I am thinking is to utilize OPENROWSET. I would change my dev code to select my production data from an openrowset and then use BCP to write my results to a CSV or txt file.
I have a few questions.
- What affect does OPENROWSET have on the Trans log?
- Is there a better way of doing this?
- Without using DTS or SSIS is there a way to set the commit/batch size of an insert statement? (I'm thinking a loop)
- Would the above mention plan work?
Much Thanks
Well Done is better than well said
- Ben Franklin