What Corrupted an MS SQL 2000 Table ?

[Richard_Finnigan]

Recently a key student information table that I've been working on was corrupted while I was demonstrating the database to staff when the Server was having a service pack installed.

 

The Access client started to run slowly and then I got an error message stating that it was not possible to save this record because another user user is writing to this record. The client then lost its connection to MS SQL Server because the Server was rebooting.

 

So I slogged through all of the possible causes until I found out that Server had been upgraded during my training session with the office staff. The table has been corrupted and I've had to do another datatransfer.

 

I think that the Server Upgrade is the most likely cause of this problem, has anyone encountered this issue before ?

[michael2k6]

Richard,

 

This could be the reason your data's been corrupted.

Technically speaking, it should of just stopped you from using it. But if you've been updating the database by saving student data, and you're on windows 2000 it might of left some tables locked etc.

You shouldnt really be connected to the database while a service pack is being applied to SQL. It usually required a server restart, as you said and so is done out of hours.

 

I believe you're one of our customers? If so and you're still have an issue please log a support call asap. If you've resolved it, i should probably mention that upgrades should always be done out of hours where possible, and also that we no longer support SQL 2000. Though if you are going to use it, service pack 4 should be on, and it's worth noting service pack 3 has a memory leak.

To check which version of service pack you have, run

 

select @@version

go

 

A version ending in .760 is service pack 3, or service pack 3a. 3 has the memory leak, 3a fixed it. Unfortunately the version number didn't change. so if you stick sp4 on, you're covered (ends in .2094 off the top of my head, but essentially, anything over 760 and you've cracked it).

 

Hope this was useful, feel free to PM me if theres anything else.

 

Michael Sanderson

Technical Team

Serco Learning

[Michael]

In my experience when applying SQL service packs or hotfixes; every SQL related service gets stopped, but of course the server reboot itself probably didn't help either.

 

If by chance you were reading/writing data to the database whilst the services are being stopped, I could imagine this causing file corruption. Does the event log on your workstation display any error messages?

[Richard_Finnigan]

Thanks for your reply, it was the Server Operating system that was being upgraded SQL Server carried on running slowly and then disconnected when the server rebooted. Nothing showed up on the Server or SQL Server logs I think the records in the table have locks placed on them, there is a procedure that you can run to identify and then clear those locks, but I have rebuilt the table so that this no longer an issue.

[Richard_Finnigan]

Thanks Micheal, we are moving on from Serco Facility at the end of the month, thats why I was doing data transfers, I'll look into the point about Service Pack 4 on MS SQL Server, though I think I've got that covered. I want to upgrade to MS SQL Server 2008 during the spring break.

[Michael]

I would recommend you upgrade to SQL 2005 SP2 or later, as I am not sure whether SQL 2008 is supported at the current time. If you purchased SQL 2008 now you should be able to use downgrade rights and upgrade to SQL 2008 maybe in a year or so.

[Richard_Finnigan]

I think that waiting for Server 2008 to get bedded in possibly with its first Service pack release might be a good idea, at the moment support is not an issue for us, we are going for an in house solution.

[Michael]

That's a fair comment, but as you say, even for an in-house solution it makes sense to use a more mature product like SQL 2005. I am sure SQL 2008 is very good (although I haven't seen or used it). I have noticed many hosting providers are now offering SQL databases based on SQL 2008. I think some of the main benefits are database compression, scalability as well as manageability (no doubt).

[michael2k6]

Richard,

 

It's a shame you're moving on but of course i wish you all the best and hope the move ultimately benefits the students, which after all is what it's all about.

 

In terms of SQL 2008, what Michael says is right, you'll be licensed for 2005 if you buy 2008.

If you're developing a new system, i'd probably go straight to 2008 to avoid any issues upgrading it at a later date (not that there should be any).

Definately do some sort of upgrading before you start writing it, 2005 will make your life much, much easier.

 

Michael Sanderson

Technical Team

Serco Learning