Solved

MSSQL 2008: Error: 9001, Severity: 21, State: 1. The log for database 'xxx' is not available.

Posted on 2010-11-26
42
4,987 Views
Last Modified: 2012-05-10
Hello,

Message
Error: 9001, Severity: 21, State: 1.
The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

I wanted to change Recovery model for my Database from Full to Simple and this error came in. On disk I have e lot of free space. Log usage:

DB      Log Size     Log space used    Status
XXX    68,11719   1,275232               0

Any ideas?
0
Comment
Question by:ser_berto
  • 15
  • 14
  • 12
  • +1
42 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216869
This sounds like there may be some corruption in your log.

Could you run DBCC CHECKDB on the database?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216903
you may also have a dodgy disk (run chkdsk) , is the database marked as suspect in management studio ?

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216919
Good point, another way to check the disk is to use a tool called SQLIOSIM as per the below link. I have used if before and it let to the firmware drivers being updated which resolved the issue.

http://support.microsoft.com/kb/231619
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216932
Evil, congrats on the "Guru" Update :-)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216945
Cheers, guess whose question pushed me over ;-D
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216982
lol,
that sqlskills sight is the tits by the way, thx !!
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216987
Yep, have a look at Paul Randals profile on there, you will see why its the best.
0
 

Author Comment

by:ser_berto
ID: 34217138
DBCC Checkdb

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
DBCC results for 'xxx'.

...

CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxx'.
Msg 9001, Level 21, State 1, Line 1
The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 9001, Level 21, State 1, Line 1
The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

DB is not marked as suspect in Management Studio. All logs are located at the same place, I have no issue with other DB logs.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217150
have you got much free disk space left ?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217169
dbcc checkdb('db_name') WITH ALL_ERRORMSGS
and post the results ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217170
Are there any other errors relating to this database in the event log?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217183
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217194
Yep that makes sense as... "Tablock is used to skip the database snapshot creation while doing checkdb"
0
 

Author Comment

by:ser_berto
ID: 34217263
I ran dbcc checkdb('xxx') with tablock,ALL_ERRORMSGS

Output:


DBCC results for 'xxx'.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
DBCC results for 'sys.xxx'.
There are 774 rows in 9 pages for object "sys.xxx".
DBCC results for 'sys.xxx'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxx'.
Msg 9001, Level 21, State 1, Line 1
The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217315
Looks like there could be some sort of space issue....

Have a look at the end of this article.

http://www.sqlskills.com/BLOGS/PAUL/post/Database-snapshots-when-things-go-wrong.aspx

You mentioned that you have loads of space on your log drive. How about your data drive?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217316
can you run this and give us the output please.

select * from sys.databases
where name = 'xxx'
0
 

Author Comment

by:ser_berto
ID: 34217330
On both drives I have hundreads of GB free. Data files are set up as follows:
data: increase 1 MB unrestricted
log: increase 10 % restrickted to 2 GB
Current sizes: 230 MG Data, 70 GB log
0
 

Author Comment

by:ser_berto
ID: 34217339
xxx      25      NULL      yyy 2010-10-27 11:14:14.853      90      SQL_Latin1_General_CP1_CI_AS      0      MULTI_USER      0      1      0      0      ONLINE      0      0      0      0      OFF      0      1      FULL      2      CHECKSUM      1      1      0      0      0      0      0      0      0      0      0      0      0      0      1      0      0      0      0      0      0      0      0      0      zzz      0      0      NOTHING      0      0      0      0
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217342
evil, you may laugh,
but I suggest

can you backup the database, and do a transaction log back up please ?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217348
230 MG Data
I presume you meant a 230GB data file ?

0
 

Author Comment

by:ser_berto
ID: 34217358
hehehe I did this:
Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server 'sss\iii'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2746.0+((Katmai_SP1_QFE-CU).091109-1630+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database. (Microsoft SQL Server, Error: 9001)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=9001&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:ser_berto
ID: 34217360
no MB !
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217368
in a query window under your database
can you run

select * from sys.sysfiles
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217370
when you tried to backup the database and got that error, it would have written a more detailed message into the windows event viewer application log.

what does it say ?
0
 

Author Comment

by:ser_berto
ID: 34217378
fileid groupid size        maxsize     growth      status      perf        name                                                                                                                             filename
------ ------- ----------- ----------- ----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1      1       28928       -1          128         2           0           xxx                                                                                                                             K:\MSSQL10.yyy\MSSQL\DATA\xxx.mdf
2      0       8720        268435456   10          1048642     0           xxx_log                                                                                                                         L:\MSSQL10.yyy\MSSQL\Data\xxx_log.ldf
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217380
also, try and shrink your transaction log to see what that does, not recommend, but I think you should do it in this case.

right click db, tasks, shrink, file, then choose transaction log in the drop down list.
0
 

Author Comment

by:ser_berto
ID: 34217389
In app log only this massage:

The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217393
very strange, when you tried the backup, can you check the SQL Server Log in management studio to see what it wrote in there if anything else ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217394
This issue is around the transaction log. Anything you try to do to it (BACKUP LOG or even a SHRINK) will most probably result in the same 9001 error.

0
 

Author Comment

by:ser_berto
ID: 34217397
After shrink try got standard error:


TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for LogFile 'xxx_log'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2746.0+((Katmai_SP1_QFE-CU).091109-1630+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+LogFile&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database. (Microsoft SQL Server, Error: 9001)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=9001&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
 

Author Comment

by:ser_berto
ID: 34217403
Nothing more in Management Studio only this information as I put already couple of times.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217416
Can you do a database backup. If so keep that backup as you may need to restore it. I have seen issues in the past where a detattch and re-attatch fix this issue but obviously that is very dangerous as if you cannot re-attatch this will cause problem. Thus the db backup.
0
 

Author Comment

by:ser_berto
ID: 34217423
The problem is that I can't backup Database. I get this error immediately when I start it. Maybe I try to do this by TDP 4 TSM.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217424
In fact thinking about it how about stopping SQL Server and then copying these files and the re-atattching these new files as a different database and see if you have the same issue with this new database.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217428
I thought you were doing a log backup before?
0
 

Author Comment

by:ser_berto
ID: 34217440
I can't even backup from TDP. Strangly I have backup from tonight...
0
 

Author Comment

by:ser_berto
ID: 34217441
I tried both db and log backups.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34217472
Has the SQL Server been stopped and started?

Im really sorry about this most repeated of all Microsoft tech support question.

I do have a valid reason for this. When SQL Server starts again the database in question will go through a recovery process which is standard and run through the log file rolling back and forward anything that needs it.

0
 

Author Comment

by:ser_berto
ID: 34217498
There was no restart recently. It seems everything was fine tonight. I have backup from 03:49 Local Europe time. I recognized issue when I wanted to change recovery model from full to simple.
0
 
LVL 3

Accepted Solution

by:
GSGDBA earned 500 total points
ID: 34217976
Hi,

Please see the below links.
http://sql-server-performance.com/Community/forums/p/679/4155.aspx
http://www.sqlservercentral.com/Forums/Topic302480-5-1.aspx

You can either restores the database or dettach and attach the database.

0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34217981
can you restart the SQL service ?
0
 

Author Comment

by:ser_berto
ID: 34237522
OK dettach and attach worked fine. Thanks for help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now