It's Two For One day here at shaunjstuart.com! That's right! Today only, you get TWO tips for the price of one!

One of the things I routinely do when setting up a new SQL Server or adding one to the flock that I already manage is to set up a job to maintain the job history maintain in the msdb database. If this isn't done, the msdb database can grow very large, especially on servers that have been running for years and are taking frequent backups. the majority of the time, you don't need all that job execution history, so I use the sp_delete_backuphistory to keep the size under control.

I recently discovered another thing that can bloat msdb - sendmail logs. With SQL 2005, it became much easier to send email from SQL Server. Database mail is a handy little utility for sending email and I use it to send all sorts of alerts about the state of my servers. In my environment, the developers have also used it to send out email from SSIS packages. We have hundreds of these that run on schedules from weekly to daily to hourly. I think it's safe to say my SQL Servers send out more email than the combined top 25% of our users.

When Database Mail sends a message, it stores a copy of the message in a table in the msdb database, as well as any attachments the email might have. It will also create a log entry in msdb regarding the sending of the message. If your server sends out a large number of emails, you may find the size of you msdb database becoming quite large. Microsoft has provided two stored procedures to help maintain these tables: sysmail_delete_mailitems_sp and sysmail_delete_log_sp. The commands have a couple of different parameters, but the most common way to use them is to supply a date for which all emails or log records prior will be deleted. Check with your business to see what your retention requirements are and then set up a job to perform this maintenance periodically. If you haven't been doing this, you can realize some significant space savings. On one of my more active servers, I freed up 250 MB of space from msdb. That translates to faster backups, restores, DBCCs, etc.

It was while I was setting this maintenance task up across my servers when I ran into a little problem that led me to my second tip. I created this job originally on a SQL 2008 R2 server and then used SSMS to script it out. I ran the script on other 2008 R2 servers without problem, but when I ran it on a SQL 2005 server, it failed with an error: @schedule_uid is not a parameter for procedure sp_add_jobschedule.

A quick visit to Google showed me the problem and the solution. SSMS can be configured to create scripts using different versions of T-SQL. I was using SSMS that came with SQL 2008 R2, so that was the T-SQL version it defaulted to. But if you go to Tools --> Options --> SQL Server Object Explorer --> Scripting, you can select a different version:

When I switched to script for SQL Server 2005, I got a new script that ran without errors on both my 2005 and 2008 R2 servers.

So what was the difference that was causing me problems? It had to do with the scripting of the job schedule. Here is the script in 2005 T-SQL:

-- 2005

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
		@job_id=@jobId,
		@name=N'Sa @ 5 AM',
		@enabled=1,
		@freq_type=8,
		@freq_interval=64,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120430,
		@active_end_date=99991231,
		@active_start_time=50000,
		@active_end_time=235959

And here is it in 2008 R2 T-SQL:

-- 2008

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
		@job_id=@jobId,
		@name=N'Sa 5 AM',
		@enabled=1,
		@freq_type=8,
		@freq_interval=64,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120430,
		@active_end_date=99991231,
		@active_start_time=50000,
		@active_end_time=235959,
		@schedule_uid=N'0623b9e9-fd03-4dcb-866d-cb156c623261'

Note the last line - the @schedule_uid parameter is not supported in SQL 2005. This is an output parameter in 2008, 2008 R2, and 2012. It doesn't appear to be well documented in MSDN. In fact, for the MSDN entries, it's not even listed in the Syntax section at the start of the entry, but you only find it as the last entry in the Arguments section. (There are a couple of Connect items filed regarding this, but Microsoft seems to have closed them all as "By Design." Even granting them the design part, I think this is a pretty poor design. At the very least, @schedule_id is also an output parameter and that is not scripted at all in any version of T-SQL. So why is just @schedule_uid scripted?)

The big news last week was the announcement from Microsoft that they are changing their certifications.  In a nutshell, the MCTS and MCITP certifications are ending with SQL Server 2008. Going forward, the new certifications will be called Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE). (I think the "MCSE" terminology is a bit unfortunate given that this acronym used to stand for Microsoft Certified Systems Engineer several years ago and will likely cause confusion and / or unrealistic expectations of for people referring to the new certification.)

There are two big changes to the new certifications. First, the SQL Developer and SQL Administrator tracks are being combined. In order to get the new certification you will need to have knowledge of both areas. Second, the certifications are no longer product version based and will require getting re-certified every 3 years.

I just watched Kendra Little's webinar about this changes on BrentOzar.com. I recommend people view that to get more information about the changes.

What are my thoughts on the certs? I think it's a mixed bag. I like the periodic re-certification requirement. I think that is a good method to ensure people who have the certification remain up to date on the latest technology. After all, doctors and Certified Public Accountants have a similar requirements to maintain their licenses. On the other hand, I have a strong suspicion that the re-certification exams are going to focus on the latest version of SQL Server that is out at the time. This puts people who work for companies that do not like to be on the "bleeding edge" at a disadvantage.

I also like the combination of the Developer and DBA tracks. I may be partial to this because I started as a SQL Developer and moved into the Database Administrator role, so I already have a background in both, but I think this is a good change. I think it may be harder on existing developers than DBAs, but over time, I think only good can come from this. Hopefully, it will force developers to perhaps write code with more of a mind towards administration and performance issues their code might generate.

What don't I like? First, I don't like that they changed the certification program yet again. I don't think the MTCS and MCITP certifications have been completely embraced by the business community. I still see ads for DBA positions that list a MCDBA certification as a requirement and that certification was retired with SQL 2000. Now hiring managers will have another set of initials to remember and look for. Combined with the previously mentioned confusion over the MCSE acronym, I don't see this to be a good thing.

(Full disclosure: I am also a bit peeved because I just received my MCITP: Database Administrator 2008 certification two days ago and now everything is changing.)

My shiny new badge

But my biggest complaint is the number of exams the new certs require. I was able to get my MCTS: SQL Server 2008 cert by taking one exam (70-432) and my MCITP:DBA 2008 cert by taking one exam (70-450). That was starting from scratch, no upgrade exams from earlier certs. Look at the new cert requirements:

MCSA: 3 exams (70-461, 70-462, and 70-463)

MCSE: MCSA plus 2 exams (70-464 and 70-465)

The upgrade path from MCTS to MCSA is two exams and the upgrade path from MCITP to MCSE is three exams.

That's a lot of exams, especially when they cost $150 each. (I'm assuming that will be the price. That's what the current MCTS and MCITP exam prices are). If your company will reimburse you for exams, it's not too bad, but if you are paying for these yourself, that's a hefty investment. And don't forget the 3 year re-certification requirement. No word yet on how many exams that will take.

During her webinar (at the 8:35 mark), Kendra said the new certs require only one more exam than the current certifications. This is somewhat misleading and smells a bit like spin to me. Her math seems to be based on comparing the number of tests to get the new MCSE cert with the number of tests to get both the MCITP DBA and MCITP Developer certs. Arguably, this is one way to look at it because the new MCSE cert combines the DBA and Developer certs. But in the old program, if I didn't need both a DBA and Developer cert, I didn't need to take all the exams. Now, I have no choice. So from my viewpoint, I went from having to take 2 exams for a cert that was 100% relevant to my job role to 5 exams for one that is maybe only 50%-75% relevant. That's an increase of 3 exams. And the upgrade path from MCITP doesn't really cut that down significantly.

(I am interested in how many people currently have an MCITP in both Administration and Development. Please leave a comment and let me know.)

As I said, I think these changes are a mixed bag. I will probably end up not getting a new certification any time soon. For one, my company has no plans to move to SQL 2012 or the cloud. For another, I have little faith that Microsoft won't decide to revamp the whole certification program again in another 5 years. I will definitely be taking the wait and see approach.

I got a request from my manager to update a document we have here called our Service Catalog. The document is published by the IT department and lists all the major software programs we use, the business units that use them, whether or not they are 24x7 systems, and some other stats about our department. My job was to update the database section.

If you can guess how many objects are in the database, you win the database!

One of the items was the number of SQL Servers we have and their versions. This was relatively easy. The next piece of information I needed to update was the total number of databases we have. A bit trickier, but since I have my Drive Space Monitoring procedure in place, and that breaks out sizes by database, it was a simple matter to write a query to count the total number of databases we have.

The last piece of information I needed to provide was a count of "database objects." Huh? I have no idea what this meant, and neither did my manager. Apparently it was put in the document by a DBA who has long since left. The size of the number didn't really give me any clues either. We had about 600 database and the database object number was just over 7,000.

To me, a database object refers to something very specific - something that is stored in a database (excluding user data). So that would include all tables, views, stored procedures, triggers, foreign keys, constraints, logins, etc. If that was truly what this number represented, a value of 7,000 for 600 databases seems very low to me. Furthermore, I'm not even sure why this number would be in the document, unless it was to make it sound like the DBA had a huge domain to watch over.

Well, whatever the case, I decided to go ahead and update the number, although I will be adding a note to the document indicating the number represents the total number of user tables, views, and stored procedures in our environment.

The next step was determining the easiest way to get that data. I do not have a Central Configuration Server set up, which probably would have made this job a bit easier. So instead, I decided to use the multi-server query capabilities of SSMS. A slight problem was that my environment has a mix of SQL 2000, 2005, and 2008 R2 servers, so the basic system tables I would need to query would be slightly different. No problem. I've written before about how I like to write my scripts to be version agnostic. Well, they aren't truly agnostic in that they don't care about which version they are running on, but they do detect the version and then use the appropriate tables. By writing scripts this way, I can execute the same script in SSMS against multiple servers at once and not get errors.

Below is my code to count all user tables, views, and stored procedures in all user databases on a server. Note that is skips system databases and offline databases. If you want to count things besides tables, views, and stored procedures, simply add them to the CASE and WHERE statements in the definition of the @SQL variable. This script will work on 2000, 2005, 2008, and 2008 R2 servers. It will probably even work on SQL 7.0 servers, but you'll need to change the IF statement that looks at the ProductVersion property.

/*
Script to count all user tables, views, and stored procedures in
	all user databases on a server.

shaunjstuart.com

Verion 1.0

*/

DECLARE @DBName AS VARCHAR(100)
DECLARE @TotalUserTables INT
DECLARE @TotalStoredProcedures INT
DECLARE @TotalViews INT

CREATE TABLE #tblDBResults
        (
         DatabaseName VARCHAR(100)
        ,Item VARCHAR(50)
        ,Total_count INT
        )

DECLARE @SQL VARCHAR(1000)
	/* basic select statement */
DECLARE @SQLWithDB VARCHAR(1000)
	/* select statement with USE db added */

SET @TotalUserTables = 0
SET @TotalStoredProcedures = 0
SET @TotalViews = 0

/* Use appropriate tables based on SQL Server version */
/* 8 = SQL 2000, otherwise assume 2005+ */

IF (SELECT  LEFT(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(1)), 1)
   ) = 8
   BEGIN

-- Configure basic select statement (without 'USE db' part) for SQL 2000 servers

         SET @SQL = 'SELECT	DB_NAME(),
							[type]
						   ,total_count = COUNT(*)
					FROM    (SELECT type = CASE WHEN xtype = ''U'' THEN ''User tables''
												WHEN xtype = ''V'' THEN ''Views''
												WHEN xtype = ''P'' THEN ''Stored procs''
										   END
							 FROM   sysobjects
							 WHERE  xtype IN (''P'', ''U'', ''V'')
							) s
					GROUP BY type;'

         DECLARE DatabasesCursor CURSOR
         FOR
                 SELECT name
                 FROM   sysdatabases
                 WHERE  dbid > 4
                        AND (512 & status) <> 512
					/* exclude system and offline databases */
   END
ELSE
   BEGIN

-- Configure basic select statement (without 'USE db' part) for SQL 2005+ servers

         SET @SQL = 'SELECT	DB_NAME(),
					[type]
                   ,total_count = COUNT(*)
            FROM    (SELECT type = CASE WHEN type = ''U'' THEN ''User tables''
                                        WHEN type = ''V'' THEN ''Views''
                                        WHEN type = ''P'' THEN ''Stored procs''
                                   END
                     FROM   sys.objects
                     WHERE  type IN (''P'', ''U'', ''V'')
                    ) s
            GROUP BY type;'

         DECLARE DatabasesCursor CURSOR
         FOR
                 SELECT name
                 FROM   sys.databases
                 WHERE  Database_id > 4
                        AND sys.databases.state = 0
				/* exclude system and offline databases */
   END

OPEN DatabasesCursor
FETCH NEXT FROM DatabasesCursor INTO @DBName

WHILE @@fetch_status = 0
      BEGIN

            SET @SQLWithDB = 'USE [' + @DBName + '];' + CHAR(10) + CHAR(13)
                + @SQL

            INSERT  #tblDBResults
                    EXEC (@SQLWithDB)

            FETCH NEXT FROM DatabasesCursor INTO @DBName

      END

CLOSE DatabasesCursor
DEALLOCATE DatabasesCursor

SELECT  Item
       ,SUM(Total_count) AS TotalItems
FROM    #tblDBResults
GROUP BY [#tblDBResults].Item
ORDER BY [#tblDBResults].Item

DROP TABLE #tblDBResults

Unfortunately, if you are running this across multiple servers using the SSMS trick I mentioned earlier, there is one additional step you need to do. SSMS, when performing a query against multiple servers, will add a column to the result set called Server Name. Your result set will include a set of numbers for each server. There is no way to have SSMS aggregate these into one set of totals. You'll need to copy and paste the results into Excel and sum up the numbers of tables, views, and stored procedures across all servers.

By the way, counted this way, my count of database objects went from just over 7,000 to over 550,000, even while the number of databases dropped by 150.  Restricting the count to only user tables, the number was still over 250,000. Clearly the old value was looking at something else. But since no one knows what it was, I'm going to redefine the term and do things my way.

A couple years ago, I was interviewing for a new job and one of the interviewers I met with asked me how I would go about tuning a query to improve performance. One of the options I mentioned was to run the query through the Database Engine Tuning Advisor, although, I added, its results should be taken with a grain of salt and not blindly implemented. This seemed to take the interviewer by surprise and he asked me to elaborate. I did so, and I will do so again here in more detail.

The Database Engine Tuning Advisor is a great tool that Microsoft started including with SQL Server 2005. It can

Who can resist Deta's cute face? A cold-hearted DBA, that's who!

take a query and look at the tables used in the query and recommend steps to improve performance. It can recommend adding clustered and non-clustered indexes, table partitions, statistics, and indexed views, to name a few. It will also report the performance improvement it thinks its changes will achieve. It's a great tool to have in your DBA toolbox.

However, as with most cases of automated analysis, a human should carefully review the recommendations and not blindly implement them. Why? There are a couple of reasons.

First, the DETA works in a vacuum. All it knows about your server is the query you gave it and the current database structure - indexes, statistics, etc. It has no idea of the server workload. Perhaps the query you are optimizing is one used once a month for reporting on an OLTP system that is handling thousands of updates and inserts a minute. Any added indexes that DETA recommends will slow down access to the table because now SQL has to update additional indexes for each DML operation. This could adversely affect performance. (Note: you can give DETA a SQL Profiler trace file as an input and it will consider that entire workload as it evaluates options, but in this case I am talking about optimizing a single query. You cannot do this and provide a workload file at the same time, unless the query is part of that workload.)

Second, I've seen DETA make some recommendations that are redundant. Well, technically, they are not redundant as the recommendations are not identical. But they are close enough so that only one of the two would be needed. For example, yesterday, I was using DETA and it recommended two indexes on a table. One index was just on Column A. The second recommended index was on Column A with Column B as an included column in the index. Technically, those aren't redundant, but in practice, the second index will provide all the functionality of the first.

Lastly, DETA can only provide recommendations that fit the parameters it has been given. Here is a good example. I've got a table in a vendor database that contains approximately 450 million rows. There is no clustered index. (I know, I know. Ridiculous.) The table takes 45 GB of space. There are three non-clustered indexes on that table. As a test, I had DETA tune a simple query that did a SELECT * FROM Table WHERE ColumnF = <some value>. ColumnF was not included in any of the existing indexes. Because this table is so big, I do not want DETA to recommend creating a clustered index. Creating that would require reordering all the data on the disk and would likely take a very long time. So I checked the option to recommend nonclustered indexes only.

What was the result? DETA recommended a nonclustered index on ColumnF with all the other fields in the table listed in the INCLUDE clause of the index. (Basically, a clustered index in all aspects except name.) This would result in a 99% performance improvement! Sounds great, right? But DETA also reports this index will require another 45 GB of disk space.In effect, DETA just told me to double the size of my table. Plus there is the before mentioned impact of DML commands having to update an additional index.

Clearly, it would be unwise to blindly accept this recommendation. A better option would be to leave off the included columns. SQL will need to do a lookup to get the other fields, but when compared to the additional storage requirements and index upkeep during DML operations, this is a small price to pay.

These examples show why it is important for an experienced DBA to carefully review the recommendations of the Database Engine Tuning Advisor and not just blindly implement them. As with any advice, the DBA is free to do with it as he or she pleases, including ignore it or just take selected bits.

That's my advice. Do with it as you please. :-)

We had a request from our internal audit team recently to come up with a process to identify which SQL logins had not been used in 30 days so we could deactivate them. The driving force behind this was to make sure that a user's access was disabled when they left the company. Now most of our access is granted via Windows Active Directory accounts, so when that account is deactivated, their SQL Server access is automatically cut off. But we have some third party programs that require SQL logins and we had to come up with a method for dealing with those. Our audit team was worried about someone forgetting to disable a SQL account and wanted to have a method of detecting old accounts that might still be active but not used.

Don't lock yourself out of your server with a trigger

Out of the box, SQL Server has no way to track the last time a login was used. (Nor the last time a database was accessed. These are two features I'd very much like to see implemented in the future.) I decided to use a logon trigger to log each time an account connected to a table in our DBA Utilities database that we have installed on each server. We could then compare those active logins to all logins on the server and see which ones hadn't been used in X number of days. That information would then be passed on to the appropriate managers, who would give us feedback as to which accounts we could delete.

So I asked my co-DBA to develop a trigger that would log this information to a simple table that contained two columns - the login account and a date field indicating the time of the login. Because we didn't need to keep historical logon data, he wrote the routine so that if there was not already a row for the existing login, it was added. If there was a row, the date field was updated to the current time. Thus, our table would have a most one row for each login on the server and not require any sort of maintenance to keep from growing over time.

It all seemed very straightforward. Then we installed it onto our test system and ran into problems right away.

One very thing to note when working with logon triggers is that they execute in the security context of the login being used. So if User A is logging in, the trigger will execute with the permissions assigned to User A. In our case, User A (and all users other than sysadmins) doesn't have access to our DBA Utility database, so the trigger was not able to read the login table. What happens then? SQL doesn't let that account log in. You will get a login failed error with the message "Logon failed for login UserA due to trigger execution."

We quickly discovered a corollary to this - when working with logon triggers, it is very easy to lock yourself out of your server! If you have some sort of error in your trigger code that causes it to fail, you will not be able to login, even using an account with sysadmin rights. Fortunately, you can get around this by connecting via the DAC or by starting the server in minimum configuration mode (using the -f command line option). Once you've done that, you can connect and disable or delete the trigger.

So, back to our permissions issue. We overcame that problem by changing our trigger to use the EXECUTE AS clause to switch to a security context that had permissions in the DBA Utilities database. Now, because we want to log the account that is connecting, we also had to take care to store the original login into a variable BEFORE issuing the EXECUTE AS command. Otherwise, the SUSER_NAME() function returns the account specified in the EXECUTE AS clause.

Once all those changes were made, our trigger worked well. Except we noticed some strange behavior. We started seeing a lot of severity 20 alerts stating "The client was unable to reuse a session with SPID XX." What's more, these alerts were being fired at times when no one was using the system - 2 AM, 5 AM, etc. and usually at the top of the hour. That pointed to some sort of scheduled job.

After some investigating, my fellow DBA came across some posts that indicated this problem might be caused when a logon trigger encounters a deadlock. This made sense. We have several SQL Agent jobs that kick off at the same time, so the trigger was trying to update the same row in our login audit table.

We had a couple different ways to work around this. We could schedule our jobs to not start at the same time - move the start time of one by 1 minute forward or backwards, for example. This is not the best solution as I imagine in the future, someone (most likely me) would forget about this and schedule a job at the same time as another again. We went with a more robust method and ended up making a logging exception table that contained a list of logins that the trigger would not log and we added the SQL Server Agent account to that table. Now, the trigger checks the exception table before trying to log a connection. This solved our problem. (Although it could still happen if two users tried to login using the same account at exactly the same time, but that scenario is highly unlikely.)

Logon triggers provide a powerful means to audit server access, but you need to be careful when setting them up, as you can easily lock yourself and others out of the server and / or cause other problems.

Everyone DBA probably has one database that just gives them fits. It seems there's always at least one problem database that causes headaches whenever you try to do anything to it. I have one of those. It's an 800 GB database on one of my SQL 2005 servers.

The size itself isn't what's driving me crazy. Yes, a full backup takes several hours and yes, doing any sort of maintenance takes forever. But all those things can be handled - if the database was designed correctly. Databases of this size need to be set up using multiple filegroups. Tables should be partitioned. In short, there are a bunch of design strategies you can implement to make working with very large databases more manageable.

This database has none of that.

And, what's more, I'm not allowed to fix it. This is a database for a third party product we use and the vendor will not support us if I make any changes to the database structure. And, as any experienced DBA can tell you, vendors rarely know how to design a database for good performance.

For regulatory reasons, we need to retain the data this db stores for 7 years. We've had the product in place for just over 2 years so far and, based on current growth rates, the database will reach a maximum size of 1.7 TB by the time it has 7 years of data in it.

1.7TB in a single database file. No partitioned tables. No filegroups. One big file.

Backups take several hours now. I hate to think how long they will take then. And forget about performing a restore, or even a DBCC CHECKDB.

Obviously, this can't go on. Apparently, other people have had the same issue because the vendor has developed a process to move data into archive databases. A business decision was made for us to retain 2 years worth of data in the live database and move data older than that to an archive database. OK. I can live with that.

That process was put in place last weekend and, once again, I am surprised by the lack of database design skills of the vendor. Well, I guess I'm not really surprised. It's more like I was disappointed to have my low expectations confirmed.

First off, the entire process is unnecessarily convoluted. The SQL job that does the archiving does so by calling a batch file. The batch file calls osql, the SQL command line utility, to execute a stored procedure. Why couldn't the job just execute the stored procedure? I could find no reason.

Second, the process moves the old data into a new database, which is named for the year of data it contains. OK. That makes sense. It's easier to have 7 archive databases than 1 big one.  Then I took a closer look at the databases the process creates.

They were created in full recovery mode. This wasn't explicitly specified in the process, but was just a result of how my model database is configured, so I can't really blame the vendor. Except.. Maybe I can. It's an archive database. By definition, it's going to have old data in it that won't be changing (except possibly by new data being inserted during the weekly archive process). There's no reason it can't be specified to be in simple recovery mode. But no big deal. I changed the recovery mode and also changed the vendor's procedure so that newly created archive databases will be in simple recovery mode.

Then I looked at the tables in the archive database. There's about 85 tables. Every single one of them is a heap. Not a single clustered index to be found. Not a single non-clustered index. Just heaps of heaps.

Because this is archived data that isn't going to be accessed much, if at all, I can understand not having non-clustered indexes. No need to take up disk space and the lack of indexes will speed data loads. But no clustered indexes? That will actually slow things down. It's also not recommended.

My first archive database is 27 GB of heaps. I want that crap off my server. I'm checking with the various business units involved with this product, but I'm hoping I can take these archive databases offline so they won't be included in my standard backup and maintenance jobs. We can still retain the databases for 7 years, but I don't think they need to be online all the time. If we need to look up any data from them, I can always load the database onto the server again.

Apparently, there is some controversy on the internet about the value of obtaining a Microsoft certification (and certifications in general, I suppose). I’ve never really followed it or even read anything about it online. I had come to my own conclusions about it and that was that. For the longest time, I felt certifications were an unnecessary expense perpetrated by software companies to increase their revenue. I figured if you were doing your job well, your boss would know and he or she wouldn’t need a piece of paper to tell them you knew what you were doing. My thinking has since changed.

My path to becoming certified on SQL Server began more or less as a fluke back in 2003. I was working for a company that was a Microsoft Certified Partner. As part of that program, the company was required to have two Microsoft Certified Professionals on staff. Well, the company’s financials went a bit south, they made some layoffs, and then someone looked around and realized they no longer had any MCPs on staff. Oops! I was asked to get certified on SQL Server 7.0 to help regain our compliance with the Certified Partner program. I passed the exam and saved the day (or so I tell myself).

I moved on to another job after that and never bothered updating my certification or getting another one. After all, as I previously said, my boss knew I knew what I was doing. I didn’t see the benefit of paying for study materials and to take the exam, especially since newer versions of SQL Server are always coming out. It seemed to me like I would be stuck in an unending cycle of becoming certified on newer versions of software. I also didn’t like the fact that the certification exam would most likely test things that were not relevant to my job. For example, if we never used replication, why should I need to know all about it?

Then I was downsized. I found myself suddenly out in the job market with a certification that was for an older version of SQL Server. I no longer had a boss who knew my knowledge level. I had to sell myself to hiring managers and convince them I had the SQL skills needed for the job. At this point, it became clear to me what a certification really is – shorthand for saying you have a certain level of knowledge. It’s not going to tell a prospective employer you will be a great DBA, but it does give them a means to verify you have a certain baseline level of knowledge. If two candidates looked equally strong, I would bet the job would go to the one who had a certification, not the one who didn’t. So I decided I needed to get certified on SQL 2005. I bought a couple practice exams, the Microsoft training book, and took the MCTS: SQL 2005 exam. I passed. The bad part about all this was that it was expensive. I think I spent close to $300 on study materials and the exam itself was around $150. And I was unemployed at the time. That’s a lot of money when you are unemployed. Heck, it’s a good chunk of change when you are employed. (I believe the training material costs are tax deductible (I’m no tax expert  - consult your tax professional for advice), so that helps somewhat.) I was also able to hedge my bets a bit. The Transcender practice exam I bought came with a money back guarantee – if I didn’t pass the exam, I could get my practice exam cost refunded. And at the time I took my exam, Prometric was running a “second chance” promo where if you failed, you could retake the exam within a certain number of days for no charge. Luckily, I didn’t have to take advantage of either of those offers, but still, it was good to know I had a bit of a limit to my costs. Once I obtained my MCTS, I opted to stop there and not go on to get my MCITP. Shortly thereafter, I did get a job, so it seemed to pay off for me.

I’ve been at two different companies since then. I’ve also started reading SQL blogs and writing this SQL blog. My knowledge of SQL Server has expanded incredibly. Over the past two years, I’ve come to this realization – I love this shit! I really enjoy working with SQL Server and getting it to run well and hum right along. Two weeks ago, I took a step back and realized just how much I know about SQL Server and I decided I might as well get certified to prove it. My company will pay the exam cost as long as I pass, so I figured it’s better to get certified now then wait until I’m unemployed again. Last Saturday, I took the 70-432 exam and passed, so I got my MCTS for SQL 2008. I’ll be taking the 70-450 exam soon to get my MCITP. SQL 2012 is coming. I know I’m entering into that cycle I was worried about in the past of constantly obtaining certifications, but at least once you have a MCITP cert, there is an upgrade path to the certification for the newer version and you only have to take one exam instead of two.

This is me.

So my outlook on certifications has undergone a complete reversal. Even if I never look for another job again, I would still continue to get certified. I think it’s a good way to show your company that you are serious about keeping your skills current and relevant. As I get older, that is something I find myself worrying about more. I’m also no longer concerned about being tested on stuff I don’t currently use. I may not be using certain features now, but I realize I need to know how all those features work and the advantages and disadvantages of them so that if a situation arises where I do need to use them, I have the knowledge to make an informed choice about which would work best in the particular situation.

Network time server?

It's one of the most frustrating errors I've ever encountered. When you try to connect to a SQL Server using Windows Authentication, you can a "Cannot generate SSPI context" error. If you Google this, you'll find the most common cause is that SQL cannot generate a Service Principle Name (SPN). This usually happens when SQL is running under the local system account, as that account does not have permissions to create SPNs. It can also happen when SQL is installed and set to use the local system account, but then is changed to run under a domain account at a later time. There's a good post from Microsoft on how to troubleshoot this issue here.

But I ran into this problem and none of those steps worked. I verified the SPN was created. I could see in the SQL logs that the SPN was successfully created when the SQL service started and was successfully de-registered when it was stopped. I was using a domain account that was used on other SQL Servers, so I knew the account had the correct permissions. To make things even more puzzling, I was able to connect with Windows authentication a couple days before and now I couldn't. Nothing had changed, to my knowledge.

So I looked to the online community for help. I posted on SQL Server Central and on Twitter with the #sqlhelp hash tag. After several suggestions, @Vickyharp said "It's a long shot, but is the system clock correct?"

Nope, it wasn't. The system clock was 7 hours behind the network clock. I corrected the time and my SSPI error went away!

I have no idea how the clock got changed or why it wasn't synced up with network time. The server in question is a virtual machine, so that might have played some role in it.

This is something to keep in mind when troubleshooting this issue. I never saw this mentioned anywhere in my searching, so it's easy to overlook.

“If it ain’t broke, don’t fix it.” Let’s face it, we all live by that more than we should. We’re busy and it’s the problems people are complaining about that get all our attention. We rarely have the time to go back and improve our routines or redesign them. (Which is one reason I insist on taking the time to plan and try to do things right the first time, but that’s another story.) And sometimes, if a process is working, we don’t even think about what it’s doing – we just know it runs fine and has been for months or years. It never even dawns on us to question how it works.

Let me tell you a story that perfectly illustrates this: Ever since I learned to drive almost 3 decades ago, I had been keeping my car keys in my left pants pocket and my wallet in my right pocket. To get into my car, I’d get the keys from my pocket with my left hand, transfer them to my right, unlock the car and start it. When parking, the process was done in reverse – shut off the car with my right hand, get out, transfer the keys to my left hand, and put them in my left pocket. I don’t know why I started doing things this way (I suspect it may be because I’m left-handed), but that was just how I always did it, so I kept doing it that way. About 2 years ago, it stuck me – I was doing a lot of unnecessary shuffling of keys. If I just switched the location of my wallet and keys in my pockets, I could use my right hand for everything key-related. That resulted in fewer steps, less dropped keys, and made things easier when I had my hands full.

Many times, our SQL scheduled jobs are like this. They perform tasks without failing and we don’t think about them. As we migrate servers, we blindly transfer the jobs and make sure they run, without really looking into what they do. Sometimes, the job may be part of a bigger process that different people have worked on and no one has a clear picture of all the steps involved. I recently discovered a process here like this. There was a SQL job that imported data from a text file into a database. The text file was generated each week by someone in accounting, who made it by exporting a report from Crystal Reports. We had the opportunity to revisit this process for another reason and discovered something surprising – the Crystal Report was pulling data from a database on the same server we were later importing into! We quickly realized we could eliminate the middleman, and a huge source of potential error (we had had problems in the past with the text file being saved in an incorrect format), by simply taking the query the report used and incorporating that into our import routine. Now, the routine pulls data directly from one database into another with no intermediate text file or human intervention. That means better reliability, which means fewer calls to me to fix a problem. And I’m all for that. I have no idea why it was originally designed this way, but by taking a fresh look at it, we were able to rebuild it and take a chore away from an accountant who has better things to do. We later found 2 more processes that did the same thing, freeing up even more time for the person.

I've written in the past about the need to create a baseline of your SQL Server's performance and how to do that. The list of Perfmon counters I used was from a list that Brent Ozar recommended in one of this videos. I saw something last week that made me add a couple.

Paul Randal and Kimberly Tripp and the rest of the gang over at SQLSkills.com have a free club you can join called SQL Insiders. Joining will get you a bi-weekly newsletter with cool info from Paul and a video about some SQL Server-related topic. I highly recommend signing up. Plus, you get a free sticker when you do! (And really, who doesn't want a free sticker?)

The last newsletter featured a short video from Jonathan Kehayias about monitoring virtualized SQL Servers. One of the items he showed was a handful of Perfmon counters for VMs. I've got a lot of virtualized SQL Servers and the plan here is to move more in that direction, so I felt this was something I needed to monitor and went about adding the new counters to my standard counter collection process.

Only when I tried, Typeperf gave me a "Error: no valid counters" error. I knew from past experience that this is pretty much Typeperf's generic "something's wrong" error, so I had to do a little digging. First, I connected to the VM machine I was interested in and verified that I could see the counters via Perfmon locally. I could. I then connected to my collection machine and tried to look at the counters from a remote computer. No luck. I could see all the other counters, so I knew I had permissions set up correctly. All the non-VM counters were being collected without problem.

Hmm.. My first thought was that I mis-typed the name of the counter. I double-checked that, but the error persisted. I spent the better part of a couple hours trying to figure out what was wrong. At one point, I thought I was able to see the counters on the remote machine using Perfmon, but not Typeperf - which would be very strange indeed. But I was unable to duplicate that the following day, so I must have been mistaken.

A bit of googling finally pointed me to the solution: Restart the Remote Registry Service on the host machine. Once I did this, the VM counters showed up and Typeperf was able to see them.

I won't steal Jonathan's thunder by going in to details about what counters to watch and why. Join SQL Insiders and you can download the video yourself. The video is only about 15 minutes long and joining is free. You don't get spammed and you get lots of good information. I don't see any downside to joining.