It turns out it was a CPU resource allocation issue, whereby the cores allocated to the particular virtual machine were insufficient, as well as being an issue with the SAN.
In an effort to rule out any and all database possibilities (which I had a feeling there were none in terms of SQL Server problems), I decided to move the database onto a server with very little activity on it (and the only one of our production servers that isn't mission critical). The issue with that was that it's a SQL Server 2005 server, and the current database sits on a SQL Server 2008 R2 machine. As we all know, you can't regress in SQL.
I've found a long, but reliable way around this. In broad terms but point format, perform the following:
- Generate scripts for all databases concerned, making sure to script logins, bindings, collation and object permissions.
- Run scripts on new server.
- Before importing any data, run the following:
- In SSMS, right click on the source database, goto Tasks/Export Data. Select your source and destination server, the appropriate login details, Copy Data... option, and then click on the check box in the top left corner of the list of tables to select all tables (scroll down the list to uncheck any views - you don't want to copy those because they're already scripted).
- Click on the Edit Mappings button and select "Enable Identity Insert".
- Click Next a few times, and let it do its thing.
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
If, for some reason, the export fails half way through, you can run either the first or second script to remove the data (because even though constraints are disabled, truncating a table doesn't work):
EXEC sp_msforeachtable "DELETE FROM ?"Or:
DECLARE @TableName VARCHAR(200) DECLARE @SQL NVARCHAR(500) DECLARE CurTables CURSOR FAST_FORWARD FOR SELECT [name] FROM sys.tables WHERE [type] = 'U' OPEN CurTables FETCH NEXT FROM CurTables INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'DELETE FROM ' + @TableName --PRINT @SQL EXEC sp_executesql @SQL FETCH NEXT FROM CurTables INTO @TableName END CLOSE CurTables DEALLOCATE CurTables
The second query is there in case you get an error talking about quoted identifiers and how they need to be set. According to Microsoft, you can't set quoted identifiers during runtime, so the second query does what the first one does, but with more visible code.
Apart from that, everything works well afterwards. Fun times.