January 10, 2014 at 8:14 am
Hi,
We had this issue today:
One of the database was unavailable for 20 minutes. We have more than 10 DB's and i was able to access all the other DB's . When I was trying to select the DB from the dropdown list in SSMS I was getting timeout error.
Can deadlock be a possible cause. At this instance there was a deadlock too. The DB was back 5-10 minutes after I killed the process.
Found the following errors in Error Log:
Message
[298] SQLServer Error: 4060, Cannot open database "MyDB" requested by the login. The login failed. [SQLSTATE 42000]
Message
[298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [SQLSTATE 28000]
Please advise.
January 10, 2014 at 8:18 pm
I dont think deadlock will be the cause. Rights for that user have been revoked by any chance ?
January 11, 2014 at 8:14 am
Nothing to do with the deadlock, unlikely to be permissions.
What does the SQL error log say? Post all messages from those 20 minutes.
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 12, 2014 at 8:09 am
Please find attached the Error Log.
January 12, 2014 at 8:32 am
01/10/2014 19:27:42,spid121,Unknown,A read of the file 'F:\Database\MyDB1\MyDB1MyDB1.mdf' at offset 0x000003b0670000 succeeded after failing 1 time(s) with error: 1117(The request could not be performed because of an I/O device error.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.
01/10/2014 19:27:42,spid5s,Unknown,Write error during log flush.
01/10/2014 19:27:42,spid5s,Unknown,LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.
Might want to have a look at that IO subsystem....
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 13, 2014 at 3:27 am
I was checking the logs & found that the Full & Differential backups were failing.
I ran the command DBCC CHECKDB('MyDB1') WITH NO_INFOMSGS,ALL_ERRORMSGS.
Attached is the output of DBCC.
Please advise what actions should I take to rectify the DB.
January 13, 2014 at 3:51 am
Attached?
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 13, 2014 at 4:39 am
Sorry !!! Now attached.
January 13, 2014 at 4:47 am
The table is high transaction table with hourly job to delete unwanted data. Could this be the issue?
January 13, 2014 at 5:17 am
Hakuna Matata (1/13/2014)
The table is high transaction table with hourly job to delete unwanted data. Could this be the issue?
No. The issue is that you have problems with your IO subsystem.
I suggest you restore from a clean backup. If you have log backups scheduled then you should be able to restore without data loss.
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 13, 2014 at 5:33 am
Unfortunately we don't have a backup for the DB. We have set deletion of the Backup every 4 days.
Hence all the backups have been deleted . Is there a way to restore the DB with minimum data loss.
January 13, 2014 at 7:57 am
USE MyDB1
GO
ALTER DATABASE MyDB1 SET SINGLE_USER;
DBCC CHECKTABLE (Table1,REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MyDB1 SET MULTI_USER;
Is the above option safe? Will I lose only the data that is corrupted or will I lose other data too. Will it lead to DB being unusable?
January 13, 2014 at 8:06 am
Nope, not that one
DBCC CheckDB('<database name>',REPAIR_ALLOW_DATA_LOSS)
Set the database to single user mode first
You will lose data, not necessarily just the corrupted data, you may lose other data as well. CheckDB just deallocates whatever's broken, if it has to deallocate an entire page because there's a corrupt row, that's what it will do.
You probably also need to do something about your backup strategy, your alerts for unsuccessful backups and alerts for failed database integrity jobs.
And have a look at that misbehaving IO subsystem.
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 13, 2014 at 9:48 am
Thanks a lot Gail for Guidance & Feedback.
Will the DB be in a usable state when DBCC is performed?
Not sure how to tackle the IO subsystem. Any suggestions ?
January 13, 2014 at 2:47 pm
Yes, it will be usable.
Maybe speak with whoever is responsible for the SAN, they'd know the most about it. Or contact the storage vendor
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply