Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-26
42
Medium Priority
?
5,767 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

886 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