One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Thursday, February 16, 2012

Error 14269 on SQL Server 2000

As I noted in my last post, I am currently on a project to review some older servers (SQL 2000 and 2005), and as such I am tripping over some old quirks of the SQL Server system.

On one of the SQL 2000 servers I tried to modify an existing job's schedule, and was awarded with the following error:



 The thing I first noticed was that the servername (masked here) was truncated...the last few characters were missing from the name.  Googling 14269 brought me here:

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

 "BUG: You receive an "Error 14269" error message when you try to modify the SQL Server Agent job if the combination of the computer name and the instance name is more than 30 characters in SQL Server 2000"

I had noted that the instance I was reviewing is a named instance with a long name, which is annoying for my lazy typist side but just a reality of how this older system was set up.  

According to the SUpport KB, there was apparently a bug "by design" in the sp_add_jobserver stored procedure whereby the servername (Server\Instance) is processed as an nvarchar(30) parameter, which does not work with the long servername.

What made me laugh out loud was the number one workaround in the Support KB:







Since I was pretty sure I couldn't re-install the aged server, I went with option 2 and scripted the job to drop and re-create with the new schedule.

----

I realize that in the year 2012 the odds that someone else out there is running SQL 2000 with a greater than 30 character server name is slight. but if you are out there, hopefully this will help!

Backing Up Your Old Systems

I am currently on a project to review some older servers (SQL 2000 and 2005) for SIMPLE recovery (and therefore missing LOG backups) and for missing backups altogether!

I haven't worked with SQL 2000 much for a while, and as such it was a little trial and error (and more than a little Google) to work out the appropriate syntax to look up recovery models in SQL 2000:

select name, databasepropertyex(name, 'Recovery') as RecoveryModel
from master.dbo.sysdatabases
order by databasepropertyex(name, 'Recovery'), name

This shows me which databases are in SIMPLE recovery and so need to be changed to FULL.  I manually changed the appropriate databases from SIMPLE to FULL recovery.  The next step is to add LOG backups to these databases - or is it?

For this project, it is not, because they use dump devices (remember those?) for their disk-based backups.  No big deal, right?  Just create new dump devices for the new log backups.




(Remember Enterprise Manager?)

The catch in this case is that the existing dump devices are large multi-part names (REMOTE_DUMP_<Servername>_<DatabaseName>_DATA_DUMP) and I wanted a way to copy-paste or programmatically add the devices based on those current models.  This was further complicated by the fact that the names are not easily copy-pasteable from the properties window in Enterprise Manager:



So now I needed to find the table where this information was stored.  I remembered that they were added with the system stored procedure sp_addumpdevice, but where is the data actually at?

After some further Googling of sp_addumpdevice I found that the information is stored in sysdevices - but not msdb.dbo.sysdevices where I expected it to be; it is in MASTER.dbo.sysdevices:

select *
from MASTER.dbo.sysdevices
order by name

Rather than programmatically building dynamic SQL, I used the poor man's dynamic SQL and copy-paste-replaced values into the sp_addumpdevice string in Query Analyzer and created my devices, and then created LOG backup jobs and prepared to run the first LOG backup job to test it...


Changing from SIMPLE to FULL and then taking LOG backups makes everything good, right?

First a new FULL backup is required, or there is no continuity of the restore chain (at least not until the next FULL) - look at this:



To which point can this database be restored?  Unless a new FULL backup is taken after the 9am switch to FULL recovery to start the restore chain, there is no LOG backup available of the time from 5am-9am that the database was in SIMPLE recovery, and therefore no point-in-time recovery available other than back to the 5am FULL backup.

 (NOTE: this part - about requiring a new FULL (or DIFF) backup after the change from SIMPLE to FULL is true in *all* versions of SQL up through the present.)

I proceeded to take a new FULL backup, run the new LOG backup job (success!) and note my changes in our change system log.

-------

A few notes on this entry - first, if you are running SQL 2005 and up, use Hallengren maintenance!  It wouldn't have helped me in this situation (since I was dealing with SQL 2000) but any time I deal with backups/re-indexing/etc. I am reminded how much I prefer dealing with Ola Hallengren's awesome maintenance solution.

Second, why did I take time to write this at all?  I found that the web (including MSDN/Technet) has greatly decreased the amount of information easily Google-able about SQL 2000 since it went off mainstream support a couple years ago.  This was evidenced by how difficult it was to find some of the information I needed from the days of old before Management Studio and the sys schema, and unfortunately some of us still have to deal with SQL 2000 on a semi-regular basis - so hopefully this helps someone else!

Stop Sign from http://www.flickr.com/photos/donkeyhotey/6503264653/sizes/m/in/photostream/