SQLServerPedia

Began receiving alerts on a job failing.  The job was "suspended".  The error in the history event log is:

Unable to start execution of step 2 (reason: The ActiveScripting subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed.

A quick review of the server shows MSDE was installed on the machine, somehow this causes an error in the registry.

Review the below registry entry and ensure there is an "s" at the end.  According to Microsoft this happens sometimes with MSDE installations and upgrades.  You can see the picture of mine, that it was missing

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup]

"SQLPath"="C:\Program Files\Microsoft SQL Server\80\Tools"

I added the "s" to the end.  Started and stop the instance (i did both services because I could, but may only need to have the agent service restarted.)

Everything is working fine now.

This was on a sql server 2000 installation.

If you’re well-versed in using PerfMon, I’d like to hear about the counters that you use regularly and what constitutes a good or bad value. 

However, it’s a rare individual who knows their way around PerfMon and its multitude of performance objects and counters.  Which ones should you track?  And even if you know the right ones to track, what values indicate good or bad performance? 

If you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com.  They’ll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server.  It’s a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted.  These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

If you’re well-versed in using PerfMon, I’d like to hear about the counters that you use regularly and what constitutes a good or bad value. 

However, it’s a rare individual who knows their way around PerfMon and its multitude of performance objects and counters.  Which ones should you track?  And even if you know the right ones to track, what values indicate good or bad performance? 

If you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com.  They’ll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server.  It’s a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted.  These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.

Enjoy!

-Kevin

P.S. I cover a variety of PerfMon counters in these screencasts.  Many of them are discussed in an excellent document and in blog posts put together by Microsoft PSS.  Read their blog at http://blogs.msdn.com/psssql/default.aspx.

If you’ve read SQL Server Magazine, then you know that I write a monthly column about free SQL Server tools called Tool Time. I recently wrote about a set of automation scripts from Microsoft’s internal MIS group that automates all sorts of regular tasks they perform on their SQL Server’s.

Today, I’d like to share some more scripts brought to my attention by Ola Hallengren. Ola wrote some great scripts for backup, integrity check and index optimization that add to what Microsoft had written. I think that it in some areas has some advantages over Microsoft’s solution.

Ola’s index optimization stored procedure rebuilds indexes online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level and lob existence. This solution was made about 6 months ago and it has been running in a large production environment since then. You can see them at:

• Ola’s homepage – http://ola.hallengren.com
• Release notes – http://ola.hallengren.com/Versions.html
• Documentation – http://ola.hallengren.com/Documentation.html
• Scripts – http://ola.hallengren.com/scripts/MaintenanceSolution.sql

Ola has put a lot of time in this and would really appreciate your feedback. You can see more of his work at http://ola.hallengren.com.

I’d love to hear about any other database automation scripts you may have written. Be sure to let me know so I can share them with the community.

Enjoy!

-Kevin

There are a couple new features that I would like to quickly point out. These are basically just tools that I use daily so I integrated them into the site. I hope you find them useful.

• I have everything running faster. Not a new feature per say but it definitely helps usability.
• I have extended the blog aggregator to include a lot more feeds. Let me know if you would like your feed to be added!
• I have added a digg SQL feed. I am using digg more often and find it useful so start digging(preferably starting with this post) so I do not have to resort to web 1.0 google searches.
• The SQL Server 2008 facebook group is linked. Join up and add me as a "web pal" Note: I am happily married and discourage webbernet stalking so please maintain so self control.
• I have also added an event page on where you can catch me speaking or download session slides and code from previous presentations.
• And if you have not had a chance to check out the MSDN and TechNet feeds, you are missing out.

ALT HEAD: HOWTO: Inserting dummy data

Recently I had to evaluate a migration from MSDE to SQL Express.  Always being proponent of staying on a current and supported DBMS, you might be surprised that my recommendation was to stay with MSDE.

Given that SQL Server Express 2008 is being ready for release, it does not seem prudent to migrate to SQL Server Express 2005.  It may be better to use a leap frog strategy and skip SQL Express 2005, moving right to sql express 2008.

There is no doubt that SQL Express is better than MSDE, but for us, we really don't need any of the features of SQL Express, MSDE is adequate.  But we do need to be on a supported DBMS that runs on Vista, and it is what is driving the upgrade.

Before the original post, I had submitted a connect item for something similar.

It turns out that filtered indexes may not be used when auto-parameterization(or for stored proc) occurs without a recompile hint. This is sort of understandable but I can think of workarounds and I suspect additional logic will be added in the future as mentioned below. Here is the feedback from a connect item I submitted.

"

Thanks for your feedback. The query in question, select lastname from Person.Contact where ContactID <=(100-20) is auto-parameterized by SQL Server into the following form, using the standard auto-parameterization rules: select lastname from Person.Contact where ContactID <=(@1-@2) These rules are designed to minimize compile time cost for simple queries like this, possibly at the expense of additional optimizations such as using a filtered index. Admittedly, it is a difficult tradeoff. In a future release, we'll consider extending the design to make a better decision in a cost-based way. For now, this behavior is by design.

"

alt head: You can't have your cake and eat it too.

We regret to inform you of a passing in the SQL community.   Read more here.

Rick Heiges has a post on my favorite new feature in SQL Server 2008, filtered indexes. 

So does Decipherinfosys along with a good description on the difference of indexes and statistics if you need some background.

SQLBlogcasts has gotten an upgrade and Tony posts some great stats. Congrats!

Not SQL per say but the Hyper-V release candidate has been released. Speaking of Hyper-V, Sriram posts his slide decks on virtualizing SQL. Part 1 and Part 2.

This is a great starting point for SQL Server 2008 as is this. These come by way of the MSDN\Technet update blog.

The SQL Server 2005 sp3 debate continues.

The SQL ISV team posts a performance improving cursor rewrite sample. However, it is not ANSI compliant :) which is odd since most ISV tsql code needs to be portable.

Paul Nielson will be releasing a DVD.

While we are at it, check out the new SQL Server social network.

alt head: Got ADD?

WIMP. Not Weakly Interacting Massive Particles of dark matter fame.  Windows, IIS7, MSSQL, PHP; Microsoft is going after the LAMP markets. I suspect they have a pretty good chance of converting quite a few customers. This means DBA's should be prepared. Hopefully, it will be better to support than JAVA and SQL Server :)

There are substantial improvements in IIS7(IIS6 with an addon) for PHP support through the Fast CGI open protocol and other enhancements through the Zend partnership.

Microsoft has also released the PHP Driver for SQL Server CTP. More info can be found on the team blog.

I don't have much more info on the PHP driver but I am going to keep up with it. There is a time and place for everything. If MS delivers as good or better than LAMP, why reinvent the wheel in .net when an enterprise ready production proven open source app is the right answer. For example, Word Press. Now there are a million variables that go into that decision but having options in your toolbox can't be a bad thing.

ALT HEAD: Wimpy, Wimpy Wimpy.... Hefty, Hefty, Hefty....

In SQL2005, you can create SQL Agent jobs. They can be scheduled, have multiple steps, alert, notify, etc. Pretty great setup. There are some downfalls though.

Like in order to call a job from a job, you need to execute the second job with T-SQL. Thing is, it doesn’t run synchronously. It runs asynchronously, which really stinks if you want to wait to see if the second job completes successfully or what not.

Another thing, if you call an XMLA script from a SQL Agent job, if the XMLA query or command fails, the SQL Agent job still reports success - that’s no good! What can you do? Use ascmd.exe!

ascmd.exe is a utility that you actually have to download the SQL Samples from CodePlex (http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000) and then build the ascmd solution, to get ascmd.exe A few notes. The samples are VS2005, and I don’t have that installed ,so I had to open with VS2008, then it is digitally signed, and when building couldn’t find the .snk file to sign it, so I turned that off as well, after I had it built, I did some testing locally and made sure it would work as I wanted it to.

You can test by just calling it from the cmd line, for example:

ascmd.exe -S SSASServerName -d SSASDatabaseName -i MyXMLA.xmla

you can use -Q and try to pass in XMLA, but then you have to handle all the special characters and what not, which is a pain. Now, if you just put this on your C drive, (or wherever), then create a SQL Agent Job to call this command, it will fail the job if the ascmd.exe reports back failure. Exactly what we want!!

You will notice in the SQL Agent job setup that you can specify the value for a failed command  - 0 is the default and that is what we want.

Now, get some XMLA that you know works, set it up in the xmla file and test it, the job should succeed. Now just change something in the xmla (like CubeId) to some fake value and test it, the job should fail, and you can have it alert your DBA’s or whoever.

Pretty sweet, but I wish SQL Agent would handle failed XMLA like a failed query and report failure, I am not sure if in SQL 2008 it does that or not, but it would make life a lot easier. Otherwise you could be scratching your head trying to find where stuff is failing, looking in logs, etc, but not seeing any failures. The only way you would be able to tell is to run the XMLA manually, ugh :)