Logging options while running stored procedure

Question

Logging options while running stored procedure

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

DesNorton - Wednesday, June 20, 2018 2:20 PM
Jeff Moden - Wednesday, June 20, 2018 11:01 AM
Chris Harshman - Wednesday, June 20, 2018 10:56 AM
Grant Fritchey - Tuesday, June 19, 2018 2:29 AM
Only concern I have is that looks like a big transaction. Will there be multiple copies of this running at the same time? If so, you could see a lot of blocking across all the tables affected.
Also of note, since you have one big transaction for the whole script, if it rolls back you will have lost anything you tried to log as well.
There's a trick to that. Store the log in a Table Valued Variable. Don't write to a permanent table until an error occurs of the run successfully completes.
Another trick that I use is to write it out to a TAB delimited file using xp_CmdShell. There's no chance of it being rolled back then even if the machine were to suddenly lose power. If you need to have the log in a table when your done, just BULK INSERT the data from the file.
Another trick is to use a Loopback Linked Server, and writing outside your session.

Ooooo... that sounds awesome. Didn't know about that one. Makes perfect sense and sounds a lot more resilient than logging to a table variable. Certainly, it's also easier than writing out to a file. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

mhtanner Mr or Mrs. 500 Points: 552 More actions · Answer 2

A simple way to get around the issue of modified_on and modified_by causing records to expand is
a) Use an ID for people (e.g. an int) so it is always small and fixed width - perhaps rename column to modified_by_id
b) Always set modified_on and modified_by_id to the same value as created_on and created_by_id when the record is created
Now, when somebody updates a status_id field or whatever along with modified_on and modified_by_id there is no change in record size.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

mhtanner - Thursday, June 21, 2018 2:15 AM
A simple way to get around the issue of modified_on and modified_by causing records to expand is
a) Use an ID for people (e.g. an int) so it is always small and fixed width - perhaps rename column to modified_by_id
b) Always set modified_on and modified_by_id to the same value as created_on and created_by_id when the record is created
Now, when somebody updates a status_id field or whatever along with modified_on and modified_by_id there is no change in record size.

I totally agree with that (it was one of the things I identified in my extended writeup above) but it doesn't help existing "systems" unless people are willing to make the changes in both managed code, in the T-SQL that exists, and in the database itself. For new stuff, it's definitely the way to go.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions