Run profile on SQL 2005 database

Question

Post reply

Run profile on SQL 2005 database

Luckbox72

SSC Enthusiast

Points: 129
More actions
January 8, 2015 at 12:51 pm

#302201

We are in the process up upgrading SQL 2005 to SQL 2014, but for the time being we still have most of the production using 2005. I was trying to run the profile from sql 2014 on the SQL 2005 database and it will not run. Is there some way to do this, or is there a better option. I tried Extended Events but it does not work on SQL 2005 database. Right now I just need to see what procedures are being run. Trying to debug an issue

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Extended Events will only work in 2008 or greater.

I haven't tried connecting to 2005 from 2014 Profiler, so I'm not sure what the issue might be.

Instead of using the Profiler GUI, use trace events in a script. You can use the GUI to generate the script. Output the results to a file. You can then open the file in the GUI. That's the best way to use Profiler anyway.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Luckbox72 SSC Enthusiast Points: 129 More actions · Answer 2

When ever I open the GUI Profiler in SQL 2014 to a 2005 server I get an error that tells me it only works with sql 2005 or newer. The odd thing is the way the error is written I would think it should work since the server is running 2005. I can go to the server and open the 2005 profiler and creat a script but so far have been unsuccessful tuning the script in 2014. Not sure what the issue is

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Trace scripts should work. If you have an error you can post that would be helpful.

But really, the Profiler GUI shouldn't be connected to your servers directly. There are several [/url]good reasons [/url]for saying that. But yeah, I'm not sure why you'd see that. Is everything up to date on service packs and cumulative updates?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 4

While Grant is right, you shouldn't use Profiler GUI directly against your servers, I think it depends on what SP 2005 is on. I think it has to be at least to an upgrade version... maybe SP4?

I'm not positive on this mind you. I ran into this last yr and I it wasn't part of the project so it ended before I was able to reach a conclusion. But what I suspect is that it needs to be up to at least an upgrade level before Profiler can see it. Of course I could be completely wrong and it could just never work.

If you want to know how to do it in code, here's a video and article from Jen.

http://www.midnightdba.com/Jen/2009/09/video-server-side-trace/[/url]

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

KenpoDBA (1/9/2015)
While Grant is right, you shouldn't use Profiler GUI directly against your servers, I think it depends on what SP 2005 is on. I think it has to be at least to an upgrade version... maybe SP4?
I'm not positive on this mind you. I ran into this last yr and I it wasn't part of the project so it ended before I was able to reach a conclusion. But what I suspect is that it needs to be up to at least an upgrade level before Profiler can see it. Of course I could be completely wrong and it could just never work.
If you want to know how to do it in code, here's a video and article from Jen.
http://www.midnightdba.com/Jen/2009/09/video-server-side-trace/[/url]

I think you're right. Something along these lines is niggling at the back of my brain. But I haven't connected to a 2005 server in about 4 years, so I haven't hit the issue at all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Luckbox72 SSC Enthusiast Points: 129 More actions · Answer 6

Thanks for the help. I would check and upgrade the service pack for that instance but since we are migrating to sql 2014 I wont really worry about that since the issue will go away with the migration.