Learn how to a build a cloud-first strategyRegister Now


I am looking for a way to query database logs for Pervasive SQL and MSDE.

Posted on 2004-11-05
Medium Priority
Last Modified: 2008-02-01
I want to be able to review who has accessed these databases and maybe get reports.  Does anyone know of software that can read the logs?  Are there even logs to be read?  Does Microsoft provide any built-in reporting?
Question by:rhouston0872

Accepted Solution

SashP earned 672 total points
ID: 12511663
Hi rhouston0872

There is no built-in reporting for Microsoft SQL Server.  The transaction log records all (well most) data changes in the database including who and when.  Unless C2 auditing is turned on SQL Server does not track who logs on and unless they make a change to the data then there will be no trace that they were in the database.

Software for reading SQL Logs.

I would recomend Lumigent Log Explorer for SQL Server http://www.lumigent.com/products/le_sql.html

You may consider ApexSQL Log http://www.apexsql.com/apex_sql_log.htm but I prefer the Lumigent product.

As for Pervasice SQL.

I do not have any experience so I am unable to assist you. You may be better served posting the pervasive sql question to the  databases topic area.

Cheers Sash

Assisted Solution

wael_tahon earned 664 total points
ID: 12516723
You can use SQL Profiler

Start a new trace and select the Security Audit Events

Best regards
Wael Tahon

Assisted Solution

roshkm earned 664 total points
ID: 12521270
There is SP_Who2 procedure which you can call. It will give you the current login used, machine name, CPU usage etc.

May be you have to create a job which will call this function and insert into a table, from where you can do auditing.

something like :

CREATE table MyTable
(SPID int, Status varchar(1000), Login varchar(1000), HostName Varchar(1000), BlkBy Varchar(1000),
DBName varchar(1000), Command varchar(1000), CPUTime int, DiskIO int, LastBatch varchar(1000),
ProgramName varchar(1000), SPID_1 int )

--Configure this as the job:
insert into MyTable exec sp_who2


Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Expert Comment

ID: 12521519
Using Profiler is an unacceptable overhead for auditing a database, it should be reserved for identifying and analysing problems within a SQL installation and if possible be used on development servers and only used in production when all other avenues have been exausted.

Using sp_who or sp_who2 only tells you who is in the database at a specific point in time not who has logged in over a period of time, so either you will miss connections (especially well designed transaction processing apps which only maintain connections for milliseconds) or you will generate massive amounts of data and still not guarantee who has accessed your database you should not use this method especially if the login tracking is for compliance reasons.

Although both of these suggestions may be able to be used to acheive a result similar to the request they are not suitable for an enterprise or production database.

Cheers Sash

Expert Comment

ID: 12522944
SP_Who2 will get u who is connected at a specific time, true. But if u increase the freq of the job, u can capture the minutest details. It purely depends on the necessity of the author. There are tools available as well as built in apps to capture this, but will cost you performance. The job i suggested can be configured in either way based on the need. The temp table can be cleared after a specific time, hence clearing of junk data. All these are upto the DBA to decide.

It is difficult to take the details of who connected from the transaction log, since the same will be updated and cleared once the transaction is complete.



Expert Comment

ID: 12523048
Your statement about the transaction log being cleared once a transaction is complete is not actually correct.  The recovery model chosen for a database determines how much of the transaction log inis retained.  The only time that the log is not retained is when simple recovery model is selected. Normally the transaction log is cleared when a backup of the transaction log is performed or when a manual truncate of the log is executed.

I would not suggest using the log for determining who is in your database, as the log does not record data reads only changes to the database.

However creating an extra process that hits the database continually does not guarantee capturing who is in the database.  As you increase the frequency of the sp_who(2) execution the increase the likely hood that you will catch the user, but efficient systems do not necessarily maintain connections for a long period of time.

I do not believe that this is an appropriate approach.  C2 Auditing is as far as I am aware the only method that is suitable for compliance reasons that is suitable for this task.


Expert Comment

ID: 12523169
When i said 'Transaction complete', i meant after the backup or defrag operation etc in case of Full model and in case of a successful query execution in case of Simple model.

I hope that our owner is not chasing a virus..  :)


Author Comment

ID: 12525417
Thanks for all the responses.  I'll have to wade through this and try and figure out all your answers, much less the solution.  By the way, I'm not chasing a virus.
I'll come back later today with any followup questions and hopefully find a solution.
I appreciate all the help.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question