June 11, 2007 at 1:35 pm
So the question is what happened?
Last week we had an issue w/ some data missing from some tables that messed up some products for sale on our companies web site. This is an interesting case because it wasn't that data was deleted or anything but rather all of the fields in a table (besides the pk) were null.
Not being the best DBA (hired as a B.I. Analyst but am stuck doing DBA work) my first thought is to find the root of the problem. Okay, it's the table w/ the null values. So what caused it? Perhaps a stored proc that makes an update or insert into that table that didn't run properly? This rout of investigation is quickly stifled by my boss (MS Genius) who tells me to take a look at the transaction logs to see what was going on during the time of the 'update' job ran.
So the question is what happened...and my boss thinks the answer is in the transaction logs. So my question that I need help with is:
How do I view the transaction logs to determine 'what happened'?
When I try to open them in notepad it gives me an error 'cannot open file because it is being used by another person or program' which means I would need to shut down SQL in order to access them. I also see there is some 3rd party software solutions for this but I have a feeling my boss won't pony up the coin for that.
All comments are appreciated...thanks!
Ben Sullins
bensullins.com
Beer is my primary key...
June 11, 2007 at 2:02 pm
Ben,
Do you know if you have any monitoring or audit software. If you do, you may be able to find out if a user touch the table. As for purchasing auditing software, try to find out if you fall under SOX regulations. If you do, you might as well get the PO ready.
June 11, 2007 at 2:03 pm
You can't view the Transaction Logs in SQL Server without a third-party tool.
The one I see most DBAs suggest/recommend is Lugiment's Log Explorer. However, I do believe you can only review logs created after the application is installed. I've never used it, so I could be wrong.
-SQLBill
June 11, 2007 at 2:20 pm
Thanks guys...we do fall under SOX regulation but do not have any auditing or monitoring software in place. Any suggestions on a good solution for this?
Ben Sullins
bensullins.com
Beer is my primary key...
June 11, 2007 at 3:16 pm
Ben,
Log Explorer, in my opinion, is worth the money in these cases but you can get a rudimentary look into the T-log with DBCC LOG.
Syntax:
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
where:
dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID
by default, type = 0
Greg
Greg
June 11, 2007 at 4:04 pm
Thanks for the tip Greg...Looking @ this data (from DBCC log()) I don't really see anything useful that tells me whats going on.
Is there some way to translate the hexadecimal dump into some readable sql code?
Not sure if that makes sense but my understanding is that basically each line here represents some action performed on the database so I would hope to find the specific action that cause my discrepancy in the data.
Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply