We've got a group of views on the CommServ database with the names dbo.CommCell% that allow us to query various activities and report on them via tools other than the CommCell reporting module. 

One of the things I am having difficulty with is determining how to query for specific Event information.  I am trying to query for situations where a database has been converted from FULL to SIMPLE recovery model and the Event Viewer is displaying the following message: "The [Transaction Log] Backup of [%d] is being converted to a Differential Database Backup because [SIMPLE] Recovery Model is enabled."

I need to find these events using a query against the CommServ database, can that be accomplished via the views available in the DB?

Hi DB Dave,

Check out htis link

Here is the error code associated with the statement you are trying to draw on.

http://documentation.commvault.com/commvault/release_8_0_0/books_online_1/english_us/error_codes/error_codes1.htm

As you can see the error code is generated from several components in the DB drawing on the specifics of the job and components used in the backup.

Working from this info.  Please elaborate on what you need.  Also look at the CommCell Explorer Views in BOL link below and the tool if you are licensed for it for data entry points.

http://documentation.commvault.com/commvault/release_8_0_0/books_online_1/english_us/prod_info/commcell_explorer.htm

I'm trying to use the CommCell Explorer views to query Events looking specifically for the error code you mention.  I can't determine how to accomplish this with the CommCell Explorer views.

Hi DB Dave,

THe error code exists in two pieces and is put together at time of error generation.

I am not a SQL DB guru and will have to defer to others to help you get to where you want to go.

I think 'CNeventview' provides this information, assuming you know the messageID of the event.  If you have one recently you can easily track it down via jobID which the view provides.

That view is not documented in the CommCell Explorer Books Online so I don't fully understand all the data in the view.  The MessageID you mention is not what is displayed in the CommCell GUI, I only have the Event Code available, can you describe how to map the Event code displayed in the GUI to the MessageID in the view?

Also, there are very few records in the CNEventView, what is the maximum length of time I can expect to find an event in this view from when the event is generated to when it is removed from the view?

Is there anyplace I can view the events generated over hte last 24 hours, similar to the default Events report provided in the CommCell GUI?

The error you specified  has a messageID of 503316742. 

Since the view is locked theres not much more I can supply. 

DBA Dave:

Is there anyplace I can view the events generated over hte last 24 hours, similar to the default Events report provided in the CommCell GUI?

Excuse my shocking t-sql but I just whipped this up, let me know if it does what you're after - you would probably need to throw in a join to translate the messageId to text, will give this a go when I get a chance...

USE commserv;

SELECT * FROM evMsg
WHERE lastOccurrence > DATEDIFF(second, {d '1970-01-01'}, DATEADD("d", -1, GETDATE())) 
ORDER BY id DESC 

This seemed to work for me.

-- Show events over last 24 hours.  Assumes en-us.

SELECT e.id, e.timeSource, e.severity, e.timeServer, e.type,  e.jobId_h, e.jobId_l, e.client, e.subsystem,  e.lastOccurrence, emc.Message

FROM evMsg e
LEFT JOIN evMsgCache emc ON emc.EvMsgId = e.id

WHERE lastOccurrence > DATEDIFF(second, {d '1970-01-01'}, DATEADD("d", -1, GETDATE()))
AND emc.LocaleId = 0
ORDER BY lastOccurrence DESC;

That query is perfect!

Hi

Sorry I am no DBA but I am the Simpana admin on my site.  My question is, can I run the SQL above against my own database without modification?

Because I have exactly the same requirement as the original poster here, probably for the same reason - my reason being, I suspect there are many more Simple Recovery mode clients' backup jobs being converted than I know about, which are taking up more maglib space than they are entitled to!

Thanks

Rob

The query should work without modification for you as well.

Thanks Dave!  I will give it a go, this appears to be extremely useful to me.

You mentioned that you have 'some' queries that you use - got any other good ones..?

Hm..thanks for the code but when I ran it I got

Msg 208, Level 16, State 1, Line 1

Invalid object name 'evMsg'.

Any ideas? (without spending too much time on it, not looking for free support)

Oops disregard that, user error, I had neglected to selct the correct database