SQL Service Start before App DBs Drives on line

Question

SQL Service Start before App DBs Drives on line

Budd

Hall of Fame

Points: 3772
More actions
March 4, 2019 at 9:55 am

#382855

What happens if SQL starts up from C drive, but it takes 5 minutes before the E and F drives (containing the application DBs) come online?
Will SQL continue to continue to reach them or will it just try once and stop searching when it can't see the drive ?
I have been asked to imagine certain scenarios and cannot find an answer to this one. Does anyone out there know?
Thanks..

Viewing 8 posts - 1 through 7 (of 7 total)

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

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

sql server will be up assuming all system databases are on c drive, I would assume database(s) will be in suspect mode (I doubt it will retry to scan databases), you can create dummy database, bring down service and delete mdf/ldf files for dummy database and start up the service.. should be same result as missing drive i.e. file not found errors in errorlog.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Budd - Monday, March 4, 2019 9:55 AM
What happens if SQL starts up from C drive, but it takes 5 minutes before the E and F drives (containing the application DBs) come online?
Will SQL continue to continue to reach them or will it just try once and stop searching when it can't see the drive ?
I have been asked to imagine certain scenarios and cannot find an answer to this one. Does anyone out there know?
Thanks..

I believe those databases would be listed as recovery pending and the SQL error logs would have errors - unable to open the file, cannot find the file specified. You can usually just restart SQL Server when this happens and the drives are online. And then fix the issue of the drives taking 5 minutes to come online.
If you want to test it yourself on a sandbox or development instance, just stop the service and move the database files for one of the databases, start SQL Server and then check that database and the SQL Server error log.

Sue

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

Budd - Monday, March 4, 2019 9:55 AM
What happens if SQL starts up from C drive, but it takes 5 minutes before the E and F drives (containing the application DBs) come online?
Will SQL continue to continue to reach them or will it just try once and stop searching when it can't see the drive ?
I have been asked to imagine certain scenarios and cannot find an answer to this one. Does anyone out there know?
Thanks..

Can we get a bit more information about your environment?
Is this a cluster, and the clustered drives come up after SQL tries to start? if so, then you need to set SQL to be dependent upon these drives. The dependency will keep SQL from starting until the drives are fully online.

If it's not a cluster, and the drives are on a SAN, then it seems like there may be some configuration issues that need to be rectified so that these drives can come up faster.

You can also set the SQL services to a delayed start, but that may not be a solution to the problem, only a band-aid.

Is this a hypothetical question, or, is it really how your servers are running?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

blakemcneill SSC Enthusiast Points: 162 More actions · Answer 4

Sue_H - Monday, March 4, 2019 1:43 PM
Budd - Monday, March 4, 2019 9:55 AM
What happens if SQL starts up from C drive, but it takes 5 minutes before the E and F drives (containing the application DBs) come online?
Will SQL continue to continue to reach them or will it just try once and stop searching when it can't see the drive ?
I have been asked to imagine certain scenarios and cannot find an answer to this one. Does anyone out there know?
Thanks..
I believe those databases would be listed as recovery pending and the SQL error logs would have errors - unable to open the file, cannot find the file specified. You can usually just restart SQL Server when this happens and the drives are online. And then fix the issue of the drives taking 5 minutes to come online.
If you want to test it yourself on a sandbox or development instance, just stop the service and move the database files for one of the databases, start SQL Server and then check that database and the SQL Server error log.
Sue

Sue has it right. The database comes up in a recovery state. The better question would be why is it taking your disks 5 minutes to come online? You should be talking to your server or SAN admin at that point.

Budd Hall of Fame Points: 3772 More actions · Answer 5

Michael L John - Monday, March 4, 2019 1:49 PM
Budd - Monday, March 4, 2019 9:55 AM
What happens if SQL starts up from C drive, but it takes 5 minutes before the E and F drives (containing the application DBs) come online?
Will SQL continue to continue to reach them or will it just try once and stop searching when it can't see the drive ?
I have been asked to imagine certain scenarios and cannot find an answer to this one. Does anyone out there know?
Thanks..
Can we get a bit more information about your environment?
Is this a cluster, and the clustered drives come up after SQL tries to start? if so, then you need to set SQL to be dependent upon these drives. The dependency will keep SQL from starting until the drives are fully online.
If it's not a cluster, and the drives are on a SAN, then it seems like there may be some configuration issues that need to be rectified so that these drives can come up faster.
You can also set the SQL services to a delayed start, but that may not be a solution to the problem, only a band-aid.
Is this a hypothetical question, or, is it really how your servers are running?

Hypothetical.

Budd Hall of Fame Points: 3772 More actions · Answer 6

blakemcneill - Monday, March 4, 2019 2:05 PM
Sue_H - Monday, March 4, 2019 1:43 PM
Budd - Monday, March 4, 2019 9:55 AM
What happens if SQL starts up from C drive, but it takes 5 minutes before the E and F drives (containing the application DBs) come online?
Will SQL continue to continue to reach them or will it just try once and stop searching when it can't see the drive ?
I have been asked to imagine certain scenarios and cannot find an answer to this one. Does anyone out there know?
Thanks..
I believe those databases would be listed as recovery pending and the SQL error logs would have errors - unable to open the file, cannot find the file specified. You can usually just restart SQL Server when this happens and the drives are online. And then fix the issue of the drives taking 5 minutes to come online.
If you want to test it yourself on a sandbox or development instance, just stop the service and move the database files for one of the databases, start SQL Server and then check that database and the SQL Server error log.
Sue
Sue has it right. The database comes up in a recovery state. The better question would be why is it taking your disks 5 minutes to come online? You should be talking to your server or SAN admin at that point.

Thanks. That's a good point, but I was looking to confirm that SQL would respond in the same way to
a drive becoming available after startup completed,
as it does
to MDF/LDF files becoming available after startup completed.
Is it really the same?

goher2000 SSCertifiable Points: 5479 More actions · Answer 7

you can test by creating/attaching/formatting a VHD drive in 'Disk Management' , let us know the results.