SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log not available error 9001


Log not available error 9001

Author
Message
MSQLDBA
MSQLDBA
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 577
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...


chandan_jha18
chandan_jha18
SSC Eights!
SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)

Group: General Forum Members
Points: 893 Visits: 2134
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
MSQLDBA
MSQLDBA
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 577
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...


SevereIdea
SevereIdea
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 90
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!!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86134 Visits: 45229
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, MVP, M.Sc (Comp Sci)
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


UMC_DBA
UMC_DBA
SSC Eights!
SSC Eights! (842 reputation)SSC Eights! (842 reputation)SSC Eights! (842 reputation)SSC Eights! (842 reputation)SSC Eights! (842 reputation)SSC Eights! (842 reputation)SSC Eights! (842 reputation)SSC Eights! (842 reputation)

Group: General Forum Members
Points: 842 Visits: 89
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'.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86134 Visits: 45229
Please post new questions in a new thread. Thank you.

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

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


jkosgei
jkosgei
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 36
This thread save my neck.
I came to work this morning and found the error 9001. I google it and came straight to here. Do as per what the expert said and I solved the problem. Thanks so much folks for the update and solution.:-)
Junior DBA
satwikchoudary
satwikchoudary
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 86
Hi,

We had exactly same issue this morning, autoclose option is disabled on DB settings.
But we had san outage (1min) which caused network drive disruptions on the server, so the SQL server couldn't find one of the database log other user db are fine just one impacted.

After taking offline/online resolved this issue, I think taking offline would have reconnected the log paths in sql system database!! this my guess.
May be SQL experts could throw some info on what happen when you take offline with in the SQL server. Or what happen to the log data?

Environment is - virtual on SQL Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
MARIA JOSE CASCALES
MARIA JOSE CASCALES
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 66
Andrew G - Tuesday, June 12, 2012 1:43 AM
To resolve, I took the database offline, brought online, DBCC CHECKDB and found no alllocation or consistency errors. Very weird.
Thank you, It resolves my issue. Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search