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.

A while back, Quest Software started a series of webcasts called “The Pain of the Week”.  These webcasts are about a specific area of difficulty for SQL Server DBAs and developers, and how specific Quest tools can help.

In the next webcast, I and my colleague from Quest, Bryan Oliver, will present a methodology and show you how to code faster for faster code using Toad for SQL Server.

As a SQL Server DBA, you’re the go-to for writing SQL and T-SQL and to do this effectively requires a special set of skills. Some of the challenges with this is that not only do you have to use multiple native tools, you have to search all over for scripts and stored proc templates, and you have to be an expert in SQL tuning – just to name a few.

Toad for SQL Server is uniquely designed to make work easier and more efficient for anyone coding Transact-SQL scripts and routines. From its top-rated debugger and built-in Intellisense, to its powerful tools for comparing and synchronizing multiple instances of SQL Server, Toad delivers improved performance that will save database developers hundreds of hours per year. 

All of the details for the event, including registration, are here:

     http://info.quest.com/kkblog-pow-codefaster-toadexpert

If you’re interested in coding faster in SQL and Transact-SQL and are open to trying new tools, then please join me for this presentation on April 3rd, Thursday, at 10:00 am central time.

Thanks,

-Kevin

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....