Hi,
Im managing our companys database which has approx 250 tables in it.
It is a design i have inherited from the people who originally created it, and i'm just having a look at all the foreign key relationships in it.
The actually have not defined many of these relationships. I'm just working through it now and placing these in now.
My question is this: Being such a complex database, can you have too many relationship?
For example, columns 'product_ID','Customer_ID', 'Branch_ID', and 'User_ID' occur in around 50 tables each.
If I enforce all these relationships, is it going to have a negative impact on the database? Is there any other factors i should consider? Is there a limit to how many relationships you should enforce?
Any help would be appreciated.
Josh DolanIMO it would really depend on who is access the database and how...
Sometime enforcing foreign key contraints is going to cause more problems then they are going to solve... but it should be evaluated on a case per case basis...|||I don't have any problem with enforcing all the constraints i want to implement. I would rather deal with the problems that it may occur and have them implemented than nothing at all.
I'm just thinking from the performance and database design angle.
Is it going to degrade performance that much? And, it is good database design to enforce most if not all foreign keys?
Thanks,
Josh|||Providing the foriegn key contraints are valid then IMO you can do no harm in implementing them providing you are happy to deal with possible issues.
Performace may be affected but not to any real extent IMO.|||You don't have too many relationships. They are necessary to maintain the relational integrity of your data, and if you remove them and allow inconsistencies to develop in your data then the extra coding and admin you will need to do to account for the errors WILL slow down your system.
It is more likely that you have too many tables, not too many relationships. Unfortunately, if there is already an interface and/or reporting structure developed around this schema, you are probably stuck with it.
The next time your company is considering developing an important database, advise them to hire a professional database designer to help them. A database is as important to an application as a foundation is important to a house. When I had my home built, I didn't go buy a book at Home Depot and then start pouring concrete myself. I hired professionals.
blindman
Showing posts with label inherited. Show all posts
Showing posts with label inherited. Show all posts
Saturday, February 25, 2012
Tuesday, February 14, 2012
Database option "autoclose" is set. Why?
I have inherited a 2000 server with a database with "autoclose" set to
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
MichaelYes, it should be off.
http://groups-beta.google.com/groups?hl=en&q=group%3A*sqlserver*+should+autoclose
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael|||Thanks for your comments. The database in question is the unfortunate target
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft Tech Support offered little on this) and I was wondering if
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:
> Yes, it should be off.
> http://groups-beta.google.com/groups?hl=en&q=group%3A*sqlserver*+should+autoclose
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> > I have inherited a 2000 server with a database with "autoclose" set to
> > "true". It appears to fill the log with "Starting up database dbname"
> > messages. A search of MSDN yielded no explanation as to what this setting
> > really does or why one would wish to set it on. I am tempted to set it
> off.
> >
> > Any wisdom?
> >
> > Michael
>
>|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft Tech Support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
> > Yes, it should be off.
> >
http://groups-beta.google.com/groups?hl=en&q=group%3A*sqlserver*+should+autoclose
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Snake" <Snake@.discussions.microsoft.com> wrote in message
> > news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> > > I have inherited a 2000 server with a database with "autoclose" set to
> > > "true". It appears to fill the log with "Starting up database dbname"
> > > messages. A search of MSDN yielded no explanation as to what this
setting
> > > really does or why one would wish to set it on. I am tempted to set
it
> > off.
> > >
> > > Any wisdom?
> > >
> > > Michael
> >
> >
> >|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
MichaelYes, it should be off.
http://groups-beta.google.com/groups?hl=en&q=group%3A*sqlserver*+should+autoclose
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael|||Thanks for your comments. The database in question is the unfortunate target
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft Tech Support offered little on this) and I was wondering if
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:
> Yes, it should be off.
> http://groups-beta.google.com/groups?hl=en&q=group%3A*sqlserver*+should+autoclose
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> > I have inherited a 2000 server with a database with "autoclose" set to
> > "true". It appears to fill the log with "Starting up database dbname"
> > messages. A search of MSDN yielded no explanation as to what this setting
> > really does or why one would wish to set it on. I am tempted to set it
> off.
> >
> > Any wisdom?
> >
> > Michael
>
>|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft Tech Support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
> > Yes, it should be off.
> >
http://groups-beta.google.com/groups?hl=en&q=group%3A*sqlserver*+should+autoclose
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Snake" <Snake@.discussions.microsoft.com> wrote in message
> > news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> > > I have inherited a 2000 server with a database with "autoclose" set to
> > > "true". It appears to fill the log with "Starting up database dbname"
> > > messages. A search of MSDN yielded no explanation as to what this
setting
> > > really does or why one would wish to set it on. I am tempted to set
it
> > off.
> > >
> > > Any wisdom?
> > >
> > > Michael
> >
> >
> >|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Database option "autoclose" is set. Why?
I have inherited a 2000 server with a database with "autoclose" set to
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
Michael
Yes, it should be off.
http://groups-beta.google.com/groups...ld+autoclos e
http://www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael
|||Thanks for your comments. The database in question is the unfortunate target
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft Tech Support offered little on this) and I was wondering if
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:
> Yes, it should be off.
> http://groups-beta.google.com/groups...ld+autoclos e
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> off.
>
>
|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft Tech Support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
[vbcol=seagreen]
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
http://groups-beta.google.com/groups...ld+autoclos e[vbcol=seagreen]
setting[vbcol=seagreen]
it[vbcol=seagreen]
|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
Michael
Yes, it should be off.
http://groups-beta.google.com/groups...ld+autoclos e
http://www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael
|||Thanks for your comments. The database in question is the unfortunate target
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft Tech Support offered little on this) and I was wondering if
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:
> Yes, it should be off.
> http://groups-beta.google.com/groups...ld+autoclos e
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> off.
>
>
|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft Tech Support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
[vbcol=seagreen]
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
http://groups-beta.google.com/groups...ld+autoclos e[vbcol=seagreen]
setting[vbcol=seagreen]
it[vbcol=seagreen]
|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Database option "autoclose" is set. Why?
I have inherited a 2000 server with a database with "autoclose" set to
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
MichaelYes, it should be off.
http://groups-beta.google.com/group...www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael|||Thanks for your comments. The database in question is the unfortunate targe
t
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft tech support offered little on this) and I was wondering i
f
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:
> Yes, it should be off.
> http://groups-beta.google.com/group...toclos
e
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> off.
>
>|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft tech support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
[vbcol=seagreen]
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
>
http://groups-beta.google.com/group...hould+autoclose[vbcol=seag
reen]
setting[vbcol=seagreen]
it[vbcol=seagreen]|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.
4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
MichaelYes, it should be off.
http://groups-beta.google.com/group...www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael|||Thanks for your comments. The database in question is the unfortunate targe
t
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft tech support offered little on this) and I was wondering i
f
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:
> Yes, it should be off.
> http://groups-beta.google.com/group...toclos
e
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> off.
>
>|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft tech support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
[vbcol=seagreen]
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
>
http://groups-beta.google.com/group...hould+autoclose[vbcol=seag
reen]
setting[vbcol=seagreen]
it[vbcol=seagreen]|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.
4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:
>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)