log for database is not availabe

Question

Post reply

log for database is not availabe

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Error log please.

What is the exact state of that database?

Check sys.database_files, what log files does it have, are they all where they should be?

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

sql2k8 Hall of Fame Points: 3455 More actions · Answer 2

Hi,

name user_access_ desc log_reuse_wait log_reuse_wait_desc state_desc

Commun MULTI_USER 7 DATABASE_SNAPSHOT_CREATION ONLINE

name physical_name statestate_descmax_size growthis_read_only is_percent_growth

commun z:\DATA\Commun.mdf 0ONLINE -1 128 0 0

commun_log z:\DATA\Commun.ldf 0ONLINE 268435456 10 0 1

Below mentioned are the errors.

Sourcespid12s

Message

Transaction (Process ID 12) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Sourcespid26s

Message

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

Message

Error: 9001, Severity: 21, State: 5.

These are the only error messages which we have.

I have DBCC Checks running every minute and auto close on property set to true for the Commun database.

I dont know where these database checks are comming up . They are not from the vendor or from my SQL Server Jobs

Thank You,

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 3

"auto close on property set to true"

If this is a production box, I recommend setting "auto close on" property to false.

This way sqlserver will bring the db online when the engine starts and only performs these checks once.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sql2k8 (8/20/2012)
name physical_name statestate_descmax_size growthis_read_only is_percent_growth
commun z:\DATA\Commun.mdf 0ONLINE -1 128 0 0
commun_log z:\DATA\Commun.ldf 0ONLINE 268435456 10 0 1

And those files exist?

I have DBCC Checks running every minute and auto close on property set to true for the Commun database.

You almost certainly don't have checkDB running every minute, you have the DB opening every minute and SQL posting a message about when checkDB last ran successfully. That's all.

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

sql2k8 Hall of Fame Points: 3455 More actions · Answer 5

Hi,

I had turned of autoclose on the database and now things are working well.

Integrity checks and backups are running well now.

Thank You very much for the help.

As autoclose is on .It is forcing the database to close and reopen. That is causing the checkdbs to happen again and again

Thank You,

Jared SSC Guru Points: 61793 More actions · Answer 6

sql2k8 (8/21/2012)
Hi,
I had turned of autoclose on the database and now things are working well.
Integrity checks and backups are running well now.
Thank You very much for the help.
As autoclose is on .It is forcing the database to close and reopen. That is causing the checkdbs to happen again and again
Thank You,

As Gail said, checkdbs are not occurring again and again... It is just notifying you each time it starts of the last time checkdb WAS run.

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sql2k8 (8/21/2012)
As autoclose is on .It is forcing the database to close and reopen. That is causing the checkdbs to happen again and again

Once more with feeling....

CheckDB is NOT running. SQL does NOT automatically run checkDB. Autoclose does NOT mean checkDB is run when the DB is opened.

When SQL starts up the DB, it prints a message saying when the last successful checkDB was done. Check what the message actually says.

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

SQL-learning SSC Journeyman Points: 75 More actions · Answer 8

Hi,

I have the similar issue & getting the following error message when I try to stop auto close on. Please suggest.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Msg 9001, Level 21, State 1, Line 1

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

Original issue is that I can't run backup, dbcc check on the db with error message of log file not available for the db. I find that the auto close is set on & so wanted to set it off and run the check again but it fails. DB is online so as the db files and db is in simple recovery mode. I would be thankful for any help to troubleshoot the issue.

Thanks,