Solved

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

Posted on 2010-11-26
42
5,138 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

808 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