July 21, 2009 at 9:41 am
Hi guys,
I need some help in understanding SQL transaction logs. I want to find out whether certain information was put into a database using insert or update statements (basically an audit of user activity on the database). Which logs/file would give me information showing insert, update and delete sql statements and which user generated them. Is this information stored in the .ldf files? and if they are, how can I read them?
Secondly, is it possible to get this info if 'Trace' was not enabled?
How do you enable 'Tracing'?
How can I find out if Tracing was enabled or not?
I would VERY VERY much appreciate a response.
Cheers.
July 21, 2009 at 9:56 am
I hope I can help clarify a bit.
some of the 3rd party Log readers , like from RedGate, can read your database and log files, and show you who did what and when, and can even script out the reversal of some of those changes...but ONLY if the database Recovery Model is set to FULL and not 'SIMPLE' or 'BULK_LOGGED"
The log has all the actual changes to the data. if the Recovery Model is SIMPLE, the oinly thing in the log is uncommited items, like things awaiting a COMMIT for a transaction...so there's nothing to look back into previous history.
traces are useful for auditing..you can find out who ran what command, for example DELETE FROM YOURTABLE WHERE ID BETWEEN 1 AND 100, but the actual data of what was in the stuff that was deleted is not available in a trace...just in the LOG as described above.
I like to think traces are good for fingerpointing...you can find out who did what, or determine where an app is messing up, and also see some performance data on what commands are going slow, which are fast, etc.
you can see the traces that are on your machine now by doing SELECT * FROM sys.traces
the trace #1 is the "default" trace you hear about , which tracks changes to objects: alter table/create proc, drop view, etc., but nothing related to DML items...INSERT/SELECT/UPDATE/DELETE...for that you want to add an additional trace.
I've posted what i think is a good DML trace in this post:http://www.sqlservercentral.com/Forums/FindPost745574.aspx
nobody's really critisized it, so it's got at least past the first level of peer review.
What's kind of helpful is that it adds a view so you can see the trace results whenever you feel like it; that's probably a good thing to do for all your traces.
you cannot see anything that happened before a trace is created...if you didn't have one running, you can never see what happened before the trace existed...so it's a good practice to create traces for various tracking functions.
psemenye (7/21/2009)
Hi guys,I need some help in understanding SQL transaction logs. I want to find out whether certain information was put into a database using insert or update statements (basically an audit of user activity on the database). Which logs/file would give me information showing insert, update and delete sql statements and which user generated them. Is this information stored in the .ldf files? and if they are, how can I read them?
Secondly, is it possible to get this info if 'Trace' was not enabled?
How do you enable 'Tracing'?
How can I find out if Tracing was enabled or not?
I would VERY VERY much appreciate a response.
Cheers.
Lowell
July 21, 2009 at 9:56 am
psemenye (7/21/2009)
Hi guys,I need some help in understanding SQL transaction logs. I want to find out whether certain information was put into a database using insert or update statements (basically an audit of user activity on the database). Which logs/file would give me information showing insert, update and delete sql statements and which user generated them. Is this information stored in the .ldf files? and if they are, how can I read them?
Yes, all activities that you perform are logged into the transaction log file first and then written back to the data file. You cannot read from a log file. There are some third party log reader tools. Pls google to find them out but they'll cost you.
Secondly, is it possible to get this info if 'Trace' was not enabled?
How do you enable 'Tracing'?
If you know the trace number, you can check it's status by running DBCC TRACESTATUS. Check out BOL for details.
I guess you mean running a profiler or server side trace. I'd suggest you to read some articles on this site on out to enable a trace.
These two sites are very good ones.
[/quote]
July 23, 2009 at 6:38 am
Thanks Lowell and PS for your useful information, much appreciated.
I am very new to SQL and have alot to learn. I am a computer forensic investigator and have a case that requires me to analyse an SQL database (first time to get such a case).
Now, my next assignment or task to find out if certain rows of data were inserted in the database (tables). I want to look at the database and examine the rows of data in the tables and see whether I can find certain names.
I dont have the running database with me, all I have is the .ldf and .mdf files. I have 2 .mdf files of sizes 17GB and 41GB and 3 .ldf files of sizes 353MB, 123MB and 493MB. SQL server 2005 was in use.
How do I go about reading the rows of data from the .mdf files?
Do I have to install SQL server 2005?
Will SQL server 2005 Management Studio Express do the job? (I have been told that this may have some limitations)
Please advice on what to install and procedure to follow. I have a Vista 64Bit and XP SP3 machine
Cheers.
July 23, 2009 at 6:58 am
psemenye (7/23/2009)
Do I have to install SQL server 2005?
Yes
Will SQL server 2005 Management Studio Express do the job? (I have been told that this may have some limitations)
No. Management studio is just the client tools. To be able to work with a SQL database, you need the database engine running (SQL's not like MS Access)
Download SQL Server Express edition and SQL Server Management Studio Express. One's the database engine, one's the client tools
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2009 at 6:59 am
computer forensic investigator KEWL;
obviously I don't need to tell you to only perform work on copies of the mdf and ldf files.
i would definitely grab a trial version of a log reader; I'll download one from redgate and report back to see if I think it would be of any use. I'm thinking that if the log has not been backed up, that is why the ldf is somewhat large, and you might be able to get something out of it with a log reader, but this is interesting enought hat I'd like to try it as well.
also stating the obvious, the mdf and ldf files are binary, so it's not like you can read them with a hex editor; you have to attach them as databases to a SQL instance, and yes, an express verison will work just fine;
Lowell
July 23, 2009 at 7:12 am
Lowell (7/23/2009)
I'll download one from redgate and report back to see if I think it would be of any use.
RedGate's log reader does not work on SQL 2005 databases. It's 2000 only.
Apex sells a SQL 2005 version, as do a couple other companies that I can't offhand recall.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2009 at 8:17 am
Express edition (which is free) might not be useful to you as the maximum database size if 4GB. Developer's edition or any other edition will allow you to attach a database of this size.
July 23, 2009 at 8:23 am
ps (7/23/2009)
Express edition (which is free) might not be useful to you as the maximum database size if 4GB.
Good catch.
In this case, I recommend getting developer edition. It's only $50. Licensing restrictions are that it can't be used in production, but for what you're doing it sounds fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2009 at 2:41 am
GilaMonster (7/23/2009)
ps (7/23/2009)
Express edition (which is free) might not be useful to you as the maximum database size if 4GB.Good catch.
In this case, I recommend getting developer edition. It's only $50. Licensing restrictions are that it can't be used in production, but for what you're doing it sounds fine.
You guys are great! Thanks alot.
I have managed to get a hold of a fully licensed SQL server 2008. Before I start struggling with it, do you guys think it will do the trick? My concerns are that SQL server 2008 will not be able to read mdfs and ldfs from a SQL server 2005 due to compatability issues.
July 24, 2009 at 2:51 am
SQL 2008 has no problems with 2005 data files. It will convert them to 2008 format the first time they're attached. Once that conversion is done, there's no going back to 2005 though. Make sure you keep a copy of the data and log files pre-attach if you think you'll need to use them on SQL 2005 again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply