I have a database on a network drive that I use for archiving purposes, but when the server is rebooted the database becomes suspect.

[war_wheelan]

I created the db with the attached script and I am able to access it
until I reboot the server. I've tried enabling flag 1807 via the SQL
server service and the startup parameters of the instance. In all
cases the database always come up suspect after a reboot. There was
one instance where I was able to recover, but I am not sure how that
happened.

Does anyone have an idea of how I can reboot the server without the
database becomming suspect?

USE MASTER
GO
DBCC TRACEON(1807)
GO
--DBCC TRACEOFF(1807)
--DBCC TRACESTATUS(1807)
GO
CREATE DATABASE ReadyNAS ON
( NAME = ReadyNAS_Data,
FILENAME = '\\NAS1\NASDisk\SQL Server\ReadyNAS\ReadyNAS_Data.mdf',
SIZE = 100MB,
MAXSIZE = 20GB,
FILEGROWTH = 20MB)
LOG ON ( NAME = ReadyNAS_Log,
FILENAME = '\\NAS1\NASDisk\SQL Server\ReadyNAS\ReadyNAS_Log.ldf',
SIZE = 20MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)

[war_wheelan]

On Mar 30, 10:08 am, [e-mail] wrote:

I created the db with the attached script and I am able to access it
until I reboot the server. I've tried enabling flag 1807 via the SQL
server service and the startup parameters of the instance. In all
cases the database always come up suspect after a reboot. There was
one instance where I was able to recover, but I am not sure how that
happened.

Does anyone have an idea of how I can reboot the server without the
database becomming suspect?

USE MASTER
GO
DBCC TRACEON(1807)
GO
--DBCC TRACEOFF(1807)
--DBCC TRACESTATUS(1807)
GO
CREATE DATABASE ReadyNAS ON
( NAME = ReadyNAS_Data,
FILENAME = '\\NAS1\NASDisk\SQL Server\ReadyNAS\ReadyNAS_Data.mdf',
SIZE = 100MB,
MAXSIZE = 20GB,
FILEGROWTH = 20MB)
LOG ON ( NAME = ReadyNAS_Log,
FILENAME = '\\NAS1\NASDisk\SQL Server\ReadyNAS\ReadyNAS_Log.ldf',
SIZE = 20MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)

I've figure out how to reboot the server and make the networked
database accessible again. After the reboot, I need to manually
restart the SQL Server service.

Does anyone have an idea why I need to restart the service when I is
configured to start automatically? Also is there a way to have this
happen automatically via a script of something?

[Erland Sommarskog]

(wa*********@yahoo.com) writes:

I've figure out how to reboot the server and make the networked
database accessible again. After the reboot, I need to manually
restart the SQL Server service.

Does anyone have an idea why I need to restart the service when I is
configured to start automatically? Also is there a way to have this
happen automatically via a script of something?

I would guess what happens is that the SQL Server service starts up
before the network connection has been established. Since the database
is not found at the point, it's marked suspect.

One thing you could try is to set the database to autoclose. I'm not sure
that it will actually work; maybe SQL Server still tries to access the
database on startup.

I don't know if it's possible to delay SQL Server starting up by making
it depending on the network service that makes network shares available.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

<wa*********@yahoo.comwrote in message
news:11**********************@b75g2000hsg.googlegr oups.com...

I've figure out how to reboot the server and make the networked
database accessible again. After the reboot, I need to manually
restart the SQL Server service.

Does anyone have an idea why I need to restart the service when I is
configured to start automatically? Also is there a way to have this
happen automatically via a script of something?

Yes, my guess is that SQL Server is starting BEFORE the workstation service
(I believe that's the one required) so that UNC shares are not available
until AFTER SQL Server starts.

Restarting SQL Server works because the drives are available at that point.

You can go into the properties of the SQL Server service and play around
with dependencies and that should allow you to fix it.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[war_wheelan]

On Apr 1, 10:01 am, "Greg D. Moore \(Strider\)"
<[e-mail]:

<[e-mail] in message

news:11**********************@b75g2000hsg.googlegr oups.com...

I've figure out how to reboot the server and make the networked
database accessible again. After the reboot, I need to manually
restart the SQL Server service.

Does anyone have an idea why I need to restart the service when I is
configured to start automatically? Also is there a way to have this
happen automatically via a script of something?

Yes, my guess is that SQL Server is starting BEFORE the workstation service
(I believe that's the one required) so that UNC shares are not available
until AFTER SQL Server starts.

Restarting SQL Server works because the drives are available at that point.

You can go into the properties of the SQL Server service and play around
with dependencies and that should allow you to fix it.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

I tried setting the database to auto_close and also tried read_only
which didn't correct the problem. What I mean is I still had to
restart the service when the database was set to read_only, but I was
able to access it. I also found that accessing the tables from the
read_only database was quicker. On the other hand, when I set the
database to auto_close I couldn't access it after a reboot even when
restarting the service. The only was that I was able to access the
database was to remove the auto_close options.

[Greg D. Moore \(Strider\)]

<wa*********@yahoo.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...

On Apr 1, 10:01 am, "Greg D. Moore \(Strider\)"
<[e-mail]:

><[e-mail] in message

news:11**********************@b75g2000hsg.googleg roups.com...

I've figure out how to reboot the server and make the networked
database accessible again. After the reboot, I need to manually
restart the SQL Server service.

Does anyone have an idea why I need to restart the service when I is
configured to start automatically? Also is there a way to have this
happen automatically via a script of something?

Yes, my guess is that SQL Server is starting BEFORE the workstation
service
(I believe that's the one required) so that UNC shares are not available
until AFTER SQL Server starts.

Restarting SQL Server works because the drives are available at that
point.

You can go into the properties of the SQL Server service and play around
with dependencies and that should allow you to fix it.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html

I tried setting the database to auto_close and also tried read_only
which didn't correct the problem.

Ok, not really sure why you took this approach. Did you try my advice?

What I mean is I still had to
restart the service when the database was set to read_only, but I was
able to access it. I also found that accessing the tables from the
read_only database was quicker. On the other hand, when I set the
database to auto_close I couldn't access it after a reboot even when
restarting the service. The only was that I was able to access the
database was to remove the auto_close options.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html