May 9, 2014 at 3:37 pm
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'.
May 9, 2014 at 3:40 pm
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
March 31, 2015 at 2:47 pm
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
July 12, 2016 at 3:15 am
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)
January 30, 2017 at 8:29 am
Andrew G - Tuesday, June 12, 2012 1:43 AMTo 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. 🙂
January 30, 2017 at 10:02 am
We had a similar issue ages ago and after running DBCC checkdb it said things were good. A few days later we had the problem again. Did more snooping and discovered that the problem wasn't with the database, but was with the disk.
We were using a 3rd party tool called Melio for handling cross server disk management. Basically it allows for a single disk to be visible to multiple phisical or virtual computers. The tool somehow corrupted the disk. Ran the Melio version of chkdsk on it and it reported back tons of errors. Added the repair flag and the tool never completed. We think the issue was related to how Melio and our SAN played together. Had to rebuild from backup to resolve the issue.
Support had us try a bunch of different tricks too but a rebuild from backup was the only solution we could find that worked successfully.
We ditched that as soon as our HADR software DxEnterprise ditched Melio in favor of windows disks and haven't had issues since.
TL;DR - make sure to also run a chkdsk on the disk as it could be a sign of hardware failure.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 26, 2017 at 7:13 am
Hi there, I was going through replies and also struggling with 9001 error . I am trying to deploy transaction replication between 2016 SQL vm instances and every time publication is initialized i am getting 9001 on
publication server : tempDB or publication DB
or
subscriber server: tempDB
drives show up as VMWARE virtual storage
am I correct saying this could this be possibly storage issue?
May 26, 2017 at 8:27 am
gmodified - Friday, May 26, 2017 7:13 AMHi there, I was going through replies and also struggling with 9001 error . I am trying to deploy transaction replication between 2016 SQL vm instances and every time publication is initialized i am getting 9001 onpublication server : tempDB or publication DB
or
subscriber server: tempDBdrives show up as VMWARE virtual storage
am I correct saying this could this be possibly storage issue?
Did you do what was recommended in a previous post (ie DBCC CHECKDB) on all of the DB's and chkdsk (if you can warrant the downtime)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 26, 2017 at 9:22 am
CHECKDB TempDB also returns 9001 log unavailable error. obviously restart brings tempdb back but it happened 3 times when trying to initialize publication.
Publication DB also returns 9001 from time to time but is able to recover automatically.
I can also see some of these errors:
SQLServerLogMgr::LogWriter: Operating system error170(The requested resource is in use.) encountered.
May 26, 2017 at 9:41 am
gmodified - Friday, May 26, 2017 9:22 AMCHECKDB TempDB also returns 9001 log unavailable error. obviously restart brings tempdb back but it happened 3 times when trying to initialize publication.
Publication DB also returns 9001 from time to time but is able to recover automatically.I can also see some of these errors:
SQLServerLogMgr::LogWriter: Operating system error170(The requested resource is in use.) encountered.
That is concerning that files are disappearing. TempDB shouldn't have its log disappear!
I'd be checking the disks and make sure your antivirus isn't scanning the mdf or ldf files. If it locks the files, your databases will be grumpy.
I would still run CHECKDB on the publication DB (if possible) and chkdsk on the drives. It sounds to me like something is goofy with your disks or something is configured incorrectly and locking the files.
Do the 9001 and the LogWriter errors occur around the same time? If so, fixing the LogWriter error may resolve the 9001.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply