couple years ago I started consolidating all of my DBA scripts into a portable DBA database that I could use for diagnostics (hence the silly name) and for maintenance scripts. To help me to continue to be inspired to update the database and to get some feedback, I plan to post a number of the tables, procedures, and other database components over the next few weeks.

Hopefully others will get as much use out of these as I have. To make it easier to find these posts, you can search my blog for the tag DBADiagnostics and all of those future posts should pop right up.

For convenience sake, here’s is the script that I used to create the DBADiagnostics database:

USE [master]

GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = 'DBADiagnostics')

    DROP DATABASE [DBADiagnostics]

GO

CREATE DATABASE [DBADiagnostics] ON PRIMARY (

    NAME = N'DBADiagnostics'

    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBADiagnostics.mdf' 

    ,SIZE = 10240KB 

    ,MAXSIZE = UNLIMITED

    ,FILEGROWTH = 10240KB 

)

,FILEGROUP [Data] DEFAULT (

    NAME = N'DBADiagnostics_data'

    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBADiagnostics_data.ndf' 

    ,SIZE = 10240KB 

    ,MAXSIZE = UNLIMITED

    ,FILEGROWTH = 10240KB 

)

LOG ON (

    NAME = N'DBADiagnostics_log'

    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBADiagnostics_log.ldf' 

    ,SIZE = 10240KB 

    ,MAXSIZE = 2048GB 

    ,FILEGROWTH = 10240KB 

)

GO

Of course, depending on your directory structure above you may need to change those values.

I was reading the high-availability blog on MSDN today and found a post with some free training mentioned within it.  Here are the details from the blog:

Microsoft is offering some free training for Hyper-V which includes modules on HA and Failover Clustering.

Free eLearning: Exam 70-652: TS: Windows Server Virtualization, Configuring

This collection of five 2-hour courses helps you develop the skills necessary for implementing and managing Hyper-V in an IT environment, as well as, creating and managing virtual machines and hosts in a virtual environment.
Use promo code: 9350-Y2W6-3676
The courses within the collection include the following topics:

· An overview of the Hyper-V technology

· Creating a virtual environment

· Deploying systems in a virtual environment

· Configuring high availability in a virtual environment

· Administering a virtual environment with SCVMM

Details: https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=228779

Exam Details: http://www.microsoft.com/learning/en/us/exams/70-652.mspx

Free eBook: Understanding Microsoft Virtualization Solutions

This guide will teach you about the benefits of the latest virtualization technologies and how to plan, implement, and manage virtual infrastructure solutions. The technologies covered include: Windows Server 2008 Hyper-V, System Center Virtual Machine Manager 2008, Microsoft Application Virtualization 4.5, Microsoft Enterprise Desktop Virtualization, and Microsoft Virtual Desktop Infrastructure.

Details: http://csna01.libredigital.com/?urmvs17u33

I was reading Glenn Berry’s blog and found out about an opportunity for free Microsoft Exam vouchers.  This is a great deal that is part of Microsoft’s DreamSpark program.  Through this program, students at accredited academic institutions can take Microsoft Certified Technical Specialist exams for free.

As I mentioned the other day, there are a number of reasons to get certified.  One of these reasons that applies to students is that it helps bridge the experience gap.  Classes at school will help provide a foundation of the theoretical and a foundation to build a career upon.  Certifications provide a place to apply the information gained through school and apply towards the tools in the industry.

Until a student is able to make the move from being a student to having a career and building experience, certifications provide a leg up to help you differentiate yourself from the crowd.  That extra dedication and commitment that may make the difference on your next interview.

Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working with.  After some initial installation issues, I was ready to check out their SQL Server environment.

While the tool was running, I started checking out the SQL Server event logs to see if there was anything interesting in the logs.  Of course there was, otherwise this would be a very short and uninteresting blog.  Counting successful backup messages in the log is how I roll.

In the SQL Server event log there were a few interesting messages.  One in particular looked a lot like the following:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: XX seconds. Working set (K:B) XX, committed (K:B) XX, memory utilization: XX%.

Ah man, this doesn’t look good.  The message is talking about paging and performance degradation.  That always sound bad.  Of course, I started digging into the server to figure out what was causing these performance issues.  I found some promising information on this issue here and here.

While I was checking this information out, the Best Practice Analyzer finished its work.  I decided to wrap up the BPA work before getting too deep into this issue.  As I was exporting the report results, the first item in the list read the following:

There are known issues that exist in Windows and various Device Drivers which can trigger conditions that result in working set of all processes currently running on the machine to be trimmed. This will cause excessive paging and result in drastic reduction in performance of all applications on the machine (specifically SQL Server).We found that TCPIP.SYS present with version [5.2.3790.4318] and BXVBDA.SYS is also present. So we recommend you to disable TCP Chimney.

This issue tracks back to the issue that I found in the SQL Server error log at the beginning of this post.  As a result of using the BPA to check out the client’s SQL Server instance a potentially difficult issue to resolve may be resolved with next to no effort.

The client is currently implementing the recommendation and I should know shortly whether this resolved the issue.  I am fairly confident that this will resolve the issue for them but will follow up on this post when I know for certain.

The lesson here is that BPA identified and offered a resolution for an issue that could have taken a lot of effort to resolve.  The question now is have you run BPA on your SQL Server environment to validate that your environment is up to snuff?

I was hoping to have a case of the Mondays the other day.  Whenever one hopes for “the Mondays”, Monday will always deliver.  I was using a new PC at a client and installing some tools for an assessment that I was working on.

After some download and installing, Best Practices Analyzer 2005 was installed and ready to go.  I got the tool fired up and pointed it at the server I needed to look at and pressed go.

And then it stopped…  I had an unhandled exception error.

Not a big deal.  I can handle exception error because I can read.  After opening up the details window, the following was presented to me:,

See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************System.NullReferenceException: Object reference not set to an instance of an object.   at Microsoft.WindowsServerSystem.BestPracticesAnalyzer.UserInterface.BPALink.LinkClicked(Object sender, EventArgs e)   at System.Windows.Forms.Control.OnClick(EventArgs e)   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)   at System.Windows.Forms.Control.WndProc(Message& m)   at System.Windows.Forms.Label.WndProc(Message& m)   at System.Windows.Forms.LinkLabel.WndProc(Message& msg)   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************mscorlib    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)    CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll----------------------------------------SqlBPA2005    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/SqlBPA2005.exe----------------------------------------System.Windows.Forms    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll----------------------------------------System    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll----------------------------------------System.Drawing    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll----------------------------------------BPA.UserInterface    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/BPA.UserInterface.DLL----------------------------------------BPA.Common    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/BPA.Common.DLL----------------------------------------SqlBPA.Shared    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/SqlBPA.Shared.DLL----------------------------------------System.Xml    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll----------------------------------------System.Configuration    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll----------------------------------------************** JIT Debugging **************To enable just-in-time (JIT) debugging, the .config file for thisapplication or computer (machine.config) must have thejitDebugging value set in the system.windows.forms section.The application must also be compiled with debuggingenabled.

For example:

<configuration>    <system.windows.forms jitDebugging="true" /></configuration>

When JIT debugging is enabled, any unhandled exceptionwill be sent to the JIT debugger registered on the computerrather than be handled by this dialog box.

Great.  I can read – but not this.  I like error messages that are a little more direct that this.

Fortunately, after a little digging I found a similar issue over at MSDN forums.  To solve this issue I needed to install both of the following SQL Server 2005 components:

• Microsoft SQL Server Native Client
• Microsoft SQL Server 2005 Management Objects Collection

I hadn’t expected this issue since I had SQL Server client tools installed on the PC that I was using.  The catch was though that I had the SQL Server 2008 tools installed, not the SQL Server 2005 tools.

Where, oh where would I get these components?  Alas!  They are available in the SQL Server 2005 Feature Pack.

Now that I’ve got BPA installed it’s time to use it.  My question for you is do you use BPA?

This project has been on CodePlex for a while, but I thought I would give it a plug.  It’s a really good way to generate a load against a SQL Server without a lot of effort.  I’ve used it quite a number of times with presentations and use it when testing different code choices to see what happens when I run it often.

It probably won’t be as interesting as Old School, but come out this afternoon if you are in the Twin Cities for the PASSMN meeting you could attend.  It starts at 3:00 PM and we meet at 2:30 PM for snacks and socializing.  And by snacks, I usually mean pizza but no promises.

We’ll be hearing about SSIS – Team Development, Deployment and Configuration & Securing and Troubleshooting Service Broker.

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

I’ve mentioned the 2nd Chance Certification offer that Microsoft provides in the past.  And if you’ve tuned in at the PASS MN meetings, I’ve thrown the plug out there a few times.

2nd Chance offers test takers the opportunity to take a run at an exam with the chance to take it a 2nd time for free if the first attempt isn’t successful.  It’s a great offer that I’ve used quite a few times in the past.  My co-workers reading this probably think I should can it on this offer since I am always bringing it up.  Hello, obsessive compulsive me.

On top of that, right now you can also get an E-Learning Collection with the offer for just $35 more.  That’s a90% discount off the regular price.

So there must be a catch…

The offer expires on June 30th.  As in after next month it will no longer be available.  This offer has been available for quite some time, but don’t take it for granted and miss out on it.

But who cares about certifications?

Will being certified make you an expert in the subject area of the exam?  Not at all, but it does show that you’ve got the determination to spend time studying on the subject.  It also shows that you’re minimally qualified to work with the technology that the exam covers.

Along the same lines, certifications cover a lot of features and functionality on their topics.  Most employers don’t have the opportunity to allow people to use all of those features and functionality in their environments.  When I was working on some of SQL Server 2008 exams last summer, we often talked about how cool it would be to work at an employer that used all of the features of SQL Server 2008 in a single environment.

But unfortunately that environment doesn’t exist.  Or I haven’t found it yet…

Certifications, though, give you a reason to go out and find out about all those features that you don’t currently use, because they might be on the test.  Studying for certifications is one of the greatest benefits that getting certified will offer you.

Another thing to think about with certifications is that with today’s uncertain economic conditions, a certification could be that bit that helps you get in the door for an interview.  Or maybe it’ll be the extra effort that your boss sees you putting in that keeps you in the door.

As Brent mentioned a while back on his blog, certifications are the icing on the cake.

Why go for the 2nd chance?

You’re going to study, study, study, and study.  It takes time.  Time that you could do other things, like enjoying the summer or watching the new Star Trek movie.  Seriously, is anything left as canon anymore now that the timeline is fragged.  Each day only has 24 hours in it and you need to prioritize.  The 2nd chance offer lets you do that.

Suppose you planned to spend 40 hours studying and when life got in the way you only spent 20 hours studying.  Are you ready?  Should you re-schedule?

If you’ve been working with the technology for a few years and you may not need 40 hours to study.  You might already be ready and not know it.  Don’t reschedule.  Take the exam and see how you do.

If you pass, that’s probably another 40 hours of free time that you would have tried to study and a bucket full of stress and guilt between choosing between a certification and your kids, wife, family, etc.  And if you don’t pass, you know exactly where to study versus going back and hitting all areas of the test.  Which might mean you only need to spend 10 hours studying before the next try.  Meaning more free time and less stress and guilt.  A definite win-win situation.

So, are you willing to take your 2nd chance?

The final answer has mostly arrived, the long awaited projects “Madison” and “Kilimanjaro” are getting near to CTP stage and will be available soon as SQL Server 2008 R2 CTP.  This was announced earlier today at TechEd; which also concided with the release of the SQL Server 2008 R2 site.

Some of the details from the new site:

What’s New in R2

With this new release, IT professionals, developers and business users will experience the following benefits:

Capitalize on Hardware Innovation

Increase in the number of logical processors supported from 64 up to 256.  This will provide customers with more choices for obtaining single system scalability with high performance.

Optimize Hardware Resources

Dashboard viewpoints provide real-time insight into utilization and policy violations to help identify consolidation opportunities, maximize investments and maintain healthy systems.

Manage Efficiently at Scale

Through new extensions in SQL Server Management Studio, organizations will gain insights into their growing applications and databases and help ensure higher service levels through policies and dashboard viewpoints.

Enhance Collaboration Across Development and IT

Streamline Application Lifecycle Management through integration with Visual Studio. A new project type enables a single unit of management for packaging database schema with application requirements. This ensures higher quality application development while also accelerating deployments, moves, and changes over time.

Improve the Quality of Your Data

Centralized approach to defining, deploying, and managing master data can ensure reporting consistency across systems and deliver faster more accurate results across the enterprise.

Manage User-Generated Analytical Applications

Comprehensive management thru Microsoft SharePoint gives IT the ability to manage and secure all BI assets, thus freeing the original authors to focus on the priorities of the business.

Report with Ease

Decrease time and costs developing reports by giving users the ability to design their own queries, reports and charts through powerful and intuitive authoring and ad hoc reporting capabilities.

Get More Out of Your Data

New support for geospatial visualization can produce new insights and discoveries when geospatial data is combined with corporate data for reporting and analysis.

Build Robust Analytical Applications

With the in-memory analytics add-in for Microsoft Excel 2010, business users can quickly access, analyze and summarize vast amounts of data directly in Excel without the assistance of the IT department.

Consolidate Your Data

New “data mash up” capabilities will simplify time consuming data gathering and consolidation tasks.  Integrate data from multiple sources, including corporate databases and external sources, using powerful tools within Microsoft Excel.

Share and Collaborate with Confidence

New collaboration tools make it easy to share analytic applications and reports through Microsoft Office SharePoint, where they are refreshed automatically, maintained, and made accessible to others.

I’m really excited to play around with the CTP and dig into the MDM and multi-server management components.  Hopefully when the CTP is out I’ll find some time to try and post on the new features.

I changed my twitter name from h4ppyd4y to StrateSQL earlier this week.  This shouldn’t have made much of a difference to people that are following me since twitter now allows for name changes without having to create a new account.  Very cool!

Hopefully this change doesn’t confuse anyone.  If you aren’t using twitter, I do recommend it as a way to network with your SQL Server peers.  The videos below provide some insight into twitter.  Of better yet, check out Brent Ozar’s twitter posts.