Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Log not available error 9001 Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 1:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:43 AM
Points: 131, Visits: 558
Hi Buddies,

I am facing the same problem even my database Auto_close option is False. When the application team running package, database going on suspect mode. rest of the time its working fine. I am also not getting any error message in Evnt log and SQL server error log for the same. I put database on emergency mode and run dbcc checkdb i found one table corruption in the database, after putting database on online i didn't get any corruption in the database, result is showing no error.
Could you please help me to fix this issue?

Thanks in advance.
M.I


________________________________________
M.I.

Learning is a path with no destination...

Post #1522194
Posted Thursday, December 12, 2013 2:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:03 AM
Points: 466, Visits: 1,920
MSQLDBA (12/12/2013)
Hi Buddies,

I am facing the same problem even my database Auto_close option is False. When the application team running package, database going on suspect mode. rest of the time its working fine. I am also not getting any error message in Evnt log and SQL server error log for the same. I put database on emergency mode and run dbcc checkdb i found one table corruption in the database, after putting database on online i didn't get any corruption in the database, result is showing no error.
Could you please help me to fix this issue?

Thanks in advance.
M.I


Do you have the checkdb output handy? Can you paste it here.

Thanks
chandan
Post #1522196
Posted Thursday, December 12, 2013 5:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:43 AM
Points: 131, Visits: 558
Thanks for the reply,

Run DBCC CHECKDB in Emergency mode:
==============================
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156536) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156537) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156538) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156539) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156540) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156541) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156542) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:1156543) allocated to object ID 2121058592, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594166050816 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'Table1' (object ID 2121058592).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'AdventureDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureDB).

After DB come Online DBCC Checkdb result:
=================================
DBCC results for 'AdventureDB'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 965 rows in 10 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 98 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 116 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 20 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 136 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 64 rows in 1 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 824 rows in 24 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 188 rows in 10 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 333 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 187 rows in 57 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 146 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 121 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 97 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'REP_DatasetAttributeDefinition'.
There are 381 rows in 9 pages for object "REP_DatasetAttributeDefinition".
DBCC results for 'REP_LifeCycle'.
There are 0 rows in 0 pages for object "REP_LifeCycle".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'REP_Associations'.
There are 3044883 rows in 173635 pages for object "Table1".
DBCC results for 'REP_TransposedDocAttributes'.
There are 92415 rows in 5125 pages for object "Table2".
DBCC results for 'REP_TagAttributes'.
There are 13736431 rows in 906374 pages for object "Table3".
DBCC results for 'REP_TransposedTagAttributes'.
There are 704117 rows in 74856 pages for object "Table4".
DBCC results for 'REP_DocAttributes'.
There are 749995 rows in 38073 pages for object "Table5".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


________________________________________
M.I.

Learning is a path with no destination...

Post #1522242
Posted Thursday, February 13, 2014 10:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:57 AM
Points: 16, Visits: 87
THANKS!!! I had a db that up until today was doing a CHECKDB every few seconds... never reported any errors, but certainly filling up my event log. Yesterday the db hit a deadlock and then all of sudden the log file became unavailable... restored the last good back up and it all happened again.... 3x in 2 days.... setting the auto_close to OFF seems to have resolved the issue. Why was that even set? could it have been because this particular database was upsized from Access <facepalm> before I came on board here? IDK, but I think this is fixed now. Just wanted to say THANKS!!
Post #1541314
Posted Thursday, February 13, 2014 10:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
SevereIdea (2/13/2014)
THANKS!!! I had a db that up until today was doing a CHECKDB every few seconds... never reported any errors, but certainly filling up my event log.


It wasn't running checkDB every few seconds. It was closing and reopening every few seconds and when SQL opens a database it prints the last date checkDB succeeded into the error log.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1541322
Posted Friday, May 9, 2014 3:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 1, 2014 11:47 AM
Points: 832, Visits: 76
We have 20 SQLServer Express databases and we have been getting the same behavior on ~5 of these servers in the past 25 days.

Although it does not happen every time, there has been deadlocks at the same time that a database is "AutoStarting". After the deadlock the 9001 error starts showing up in the log. Taking the database Offline then Online fixes on the short term, but they are prone to repeating the error.
Setting AutoClose to False has fixed the problem, so far.

04/17/2014 14:52:59,spid59,Unknown,Starting up database 'xyzDB'.
04/17/2014 14:52:59,spid17s,Unknown,The log for database 'abcDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
04/17/2014 14:52:59,spid17s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
04/17/2014 14:52:59,spid24s,Unknown,The log for database 'abcDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
04/17/2014 14:52:59,spid24s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
04/17/2014 14:52:59,spid59,Unknown,Starting up database 'xyzDB'.
04/17/2014 14:46:55,spid18s,Unknown,The log for database 'abcDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
04/17/2014 14:46:55,spid18s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
04/17/2014 14:46:55,spid26s,Unknown,Transaction (Process ID 26) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/17/2014 14:46:55,spid26s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.
04/17/2014 14:46:55,spid26s,Unknown,Transaction (Process ID 26) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/17/2014 14:46:55,spid26s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.
04/17/2014 14:46:53,spid65,Unknown,Starting up database 'xyzDB'.
04/17/2014 14:46:53,spid65,Unknown,Starting up database 'abcDB'.
Post #1569466
Posted Friday, May 9, 2014 3:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
Please post new questions in a new thread. Thank you.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569470
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse