The Problem

The server was on a VM and the system drive was backed up. We had separate drives for SQL Data, SQL Logs and SQL Backups. Only the backups went to tape.
As part of the decommissioning of a server we lost all the drives on our production box.
They were replaced and the system disk restored. The SQL Backups were also restored. The data and log drives were empty.

The Solution

The restore of the system drive gave us the SQL binaries but we lacked the system databases. In our support database I had a record of where the data and log files were stored so I could recreate the directory structures. I also used the SQL Config manager to find out where the error logs went and created those directories too.
SQL wouldn’t start, however, as there was no master db. To get it going I found another instance of SQL which was at the same product level. I copied the master mdf and ldf into the appropriate drives (I had to stop this SQL instance to get these files as they can’t be copied while SQL is running). I was now able to get SQL to start for a second. It was filing now due to a lack of Tempdb.
At first I was a bit confused as Tempdb is created when SQL server starts. Then I remembered that it was created from model. I went back to that other SQL install and took a copy of model mdf and ldf.
I was now able to Start SQL up and attempt to do a restore of the local master db.
At this point I encountered another issue. To restore master you need SQL to be running in single user mode. That’s fine but there was an app which was connecting to this box every half-second and it kept stealing that single session.
To stop the app I used IPSec (http://searchwindowsserver.techtarget.com/feature/Use-IPSec-to-manage-connections) to block the ip address the app was using – I got this from the SQL error logs. Thinking about it now I might have been able to turn of TCIP and used shared memory.
With the app blocked I was able to restore maters and then MSDB. I didn’t do Model as the copy I had borrowed was from an identically set-up SQL. You may want to restore model to be on the safe side.
At this point I could start SQL up normally and from SSMS I did a restore of all the user databases.

Lessons

We are now creating an archive of Master and model mdf and ldf files. We can then use these without stopping another SQL instance.
You could also copy master and model files into the backup directory whenever you do a new build, apply a service pack or create a hot fix (ie whenever the version number changes). You could then pull these from the backup directory.

The Problem

When I tried to deploy our SQL Maintenance plans to a server nothing happened.
I then checked the server and go the error: The affinity mask specified does not match the CPU mask on this system.

I ran sp_configure and saw that advanced options were not turned on. I wanted to turn them on so I could set the affinity mask properly. When I tried to do this I got the error: The affinity mask specified does not match the CPU mask on this system. I ran sp_configure again and I saw that the configured value was set to 1 but the run value was set to 0. This happened even I fI used Reconfigure with Override.
I restarted the instance to see if that would help. It didn’t.
I tried to change the affinity mask through Properties on the Instance in SSMS. It was all greyed out so I was unable to change anything.

Solution

I won’t detail all the things I tried which didn’t work, but there were many.
I changed the server start-up parameters to include –f. This starts the server in single user mode with limited configuration.
I also stopped the SQL Agent Servers as I didn’t want it taking my single connection.
There was an application which connects as soon as SQL starts. This was local so I couldn’t disable TCP/ip or use ipSec to stop it getting in. The solution was simpler – I disabled the account as it was using a SQL Login.
I then restarted SQL.
In SSMS I created a DAC connection to the box by going to File > New Query > then connected as
ADMIN:Servername
I ran sp_configure and could see that affinity mask had a configured value of 2 but a run value of 0 – due to the limited configuration start up. I ran sp_confiure and set it to 0. I did the same with the I/O affinity mask.
I then removed the –f start up parameter and restarted SQL. I re-enabled the SQL Login I’d disabled and started up the SQL Agent.
All was now fine.

Why did it happen?

This was a VM with only one CPU. I assume the affinity settings were put in place on a physical box. The machine was then virtualised with one CPU and the problems started.

No SQL

Posted: July 31, 2013 in Uncategorized

And when I say No SQL I mean No SQL. This isn’t a SQL post at all. I haven’t been keeping this blog up-to-date as I’ve been writing Crime Novels instead.

My first book, Salazar, is out now.

Reviews:

‘Combines the appeal of a colourful and fresh lead character with an intriguing Parisian setting at a fascinating moment in time. A promising debut.’
Chris Ewan, author of Safe House and Dead Line

‘An intelligent, moody novel, brimming over with well-drawn characters and unexpected insights. But more than anything else, Lynch gives us a Paris that feels real; his knowledge of the city in that particular time is staggeringly evocative, and makes Salazar an exceptional novel.’
Heath Lowrance, author of City of Heretics and The Bastard Hand

‘Superbly evoking the atmosphere of 1930s Paris, Salazar is a taut and engaging mystery novel from a great new writer. Lynch has created a classic anti-hero. Still carrying the scars of war, he’s the kind of man driven to do the right thing, regardless of the cost to himself in this engrossing debut novel.’
Nick Quantrill, author of Broken Dreams and The Late Greats

‘An absorbing twist on the hardboiled detective. Lynch has invented a whole new landscape: Paris-noir in the dissolute 1930s.’
Gary Corby, author of The Pericles Commission and The Ionia Sanction

Book description:

The debut novel from Seth Lynch…

Paris. 1930. An English detective haunted by his experiences of the Great War, Salazar whiles away the days playing chess and taking on as little work as possible. When the alluring Marie Poncelet hires him to find a missing man, Gustave Marty, it’s a case he’ll soon wish he’d refused.

Because finding a missing man isn’t anything like finding a man who doesn’t want to be found. And Gustave Marty has covered his tracks with a smokescreen that will push Salazar beyond the limits of physical endurance and to the edge of insanity.

As he’s drawn ever deeper into the shadowy underbelly of the City of Light, Salazar’s closed, structured world is blown apart by the arrival of a friend from his pre-war youth, the beautiful Megan Fitzwilliam, whose tenderness and love of life is a stark contrast to the brutal violence that lies within him.

When that violence threatens to engulf them both, Salazar must seek redemption or lose that which has finally made his life worth living.


And if you’ve read this far why not hop over to Amazon and read th esmaple chapter?

Get Salazar on Amazon UK or on Amazon USA

Problem

We have some SQL Server 2000 instances where Identity Checks and Index Reorganisations were failing. The error stated that Quoted Identifier was set to OFF.

Solution

Open the job and add this -S serverName\Instance Name before -PlanId.

Then at the end add –SupportComputedColumn

Note

If the maintenance plan itself is changed then the job definition will change and you’ll need to re-fix them.

This solution comes from an MS article which you can read in full here:

http://support.microsoft.com/kb/902388

This happened on a SQL 2000 system. I wanted to change the autogrowth on a file from 10% to a MB setting. I did this through the GUI and got an error saying the system couldn’t find the file I was trying to change.

I then tried to change it using ALTER Database. It still said there was no such file.

I right-clicked on the DB and checked the files again. Then I had a look in dbo.sys.files and could see it. I went through windows Explorer and took a look at the file. It was there and in the place where SQL was telling me it was – yet it still insisted it wasn’t there.

Solution

I ran this script:

SELECT *
FROM master..sysaltfiles
WHERE dbid= DB_ID();

This time the file had a different logical name. It was actually a better name than the one sysfiles was claiming. So I ran a script to change th efile’s name to the one sysAltFiles had:

ALTER Database DBName
MODIFY FILE (NAME = ‘DBName_data’, NEWNAME = ‘ DBName _Data’);

Now I was able to get in and change the autogrow setting.

This is obvious when you think about it, but it caught me out. We have a set minimum size for our databases of 50MB. It’s slightly abstract and 30MB would probably suit just as well. We also have a reporting system which lets us know if a file is getting full – so we could grow it manually rather than wait for AutoGrow.

I saw a report on a file to say it was full and that the file was only 1MB in size. The file was the TempDB log file. I logged in and grew it to 50MB. The next day I had the same report show up. Logged in again grew it to 50MB, certain that I’d done all this yesterday. So I right-clicked on the database and went to Reports >> Schema Changes – my changes were there but no others.

I checked that there were no jobs shrinking the file and that AutoShrink was turned off.

Today I grew the file to 50MB and then shrank it using DBCC Shrinkfile. Then I checked the report again. There was nothing to show that the file had been shrunk. I said at the beginning it was obvious. The report shows schema changes and so only operations which use Alter Database Modify File show in the report. DBCC commands are not schema changes and so won’t show.

In the meantime I’m going to run a trace and catch that DBCC shrink file command…

SQL Server 2005 SP3 and above is supported on Windows 7. Trying to install this I ran into the catch 22 situation: it won’t let me install SQL Server as only SP 3 and over is supported. I can’t install the service pack until SQL is installed. You can have hours of fun like this.

You may be able to get a SQL Server 2005 disc with SP3 applied but I couldn’t find one. What I did find was that it let me install developer edition. After that I installed SP4. Then I upgraded to Enterprise.

That all sounds too easy…

Of course it didn’t let me install developer edition just like that. It had to fail a couple of times first. The failure reported that SQLNCLI.msi was missing. It was there so I tried again. It failed. I then did some searching and found that this was because an earlier version of the client was installed. I went to add remove programs and removed it. After that the install went through OK.

This link will take you to my previous post on upgrading SQL Server 2005