Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Maria
Starting Member
12 Posts |
 Posted - 2001-11-27 : 08:00:22
|
| Hello!I couldn't create a database called DOGSTraining because there used to be a DOGSTRAINING database which was renamed to DOGSTRAININGold. The database files were still called DOGSTRAINING.mdf/.ldf, so that's why i couldn't create a new one called DOGSTraining (clash in db file names)What i did was this: (using sp_attach and sp_detach) I detached DOGSTRAININGold database, moved it's db files to another folder, re-attached the database to the server from this new location, and finally created the new DOGSTraining!! This whole process used spid231.....now here's the catch:A couple of hours later, i began running heavy deletion scripts on newly created (from a backup of the live db) DOGSTraining. These scripts (for some reason) also began using spid231 (?).The result: all services on the SQL SERver stopped, i.e. it crashed!!!Had to restart all the services because no one could do anything on any database. This is bug: 56266 - Lock Escalation With Parallel Query May Cause 1203 Error And Server Shutdown:(occurs on multiple process servers, like ours)check it out at:http://support.microsoft.com/support/kb/articles/Q240/8/53.ASPGuys, i need to know this: should I have manually killed spid231 after detaching and attaching the database? And why would a script run 2 hours after the de/attachment activity default to the same spid? Could this have to do with my own login being the same? or is it to do with the fact that i restored a database called DOGSTraining(similar name) immediately after the de/attach activity. i looked in the SQL error log and noticed an I/O Operating System Error 3(unknown)during the restore operation(it was not severe enough to notify me, or stop the restore operation). This operation was using spid104.What is going on?? is there something i need to know about attaching and detaching databases from the server?Please help! ^_^...thanks....Maria |
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2001-11-28 : 17:03:10
|
| Spids get re-used all the time. I don't think that was a contributing factor to your server crash. We do attach/detach around here at least 20 times a day to various servers and haven't seen this particular issue.If you've correctly diagnosed the problem as being related to the kbase article you've referenced, and there's no reason to believe otherwise, then you need to follow the workarounds suggested in the article, which will probably mention things like reducing the parallelism of your query.-rs |
 |
|
|
|
|
|
|
|
Process Locking sql bug