Hope fully there is someone who has done a similar stupid thing! We have got
ourselves into a bit of trouble when MDAC 2.7 was accidentally (?) installed
onto our Windows NT Cluster wich still runs SQL 7.0.
The result was that the SQL Agent stopped working. However the 2
applications that access the database running on the same cluster are all
working 100% and all client application accessing the database are working
correctly.
The solution offered by Microsoft in KB 820754 is that we should either:
- uncluster SQL
- re-install the sytem from scratch
- Install SQL 2000
Neither of these options are very appealing at this point as the system is
due for a major upgrade to Windows 2003/SQL 2000 in the near future (2-6
month)
Has anyone had a similar problem and how was the problem solved ? What risk
would we have if we continued to run like this and just removed the SQL Agent
resource. Currently we use the SQL Agent to schedule a few database
maintenace tasks which could easily be done manually.
Andrew
You could try to use the MDAC rollback functionality to recover the 2.5
installation. You could also try to reinstall 2.5. Otherwise, you are
pretty much as how the KC describes it, leave it alone and do without or do
some sort of rebuild.
Sincerely,
Anthony Thomas
"Andrew Caldwell" <AndrewCaldwell@.discussions.microsoft.com> wrote in
message news:D721101F-CEDB-4E01-9AF3-1F79882AD1F2@.microsoft.com...
Hope fully there is someone who has done a similar stupid thing! We have
got
ourselves into a bit of trouble when MDAC 2.7 was accidentally (?) installed
onto our Windows NT Cluster wich still runs SQL 7.0.
The result was that the SQL Agent stopped working. However the 2
applications that access the database running on the same cluster are all
working 100% and all client application accessing the database are working
correctly.
The solution offered by Microsoft in KB 820754 is that we should either:
- uncluster SQL
- re-install the sytem from scratch
- Install SQL 2000
Neither of these options are very appealing at this point as the system is
due for a major upgrade to Windows 2003/SQL 2000 in the near future (2-6
month)
Has anyone had a similar problem and how was the problem solved ? What risk
would we have if we continued to run like this and just removed the SQL
Agent
resource. Currently we use the SQL Agent to schedule a few database
maintenace tasks which could easily be done manually.
Andrew
|||Thanks
We found a way to rollback the MDAC to 2.5 which seems to have worked.
Andrew
Andrew
"Anthony Thomas" wrote:
> You could try to use the MDAC rollback functionality to recover the 2.5
> installation. You could also try to reinstall 2.5. Otherwise, you are
> pretty much as how the KC describes it, leave it alone and do without or do
> some sort of rebuild.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Andrew Caldwell" <AndrewCaldwell@.discussions.microsoft.com> wrote in
> message news:D721101F-CEDB-4E01-9AF3-1F79882AD1F2@.microsoft.com...
> Hope fully there is someone who has done a similar stupid thing! We have
> got
> ourselves into a bit of trouble when MDAC 2.7 was accidentally (?) installed
> onto our Windows NT Cluster wich still runs SQL 7.0.
> The result was that the SQL Agent stopped working. However the 2
> applications that access the database running on the same cluster are all
> working 100% and all client application accessing the database are working
> correctly.
> The solution offered by Microsoft in KB 820754 is that we should either:
> - uncluster SQL
> - re-install the sytem from scratch
> - Install SQL 2000
> Neither of these options are very appealing at this point as the system is
> due for a major upgrade to Windows 2003/SQL 2000 in the near future (2-6
> month)
> Has anyone had a similar problem and how was the problem solved ? What risk
> would we have if we continued to run like this and just removed the SQL
> Agent
> resource. Currently we use the SQL Agent to schedule a few database
> maintenace tasks which could easily be done manually.
> --
> Andrew
>
>
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Maximum value for Unique ID
Hi,
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:
> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>>Hi,
>>Am a newbie to sql server so apologies if this question seems a bit
>>simple but here goes anyway. All our tables are defined with a unique
>>id column with the identity attribute. What is the maximum value for
>>this identity column and what happens if this maximum value is reached?
>>
>>Many thanks in advance
>>Lee.
>
>|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>> It depends what type the column was declared as. IDENTITY is a property,
>> not a type. There is an underlying column type - it could be an integer
>> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
>> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
>> range
>> of the declared type, you will get an overflow error.
>> SK
>>
>> "monkey" <monkey@.email.com> wrote in message
>> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>> Hi,
>> Am a newbie to sql server so apologies if this question seems a bit
>> simple but here goes anyway. All our tables are defined with a unique
>> id column with the identity attribute. What is the maximum value for
>> this identity column and what happens if this maximum value is reached?
>>
>> Many thanks in advance
>> Lee.
>>
>>
>
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:
> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>>Hi,
>>Am a newbie to sql server so apologies if this question seems a bit
>>simple but here goes anyway. All our tables are defined with a unique
>>id column with the identity attribute. What is the maximum value for
>>this identity column and what happens if this maximum value is reached?
>>
>>Many thanks in advance
>>Lee.
>
>|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>> It depends what type the column was declared as. IDENTITY is a property,
>> not a type. There is an underlying column type - it could be an integer
>> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
>> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
>> range
>> of the declared type, you will get an overflow error.
>> SK
>>
>> "monkey" <monkey@.email.com> wrote in message
>> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>> Hi,
>> Am a newbie to sql server so apologies if this question seems a bit
>> simple but here goes anyway. All our tables are defined with a unique
>> id column with the identity attribute. What is the maximum value for
>> this identity column and what happens if this maximum value is reached?
>>
>> Many thanks in advance
>> Lee.
>>
>>
>
Maximum SQL 2005 Database Size
Hello! I'm trying to figure out what the ultimate size limitation for a SQL 2005 Enterprise server is. This document is helpful but I'm a bit confused:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
In the document, it says that the maximum database size is 524,258 terabytes; however, it also says that the maximum data file size--which I assume is the .MDF file--is 16 terabytes. My question is, how can you create a 524,258 TB database if the maximum file size 16 TB?
Dumb question, I'm sure...please enlighten me!
Norm
A database have minimum a data file and a log file so
(I quote from that document)
File size (data)
16 terabytes
16 terabytes
File size (log)
2 terabytes
2 terabytes
mean that you can have (528,258-2):16=33016 data files (id est 1 mdf file and 33015 ndf files) and 1 log file.
Or other combination of data and log files that totalize 528,258 terabytes.
|||I still don't understand. If I create a database, call it NewJack, the system automatically creates an .MDF file named newjack.mdf. If that file is limited to 16TB, then my NewJack database can only be 16TB...right?|||In Management Studio right click on your database go to properties then go to files and file groups, these two properties let you add and remove MDF, LDF and NDF as needed. That is you can place a set of tables in a file group and the indexes in separate file groups try the link below for how you can use the file groups to separate fast growing databases into smaller manageable file groups.
http://msdn2.microsoft.com/en-us/library/ms179316.aspx
Monday, March 12, 2012
Maximum number of Tables - is 4^15 big a number?
Hello people,
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?
The maximum number of objects in an SQL Server database is 2^31-1,
or 2.147.483.647 and thus 4^15. "Objects" in this context are tables,
stored procedures, views etc. Note that this answer applies specifically
to MS SQL Server. If you with "SQL Database" means about any RDBMS, beware
that the answer is likely to be different for each product.
In addition to Erland's answer: there might be some practical
limitations.
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?
I mean, is it possible at all? There might be a question of where
anyone would want so many tables, but i'm a bioinformatics guy and I'm
trying to deal with genomic sequences and was coming up with a new
algorithm, where the only limit is the number of tables I can put into
a Database.
So, can you please advise if its possible to put in so many tables into
a SQL database? Or is the Bekerley DB better?(ramraj@.gmail.com) writes:
Quote:
Originally Posted by
Hello people,
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?
>
I mean, is it possible at all? There might be a question of where
anyone would want so many tables, but i'm a bioinformatics guy and I'm
trying to deal with genomic sequences and was coming up with a new
algorithm, where the only limit is the number of tables I can put into
a Database.
>
So, can you please advise if its possible to put in so many tables into
a SQL database? Or is the Bekerley DB better?
The maximum number of objects in an SQL Server database is 2^31-1,
or 2.147.483.647 and thus 4^15. "Objects" in this context are tables,
stored procedures, views etc. Note that this answer applies specifically
to MS SQL Server. If you with "SQL Database" means about any RDBMS, beware
that the answer is likely to be different for each product.
I don't really know why you think you need this many tables. But if the
tables all are to have the set of columns, then you only need one table,
but with more columns to keep the different portions of the table apart.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ramraj@.gmail.com wrote:
Quote:
Originally Posted by
>
Hello people,
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?
>
I mean, is it possible at all? There might be a question of where
anyone would want so many tables, but i'm a bioinformatics guy and I'm
trying to deal with genomic sequences and was coming up with a new
algorithm, where the only limit is the number of tables I can put into
a Database.
>
So, can you please advise if its possible to put in so many tables into
a SQL database? Or is the Bekerley DB better?
In addition to Erland's answer: there might be some practical
limitations.
The smallest possible table, without indexes and only one row will
reserve 16 kilobytes. If you were to create 4^15 tables (without
indexes, primary key and unique constraint), and populate each table
with only 1 row, then you would need 16 TB. If all these tables have a
primary key constraint, that would be 24 TB. Also, if only a few bytes
per 8KB-page are used, then the Buffer Pool cannot be managed
efficiently, with the risk of poor performance.
Gert-Jan
P.S. Yes, it does sound crazy. If at all possible, then don't do it...|||... and if access to the tables in not heavily skewed there will also be
no opportunity for the SQL Compiler to cache query plans.
Death by compile...
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html
Maximum number of tables - 4^15 too big?
Hello people,
I might sound a little bit crazy, but is there any possibility that you can incorporate 1,073,741,824 tables into a SQL Database?
I mean, is it possible at all? There might be a question of where anyone would want so many tables, but i'm a bioinformatics guy and I'm trying to deal with genomic sequences and was coming up with a new algorithm, where the only limit is the number of tables I can put into a Database.
So, can you please advise if its possible to put in so many tables into a SQL database? Or is the Bekerley DB better?
SQL Server DB forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1) might be better for such questions.
According to http://msdn2.microsoft.com/en-us/library/ms143432.aspx, there is a limit of 2^31 or 2,147,483,647 on the total number of objects in the database, including tables, views, etc. It is less than your requirement, but close.
I would discuss this design however at the forum above - I suspect the problem can be solved more effective in a different way.
Friday, March 9, 2012
Maximum Memory Settings for Analysis 2005
Dear Anyone,
We have a live machine with an Analysis Services 2005 (32 bit) installed. The machine has been running for a couple of months. They recently upgraded to 8GB (formerly 4GB). It seems that the 32 bit version of MSAS 2005 does not support 8GB or memory. Can anyone please suggest memory settings to maximize the use of the 8GB of memory in the server? Or does anyone know of the maximum memory setup that we can give Analysis Services 2005?
Thanks,
Joseph
AS2K5 is not AWE-aware. As such, its capabilities are based on the underlying OS' virtural address space limitations. The general limits are (32-bit):
2GB -- with an OS that doesn't support the /3GB switch in boot.ini
3GB -- with /3GB enabled in the OS
4GB -- running on an x64 system under WOW
Otherwise, to go more, you need to run in native 64-bit using either x64 or ia64 hardware.
_-_-_ Dave
Maximum Memory Configurations
What is the maximum amount of memory SQL2K can address per instance using the following configuration
WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
AWE Enabled
/PAE switch in boot.ini
Lock pages in memory enabled for MSSQL Service Account.
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Server but has no mention of max memory allowed.
It is my understanding that each instance will be able to use all of its =
addressable RAM. In your scenario you could configure three instances =
to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =
would allow 4GB of free memory for the OS to work with. Of course you =
could install more instances, but you would want to limit the amount of =
RAM each uses so that you do not run into problems by trying to allocate =
more memory than you have in the server.=20
--=20
Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =
news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =
using the following configuration
>=20
> WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
>=20
> The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =
Advanced Server but has no mention of max memory allowed.
WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
AWE Enabled
/PAE switch in boot.ini
Lock pages in memory enabled for MSSQL Service Account.
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Server but has no mention of max memory allowed.
It is my understanding that each instance will be able to use all of its =
addressable RAM. In your scenario you could configure three instances =
to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =
would allow 4GB of free memory for the OS to work with. Of course you =
could install more instances, but you would want to limit the amount of =
RAM each uses so that you do not run into problems by trying to allocate =
more memory than you have in the server.=20
--=20
Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =
news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =
using the following configuration
>=20
> WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
>=20
> The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =
Advanced Server but has no mention of max memory allowed.
Maximum Memory Configurations
What is the maximum amount of memory SQL2K can address per instance using the following configuratio
WIN 2003 32 bit Enterprise Edition OS with 32 GB RA
SQL2K SP3 32 bit Enterprise Edition w/ hot fixe
AWE Enable
/PAE switch in boot.in
Lock pages in memory enabled for MSSQL Service Account
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Server but has no mention of max memory allowed.It is my understanding that each instance will be able to use all of its =addressable RAM. In your scenario you could configure three instances =to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =would allow 4GB of free memory for the OS to work with. Of course you =could install more instances, but you would want to limit the amount of =RAM each uses so that you do not run into problems by trying to allocate =more memory than you have in the server.
-- Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =using the following configuration
> > WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
> > The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =Advanced Server but has no mention of max memory allowed.
WIN 2003 32 bit Enterprise Edition OS with 32 GB RA
SQL2K SP3 32 bit Enterprise Edition w/ hot fixe
AWE Enable
/PAE switch in boot.in
Lock pages in memory enabled for MSSQL Service Account
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Server but has no mention of max memory allowed.It is my understanding that each instance will be able to use all of its =addressable RAM. In your scenario you could configure three instances =to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =would allow 4GB of free memory for the OS to work with. Of course you =could install more instances, but you would want to limit the amount of =RAM each uses so that you do not run into problems by trying to allocate =more memory than you have in the server.
-- Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =using the following configuration
> > WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
> > The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =Advanced Server but has no mention of max memory allowed.
Maximum Memory Configurations
What is the maximum amount of memory SQL2K can address per instance using th
e following configuration
WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
AWE Enabled
/PAE switch in boot.ini
Lock pages in memory enabled for MSSQL Service Account.
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Se
rver but has no mention of max memory allowed.It is my understanding that each instance will be able to use all of its =
addressable RAM. In your scenario you could configure three instances =
to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =
would allow 4GB of free memory for the OS to work with. Of course you =
could install more instances, but you would want to limit the amount of =
RAM each uses so that you do not run into problems by trying to allocate =
more memory than you have in the server.=20
--=20
Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =
news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =
using the following configuration
>=20
> WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
>=20
> The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =
Advanced Server but has no mention of max memory allowed.
e following configuration
WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
AWE Enabled
/PAE switch in boot.ini
Lock pages in memory enabled for MSSQL Service Account.
The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K Advanced Se
rver but has no mention of max memory allowed.It is my understanding that each instance will be able to use all of its =
addressable RAM. In your scenario you could configure three instances =
to use 8GB of RAM and you could have a fourth using 4GB of RAM. That =
would allow 4GB of free memory for the OS to work with. Of course you =
could install more instances, but you would want to limit the amount of =
RAM each uses so that you do not run into problems by trying to allocate =
more memory than you have in the server.=20
--=20
Keith
"Douglas Luke" <anonymous@.discussions.microsoft.com> wrote in message =
news:D6F9605B-497D-49C6-9BED-FF0B19B83BC9@.microsoft.com...
> What is the maximum amount of memory SQL2K can address per instance =
using the following configuration
>=20
> WIN 2003 32 bit Enterprise Edition OS with 32 GB RAM
> SQL2K SP3 32 bit Enterprise Edition w/ hot fixes
> AWE Enabled
> /PAE switch in boot.ini
> Lock pages in memory enabled for MSSQL Service Account.
>=20
> The BOL says SQL2K 32 bit can address up to 8 GB of RAM on Win2K =
Advanced Server but has no mention of max memory allowed.
Wednesday, March 7, 2012
maximum allowed size for any data type
Hi,
I am a bit of a novice so please excuse any blinding ignorance
I recieve the following error using mssqlserver v7_1:
the size (32672) given to the type 'varchar' exceeds the maximum allowed for any data type
so how can I find out what this maximum is?search books online for data types
I am a bit of a novice so please excuse any blinding ignorance
I recieve the following error using mssqlserver v7_1:
the size (32672) given to the type 'varchar' exceeds the maximum allowed for any data type
so how can I find out what this maximum is?search books online for data types
Saturday, February 25, 2012
Maximize Sql Server 2005 Memory.
Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
--
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
--
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
--
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
--
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan
MAXFILE Size won't Change
SQL Server 2005 64 Bit Enterprise Edition.
However
When I run the the following command I get command completed successfully.
ALTER DATABASE Apollo_Replication
MODIFY FILE (NAME = Apollo_Replication_log,
MAXSIZE = 'UNLIMITED')
However
when I look at the database properties the file growth is still
restricted to 2 GB. I even tried to edit the property through
SSMS. Same results...It looks like it saves ok but when I go back
in... no change.
The only thing unique about the db is it is the subscriber from an Oracle publisher.
Has anyone seen this little feature before?Are you sure it is 2GB and not 2TB? The architectural limit for each log file is 2TB.|||oops it is 2 TB. But why can't I change the properties to unlimited file growth? My current size is 31 GB. I am trying to shrink the file back down to a reasonable size and change the properties.|||
UNLIMITED is never really unlimited. For log files it is 2TB. For data files it is actually 16TB. Its just a reporting issue on what the UI or report considers to be "unlimited."
Monday, February 20, 2012
Max() query problems
I have a bit of code that checks through all orders and looks for all
orderlines where the status is either closed, completed or cancelled, but
where at least one is cancelled, and it sets the status of the order to
closed.
I also have an equivalent script which sets the order status to complete
where all order lines are completed or cancelled but none are closed.
I want to modify both scripts to update the Orders table by setting the
LastModified field to the most recent LastModified value from the
Orderdetail table (eg Max(d.LastModified)). This is where I have my
problem - I havent found the right code to make this work.
Here is my code snippet:
update orders
set status = 'Closed'
where orderid in
(select
o.orderid
from
orders as o
inner join
orderdetail as d
on o.orderid = d.orderid
group by
o.orderid, o.Status
having
sum(case when d.status in ('Complete', 'Cancelled', 'Failed Delivery',
'Closed')
then 1 else 0 end) = count(*)
and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
and (o.status <> 'Complete' and o.status <> 'Closed'))
Any ideas?
Thanks in advance...
CJM
--
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]CJM (cjmnews04@.newsgroup.nospam) writes:
> I want to modify both scripts to update the Orders table by setting the
> LastModified field to the most recent LastModified value from the
> Orderdetail table (eg Max(d.LastModified)). This is where I have my
> problem - I havent found the right code to make this work.
> Here is my code snippet:
> update orders
> set status = 'Closed'
> where orderid in
> (select
> o.orderid
> from
> orders as o
> inner join
> orderdetail as d
> on o.orderid = d.orderid
> group by
> o.orderid, o.Status
> having
> sum(case when d.status in ('Complete', 'Cancelled', 'Failed Delivery',
> 'Closed')
> then 1 else 0 end) = count(*)
> and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
> and (o.status <> 'Complete' and o.status <> 'Closed'))
Without table definitions and that, it will have to be a bit of
guesswork:
update orders
set status = 'Closed',
closedate = od.MaxLastModified
from orders o
JOIN (select od.orderid, MaxLastModified = MAX(od.LastModified)
from orders as o
inner join orderdetail as d on o.orderid = d.orderid
group by o.orderid, o.Status
having
sum(case when d.status in ('Complete', 'Cancelled',
'Failed Delivery', 'Closed')
then 1 else 0 end) = count(*)
and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
and (o.status <> 'Complete' and o.status <> 'Closed')) AS od
ON o.orderid = od.orderid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715F4136B20Yazorman@.127.0.0.1...
> CJM (cjmnews04@.newsgroup.nospam) writes:
> Without table definitions and that, it will have to be a bit of
> guesswork:
> update orders
> set status = 'Closed',
> closedate = od.MaxLastModified
> from orders o
> JOIN (select od.orderid, MaxLastModified = MAX(od.LastModified)
> from orders as o
> inner join orderdetail as d on o.orderid = d.orderid
> group by o.orderid, o.Status
> having
> sum(case when d.status in ('Complete', 'Cancelled',
> 'Failed Delivery', 'Closed')
> then 1 else 0 end) = count(*)
> and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
> and (o.status <> 'Complete' and o.status <> 'Closed')) AS od
> ON o.orderid = od.orderid
>
Erland,
Sorry, I omitted the DDl for speed - I figured it was simple enough to
figure out. You got it right anyway.
Yes that Join did the trick for me.
Thanks for your help
Chris
orderlines where the status is either closed, completed or cancelled, but
where at least one is cancelled, and it sets the status of the order to
closed.
I also have an equivalent script which sets the order status to complete
where all order lines are completed or cancelled but none are closed.
I want to modify both scripts to update the Orders table by setting the
LastModified field to the most recent LastModified value from the
Orderdetail table (eg Max(d.LastModified)). This is where I have my
problem - I havent found the right code to make this work.
Here is my code snippet:
update orders
set status = 'Closed'
where orderid in
(select
o.orderid
from
orders as o
inner join
orderdetail as d
on o.orderid = d.orderid
group by
o.orderid, o.Status
having
sum(case when d.status in ('Complete', 'Cancelled', 'Failed Delivery',
'Closed')
then 1 else 0 end) = count(*)
and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
and (o.status <> 'Complete' and o.status <> 'Closed'))
Any ideas?
Thanks in advance...
CJM
--
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]CJM (cjmnews04@.newsgroup.nospam) writes:
> I want to modify both scripts to update the Orders table by setting the
> LastModified field to the most recent LastModified value from the
> Orderdetail table (eg Max(d.LastModified)). This is where I have my
> problem - I havent found the right code to make this work.
> Here is my code snippet:
> update orders
> set status = 'Closed'
> where orderid in
> (select
> o.orderid
> from
> orders as o
> inner join
> orderdetail as d
> on o.orderid = d.orderid
> group by
> o.orderid, o.Status
> having
> sum(case when d.status in ('Complete', 'Cancelled', 'Failed Delivery',
> 'Closed')
> then 1 else 0 end) = count(*)
> and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
> and (o.status <> 'Complete' and o.status <> 'Closed'))
Without table definitions and that, it will have to be a bit of
guesswork:
update orders
set status = 'Closed',
closedate = od.MaxLastModified
from orders o
JOIN (select od.orderid, MaxLastModified = MAX(od.LastModified)
from orders as o
inner join orderdetail as d on o.orderid = d.orderid
group by o.orderid, o.Status
having
sum(case when d.status in ('Complete', 'Cancelled',
'Failed Delivery', 'Closed')
then 1 else 0 end) = count(*)
and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
and (o.status <> 'Complete' and o.status <> 'Closed')) AS od
ON o.orderid = od.orderid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715F4136B20Yazorman@.127.0.0.1...
> CJM (cjmnews04@.newsgroup.nospam) writes:
> Without table definitions and that, it will have to be a bit of
> guesswork:
> update orders
> set status = 'Closed',
> closedate = od.MaxLastModified
> from orders o
> JOIN (select od.orderid, MaxLastModified = MAX(od.LastModified)
> from orders as o
> inner join orderdetail as d on o.orderid = d.orderid
> group by o.orderid, o.Status
> having
> sum(case when d.status in ('Complete', 'Cancelled',
> 'Failed Delivery', 'Closed')
> then 1 else 0 end) = count(*)
> and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
> and (o.status <> 'Complete' and o.status <> 'Closed')) AS od
> ON o.orderid = od.orderid
>
Erland,
Sorry, I omitted the DDl for speed - I figured it was simple enough to
figure out. You got it right anyway.
Yes that Join did the trick for me.
Thanks for your help
Chris
Max Worker Threads and User Connections
Hi
we run SQL 2005 Enterprise on W2003 Enterprise 32 bit on 8 dual core Xeon 7020 with 32Gb RAM.
The max worker thread settings is 0 now. But 3d party consultancy which monitors our servers on the POP is suggesting to make this figure to be 1200. They said - there are 1000 connections at peak time. I am going to check if it is correct...
But my question is - is it proper thing to do to set worker thread number that high? (BOL says - MS doesnt recommend more than 1024 on 32 bit systems)
Is it that useful to match number of connections with number of threads?
Thank you
P.S. that consultancy let us down several times. I dont trust then at all.. I need a real reason to change such a thing
Well, I probably wouldn't recommend it generally speaking.
First off, just because there are 1000 connections at a given time, definately doesn't imply that there are 1000 concurrent processing requests...connections are frequently idle in many types of systems processing 'think' time by end users, or just sitting idle in a connection pool (.NET or otherwise). If you are using connection pooling, I'm sure you'll find that many of your connections are idle a fair amount of the time...
In a 32-bit system, on startup Sql server will reserve enough memory to load it's own binaries in, then some to handle 8kb and greater memory requests (typically 128mb by default, but can be modified using a startup switch), then enough memory to be able to allocate stacks for the total number of threads possible to use, which is determined by your 'max worker thread' setting.
Each thread stack is uses about .5mb of memory, which means using a setting of 1200 will result in a reserved space of about 600mb for just stack allocation on a 32bit box. Bear in mind that on a 32bit box of this size (assuming you aren't using a /3gb or /USERVA switch...if you are you ain't going to get to use most of your physical memory), the maximum VAS for a user-mode process like Sql server is 2gb, so stack space is going to use over a 1/4 of that space alone.
By default, in Sql 2005 on a 32bit box, Sql Server will dynamically handle threading using a simple algorithm where with 4 procs or less, 256 max threads will be used, then an additional 8 threads per CPU...in the case of a 16-way machine (yours is an 8-way dual core, 16 schedulers), Sql will use a max thread dynamic setting of 352 on that machine. If you bump that to 1200, your allocating about 850 more than the dyamic configuration would.
Given what I mentioned earlier about the fact that many connections are typically idle a fair amount of time, connections will pool the thread use at the OS level, and typically the dynamic default configuration is hightly recommend.
I'd ask the consultants to provide you extensive documentation on why they think it would be beneficial...typically, recommending something like this would take some extensive testing, and intimate knowledge of a system...
HTH,
|||Thank you Chad,
I have same ideas but just needed some authoritative information to argue with the consulters.
I really hate 'solutions' made from thin air. I refused to do any changes and requested concrete measures have been made before they decided on the advice. I think they have none.
Might be there is a chance they are right (that advice was given to resolve issues I addressed in my previous topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436762&SiteID=1). Fortunately these accidents havent repeated since I went through the application and put some NOLOCKs into critical places..
Subscribe to:
Posts (Atom)