how to work out how many transactions per day

Question

how to work out how many transactions per day

winston Smith

SSCoach

Points: 19500
More actions
March 11, 2008 at 8:49 am

#183893

my company has a heavily transactional database. their backup procedure is a full backup once a week and a diff backup once daily.
This leaves a potential to lose a full day of transactions.
Is there a way i can extract the data from the tran log since the most recent diff backup, so as to demonstrate how much data they could potentially lose?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

winston Smith (3/11/2008)
my company has a heavily transactional database. their backup procedure is a full backup once a week and a diff backup once daily.
This leaves a potential to lose a full day of transactions.
Is there a way i can extract the data from the tran log since the most recent diff backup, so as to demonstrate how much data they could potentially lose?

Yes, it is called log backups. Check books online for "backup log".

Then when you restore the db you will need to restore the last full backup, the very last differential backup, and then all the subsequent log backups. More info on http://technet.microsoft.com/en-us/library/ms186858.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

winston Smith SSCoach Points: 19500 More actions · Answer 2

thanks for the reply. i am aware of log backups. the reason i am asking the question is because i want to have a strong case when i go to my manager asking for us to alter our backup strategedy to include log backups.

I need to know the total number of transactions ina log so that i can demonstrate how many transactions we could lose by not backing up the tran log.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

winston Smith (3/11/2008)
thanks for the reply. i am aware of log backups. the reason i am asking the question is because i want to have a strong case when i go to my manager asking for us to alter our backup strategedy to include log backups.
I need to know the total number of transactions ina log so that i can demonstrate how many transactions we could lose by not backing up the tran log.

I assume you are on 2005, then you can use:

dbcc log (1,-1) with tableresults

the first number is the dbid

This will not give you back all the log records since the last diff backup (either more or less, depends on the active log partion), but gives you quite a lot of info. Search for

LOP_BEGIN_XACT for transaction begins. Not sure how this information will help. Information like today's sales would be lost is probably more compelling 🙂

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Ya beat me to it, Andras... 😀

--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)

winston Smith SSCoach Points: 19500 More actions · Answer 5

winston Smith

SSCoach

Points: 19500

March 11, 2008 at 9:33 am

#788074

Thanks guys. il look into that now! kind regards.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Or urn Profiler, look for insert/udpate/delete stuff, save the file for a day and scroll through it at a meeting. Set your scroll to move slow and let it run as you talk.

Makes a nice presentation as you're talking.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

Jeff Moden (3/11/2008)
Ya beat me to it, Andras... 😀

Finally I got lucky enough 🙂

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 8

Steve Jones - Editor (3/11/2008)
Or urn Profiler, look for insert/udpate/delete stuff, save the file for a day and scroll through it at a meeting. Set your scroll to move slow and let it run as you talk.
Makes a nice presentation as you're talking.

It is sad that you need to persuade them using such visual "tricks" though, but Steve's suggestion could be very effective (called shock therapy :D)

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 9

Hi,

To strenghen your case, ask your manager what the Recovery Time and Recovery Point Objectives (e.g. RTO and RPO) are for the company database and cross-check whether the current backup strategy is meeting these requirements. Lost data is lost data, regardless of how many transactions. Any amount of lost data is too much.

Thanks,

Phillip Cox

MCITP - DBAdmin

winston Smith SSCoach Points: 19500 More actions · Answer 10

Im loving these tactics. its almost manipulation of people, getting them to give the answer you want on their own!!! Brilliant!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

It's even simpler than that... if for every byte of data lost, the manager had to pay a fine of $100, how soon do you think that lame-o backup strategy would be improved? Call his wife on the phone and ask her if he lost 2 paychecks in a row gambling, would he actually live through it? 😀

--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)