Database compatibility level(s) - log/history

Question

Database compatibility level(s) - log/history

Danb7183

SSCrazy

Points: 2098
More actions
June 27, 2011 at 5:10 am

#253397

Hi all,
Is it possible to find out the history of compatibility levels for a particular SQL Server database running on a SQL 2005 Developer Edition database engine? I.e. When database x was changed to 90, when it was changed back to 80 etc?
I'm testing the upgrade of compatibility levels for a copy of our production DB from 80 --> 90 and evaluating how this affects our applications.
Thanks in advance!
Dan

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i just checked, and found that you can find the changes in the Application Log of the server.

It doesn't count as a DDL statement, so the default trace doesn't have it, but if you have a DML trace running, it should be found with a RPC completed event, i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Danb7183 SSCrazy Points: 2098 More actions · Answer 2

Thanks for the response Lowell. I've got a DDL trigger that exists on the database in question, but as you've stated I think it's a DML statement and not DDL.

I'll take a look through the application log.

Thanks!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Hey Lowell, got any code that reads the windows log that you can send over? I've found the link on SSC but the code is broken.

Lowell SSC Guru Points: 323518 More actions · Answer 4

well i know EXEC sys.xp_readerrorlog 0 works in 2008, but there's other parameters i'd have to look up;

i would swear you could get teh event/error code int he results, so you could then sort and order, but i'll have to search my scripts;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

No that works only for sql and agent logs. I want the windows application et al. logs...

and I'm on 2k5!

Lowell SSC Guru Points: 323518 More actions · Answer 6

ok Remi; looks like there is a command line utility called logparser which is designed to read parse/ any kind of log, whether it's iis or windows event logs.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=24659[/B]

so using that and the xp_cmdShell bazooka, you could get the logs into a table...i think i'd jsut write a little thing in .NET to put the data into a table instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Looks awesome.

I'll definitely play with this even if I have to use the bazooka! :hehe:

Lowell SSC Guru Points: 323518 More actions · Answer 8

oh lookie: you can do it in powershell as well:

i searched powershell read application log...looks a little easier to do:

# query an event (Filter Equal)

# you could change the filter to an eventID or Source too

$EventLog = new-object System.Diagnostics.EventLog('Application')

$EventLog.MachineName = "."

$filter=[string]"WSH"

$events = $EventLog.entries

$events |where-object {$_.source -eq $filter}|format-table -auto

# query an event (filter not equal)

# you could change the filter to an eventID or Source too

#

# Note security log is read only!

#

$EventLog = new-object System.Diagnostics.EventLog('security')

$EventLog.MachineName = "."

$filter=[string]"IKE"

$events = $EventLog.entries

$events |where-object {$_.source -ne $filter}|format-table -auto

# write an event

$EventLog = new-object System.Diagnostics.EventLog('Application')

$EventLog.MachineName = "."

$EventLog.Source = "WorkstationEngineering"

$EventLog.WriteEntry("I am from workstation engineering")

Chaging the machineName= to a remote machine will write and query to/

from that machine

$EventLog.MachineName = "."

$Just some other things

$filteredEvents = $events |where-object {$mess=$_.Message;

$mess.substring(0,3) -ne $filter}|format-table -auto

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I never used ps before. So I'll let you finish the script and post it over so that I can just hit GO!

Lowell SSC Guru Points: 323518 More actions · Answer 10

yeah, when i look at powershell at first, i have no idea what it does or what it can do.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Lowell (6/27/2011)
yeah, when i look at powershell at first, i have no idea what it does or what it can do.

So what the heck does that tool in the image does??

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Lowell (6/27/2011)
yeah, when i look at powershell at first, i have no idea what it does or what it can do.

Seriously, WTF is that? 😛

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323518 More actions · Answer 13

opc.three (6/27/2011)
Seriously, WTF is that? 😛

I have no idea, and neither did the original poster;that's what i thought was so funny; i know it's a tool, but I cannot even visualize what it might be for...kinda like powershell.

if you are interested, the link to the original blog wondering what the heck it is is below, but they have no answers there either. they do have picture of the other side of it though.

http://parkslibrarypreservation.wordpress.com/2010/03/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

They missed the boat.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato