Orphaned users

I'm sure many people have experienced the need to "reconnect" orphaned users after restoring a database from a different server. Not a difficult remedy, just run exec sp_change_users_login 'auto_fix','user_name' and you're good. If you run sp_change_users_login 'Report' first you can find all orphaned users for the given database.

Today I bumped into a situation where I ran the SP and received the error "An Invalid parameter or option was specified for 'sys.sp_change_users_users_login'". I was perplexed as I've run the sp many times before with no problems. I was starting to tear my hair out having searched the web with no joy until, and in hindsight, this is silly, I looked to see if the login existed on the server. And guess what, it didn't.

So, as is often the case, the Microsoft error message is, if not wrong, then at least misleading! I would have expected to see an error like 'User doesn't exist' or similar. Alas, in future, when this error appears, check whether the login exists.

See my other post with a query to resolve any and all orphaned users on a database.

My Trip to Delhi for MS Certification Training

I was made redundant/laid off in November. So decided to make something of it rather than wallow in sorrow (as it's the second time in as many years that this has happened). I had found a training centre in India almost a year ago, Koenig Solutions, and was angling to get my employer to split the cost of sending me out for training. Obviously, that was not to be. So when I got the sad news I decided to book a course for myself.

I was a little worried about the standards even though I had read good things about the company. You never know whether testimonials are genuine or not. But the price was good, even with the flights, and they provided everything: accommodation; full board; etc.

Next thing I know I've paid the deposit on the SQL Server 2008 DBA MCITP course, gotten a visa to India and booked my flights! No turning back. In the end everything was pretty much as promised. And the proof is in the pudding: I passed my certification and am that much more prepared for the dreaded job hunt in the new year.

Below are a few (understatement) pictures from my trip.

Logon Triggers

I just learned of a nice feature, Logon Triggers. A nice example of what they can do is limit a specific user to a certain number, 3 for example, concurrent logons. A nice little example of how to do this is as follows: Let's say we have a login name of login_test.

The interesting feature of this type of trigger is that the server authenticates the login but BEFORE the user is connected to the server or database the trigger is fired and the appropriate action is taken.

See the full explanation of logon triggers at http://msdn.microsoft.com/en-us/library/bb326598.aspx

There's also a good explanation of how these triggers relate to trace events and event notifications.

See my other post detailing a good use for such a trigger.

Drop Table Loop

I often, for various reasons, need to import data from external sources. Most of the time I import into tables on a staging database before further manipulating the data.

What I then tend to find after a few months is a very large and cluttered database. So below is a simple little query that creates a temp table of objects that then get dropped via a while loop. Nothing fancy, just a small time saver.

Quick Cheat to Shrink Database Log Files

This is a quick way to reduce the size of a database log file. As a caveat to beginners, this should not be used on mission critical db systems. It is intended as a quick and dirty fix for non-essential systems.

For those not familiar with the Attach and Detach commands:
--You need to make sure the database is not being used by anyone else/any other process. That is why there is a set single_user statement.
--If your database has multiple files/filegroups, you need only specify the primary file in the create statement (if you haven't made any file modifications after detaching).
-- You may need to enable xp_cmdshell therefore I've included the sp_configure statements.

Comparing Rows in a Table

As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().

To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.

And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.

I will definitely be employing this

Parse a Comma Separated List

Here is a simple user-defined function to parse a comma separated (or any other character separated) list and return a table of the values in the list. The input variable must be a text data type (varchar). It returns the results as varchar so depending on your needs you may need to convert to int or datetime, etc.

A Quick Cheat to Create a Comma Separated List

The select statement below is an easy way of creating a comma separated list within a larger select query without the need for a cursor, user defined function or even a paramenter. It exploits the 'for xml' statement. By not defining any xml elements the column you select gets concatenated into a single line.

In my scenario I have keywords that can be members of multiple adgroups. Since my revenue figures are aggregated by keyword and not broken down by adgroup I need to display all adgroups in the same row as the keyword or I will get duplicate revenue figures.
I owe a lot to the colleague who showed this to me. It's saved me tremendous amounts of time and trouble over the past few years. I hope you find this as helpful as I did.

Find SSRS Subscription Jobs

A helpful query found on Patrick LeBlanc's blog:


The result set returns a list of SQL Server Reporting Services Reports matched to the jobs used to run the report subscriptions. A very helpful query in that SSRS report manager names the regular subscription and data driven subscription jobs based on a uniqueidentifier. This makes it difficult to identify which jobs run which subscriptions when viewing jobs through SQL Server Management Studio.

Problems Moving tempdb Files

I had a situation where I had miscalculated the disk I/O load of the TempDB data and log files and therefore needed to alter the RAID configuration and move the tempdb files to the new configuration.
I had originally placed the tempdb data files on a RAID 10 (E:) partition with one mirrored physical disk and the tempdb log file on a separate but identical RAID 10 (G:) partition. The disk queue length for the data files was far too large. The log file was not used nearly as much. I decided (since resources were tight) to merge the E and G partitions into one RAID 10 partition with two mirrored disks.
The steps that were needed to accomplish this were as follows:
- Move tempdb data and log files to spare partition
- Merge E: and G: partitions
- Move tempdb data and log files to newly merged partition

For those that don't have experience with moving database files the procedure is pretty straight forward:
Run the following SQL with the new destination for each data file
Then restart SQL Server service.
My problem ocurred once I wanted to move the the tempdb files to the newly created RAID 10 partition. I kept receiving an error message stating the tempdb log file was unavailable. I had neglected to set the proper permissions on the new folders of the new partition. However, configuring this did not resolve the problem.

I couldn't find anything referring to the problem I had and all the documentation I read stated that restarting the SQL Server service was sufficient.

In the end I restarted the machine and the problem was resolved. Though this option was not specified in any documentation. I can only assume that the database did not "recognize" the partition and/or security changes until after an OS reboot.