Search results matching tag 'Agent'

Power Pivot SQL Agent Job Monitor

manowar — Mon, 07 Jan 2013 21:23:29 GMT

In the last days I needed a quick and efficient way to monitor the performance of a job and of each step over time. There’s a lot of interesting data in MSDB database and the various sysjob tables, but the stored data is not really easy to use outside the Management Studio. Date and Time are not stored as date/time data type, there is no easy way to correlate the performance of a step with the related job execution (for example is not that easy to return all the steps of a job executed, say, yesterday at 10:00).

So I decided to write some views to make the querying easier and then the next move was to create a Tabular Model with Power Pivot on Excel to make also analysis easier.

Since this is a somehow recurring task that I find myself doing during my job – not often, but when I have to do it now I have something much more powerful in my hands – I decided to put it also con CodePlex so that that little work can be shared with the community.

You can find the scripts with the objects (3 UDFs and 4 Views) that must be created inside MSDB and the Power Pivot Excel 2010 model here:

http://jobmonitor.codeplex.com/

Enjoy!

Start a SQL Server Agent Job using the SQL Server PowerShell Provider

BuckWoody — Wed, 10 Feb 2010 13:13:45 GMT

Whew! That’s a mouthfull. Anyway, I thought I would share part of a script I wrote today to help automate (more) from PowerShell. This assumes a few things: that you’re doing this from the SQL Server 2008 PowerShell provider (not just good old regular PowerShell) and that you change the HAL9000 and SQL2K8 parts with your server name and your Instance name. Oh, and the Jobs I start are called “Test” and then something else. That’s the Test* part. You can use a specific name and drop the * or use your own naming convention.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

# Start a Job named Test in the SQL Server 2008 PowrShell Provider

DIR SQLSERVER:\SQL\HAL9000\SQL2K8\JobServer\Jobs\Test* | % {$_.Start()}

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

Connect Digest : 2009-09-04

AaronBertrand — Fri, 04 Sep 2009 11:30:00 GMT

I am heading to my home town for a week; we rented a cottage on Lake Nipissing, and are going to relax. No, really : no howling beagles, no computers, and no schedule. Sounds like a week of bliss to me.

So, as you might imagine, there won't be a digest next week. I think I have some interesting items to point out to you this week to hold you over.

Users won't stop shrinking, so take the option away

Even with boatloads of advice to the country, a LOT of people are still shrinking their databases as a whole (as opposed to per file), and even doing so as a regularly scheduled maintenance plan. While Microsoft has advised us that settings like AutoShrink will be removed in some future version, personally I just don't think that's enough. People don't need to be tempted in so many ways to shoot themselves in the foot and destroy their database performance, never mind to set the system up so it shoots their feet off automatically.

#486614 : Deprecate all "shrink database" options

Make it harder for users to raise 600 GB log files

Steve Jones formalized a thought I alluded to in an earlier blog post: make the default recovery model simple. The reason this will be better for users is that if you choose full by mistake, you don't realize until your transaction log has taken up your whole disk -- and then you're back into the need to shrink files again. If you choose simple by mistake, you will realize your error the very first time you attempt to take a log backup, and at that point corrective action is simple (no pun intended) and immediate. Do I think that simple recovery model should be how most databases end up? Absolutely not. But until they make other options mandatory (e.g. setting up a backup / log backup plan), I think default = full is more dangerous than default = simple. And as I mentioned, correcting for a default of simple when you really wanted full is much more obvious and immediate.

#472969 : Change Default Recovery Model

Make the SQL Server Agent / Jobs node more manageable

I probably have 50+ Connect items on how to make the SQL Server Agent components in SSMS easier to manage. But after setting up a Utility Control Point (see my most recent "under the hood" post), it became obvious how cluttered and ugly the Jobs node in Object Explorer can become. And when you look at the screen shot, kind in mind: this is before I've created a single user-defined job. Blecch. I'd like to see, at the very least, folders for each category with at least one job (this way we could control where our jobs appear simply by changing their category), with uncategorized jobs appearing at the root. But user-defined hierarchies (even if only one level deep) would be much better.

#286925 : SSMS : Allow folder grouping for SQL Agent jobs

Prevent slipstream from failing silently

Earlier this week, I created a slipstream install: SQL Server 2008 with SP1 (2531) and SP1 CU3 (2723). Little did I know that, when I grabbed the SP1 binaries off of our file server, I grabbed the pre-release CTP of SP1 (build 2520 vs. 2531). Oops.

The slipstream process itself is handy but pretty manual, and a great deal of thanks is owed to Peter Saddow of Microsoft for providing so many helpful blog posts with information. But, as a manual process, it is certainly error-prone. In my case, running a slipstreamed install yielded these results:

Build 1600 (RTM) was installed. Neither 2520 nor 2723 made it to the server.
The log files did not mention any problem, AFAICT.

Both of these outcomes are undesirable, in my opinion. If setup was not able to patch to the highest CU build I had attempted, the process should fail completely. Failing that, it should patch to the highest build possible (in this case 2520), and then make it *VERY* obvious (I'm talking sirens, flashing lights and dancing girls here) that it did not complete the full process.

Only when I attempted to run the installers from the PCU and CU folders did I realize what had happened, since the log files were of no help. I installed what I thought was SP1, and then when I tried to install CU3, it told me the minimum build # was 2531, and that I only had 2520. Face palm time.

This sequence of events should not be allowed. Since the CTP of SP1 was public, I doubt I am the first person who tried this, and I doubt I will be the last. But the more dangerous problem is that a slipstream install can *look* like it was successful, but fail silently. I noticed immediately that the server was still at 1600. But what if I didn't pay as much attention to these details? If I was relying on protecting my server from an exploit, say via some security patch in an SP or CU, I'm going to run this setup and walk away from the server, thinking I am protected when I am not.

#487051 : Slipstream : log says yes, result says no

Some polishing in R2 still required

I know it's pre-release material, but unhandled exceptions are never cool, and bugs I've submitted in the past were not always fixed in time for the initial release. So I'd like to help avoid these from being in the product this time around. When using the Utility Explorer for the first time, I came across a pretty serious and easily reproducible unhandled exception, as well as an instance of unintuitive behavior.

#487722 : R2 : Unhandled exception on Utility Explorer's Storage Utilization tab

#487719 : R2 : Utility Explorer Content links do not work

The unhandled exception one is really troubling. There are several other unresolved Connect items where SSMS raises unhandled exceptions, and it really makes me wonder why TRY/CATCH use isn't more widespread. I guess SSMS gets the same kind of error handling priority as SQL Server itself. =) Sorry, I couldn't resist.