Error 0: This server has been disconnected

Hi all,
I'm having a problem with an SQL Server that I'm working
with and I haven't been able to find any information on
how to resolve it. I am able to log in to the the SQL
Server via Enterprise Manager and on the server, there are
around 10 - 15 databases.
I can log in to all the databases but one database takes
some time to access. When I click on Tables to view all
the tables that are in that particular database, it takes
additional time to load and I end up getting the following
error: "Error 0: This server has been disconnected. You
must reconnect to perform this operation". This problem
does not occur with any other databases on the server and,
even though it says I must reconnect, I don't have to in
order to access the other databases.
The owner of the database freed up about 40 GBs of space.
He also defragged the database and shrinked it but there
has been no change. He is able to access the database
without any problems although, before freeing up the
space, he also received the same error when trying to add
a row to a table.
The only solution that I found, aside from defragging, was
to delete the Registration in Enterprise Manager and the
Alias in the Client Network Utility and then re-enter that
information. I tried that and it didn't work. Does anyone
know why this error is occurring and how it can be fixed?I'm not sure how helpful a defragmentation will help against a file in use.
What I'd do (after backup the database) is first check who is in the user
list for that database. You might want to use the sp_change_users_login
auto_fix,<your sql login> on that database to make sure nothing got
disassociated.
Number two, I'd do an sp_who while you were trying to connect to the
database and check the SQL errorlog (either through enterprise manager or
the Logs folder which is usually where the database files are).
Number three, I'd run a DBCC CHECKDB (<database_name> ) You can specify
REPAIR_FAST as the option, it will do a minor check. Then try a
REPAIR_REBUILD before trying anything that might lose data. Make sure the
database is in single user mode (set under properties for the database in
enterprise manager)
Lastly, if you really want to try defragmentation, try detaching the
database or taking it offline first so the files aren't in use. Then try
reattaching/bringing the database online afterwards.
Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Jeff T. Severson" <jseverson@.jtsdesign.com> wrote in message
news:005c01c3d150$717c5b70$a301280a@.phx.gbl...

quote:

---

> Hi all,
> I'm having a problem with an SQL Server that I'm working
> with and I haven't been able to find any information on
> how to resolve it. I am able to log in to the the SQL
> Server via Enterprise Manager and on the server, there are
> around 10 - 15 databases.
> I can log in to all the databases but one database takes
> some time to access. When I click on Tables to view all
> the tables that are in that particular database, it takes
> additional time to load and I end up getting the following
> error: "Error 0: This server has been disconnected. You
> must reconnect to perform this operation". This problem
> does not occur with any other databases on the server and,
> even though it says I must reconnect, I don't have to in
> order to access the other databases.
> The owner of the database freed up about 40 GBs of space.
> He also defragged the database and shrinked it but there
> has been no change. He is able to access the database
> without any problems although, before freeing up the
> space, he also received the same error when trying to add
> a row to a table.
> The only solution that I found, aside from defragging, was
> to delete the Registration in Enterprise Manager and the
> Alias in the Client Network Utility and then re-enter that
> information. I tried that and it didn't work. Does anyone
> know why this error is occurring and how it can be fixed?

---

|||Andy,
Thank you for this information. I've passed it on to the database
administrator and we'll see how it works out.
Thanks again,
Jeff T. Severson
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!