SQL Server Notes

A UDF called in a SELECT statement, gets called once for each row returned, meaning the recordset is somewhat like a cursor in terms of perf...

Having manually moved the SQL Resource group to another node on a Server2008R2 cluster, it was noticed that the Quorum was still on the orig...

You may need to programmatically collect the current CPU Utilization. Why, is beyond me but whatever. The following script cannot be a funct...

It takes a fair while to install SQL Server 2008 using the GUI. Clicking next through screen after screen, waiting for each form to validate...

This is pretty much an interim post until I get around to writing up a more detailed article, and forget half the set up details along the w...

Whilst on the subject of making jobs mirror aware. Often a single database (or even application) is logically split into multiple databases ...

So get this, our principal database failed in our mirrored pair. Thats cool, it automatically failed over. What isnt so cool is that the job...

Following on from my post on 29th December 2008 on the LEN() function gotcha: When comparing string values for equality, trailing blanks are...

Its possible with a recovered database, to make it so that it will start accepting log restores again. Getting the LSN's accurate is a b...

It may be required to assign values to multiple variables from multiple rows in a table. Consider the following table: configName configValu...

So, it looks like Microsoft removed the very useful 'truncate_only' option from the backup command. Citing that you should switch to...

I need to free up space quickly on a certain drive, to do so means deleting some data and then shrinking that file group. So I need to be ab...

Sometimes its useful to know the space usage of individual database files. The following script uses the FILEPROPERTY function to display th...

If you want to test how something works for the little people (i.e. Those without system admin permissions on SQL), SQL 2005 introduced the ...

Upgrading and existing SQL Server instance to 2008 can be problematic if you have a large MSDB database. If you log ship any databases, or t...

There is functionality with SQLServer 2005 to import files directly from disk into variables or tables. This can be particularly useful for ...

A couple of handy new operators are available in SQL versions from 2005 onwards, these are EXCEPT and INTERCEPT. Whilst not offering any new...

Here's a cool script I came accross - similar to SHOWCONTIG, but without being all invasive and locking your tables select * into dba..c...

Given the above schema, I had the need to create some kind of constraint that would prevent a particular gameTypeId being linked to more tha...

When using the LEN() function, be aware that before calculating the length of the passed string, SQL seems to perform an RTRIM() on the stri...

If your logshipping fails, you can usually fix it with a full database restore or even a differential, though if your database is a couple o...

Previously, I posted describing a query that can be used to determine the filename, datetime and LSN information for the last or any applied...

You may have noticed that deleting from a table with a computed column works fine in Query Analyzer, yet when its a scheduled job it fails. ...

You can use the following script to alter the output/log path for each step in every job scheduled in SQL Server Agent. It sets the filename...

If jobs or schedules are owned by a user account and that account is deleted, the job or schedule will fail to run. When creating a new job ...

You can use an extended stored procedure to manage the services that are running locally to your instance, this applies to SQL Server 2000 a...

Rather than storing a bunch of settings as Bit fields, and COALESCING them or manually checking their value using a BIT MASK will allow you ...

Ahhh, this is frightening shit. What I'm doing is setting up a database with extra data so that I can simulate dropping a table and subs...

Whilst on the subject of deadlocks, here is a cool way to capture a deadlock, and extract more information from it, other than a SPID and an...

This mornings fiasco was interesting. For no obvious reason, the number of deadlocks on the server increased, soon it was every other statem...

In a previous post I demonstrated some SQL Server 2005 queries that would identify when a log shipping target was out of date, this post inc...

If you have lots of database on a server, that are all used together, say in CROSS database queries, or perhaps they are bolted together via...

In some cases you may need to prevent statements or sub-stored-procedures from being executed either outside of a transaction or directly in...

Here's a couple of queries, they only apply to SQL Server 2005+ that allow you to track what log was applied and when; and which was the...