March 6, 2012 at 8:39 am
I see these error messages..As per my earlier experience, this looks like data corruption. Please throw some light on this
During undoing of a logged operation in database 'Maximum', an error occurred at log record ID (112736:2730:213). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2012-03-04 04:17:28.17 spid416 Error: 9001, Severity: 21, State: 1.
2012-03-04 04:17:28.17 spid416 The log for database 'Maximum' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2012-03-04 04:17:39.34 spid28s SQL Server has encountered 7 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-03-04 04:17:39.36 spid28s SQL Server has encountered 7 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-03-04 04:17:39.41 spid28s Starting up database 'Maximum'.
2012-03-04 04:17:42.21 spid28s 1219 transactions rolled forward in database 'Maximum' (29). This is an informational message only. No user action is required.
2012-03-04 04:17:42.35 spid28s 1 transactions rolled back in database 'Maximum' (29). This is an informational message only. No user action is required.
2012-03-04 04:17:42.35 spid28s Recovery is writing a checkpoint in database 'Maximum' (29). This is an informational message only. No user action is required.
2012-03-04 04:17:42.65 spid28s Error: 17053, Severity: 16, State: 1.
2012-03-04 04:17:42.65 spid28s I:\Microsoft SQL Server\MSSQL.2\MSSQL\LOGS\Maximum.ldf: Operating system error 112(error not found) encountered.
2012-03-04 04:17:42.66 spid28s Error: 9002, Severity: 17, State: 2.
2012-03-04 04:17:42.66 spid28s The transaction log for database 'Maximum' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
March 6, 2012 at 9:28 am
What state is the database in at the moment? (state_desc in sys.databases)
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
March 6, 2012 at 9:37 am
GilaMonster (3/6/2012)
What state is the database in at the moment? (state_desc in sys.databases)
I am afraid, I don't find the state_desc column in sys.databases..
March 6, 2012 at 3:31 pm
Status_desc
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
March 7, 2012 at 2:59 am
GilaMonster (3/6/2012)
Status_desc
I see only the status and status2 columns and following are the values:
Status - 16
Status2 - 1090519040
March 7, 2012 at 3:24 am
execute the follow t-sql:
select name, state_desc from sys.databases
where name = 'maximum'
March 7, 2012 at 3:28 am
rfr.ferrari (3/7/2012)
execute the follow t-sql:
select name, state_desc from sys.databaseswhere name = 'maximum'
Error message:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'status_desc'.
March 7, 2012 at 3:53 am
sorry, is state_desc!!!
select name, state_desc
from sys.databases
where name = 'maximum'
March 7, 2012 at 3:55 am
Query sys.databases, not the old sysdatabases view. I can't recall offhand if it's state_desc or status_desc, query it and see, post the value.
The old sysdatabases view is for backward compatibility with SQL 2000 only and is missing a lot of information that the newer sys.databases view returns.
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
March 7, 2012 at 4:00 am
GilaMonster (3/7/2012)
Query sys.databases, not the old sysdatabases view. I can't recall offhand if it's state_desc or status_desc, query it and see, post the value.The old sysdatabases view is for backward compatibility with SQL 2000 only and is missing a lot of information that the newer sys.databases view returns.
state_desc is showing as online only..
March 7, 2012 at 4:20 am
Benki Chendu (3/7/2012)
...... state_desc is showing as online only..
is database working fine?
did you lost any data???
March 7, 2012 at 4:26 am
rfr.ferrari (3/7/2012)
Benki Chendu (3/7/2012)
...... state_desc is showing as online only..is database working fine?
did you lost any data???
It seems to be working fine now.
But, we had a P1 incident over the weekend when few websites went down which were using this database..
SQL Instance was up and running. It seems the customer took the database offline and got it back online and the issue was resolved.
When I was going through logs, I found these messages and hence, thought of taking some inputs here:-)
March 7, 2012 at 4:45 am
are they running administrative commands on server? (for example: DBCC) or the access only by website without admin commands?
March 7, 2012 at 4:47 am
rfr.ferrari (3/7/2012)
are they running administrative commands on server? (for example: DBCC) or the access only by website without admin commands?
They access only by website without admin commands as per my understanding
March 7, 2012 at 6:32 am
Check for any IO-related errors in the windows event log, SAN logs, etc.
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