Taking it futher

The awesome SQLRockStar came out with a nifty tip about fragmented Virtual Log Files. SQL has this tendency to split the transaction log file out into different chunks. It might be physically contiguous, but, logically, it is fragmented, and this affects performance.



The tip teaches us how to use Policy Based Management in SQL 2008 to work out which databases have extensive Virtual Log File fragmentation. So far, so good. But hark! I hear you cry! I only have one SQL 2008 Server. Everything else is still SQL 2000 or SQL 2005. There is no Policy Based Management tool there.


Panic not. If you have set up a Central Management Server in SQL 2008, and registered your servers within it, you can evaluate the Policy against all your servers. Just right click on the group you want to check, pick 'Evaluate Policies', choose your source (i.e. the SQL 2008 box on which you set up the initial policy), and let it run. It tends to look as though it's stuck if one of the servers in the group you've chosen isn't running SQL or is turned off, but leave it to run, and, eventually it'll stop trying to connect to that box, and move onto the next one.

Once you know what's up, you can follow the instructions here. I did make up a handy script to make life a little easier, and I cannot emphasise enough that you must back everything up properly before you get going. It will save a vast amount of heartache in the long run. Honest truly.

USE [databasename]
-- Backup the Log File

BACKUP LOG [databasename] TO devicename
GO

-- Set recovery to simple mode, otherwise the log file refuses to shrink

ALTER DATABASE [databasename]
SET recovery simple
GO

-- Shrink the log file

DBCC shrinkfile ( transactionloglogicalfilename , truncateonly )
GO

-- Grow it in ONE BIG STEP
ALTER DATABASE [databasename]

MODIFY FILE

(

NAME = transactionloglogicalfilename

, SIZE = newtotalsize --MB

)
GO

-- Set recovery back to full mode

ALTER DATABASE databasename
SET recovery FULL
GO

Finally, run a full backup, so that all future transaction log backups have something to work from.

(I used the instant SQL formatter to format the SQL for this post. It looks, um, interesting....)

Error Number: 3241 Severity: 16 state: 73

When performing a restore from a SQL 2005 .bak file to a SQL 2000 Server, the above is likely to happen.

Another hint that things aren't going to work is the "Name" field being filled with the value *** INCOMPLETE ***.

*head*desk*

Sometimes, it ought to be obvious....

Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.

At which point, the backup job, created via BlackBaud, failed.

First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.

Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.

Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.

It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.

Backups are now running.

sp_fulltext_catalog

And a happy new year to you. Mine started like this (among other issues), with an error for an hourly rebuild index job.


USE [thisdatabase]


EXEC sp_fulltext_catalog

N'this_database_index' ,

N'start_full'

Gave this result

Executed as user: Machinename\SQLAdmin. Execution of a full-text operation failed. The Gatherer is shutting down. [SQLSTATE 42000] (Error 7619). The step failed.

The Gatherer? You what?

This was on a non-clustered SQL 2000 box (8.0.2055), and the solution was to restart MSSeach. This I did via Services Administration, where it is helpfully labelled as 'Microsoft Search'. I have no idea what The Gatherer was, or did, or does, but it seems to be happy now.

Nifty...

The Midnight DBA has posted something rather nifty that can be found in SQL Profiler. There is the option, using sp_trace_setevent, to check for deprecated code - code that just won't work in future versions of SQL. Her explanation is rather more coherent than mine, I suspect she wasn't quite as excited.

I wish I had known about this one before I started migrating databases from SQL 2000 to SQL 2008....

I'll be implementing it on the last databases to be moved tomorrow. Murphy's Law says that if I don't, something ghastly will happen.

Why service packs are a Good Idea

"The job failed. Unable to determine if the owner (Domain\Administrator) of job IpmsaPolicyLogMaintenance has server access (reason: Violation of PRIMARY KEY constraint 'PK__syscachedcredent__0AD2A005'. Cannot insert duplicate key in object 'syscachedcredentials'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621))."

The quick & dirty fix? Set it to run at a slightly different time from any other SQL jobs, as the error occurs when two jobs start simultaneously, and both try to run an insert in the syscached credentials table, rather than one running an insert and one an update. Microsoft explain it more coherently.

The better fix? SQL 2000 SP1. Yup. SP1.

This morning, I 'ave been mostly deleting old SQL Agent Jobs

And these have been those annoying jobs that are, apparently, undeletable and unwanted maintenance jobs.

Job #1

A rather ancient backup job, or, rather, the remnants thereof, sitting in SQL 2005 Agent but with no maintenance plan behind it, no subplan schedule within it, and generally going 'Ha Ha' in a manner eerily reminiscent of Nelson Muntz. Assuming that the following error message can be translated as 'Ha Ha!'.

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.The statement has been terminated. (Microsoft SQL Server, Error: 547)

First, I had a look in the sysjobs table


SELECT *
FROM msdb.dbo.sysjobs
WHERE name like 'plan name%'

This gave me the ID for the job, and then I was able to run the following against the sysmaintplan_subplans table


SELECT *
FROM sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

(to confirm that I was looking at the correct job)


DELETE sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

Then I was able to delete the job via Job Activity Monitor.

Job #2

This was sitting on a SQL 2000 SP4 box, which had been created in the time honoured fashion: take an image of the original server and rename it. You get the most infuriating error

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

For this one to be fixed, you need to restart SQL. First, find out what the name of the original server was by running

SELECT @@SERVERNAME

Then, rename it with the correct name

sp_dropserver 'OldServerName'

sp_addserver 'CorrectServerName', 'local'

Then, restart SQL. I got lucky. No-one was using the Server.

Run the following


UPDATE msdb..sysjobs
SET originating_server = 'CorrectServerName'
WHERE originating_server = 'OldServerName'

Bob's your Uncle, Charlie's your Aunt, and it should now be possible to delete the recalcitrant job.

This is a known problem that was introduced in SQL 2000 SP3, and has apparently persisted. Grrrr.

(This week i 'ave mostly been.... ref)

Servernames

@@SERVERNAME. Sometimes, it isn't the same as the actual server's name. @@Servername is incorrect, it's wrong, it doesn't match. Argh!! It's easy enough to map though:


SELECT @@SERVERNAME AS[@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

The problem arises, when someone decides to rename the Server after SQL has been installed. The new Servername will get recognised by SQL during startup: and, default or instance, it will happily allow you to connect to SQL using the new Servername afterwards.

But....

The sys.servers (or sysservers, if you are in SQL 2000) table still holds the old information, hence the erroneous information from @@SERVERNAME (if you run SELECT SERVERPROPERTY('ServerName') you will get the correct information).

So. You can change the sys.servers table information thus (having disabled replication AND dropped any remote logins first AND switched off mirroring AND scripted out all your maintenance plans AND deleted them...):

sp_dropserver old_servername\instancename
GO
sp_addserver new_servername\instancename, local
GO

If you don't disable replication or drop the remote logins, then there will be errors. Don't say you weren't warned. You may need, if you have linked servers, to re-add those. Client aliases using named pipes will also need to be checked and corrected.

If you didn't script out and delete your maintenance plans before you got going, there is now (as of SQL 2008) a script to deal with the fact that they cannot be changed nor deleted....

Which all goes to show that, really, once you have decided what the Server is going to be called, you should stick with it. Avoid calling servers the same as the previous server, but with 'New' on the end. Or calling anything 'Temp'. Or using a default name. SQL2000Win2003 might be descriptive, but it won't help if you don't identify which one of the four servers with that name it really is.... Particularly, avoid having a server named, say, George, calling the new server, NewGeorge, and then creating a third server to replace NewGeorge which is, confusingly, called George, but isn't the same server as the Original George was. Also confusing is a server which was called Mabel, but is now called Sybil, while another server used to be called Sybil but is now called Fiona, while there still exists a server which has been called Mabel all along.

In other words. THINK.

OSQL sort of isn't...

'OSQL' is not recognized as an internal or external command, operable
program or batch file.

Oh. But it's where it should be! C:\Program Files\Microsoft SQL Server\80\Tools\Binn (not my installation).

Within CMD, you can type 'set path' to check the variables in the default path. If the actual location of osql.exe isn't in there (or, rather, its parent folder - and it should be there, because SQL is supposed to install it!)

Follow the instructions here.

Sorted. And so quickly.

SQL Integrity Checks

The job failed "Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1' sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." That's the job history. The history against the Maintenance plan (I only learned about this more detailed history last week, and I think it's fab), said "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode."

There are all sorts of ways of dealing with this.

Firstly, you can untick the box that says "Attempt to repair any minor problems" within the database maintenance plan. This is the same as changing the end of the SQL statement in the scheduled job to read "-CkDB" instead of "-CkDBRepair" If the job fails, this will flag up that there are errors within the database. Run DBCC CHECKDB at some point when you get in, and investigate from there. Databases need to be in single user mode to repair themselves. Sometimes, this is not possible (for example, SQL boxes that support websites with 24/7 availability) and you need scheduled downtime.

Secondly, you can ensure that users are not logged in. In one case, I shifted the job to run at 23:45 instead of at 19:30 - there were a couple of extremely enthusiastic users who were still logged in at 19:30.

How did I find out who they were? Script... On all servers, I find that an admindb is very helpful. So, I created a new table:


USE [admindb]
GO
/****** Object: Table [dbo].[tbl_sweetsql_usersloggedin] Script Date: 07/17/2009 10:15:11 ****** (C) Sweet SQL Lass/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_sweetsql_usersloggedin](
[loginame] [varchar](50) NOT NULL,
[logintime] [datetime] NULL,
[currenttime] [datetime] NULL,
[hostname] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

and then ran the following as a scheduled job:

USE [master]
GO
/****** Script Date: 07/17/2009 10:15:11 ****** (C) Sweet SQL Lass/
INSERT INTO
admindb..tbl_sweetsql_usersloggedin
SELECT loginame,
login_time,
getdate(),
hostname
FROM
sysprocesses
WHERE dbid = 7

(use
SELECT *
FROM sysdatabases
to find out what the dbid is).

The above code works on SQL 2000. For SQL 2005/8, use sys.sysprocesses and sys.databases views to get the same information. This is published under a creative commons license. (c) Sweet SQL Lass.

If the users are really lazy, and just didn't log out, and you want to repair things in the middle of the night, then you may need to use some SQL code to deal with this and throw them out of the database. There is plenty out there (and I have a version a colleague produced, and am seeking permission to publish, because it is rather nifty). Personally, I wouldn't recommend this. At the very least, warn your users that they will get logged out.

Heck. Microsoft doesn't recommend automatically repairing your database as part of an Integrity Check job. So who am I to go against that idea?

Finally, don't forget - log the information for your Maintenance Jobs to a Report File. It makes life much easier if you've done something really daft, like drop a database from the server without bothering to remove it from the Maintenance Job first.