SQL Question

[ShowMe]

Is there a SQL utility that can show me the actiivty against a database over a certain period of time?

I thought something like this would be fairly common but I haven't found anything like it. I am, admittedly, very new at the SQL game.

 

[Arkan_Wolfshade]

What kind of activity are you trying to monitor? I/O? CPU? Logical reads? Disk sorts? etc

 

[CriticalThanking]

Arkan hit the key question - what type of activity? My initial guess was you were talking about C2 security: who created/modified/read the data. Yet another possibility is about who is creating/altering or dropping database objects.

Regardless of which you are talking about, it will likely be product-specific. The system catalogs are different for each.

CT

 

[a_unique_person]

You have to name the DB. Some have good facilities built in, some you have to go to third party vendors.

 

[ShowMe]

It's an accounting package called Great Plains. There are transactions that are being "lost" ; it doesn't always happen, but occasionally something that was added simply...vanishes.

CriticalThanking is correct, I'm looking for something that can monitor who is doing what to the database, mostly likely drops or deletes. Nobody thinks anyone is doing anything intentionally but it would be nice to see what is getting poked when this happens.

 

[Wudang]

From what I found it looks like Great Plains can use different databases under the covers. Most likely it's SQL Server in which case see
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

 

[Arkan_Wolfshade]

Simplist approach may be to add a trigger (on update/delete) to the table that the data is disappear from and have it log all the info (including session info) to a log table.

If you have a small number of connects you could trace them all and watch what is going on as well. Lots of options.

 

[a_unique_person]

I would raise a case with Great Plains support. It is 99.99% probability a programming error, not a problem with the database.

I administer an SAP system. There are no end of bugs in the code, it is just a given that in a system that complex, there will be bugs. When you find a bug, all you can do is log a case with Technical Support, to get the bug fixed. If Great Plains is anything like SAP, there will be regular fix packs sent out. These should be applied regularly. 99% of the time, if you have found a bug, someone else has found it before you, and already had it fixed.

No data that was in the database itself has ever been lost or corrupted.

 

[CriticalThanking]

No data that was in the database itself has ever been lost or corrupted.

TWEET - FLAGRANT WISHFUL THINKING! 5 yards, replay the down.

Ok - yes - real databases rarely lose and infrequently corrupt data, but it still does happen. Databases are still just software with millions of lines of code. Just like SAP. And I do agree that the culprit is most likely Great Plains or an end user who can't or won't admit to being... less than fully qualified.

CT

 

[a_unique_person]

Yes, you are correct. Talking to users with problems reminds me of House talking to his patients.

PS, always have the DB backed up, just in case, but have never had to recover anything in the more recent times.

 

[VPescado]

Simplist approach may be to add a trigger (on update/delete) to the table that the data is disappear from and have it log all the info (including session info) to a log table.

If you have a small number of connects you could trace them all and watch what is going on as well. Lots of options.

Beware of performance degradation if you try these approaches.

 

[Arkan_Wolfshade]

Beware of performance degradation if you try these approaches.

Well, yeah. They are short term diagnostic tools, not intended for long term use. Should have made that clear in my prior post.

 

[peptoabysmal]

It's an accounting package called Great Plains. There are transactions that are being "lost" ; it doesn't always happen, but occasionally something that was added simply...vanishes.

CriticalThanking is correct, I'm looking for something that can monitor who is doing what to the database, mostly likely drops or deletes. Nobody thinks anyone is doing anything intentionally but it would be nice to see what is getting poked when this happens.

Hmmm...

Not familiar with Great Plains, but most of the "one size fits all" accounting and/or time tracking packages suffer from you can't get rid of something once you've entered it.

My wild guess is that it is being entered in a wrong group or category and you just don't see it on the report you want to see it on because the query filters it out. Does this happen to everyone using it, or can you narrow it down to a possible user malfunction?

Does this software require a login? If it does, my guess is that it is already tracking who did what and when, you just have to find out how to access that info.

 

[Arkan_Wolfshade]

Easiest way to clarify (from Oracle)

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

* Support for SQL
* Support for object-oriented programming
* Better performance
* Higher productivity
* Full portability
* Tight integration with Oracle
* Tight security