Powershell SMO Problem

April 16, 2010 at 10:14 am (PowerShell, SQL Server 2008, SQLServerPedia Syndication) (, , , , , , )

We’ve been running the Enterprise Policy Management tools available from Codeplex for a few months now (Thanks to Buck Woody’s (blog | twitter) session at the PASS Summit). They’re honestly great. It’s a fantastic way to use Policy Based Management on 2000 and 2005 servers. We did hit some issues with timeouts and looking at the script, it made a call to invoke-sqlcmd, but didn’t pass the -querytimeout value. That means it default to 30 seconds and the import to database process was taking more than a minute for some of our queries. I did a little looking around and decided to just disable the timeout by passing a value of zero (0). But, I still got timeouts. Finally, after a bit of searching around, I found a closed (because it was posted in the wrong place) Connect item. It’s pretty simple to test. If you want to see a good run, do this:

Invoke-Sqlcmd “waitfor delay ’00:00:29′” -Database master -ServerInstance SomeServer -Querytimeout 0

It’ll work fine. Change it to this:

Invoke-Sqlcmd “waitfor delay ’00:00:31′” -Database master -ServerInstance SomeServer -Querytimeout 0

You’ll get a timeout. I don’t know if this is a bug or by design, but it’s a bit of a pain that you can’t simply bypass the timeout. There is a max value (a huge max value) 65535, but what happens if I run a sql command that runs longer than that?. Please go and vote on the new Connect item.

Permalink 6 Comments

A Lack of Excitement

December 23, 2009 at 9:16 am (PASS, PowerShell, SCOM, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , , , )

I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven’t been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is.

The fact of the matter is that I’ve been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I’ve blogged before about our use of Microsoft’s Operations Manager for monitoring our servers and how we’ve built custom rules and monitors to keep an eye on things. I’ve also mentioned how we use Idera’s Diagnostic Manager as a drill-down mechanism to keep on eye on SQL Server internals that OM just didn’t do as nifty a job on. What I haven’t mentioned is, that after attending the PASS Summit (and if you didn’t go, you sure missed out) and sitting through Buck Woody’s session on monitoring using Policy Based Management, “More Servers Less Control,” I’ve implemented PBM within our organization.

If you have yet to explore Policy Based Management, PBM, get on it. I know that others have blogged about their experiences with it, and there’s a reason that people are excited. Track down Buck Woody’s session over at the PASS site (it’s worth paying for it, trust me).  You too can get this stuff up and running in no time. That’s the good news. Now for the bad news, PBM only works with SQL Server 2008. But, I’ve got some good news about that too. There’s a great utility published over at CodePlex (and isn’t that a great place to browse on occasion) called the Enterprise Policy Management Framework, EPM (you can never have enough acronyms, especially with three letters). The EPM Framework allows you to run policies against 2000 and 2005 servers. Sweet!

All of this is work has been part of a concerted effort within our organization to get really and truly proactive, to prevent errors before they occur. Between the modifications I’ve made to OM and the implementation of PBM through EPM and reports against the OM data through SSRS, SQL just isn’t having as many problems these days (I love talking through acronyms sometimes just to watch peoples toes curl). I’ve been busy as all get out, but it’s on pretty mundane, non-exciting stuff. Hence fewer blog posts.

Permalink 1 Comment