December 30, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/autoclosefordatabases.asp
February 10, 2003 at 6:00 am
Overall, I agree with your recommendation, for production databases. I do have a scenario where I use autclose, for development workstations.
We often run SQL Server on our desktop (with MSDN license) for application development. We can test and try new changes to database, have our own data for testing, and run SQL Compare to generate delta scripts to update the staging server, then production servers. We setup these local databases with simple logging (to keep transaction logs small) and autoclose (to close the data file so that remote ArcServe backups work).
The only time I've used autoclose for production is for a small application database, used frequently during 9-5, but only desired to have a once-a-day backup. Again, the Arcserve remote works well to just copy the mdf during the file backup at nite.
Mark
February 10, 2003 at 7:27 am
Interesting response to a thought provoking article, Mark. The only thing I'd question about using it in such a scenario is what would happen if the app was left open, or the user forgot to disconnect from the database before heading home? If the connection's kept open as a result, then the database won't autoclose, so the db engine will hold open the .mdf, which will prevent ArcServe from performing the backup (unless you've got the open file agent, in which case there would be no need to auto-close it anyway).
Would it not be better to schedule a regular backup, so that you know that the backup file is committed to tape on a regular basis?
Disk space concerns aside (though Desktop/MSDE DBs are limited to 2Gb anyway), can't see why this wouldn't be a more robust way of handling it than using auto close.
Edited by - jonreade on 02/10/2003 07:37:02 AM
Jon
February 10, 2003 at 9:47 am
Agree with the last post on that one. Better to setup a backup job. depending on AutoClose isn't a reliable option.
Steve Jones
February 12, 2003 at 8:46 pm
We have auto close turned on on desktop, development only copies of data. XP knocks the memory down to 7.5 meg when not in use. The connection time for the first user in is a little sluggish, but once you start messing with a potential change, you usually have QA open anyway and then the DB is open.
I totally agree that for any production DB, the auto close option should be off.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 18, 2003 at 2:38 pm
When I first upgraded some production servers from 6.5 to 7.0, either I made an error, or it was set as the default (I'm of course claiming the latter), all of my databases were set to autoclose. I was just starting my role as a DBA (from a programmer), and I was baffled. I couldn't figure out why these damn db's where taking sooo long to open in EM! From that point on, I have never used the autoclose option. If I have a local copy installed, I turn off the service when not in use.
February 23, 2003 at 11:26 am
quote:
The only thing I'd question about using it in such a scenario is what would happen if the app was left open, or the user forgot to disconnect from the database before heading home?Edited by - jonreade on 02/10/2003 07:37:02 AM
Sorry for the delay in responding. Forgot to mark the thread for notification.
The application is a 3-tier app, and only the middle-tier ActiveX DLL ever opens database connections, under MTS, and closes connections as soon as query is done. (I never like users having actual db connections to the database.)
MTS, of course, pools the db connections and leaves them open, so "opening" a new connection just reuses a connection from the pool.) So, after 10 minutes of inactivity, even if the user had left the application open, the connections are closed, then the database closes after its inactivity timeout (30 minutes? 60 minutes? I don't quite remember at the moment).
I whole heartedly agree with the "usual" practice. But in the this one, rare, extreme case, it makes practical sense. I would not recommend this for any database I've ever done anytime/anywhere else (in 15 years of doing client/server apps). But in this case, an exception to the standard accepted practice works well, works safely, and is easier to implement and maintain. This is a customer shop that does not have any DBAs, and only 2 overworked network admins that only have the time and experience to treat all servers as file servers. There was no way to get them to check a SQL job log for backup failures or problems.
Mark
February 24, 2003 at 1:23 am
Thought I'd chip in for what it's worth.
Just started here as DBA, I have a number of large databases 5Gb+ each.
One had the autoclose flag set. The only effect it seemed to have was to generate a large errorlog file. It didn't stop backups or adversley affect us in any way. It is switched off now, a 40Mb errorlog file is a sod to open.
February 24, 2003 at 10:57 am
Even with the middle tier app, I wouldn't have it set. There isn't a reason. The delay and ability to backup the file means that there is a chance the db will be unavailable to SQL Server IF another app grabs a handle to the db file.
I'll stand by my advice, no production database needs this set.
Steve Jones
March 11, 2003 at 10:14 am
This option should not be available for servers but it is quite useful for desktop applications using MSDE; in fact, MSDE databases have this set as the default. There is no need to eat up expensive resources on a user's workstation if they aren't even using the application associated with the database (Especially if you're on the third year of your lease and the machine isn't quite what it used to be)!
Another reason not to use it in the server environment is when the tape backup folks don't have their configuration right for your server, they could inadvertently try to backup files that are closed and, while the backup is taking place, the file is locked and SQL Server cannot open the database.
Bryant E. Byrd, MCDBA
SQL Server DBA/Systems Engineer
Intellithought, Inc.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply