Raster Catalog: ldf

Showing posts with label ldf. Show all posts

Friday, March 30, 2012

Reading .LDF

Dear all,
How to identity all those changes done in a db for any user in a specific
period of time?
I am not talking about put triggers in each object or something like that
(on the other hand such actions never will reach exhaustely all the actions
commited)
For example:
developer1 modify a view at 16:00 and then developer2 delete it.
Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
level (hexadecimal info)
I've been able to to order theses scenarios (values as follows belong to
field 'OPERATION':
0)AFTER TOTAL SHRINK (O KB)
LOP_BEGIN_CKPT
LOP_END_CKPT
LOP_FILE_HDR_MODIFY
1)FOR INSERT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_COMMIT_XACT
2)FOR UPDATE STATEMENT:
LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT
3)FOR CREATE OBJECT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_MARK_DDL
It seems very tricky but I suppose that there would be some method for
obtain such information. How can I do this?
Perhaps, it's a useless madness, obsessive.
--
Current location: Alicante (ES)Enric
Visit at
http://www.lumigent.com/ --explorer log for sql
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)|||> How to identity all those changes done in a db for any user in a specific
> period of time?
In hindsight:
http://www.aspfaq.com/2449
Moving forward:
http://www.aspfaq.com/2496|||Thanks a lot to both
--
Current location: Alicante (ES)
"Aaron Bertrand [SQL Server MVP]" wrote:

> In hindsight:
> http://www.aspfaq.com/2449
> Moving forward:
> http://www.aspfaq.com/2496
>
>|||There are 3rd party tools for mining information from the transaction log.
However, the intended purpose of this log is for maintaining transactions,
not auditing, so you may find it limiting if your intent is monitoring user
activity.
With a little studying and configuration, you can trace most any desired
event using SQL Server Profiler, which is a tool installed with SQL Server.
Depending on what event categories you choose, you can trace not just data
or object modifications but also things like SP executions and even table
selects. It is basically a service that runs on server, and it's output can
be archived to a file or table. You will need admin rights to use this tool.
http://msdn.microsoft.com/library/d...>
erf_86ib.asp
[url]http://www.sqlservercentral.com/columnists/bkelley/auditingwithsqlprofiler.asp[/ur
l]
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)

Wednesday, March 28, 2012

Read the transaction log (ldf) file. My first post ever :)

Hi!

The purpose is to se history of which user updated, inserted or deleted a row in the database.

Can't seem to find any publications from microsoft on how to parse and interpret the log file.

Any documentation on how the log file is structured?
Is it possible to obtain this information through the system views

I don't want to use triggers.

Thanks

Consider the use of a third-party application for this job. That is money that's well spent.

|||

Thanks for the reply.

I know there exist som applications that can read log files, like Lumigent Log Explorer.

But it would be nice with an freeware version

|||

If you really want to learn, here the docuemnt on basic level.. I found few months back very unexpectedly... Nice one....

But this document is not sufficient to start programing with Tx Log files.. As Frank says you can use the 3rd party tools.. there are lot of products available on the market...

https://www.blackhat.com/presentations/bh-usa-07/Fowler/Presentation/bh-usa-07-fowler.pdf

|||

J-A.G. wrote:

I know there exist som applications that can read log files, like Lumigent Log Explorer.
But it would be nice with an freeware version

TANSTAAFL

But in order to learn you can always use something like the undocumented

DBCC LOG (Northwind, -1)

|||

good frank!

This undocumented command works on sql2000?

|||

Yes it does. But as with all undocumented features the use is at your own risk.

|||Tks!

Monday, March 26, 2012

Read the database transaction log file ie LDF file

Hi,
I want to read the .LDF for a database without making that database offline.
.LDF file contains all the transactions in that database.
Is there any documented/undocumented approach?
Any help is appreciated.
Thanks
PushkarThere are various third-party tools that can help.
LogPI, Log Explorer, etc.
Recently I've been using Red Gate's tool quite a bit... I think it's a bit s
impler to use than the other available tools:
http://www.red-gate.com/sql/sql_log_rescue.htm
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:OOFQjIMqFHA.904@.TK
2MSFTNGP10.phx.gbl...
Hi,
I want to read the .LDF for a database without making that database offline.
.LDF file contains all the transactions in that database.
Is there any documented/undocumented approach?
Any help is appreciated.
Thanks
Pushkar|||Use a third party tool.
http://www.aspfaq.com/2449
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOFQjIMqFHA.904@.TK2MSFTNGP10.phx.gbl...
Hi,
I want to read the .LDF for a database without making that database offline.
.LDF file contains all the transactions in that database.
Is there any documented/undocumented approach?
Any help is appreciated.
Thanks
Pushkar|||You can use the undocumented command DBCC LOG. See:
[url]http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp[/ur
l]
A better approach would be to use a third-party tool, such as
Lumigent Log Explorer, ApexSQL Log or Red-Gate SQL Log Rescue.
Razvan

Read Only MDF LDF files

I have a couple of SQL database files, MDF and LDF, that were distributed on
CD media (they came w/ a Microsoft Press book).
I can attach the database, but it is read only. However, if I try to set
the DB to not read-only, I get an error:
Error 5105: Device activation error. They physical file name "C:\program
files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
incorrect.
Could not restart database "contactsdb". Reverting to old status.
ALTER DATABASE statement failed
sp_dboption command failed.
If I set the files to NOT read-only (they were read only since they were
copied from a CD), the DB gets marked suspect and/or I get the error 9003:
The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
How am I supposed to attach this dataabase in a write-enabled mode?
--
Thanks,
Martin SchmidIt sounds like the files might be bad, and I don't think just marking them
readable in the file system would do that. You could try just copying the
MDF file to the local directory (leaving the LDF on the CD), changing the
file's readonly attribute, and then try attaching the mdf only (maybe with
sp_attach_single_file_db. It might be able to build a new log for you.
Good Luck
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> I have a couple of SQL database files, MDF and LDF, that were distributed
on
> CD media (they came w/ a Microsoft Press book).
> I can attach the database, but it is read only. However, if I try to set
> the DB to not read-only, I get an error:
> Error 5105: Device activation error. They physical file name "C:\program
> files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> incorrect.
> Could not restart database "contactsdb". Reverting to old status.
> ALTER DATABASE statement failed
> sp_dboption command failed.
> If I set the files to NOT read-only (they were read only since they were
> copied from a CD), the DB gets marked suspect and/or I get the error 9003:
> The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> How am I supposed to attach this dataabase in a write-enabled mode?
> --
> Thanks,
> Martin Schmid
>|||I DON'T want the DB read only... the file is marked read only because it was
copied from READ ONLY media.
--
Thanks,
Martin Schmid, EIT, CCSA, MCDBA, MCSE
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:euGR0OCeDHA.2436@.TK2MSFTNGP11.phx.gbl...
> Hi Martin !
> Even you want the database to be read-only. you cannot achieve it by
setting
> the attribute to read-only, because during normal database checkpoints are
> written to the LOG-Files and the database. What you can do is to copy the
DB
> set the attribute to writeable and set the database (logically) to
readonly,
> so that only records can be read.
> HTH, Jens Süßmeyer.
>
> "Martin Schmid" <martinschmid@.sbcglobal.net.nospam> schrieb im Newsbeitrag
> news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > I have a couple of SQL database files, MDF and LDF, that were
distributed
> on
> > CD media (they came w/ a Microsoft Press book).
> >
> > I can attach the database, but it is read only. However, if I try to
set
> > the DB to not read-only, I get an error:
> > Error 5105: Device activation error. They physical file name
"C:\program
> > files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> > incorrect.
> > Could not restart database "contactsdb". Reverting to old status.
> > ALTER DATABASE statement failed
> > sp_dboption command failed.
> >
> > If I set the files to NOT read-only (they were read only since they were
> > copied from a CD), the DB gets marked suspect and/or I get the error
9003:
> > The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> >
> > How am I supposed to attach this dataabase in a write-enabled mode?
> >
> > --
> > Thanks,
> > Martin Schmid
> >
> >
>|||Perfect! Thanks.
--
Thanks,
Martin Schmid, EIT, CCSA, MCDBA, MCSE
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OgkrngBeDHA.1828@.TK2MSFTNGP10.phx.gbl...
> It sounds like the files might be bad, and I don't think just marking them
> readable in the file system would do that. You could try just copying the
> MDF file to the local directory (leaving the LDF on the CD), changing the
> file's readonly attribute, and then try attaching the mdf only (maybe with
> sp_attach_single_file_db. It might be able to build a new log for you.
> Good Luck
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
> news:eA32Y5AeDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > I have a couple of SQL database files, MDF and LDF, that were
distributed
> on
> > CD media (they came w/ a Microsoft Press book).
> >
> > I can attach the database, but it is read only. However, if I try to
set
> > the DB to not read-only, I get an error:
> > Error 5105: Device activation error. They physical file name
"C:\program
> > files\microsoft sql server\MSSQL$MYINSTANCE01\Data\Contacts.MDF" may be
> > incorrect.
> > Could not restart database "contactsdb". Reverting to old status.
> > ALTER DATABASE statement failed
> > sp_dboption command failed.
> >
> > If I set the files to NOT read-only (they were read only since they were
> > copied from a CD), the DB gets marked suspect and/or I get the error
9003:
> > The LSN (12:32:1) passed to log scan in statbase "contacts" is invalid.
> >
> > How am I supposed to attach this dataabase in a write-enabled mode?
> >
> > --
> > Thanks,
> > Martin Schmid
> >
> >
>

Friday, March 23, 2012

READ LDF FILES

I would like to know is it possible and how to read LOG files with SQL-DMO?

My clients would like to know who is doing what and when. Actually I would like to trace table activites these are DELETES, UPDATES & INSERTS only. Also I know that it is possible to do it with triggers & jobs by creating new tables but imagine you have a database with size greater then 10 GB. It wouldn't be a good solution at all.

Thanks in advance,
SQL-DMO does not have that capability.

However, SMO has a namespace, which allows you do to live capture of trace data. See the Microsoft.SqlServer.Management.Trace namespace in Books Online.

However that is not reading LDF files, but starting a trace. If you want to read log files there are various 3rd party product out there that offer that capability.

Wednesday, March 21, 2012

read *.mdf files

Hello,
I am new for MSDE.
Our ISA2004 server has report in *.mdf and *.ldf format.
We don't have SQL server. I installed MSDE on my workstation.
Is there any step-by-step instruction to show me, how to create Access
Project to view the ISA2004 report?
ThanksHi
Your mdf and ldf files are (probably) not a report but data and log files
for a SQL Server database. You can attach these to your MSDE instance with
the system stored procedure sp_attach_db see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp.
To call this stored procedure you will need to use the osql utility at a
command prompt see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp.
All this information is downloadable in Books Online which will allow you to
read and search for information at
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
This may help you get started with using Access as the from end for MSDE
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_msdeintro.asp.
There are also dedicated newsgroups for access (microsoft.public.access) and
MSDE (microsoft.public.sqlserver.msde), there are also other language
specific groups which you can search/browse at
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx
John
"Newbie" wrote:
> Hello,
> I am new for MSDE.
> Our ISA2004 server has report in *.mdf and *.ldf format.
> We don't have SQL server. I installed MSDE on my workstation.
> Is there any step-by-step instruction to show me, how to create Access
> Project to view the ISA2004 report?
> Thanks
>|||Thanks
"John Bell" wrote:
> Hi
> Your mdf and ldf files are (probably) not a report but data and log files
> for a SQL Server database. You can attach these to your MSDE instance with
> the system stored procedure sp_attach_db see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp.
> To call this stored procedure you will need to use the osql utility at a
> command prompt see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp.
> All this information is downloadable in Books Online which will allow you to
> read and search for information at
> http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
> This may help you get started with using Access as the from end for MSDE
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_msdeintro.asp.
> There are also dedicated newsgroups for access (microsoft.public.access) and
> MSDE (microsoft.public.sqlserver.msde), there are also other language
> specific groups which you can search/browse at
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx
> John
> "Newbie" wrote:
> > Hello,
> >
> > I am new for MSDE.
> >
> > Our ISA2004 server has report in *.mdf and *.ldf format.
> > We don't have SQL server. I installed MSDE on my workstation.
> >
> > Is there any step-by-step instruction to show me, how to create Access
> > Project to view the ISA2004 report?
> >
> > Thanks
> >
> >|||Hello John,
May I ask one more question?
1. I installed MSDE 2000 on ISA 2004 server.
2. I use Access 2003 to create a progect named ISA2004.adp connect to
ISA2004SQL. The connection test is OK. (I have 2 tables, FireWall and
WebProxy)
3. I use ODBC to create a DSN named ISA2004, ISA2004 use ISA2004SQL w/SQL
server driver. ('with Windows NT authentication using the network login ID'
checked)
4. I connect ISA 2004 server to this ODBC, then I got the error message.
The ISA Server Web filter was unable to open ODBC Data Source ISA2004,
Table: WebProxy, under User Name []. The ODBC Error description is:
[State=IM002][Error=0][Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified .
or with administrator login
The Microsoft Firewall was unable to open ODBC Data Source Isa2004, Table:
FireWall, under User Name [Server\Administrator]. The ODBC Error description
is: [State=IM002][Error=0][Microsoft][ODBC Driver Manager] Data source name
not found and no default driver specified .
Thanks
"John Bell" wrote:
> Hi
> Your mdf and ldf files are (probably) not a report but data and log files
> for a SQL Server database. You can attach these to your MSDE instance with
> the system stored procedure sp_attach_db see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp.
> To call this stored procedure you will need to use the osql utility at a
> command prompt see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp.
> All this information is downloadable in Books Online which will allow you to
> read and search for information at
> http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
> This may help you get started with using Access as the from end for MSDE
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_msdeintro.asp.
> There are also dedicated newsgroups for access (microsoft.public.access) and
> MSDE (microsoft.public.sqlserver.msde), there are also other language
> specific groups which you can search/browse at
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx
> John
> "Newbie" wrote:
> > Hello,
> >
> > I am new for MSDE.
> >
> > Our ISA2004 server has report in *.mdf and *.ldf format.
> > We don't have SQL server. I installed MSDE on my workstation.
> >
> > Is there any step-by-step instruction to show me, how to create Access
> > Project to view the ISA2004 report?
> >
> > Thanks
> >
> >|||Hi
From a command prompt can you use osql to connect to the instance and then
do select name from masters..sysdatabases
GO
If the name of your database does not appear then you will need to use
sp_attach_db as described in the previous links.
John
"Newbie" wrote:
> Hello John,
> May I ask one more question?
> 1. I installed MSDE 2000 on ISA 2004 server.
> 2. I use Access 2003 to create a progect named ISA2004.adp connect to
> ISA2004SQL. The connection test is OK. (I have 2 tables, FireWall and
> WebProxy)
> 3. I use ODBC to create a DSN named ISA2004, ISA2004 use ISA2004SQL w/SQL
> server driver. ('with Windows NT authentication using the network login ID'
> checked)
> 4. I connect ISA 2004 server to this ODBC, then I got the error message.
> The ISA Server Web filter was unable to open ODBC Data Source ISA2004,
> Table: WebProxy, under User Name []. The ODBC Error description is:
> [State=IM002][Error=0][Microsoft][ODBC Driver Manager] Data source name not
> found and no default driver specified .
> or with administrator login
> The Microsoft Firewall was unable to open ODBC Data Source Isa2004, Table:
> FireWall, under User Name [Server\Administrator]. The ODBC Error description
> is: [State=IM002][Error=0][Microsoft][ODBC Driver Manager] Data source name
> not found and no default driver specified .
> Thanks
>
> "John Bell" wrote:
> > Hi
> >
> > Your mdf and ldf files are (probably) not a report but data and log files
> > for a SQL Server database. You can attach these to your MSDE instance with
> > the system stored procedure sp_attach_db see
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp.
> > To call this stored procedure you will need to use the osql utility at a
> > command prompt see
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp.
> > All this information is downloadable in Books Online which will allow you to
> > read and search for information at
> > http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
> >
> > This may help you get started with using Access as the from end for MSDE
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_msdeintro.asp.
> > There are also dedicated newsgroups for access (microsoft.public.access) and
> > MSDE (microsoft.public.sqlserver.msde), there are also other language
> > specific groups which you can search/browse at
> > http://www.microsoft.com/communities/newsgroups/en-us/default.aspx
> >
> > John
> >
> > "Newbie" wrote:
> >
> > > Hello,
> > >
> > > I am new for MSDE.
> > >
> > > Our ISA2004 server has report in *.mdf and *.ldf format.
> > > We don't have SQL server. I installed MSDE on my workstation.
> > >
> > > Is there any step-by-step instruction to show me, how to create Access
> > > Project to view the ISA2004 report?
> > >
> > > Thanks
> > >
> > >

Friday, March 30, 2012

Reading .LDF

Wednesday, March 28, 2012

Read the transaction log (ldf) file. My first post ever :)

Monday, March 26, 2012

Read the database transaction log file ie LDF file

Read Only MDF LDF files

Friday, March 23, 2012

READ LDF FILES

Wednesday, March 21, 2012

read *.mdf files

read *.mdf files

Raster Catalog

Blog Archive

About Me