August 14, 2013 at 3:49 pm
Why does sql server automatically open a file handle for every database file when the service first starts up (even before anybody logs in to the sql server)? I want to ask if there's a way to stop it doing that, but if there's a good reason it does so then I probably won't try to stop it.
August 14, 2013 at 3:52 pm
Because part of the startup process is opening and recovering every database. If it didn't, then the first time a user connected they'd have to wait for the recovery process, which can take anything from seconds to hours (days even in some cases)
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
August 14, 2013 at 3:57 pm
Understandable. So is there a way to tell SQL Server to close the file handles after it's done validating all the databases, until someone actually logs in?
August 14, 2013 at 4:04 pm
No, but there's a way to not open the database at all until someone logs in. It's usually a bad idea.
Why? What problem is this behaviour causing?
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
August 14, 2013 at 4:07 pm
ztoddw (8/14/2013)
So is there a way to tell SQL Server to close the file handles after it's done validating all the databases, until someone actually logs in?
You can set the autoclose option for the database. As Gail indicates, it may not always be a good thing to do, but if you have a server with many, rarely-used, databases, it may make sense.
Note that starting up a database is fairly expensive, so incorrectly used, autoclose can be a big pain.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 14, 2013 at 4:10 pm
Erland Sommarskog (8/14/2013)
if you have a server with many, rarely-used, databases, it may make sense.
Or a sever with a few heavily used databases and a few very seldom used databases (autoclose only going on the seldom used databases of course)
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
August 14, 2013 at 6:00 pm
It turns out in our case that we don't even need that particular Sql server anymore, so I just disabled the service & set it to manual startup- our problem was actually running out of disk space, so at first I was just looking to delete these databases that we don't need anymore, and just wanted to be able to do it without having to log in to the sql server.
August 15, 2013 at 2:40 am
To delete databases you would always log into SQL and do the delete from inside SQL Server.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply