January 26, 2010 at 4:19 am
Hello,
We're using SQL2k at work; yesterday morning one of the databases was flagged as Suspect, so I ran DBCC CHECKDB (WITH PHYSICAL_ONLY) on it. It had been running for 4.5 hours by the time I left work yesterday - the database in question is only 10GB in size.
My PC automatically shuts down every night, so I have no idea of what the results of the check db were, but now nobody is able to connect over SQL Server, receiving:
"Unable to connect to server [servername]
Server: Msg: 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation."
I can ping the server successfully, but cannot access it using Enterprise Manager, Query Analyser or Management Studio. We don't have any access to the server itself so cannot check logs or anything else I've seen recommended.
It's not supported by IT and we don't have a DBA - please help!
January 26, 2010 at 6:41 am
Any idea why it was flagged as suspect to begin with?
mine ran and completed in 5 seconds. If your server is in heavy use when you ran it perhaps that is why it took so long to run. If the intial reason for your db being suspect was due to running out of space your running dbcc on it wouldn't help matters as I believe that increases the transaction log size.
Any ideas on what the initial suspect flag was for? Are you able to telnet into the machine and get to the DATA directory to check file sizes, space left etc?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 26, 2010 at 7:42 am
A general network error is just a general can't connect error. Most likey cause is that the SQL Service is not running. You need to get remote access to the server somehow and check that the service is running.
Not supported by IT seems to imply there is an IT dept. Why do they not support this server? Who does?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 7:46 am
MothInTheMachine (1/26/2010)
If the intial reason for your db being suspect was due to running out of space your running dbcc on it wouldn't help matters as I believe that increases the transaction log size.
Just running out of space shouldn't make a DB suspect. Suspect means that either a transaction rollback failed or the service was restarted, restart recovery started and then failed.
At least, as far as I know.
CheckDB, when run without any repair options, does not make any changes to the DB and hence should not make the log grow. It may very well make TempDB grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 8:33 am
Just running out of space shouldn't make a DB suspect. Suspect means that either a transaction rollback failed or the service was restarted, restart recovery started and then failed.
Thanks Gila...I had never heard of that term 'suspect' so I was shooting in the dark. I'll have to add that to my gargantuan list of things to lookup about SQL server..lol
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 26, 2010 at 9:26 am
MothInTheMachine (1/26/2010)
Thanks Gila...I had never heard of that term 'suspect' so I was shooting in the dark.
Forgive me for being blunt, but why are you offering advice and suggestions on a subject that you've never heard of?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 9:57 am
Thanks everyone for their suggestions! - I've been at another PC all afternoon. Update is that we're trying to get IT to reboot the server, but even that's taken most of the day (and they still havent done it yet), and it's dubious whether they'll hand over any backups... :pinch:
Moth - SQL Server will flag databases as "Suspect" and taken offline if there are data errors - there were a bunch of data integrity errors in the server log from the night before.
I'd read online that CheckDB for a database of 10GB should only take 20-30 mins - I don't think anybody else was using the server at the time, as it only gets used around the first half of the month - so probably it got into difficulties with the checkdb.
One of the senior managers handed IT a server a few years ago, so they're hosting it for us, but there doesn't actually seem to be anyone who can help us if there's any problems (eg, we've been trying to increase our server capacity for the last 6 months).
Huge thanks for all the replies!
January 26, 2010 at 9:58 am
I can ping the server successfully, but cannot access it using Enterprise Manager, Query Analyser or Management Studio. We don't have any access to the server itself so cannot check logs or anything else I've seen recommended.
It's not supported by IT and we don't have a DBA - please help!
I would think someone would support the server. Any idea who installed SQL Server? They should have access.
Although maybe IT doesn't support SQL Server, I would think someone at least manages machines on the network and patches them.
You are using tools that all are requiring SQL service to be running.
See if you can open a local Services or Event Viewer, and then see if you can connect to the remote server.
Worth a try.
Greg E
January 26, 2010 at 10:10 am
harpistic (1/26/2010)
Moth - SQL Server will flag databases as "Suspect" and taken offline if there are data errors - there were a bunch of data integrity errors in the server log from the night before.
Not necessarily. SQL will flag a DB suspect if there are data errors and those data errors are encountered during the restart-recovery process or during a transaction rollback or similar. Having data errors doesn't automatically make a database suspect.
I'd read online that CheckDB for a database of 10GB should only take 20-30 mins
There's no way to say realistically how long CheckDB will take for a particular database, setup, situation. It takes as long as it takes.
You need to get hold of someone in IT who manages this server. Escalate to your manager (and get him to escalate it further if necessary)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 10:11 am
lol..Actually, if you reread my initial post I was only asking questions about his situation and suggesting he try telnetting intot he machine if it was unreachable... not terrible advice in and of itself. My real intention was to start a discussion on a thread that hadn't been responded too from a user who was in what appeared to me as a crisis. Success!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 26, 2010 at 10:28 am
Gail - I was oversimplifying there about Suspect status - I was Googling it yesterday morning, and that seems to be the reason the database went down - but of course I can't check it to see what state it's in...
I did manage to telnet into it this morning, but I didn't mention that as I didn't know what to do once I was in... !
We did have someone friendly in IT who I think set it up at the time, but I got an out of office email from him yesterday simply saying "I am out of the office"; we don't have any other contacts over there, and the people I've been dealing with today haven't been much use. (It's taken other people 3 days to get them to restore a backup). As I said, we've been trying for 6 months to increase our server capacity - we're willing to hand them new hard disks or whatever, but neither my boss, his boss or anyone else can find someone willing to talk to us! Situation probably best summed up as "they host it, we use it, heaven help us if we need any help."
Moth - thanks for the initial response, I've never used this forum before, much appreciated!!
January 26, 2010 at 10:58 am
harpistic (1/26/2010)
Gail - I was oversimplifying there about Suspect status - I was Googling it yesterday morning, and that seems to be the reason the database went down - but of course I can't check it to see what state it's in...
It shouldn't have. General network error, as far as I know, indicates that the SQL service cannot be found at all. A suspect database (unless it's one of the system databases), won't cause that. It may cause login failures (if your account defaults to that DB), but what you have is not a login failure.
Can you access the server via remote desktop? I'm not familiar with telnet on windows servers, don't know what you'd have access to. If telnet let's you send commands to the server, then we might be able to work through this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 11:54 am
Were you able to access Services on your local machine, and then connect to the server?
If you map a drive from your machine to the server-
\\servername\c$
and are able to connect, you have admin access to the server.
Greg E
January 26, 2010 at 12:45 pm
IO subsystem of your box might be saturated and could not handle the load of DBCC CHECKDB on this large database. CHECKDB generates tonnes of IO very quickly and you might wnat to upgrade your IO subsystem. (which i dont think is easy for you base on what you mentioned....:-)
Navnish
January 26, 2010 at 9:19 pm
While researching an issue with my transaction logs, I cam across this on msdn:
from http://support.microsoft.com/?id=317375
In addition to this error message, SQL Server may mark databases suspect because of a lack of space for transaction log expansion. For additional information about how to recover from this situation, see the "Insufficient Disk Space" topic in SQL Server Books Online.
so, if I were in your shoes I would google on how to work in telnet to view the size of the .ldf files in your DATA directory.
As long as we're waiting for IT and others to chime in, can you tell me how you noticed that your db was marked 'Suspect'? Was there an entry in the logs, the event viewer or is this viewable through a stored procedure? I'm wondering if that might be something that's going on in my shop. i was going to say I suspect that this is going on but that would have been bad.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply