Prev: HELP: A severe error occurred on the current command.
Next: How to handle @@error=1785
From: A on 23 Mar 2010 13:21
SQL Server 2008 SP1 2734.

We have a database that has the data and log file extensions are different.
Data file has the .DAT extension and log file has the .LOG extension (Don't
even ask who named them like that).

Also, the database is in simple mode and set to AUTO CLOSE.

The backup failed for this database last night thorowing the error "The log
for database 'MYDB P&T' is not available. Check the event log for related
error messages. Resolve any errors and restart the database.

(Yes the & in the database name and again don't ask why. You will open the
can of vorms).

My question is what would mostly cause the backup failure.

Having the database in AUTO CLOSE mode (It is backed up at 12:15 AM and it
is 8:00 AM - 5:00 PM Production)

or having the different file extension names or neither.

Thanks for any input.


From: A on 23 Mar 2010 15:19
Never mind..........It looks like the log file is corrupted. I don't know if
the reason is having the .LOG as log file extension but it is corrupted.

Running "DBCC CHECKDB" returns

"Msg 9001, Level 21, State 1, Line 1
The log for database 'MYDB P&T' is not available. Check the event log for
related error messages. Resolve any errors and restart the database.

Thanks anyway...............


"A" wrote:

> SQL Server 2008 SP1 2734.
>
> We have a database that has the data and log file extensions are different.
> Data file has the .DAT extension and log file has the .LOG extension (Don't
> even ask who named them like that).
>
> Also, the database is in simple mode and set to AUTO CLOSE.
>
> The backup failed for this database last night thorowing the error "The log
> for database 'MYDB P&T' is not available. Check the event log for related
> error messages. Resolve any errors and restart the database.
>
> (Yes the & in the database name and again don't ask why. You will open the
> can of vorms).
>
> My question is what would mostly cause the backup failure.
>
> Having the database in AUTO CLOSE mode (It is backed up at 12:15 AM and it
> is 8:00 AM - 5:00 PM Production)
>
> or having the different file extension names or neither.
>
> Thanks for any input.
>
>
From: Jeffrey Williams on 23 Mar 2010 21:44
SQL Server does not care what the extension on the files are - you can even
have files with no extensions and SQL Server will be happy.

There is really no reason to set auto close on - unless you are managing a
server with hundreds of databases that won't all be accessed at the same
time. In your case, there is no reason to close the database just to have
it opened again when you perform your nightly maintenance.

Both of these issues have nothing to do with your problem. Without being
able to see the event log on your server, it is not possible to determine
the cause of the problem with the log file. My guess is that you lost
connection to the SAN for just long enough for SQL Server to register the
file as missing. Once that happened, SQL Server does not try to reconnect
to the file.

Restarting SQL Server in the above situation usually solves the problem - as
long as access to the drive has been restored. If that is not your issue,
review the event log and identified any errors related to IO or your disk
drives. Follow up with your SAN team and have them review their logs also.

Jeff


"A" <A(a)discussions.microsoft.com> wrote in message
news:44F73A97-698A-45DF-897A-42588A652E21(a)microsoft.com...
> Never mind..........It looks like the log file is corrupted. I don't know
> if
> the reason is having the .LOG as log file extension but it is corrupted.
>
> Running "DBCC CHECKDB" returns
>
> "Msg 9001, Level 21, State 1, Line 1
> The log for database 'MYDB P&T' is not available. Check the event log for
> related error messages. Resolve any errors and restart the database.
>
> Thanks anyway...............
>
>
> "A" wrote:
>
>> SQL Server 2008 SP1 2734.
>>
>> We have a database that has the data and log file extensions are
>> different.
>> Data file has the .DAT extension and log file has the .LOG extension
>> (Don't
>> even ask who named them like that).
>>
>> Also, the database is in simple mode and set to AUTO CLOSE.
>>
>> The backup failed for this database last night thorowing the error "The
>> log
>> for database 'MYDB P&T' is not available. Check the event log for related
>> error messages. Resolve any errors and restart the database.
>>
>> (Yes the & in the database name and again don't ask why. You will open
>> the
>> can of vorms).
>>
>> My question is what would mostly cause the backup failure.
>>
>> Having the database in AUTO CLOSE mode (It is backed up at 12:15 AM and
>> it
>> is 8:00 AM - 5:00 PM Production)
>>
>> or having the different file extension names or neither.
>>
>> Thanks for any input.
>>
>>
From: Tibor Karaszi on 24 Mar 2010 02:38
Autclose can be related to the problem, but not the sole reason. With
autoclose, if no-one is in the database, the files and be moved, copied and
deleted (since they aren't opened by SQL Server. Perhaps somebody found this
"log" file, and thought it was safe to delete it? That would not happen if
autoclose is off (unless SQL Server is stopped, of course).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Jeffrey Williams" <jeff.williams3188(a)verizon.net> wrote in message
news:02C28507-D2DB-4F16-BC7F-92E2536A72BE(a)microsoft.com...
> SQL Server does not care what the extension on the files are - you can
> even have files with no extensions and SQL Server will be happy.
>
> There is really no reason to set auto close on - unless you are managing a
> server with hundreds of databases that won't all be accessed at the same
> time. In your case, there is no reason to close the database just to have
> it opened again when you perform your nightly maintenance.
>
> Both of these issues have nothing to do with your problem. Without being
> able to see the event log on your server, it is not possible to determine
> the cause of the problem with the log file. My guess is that you lost
> connection to the SAN for just long enough for SQL Server to register the
> file as missing. Once that happened, SQL Server does not try to reconnect
> to the file.
>
> Restarting SQL Server in the above situation usually solves the problem -
> as long as access to the drive has been restored. If that is not your
> issue, review the event log and identified any errors related to IO or
> your disk drives. Follow up with your SAN team and have them review their
> logs also.
>
> Jeff
>
>
> "A" <A(a)discussions.microsoft.com> wrote in message
> news:44F73A97-698A-45DF-897A-42588A652E21(a)microsoft.com...
>> Never mind..........It looks like the log file is corrupted. I don't know
>> if
>> the reason is having the .LOG as log file extension but it is corrupted.
>>
>> Running "DBCC CHECKDB" returns
>>
>> "Msg 9001, Level 21, State 1, Line 1
>> The log for database 'MYDB P&T' is not available. Check the event log for
>> related error messages. Resolve any errors and restart the database.
>>
>> Thanks anyway...............
>>
>>
>> "A" wrote:
>>
>>> SQL Server 2008 SP1 2734.
>>>
>>> We have a database that has the data and log file extensions are
>>> different.
>>> Data file has the .DAT extension and log file has the .LOG extension
>>> (Don't
>>> even ask who named them like that).
>>>
>>> Also, the database is in simple mode and set to AUTO CLOSE.
>>>
>>> The backup failed for this database last night thorowing the error "The
>>> log
>>> for database 'MYDB P&T' is not available. Check the event log for
>>> related
>>> error messages. Resolve any errors and restart the database.
>>>
>>> (Yes the & in the database name and again don't ask why. You will open
>>> the
>>> can of vorms).
>>>
>>> My question is what would mostly cause the backup failure.
>>>
>>> Having the database in AUTO CLOSE mode (It is backed up at 12:15 AM and
>>> it
>>> is 8:00 AM - 5:00 PM Production)
>>>
>>> or having the different file extension names or neither.
>>>
>>> Thanks for any input.
>>>
>>>
From: A on 24 Mar 2010 08:54
Jeffry and Tibor,
Thank you for your replies. I was wrong about the database usage. This
database is accessed one a month. It may be Ok to have it in "Auto Close"
mode. There was nothing else other than
"The log for database ''MYDB P&T' is not available. Check the event log for
related error messages. Resolve any errors and restart the database."
in the event log. Samething in the SQL Server and the individual backup log
files. This did not tell me why the log file was not available. The server
did not have any problem with SAN (Nothing indicated that SAN was
disconnected/reconnected e.t.c).

The log file was not deleted. The was existed on the .........\Data
directory along with the data file. I did a single file attach and the
problem was resolved.

Thanks..........





"Tibor Karaszi" wrote:

> Autclose can be related to the problem, but not the sole reason. With
> autoclose, if no-one is in the database, the files and be moved, copied and
> deleted (since they aren't opened by SQL Server. Perhaps somebody found this
> "log" file, and thought it was safe to delete it? That would not happen if
> autoclose is off (unless SQL Server is stopped, of course).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "Jeffrey Williams" <jeff.williams3188(a)verizon.net> wrote in message
> news:02C28507-D2DB-4F16-BC7F-92E2536A72BE(a)microsoft.com...
> > SQL Server does not care what the extension on the files are - you can
> > even have files with no extensions and SQL Server will be happy.
> >
> > There is really no reason to set auto close on - unless you are managing a
> > server with hundreds of databases that won't all be accessed at the same
> > time. In your case, there is no reason to close the database just to have
> > it opened again when you perform your nightly maintenance.
> >
> > Both of these issues have nothing to do with your problem. Without being
> > able to see the event log on your server, it is not possible to determine
> > the cause of the problem with the log file. My guess is that you lost
> > connection to the SAN for just long enough for SQL Server to register the
> > file as missing. Once that happened, SQL Server does not try to reconnect
> > to the file.
> >
> > Restarting SQL Server in the above situation usually solves the problem -
> > as long as access to the drive has been restored. If that is not your
> > issue, review the event log and identified any errors related to IO or
> > your disk drives. Follow up with your SAN team and have them review their
> > logs also.
> >
> > Jeff
> >
> >
> > "A" <A(a)discussions.microsoft.com> wrote in message
> > news:44F73A97-698A-45DF-897A-42588A652E21(a)microsoft.com...
> >> Never mind..........It looks like the log file is corrupted. I don't know
> >> if
> >> the reason is having the .LOG as log file extension but it is corrupted.
> >>
> >> Running "DBCC CHECKDB" returns
> >>
> >> "Msg 9001, Level 21, State 1, Line 1
> >> The log for database 'MYDB P&T' is not available. Check the event log for
> >> related error messages. Resolve any errors and restart the database.
> >>
> >> Thanks anyway...............
> >>
> >>
> >> "A" wrote:
> >>
> >>> SQL Server 2008 SP1 2734.
> >>>
> >>> We have a database that has the data and log file extensions are
> >>> different.
> >>> Data file has the .DAT extension and log file has the .LOG extension
> >>> (Don't
> >>> even ask who named them like that).
> >>>
> >>> Also, the database is in simple mode and set to AUTO CLOSE.
> >>>
> >>> The backup failed for this database last night thorowing the error "The
> >>> log
> >>> for database 'MYDB P&T' is not available. Check the event log for
> >>> related
> >>> error messages. Resolve any errors and restart the database.
> >>>
> >>> (Yes the & in the database name and again don't ask why. You will open
> >>> the
> >>> can of vorms).
> >>>
> >>> My question is what would mostly cause the backup failure.
> >>>
> >>> Having the database in AUTO CLOSE mode (It is backed up at 12:15 AM and
> >>> it
> >>> is 8:00 AM - 5:00 PM Production)
> >>>
> >>> or having the different file extension names or neither.
> >>>
> >>> Thanks for any input.
> >>>
> >>>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: HELP: A severe error occurred on the current command.
Next: How to handle @@error=1785