Oh what a horrible error!

RAISERROR('Error performing SQLBackup backup.', 16, 1)SQL Backup v7.3.2.12                                                                                                                                                                                                                                             ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ERRSQB: 5240 (SQB service did not acknowledge receipt of data. (WAIT_TIMEOUT)) ()

And the log backups completely and utterly failed. Trawling round the internet gave me one post in a Red Gate Forum, and that looked scary. Removing mutex objects? Restarting the server (not exactly an option)?

However, simply restarting SQL Backup itself solved the problem. As to why it happened? I'm not sure. The cluster hadn't failed over, or received any patches. It might be co-incidence that I'd just applied the licence key after having run out of evaluation period. It might not.

We now have log backups again. Phew.

So how do I enable SQLCMD Mode?

Well, in SSMS.exe, you click on 'Query' then 'SQL CMD Mode'. Simples!

But wait! There's a catch if your script has variables, or os prompts:

Because you are not starting SQLCMD from the command line, there are some limitations when running Query Editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

What then?

Open up the Command Prompt, and load your script in thusly:

sqlcmd -S servername -U username -P password -i C:\Folder\Script.txt

And then it'll all work nicely, assuming that the script itself is sensible. For more parameters for the SQLCMD utility, see here.

Exporting employeeStartDates and employeeEndDates to SQL in FIM

Twice in the past six months, I've gone looking for this answer. The second time round, I came across my own post to Technet, which was a little embarrassing in a sitting-alone-at-my-desk manner.

Anyhow. Before I go looking a third time, this is how to get an employeeEndDate and an employeeStartDate out of the FIM Metaverse and into SQL date or date-time fields.

In VB.Net. Other languages exist. This is just the code for the Advanced Flows that you'd set up from FIM Synchronization Service: it relies on there being nothing more than the relevant dates. You may wish to build in more checks.

Select Case FlowRuleName
            Case "exportNonEmployeeEndDate"
                If (mventry.ObjectType.Equals("person")) AndAlso mventry("employeeEndDate").IsPresent Then
                    Dim NonEmpEndDateTime As DateTime = Convert.ToDateTime(mventry("employeeEndDate").StringValue)
                    Dim NonEmpEndDate As String = NonEmpEndDateTime.ToString("yyyy-MM-dd")
                    csentry("enddate").Value = NonEmpEndDate


                End If

            Case "exportNonEmployeeStartDate"
                If (mventry.ObjectType.Equals("person")) AndAlso mventry("employeeStartDate").IsPresent Then
                   Dim NonEmpEndDateTime As DateTime = Convert.ToDateTime(mventry("employeeStartDate").StringValue)
                    Dim NonEmpEndDate As String = NonEmpEndDateTime.ToString("yyyy-MM-dd")
                    csentry("startdate").Value = NonEmpEndDate


                End If

        End Select

As with all code on the internet, check it in the Development System before you throw it into the Live environment. And take backups before you get going. Just in case.

Also, has it really been a year? Sheesh. Sorry about that. 

What if you've been a fool with log shipping?

And managed to set your primary database as your secondary database, because, when you scripted out the T-SQL to create log shipping, you forgot to change server half way through... (yes, I did it twice today in a bit of a DR scenario).

First, disable the Log Shipping Copy and Log Shipping Restore Jobs on the Primary Server. You won't be able to do this by right clicking on the SQL Agent job and chosing 'disable'. You will if you go into the properties for the job. You won't, at this point, be able to delete the job.

Then, check the msdb secondary log shipping tables.

SELECT *
FROM msdb..log_shipping_secondary_databases

SELECT *
FROM msdb..log_shipping_monitor_secondary

SELECT *
FROM msdb..log_shipping_secondary

Verify that the database(s) is indeed in the list here.

Run the following SQL to delete the erroneous secondary databases

DELETE
FROM msdb..log_shipping_secondary_databases
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_monitor_secondary
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_secondary
WHERE primary_database = 'DBName'

After you have run this script for each database, the LSAlert job will stop erroring.

You will also, now, be able to delete the erroneous LSCopy and LSRestore jobs from SQL Agent.

As always, use at your own risk.

Collation

Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid = b.employeeid

If the collation on employeeid in table a does not match the collation for employeeid in table b, there will be a nasty error, along the lines of "Cannot resolve collation conflict for equal to operation"

Easy enough to deal with - tell SQL to use what *should* be the default for the database(s) in question. This worked when I was dealing with two databases with totally different collations on the same server - the default server collation didn't trickle down to a database imported from another, older, server.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid COLLATE DATABASE_DEFAULT = b.employeeid COLLATE DATABASE_DEFAULT

This is a lot simpler to implement than any solution that involves trying to re-collate one of the columns to the other table/database/column's collation. It's vastly more generic.

How to Estimate the size of a Nonclustered Index

Microsoft have a very long list of equations to work out, including the use of logarithms.

So I put it into a spreadsheet.

The spreadsheet only deals with non-unique non-clustered indexes at the moment, because that's what I was trying to work out. It should be reasonably simple - plug in the numbers in the cells adjacent to the bold text, make sure that you're calculating the correct number of non-leaf levels in cell B53, and make sure to use it in conjunction with the link above.

This spreadsheet is provided as-is, without warranty. It's for estimating the size of an index - actual size of indexes may vary.

Updated 30th June 2025 with a new link

T-SQL to give domain accounts sysadmin access

For those moments where you can only get access via osql.exe....

CREATE LOGIN [Domain\UserName] FROM WINDOWS;
GO


EXEC master..sp_addsrvrolemember [Domain\UserName],'sysadmin'

GO

Why yes. I did just lock myself out of a server accidentally this morning. Where Management Studio wasn't installed. Go figure.