Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > 39 char fields (5816 total characters)
>> What are the definitions of these columns? Are they
>> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>> > 9 tinyint fields (size = 9 * 1 = 9)
>> > 4 int fields (size = 4 * 4 = 16)
>> > 5 datetime fields (size = 5 * 8 = 40)
>> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
>> Are any of these NULLable?
>>
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> > calculate my row size.
> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Number char(20)
CUSTNMBR char(45)
VENDORID char(45)
Attn_Name char(30)
Contact_Name char(30)
ContactPhone char(20)
BusinessName char(30)
AddressLine1 char(30)
AddressLine2 char(30)
City char(25)
State char(3)
ZipCode char(10)
Phone char(20)
Fax char(20)
ICC_Number char(20)
FedID_Number char(15)
Contracted tinyint
Start_Date datetime
InsAgentName char(30)
InsAgentAddress1 char(30)
InsAgentAddress2 char(30)
InsAgentCity char(25)
InsAgentState char(3)
InsAgentZipCode char(10)
InsAgentPhone char(20)
InsAgentFax char(20)
InsAgentContact char(30)
CargoInsName char(28)
CargoInsAmount decimal(19, 5)
CargoInsExpirationDate datetime
AutoLibInsName char(30)
AutoLibInsAmount decimal(19, 5)
AutoLibInsExpirationDate datetime
Agent_YN tinyint
Broker_YN tinyint
Carrier_YN tinyint
Shipper_YN tinyint
Consignee_YN tinyint
BillTo_YN tinyint
CreatedDate datetime
CreatedUserID char(20)
Billing_Rate decimal(19, 5)
Billing_Rate_Code char(5)
Rating char(5)
Color char(20)
Flagged tinyint
FlagDate datetime
FlagUserID char(20)
FlagReason char(50)
Active tinyint
Notes char(3000)
PayCode char(5)
PayRateFlat decimal(19, 5)
PayRatePercent decimal(19, 5)
PayRateLoaded decimal(19, 5)
PayRateUnloaded decimal(19, 5)
DefaultSalesperson char(20)
Directions char(2000)
FSType char(10)
FSRateAmount decimal(19, 5)
Weight decimal(19, 5)
AgentPayAccount int
CarrierPayAccount int
DropPayAmount decimal(19, 5)
PickupPayAmount decimal(19, 5)
ISType char(10)
ISRateAmount decimal(19, 5)
Latitude int
Longitude int|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.
> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
> > all character fields are CHAR(####)
> > Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> >
> > Here is a list of the fields:
> >
> > Customer_Number char(20)
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A|||> I can create this table from scratch with the columns/sizes I listed
> above.
> The table is created with no problem. I have to up the size of one of my
> CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
I also strongly recommend you familiarize yourself with the reasons we have
CHAR and VARCHAR. CHAR(2000) is not exactly a great choice here.
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html
A|||Hi Matt
If you ALTER the length of fixed length columns, SQL Server may not reuse
the original space.
See my blog post on the subject:
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:9EF8C9DF-A8DE-406D-B1BA-1C67338D8FB3@.microsoft.com...
>I am trying to alter a table and getting the following error.
> Server: Msg 1701, Level 16, State 2, Line 1
> Creation of table 'CustomerMaster' failed because the row size would be
> 8508, including internal overhead. This exceeds the maximum allowable
> table
> row size, 8060.
> I can create a new table with the same fields, but not alter an existing
> one.
> I have calculated the maximum row size as only 6004 after the changes.
> Here
> is what I used to calculate.
> 69 columns
> 39 char fields (5816 total characters)
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Num_Cols = 69
> Fixed_Data_Size = 5989
> Num_Var_Cols = 0
> Max_Var_Size = 0
> Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
> Row_Size = 5981 + 0 + 11 + 4 = 6004
>|||On Feb 7, 2:13 pm, Matt Soukup <MattSou...@.discussions.microsoft.com>
wrote:
> Just forget it. SQL is being stupid.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
> I can create this table from scratch with the columns/sizes I listed above.
> The table is created with no problem. I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
> CREATE TABLE to fail with the same error.
Aaron asked you twice for the results of sp_help, and you've refused
to provide that, instead opting for the "SQL is being stupid"
response. WTF? If we can see the table structure, then we know
exactly what you're working with, and can provide a proper answer
instead of guessing.
Here's another suggestion that you can write off as "stupid" - think
about using VARCHAR instead of CHAR for your column definitions.
You're very likely wasting a ton of space with these fixed-length
2000+ column lengths. Read up on the differences between CHAR and
VARCHAR, hopefully the documentation isn't too "stupid".|||This may very well be it; I'm glad I cntinue to learn things from you Kalen.
But the OP stated:
"This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
Yet is able to increase the length of a CHAR(2000) column to CHAR(5000). I
would think that if previous column modifications had caused the row size
threshold to be within 10 bytes of the current row size, that just about
*any* column length extension would cause the problem. I guess having the
actual table structure from sp_help, so understanding the order of the
columns (and a history of modifications made) would help us answer the
question better. Better still would be the result of your query against
sys.partitions/columns/system_internals_partition_columns.
In any case, the last statement in your blog entry is probably the most
helpful to the OP, though Tracy and I have both suggested it to no avail:
"So be careful when using large datatypes, especially if you want to make
them fixed length instead of variable length."
The shortest path is likely to rebuild the table. But the Directions and
Notes columns should be VARCHAR, not CHAR.
A
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Matt
> If you ALTER the length of fixed length columns, SQL Server may not reuse
> the original space.
> See my blog post on the subject:
> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx|||I didn't read the whole thread in detail initially, I was just tossing this
out as a possibility.
However, now that I am going back and reading over the posts, I don't see
anywhere that he said he had already altered the table changing a column
from 2000 to 5000 bytes. He said he would have to replace a 2000 byte column
with a 5000 byte column to get the original CREATE TABLE to fail. Has he
indicated he has done other ALTERs?
Yes, it would be nice to get more details from the OP, including WHY he
thinks he needs fixed length columns. Otherwise we just can't know. I agree
Directions and Notes should be variable length.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OpFCoZ4SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> This may very well be it; I'm glad I cntinue to learn things from you
> Kalen.
> But the OP stated:
> "This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
> Yet is able to increase the length of a CHAR(2000) column to CHAR(5000).
> I would think that if previous column modifications had caused the row
> size threshold to be within 10 bytes of the current row size, that just
> about *any* column length extension would cause the problem. I guess
> having the actual table structure from sp_help, so understanding the order
> of the columns (and a history of modifications made) would help us answer
> the question better. Better still would be the result of your query
> against sys.partitions/columns/system_internals_partition_columns.
> In any case, the last statement in your blog entry is probably the most
> helpful to the OP, though Tracy and I have both suggested it to no avail:
> "So be careful when using large datatypes, especially if you want to make
> them fixed length instead of variable length."
> The shortest path is likely to rebuild the table. But the Directions and
> Notes columns should be VARCHAR, not CHAR.
> A
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi Matt
>> If you ALTER the length of fixed length columns, SQL Server may not reuse
>> the original space.
>> See my blog post on the subject:
>> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
>|||> However, now that I am going back and reading over the posts, I don't see
> anywhere that he said he had already altered the table changing a column
> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
> column with a 5000 byte column to get the original CREATE TABLE to fail.
> Has he indicated he has done other ALTERs?
Here is what I am referring to:
I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
My interpretation was that he had done that. Maybe I'm wrong. It's
ambiguous.
A|||Yes, that is what I'm referring to, and he explicitly says it is the CREATE
table and not ALTER.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23UIpXh6SHHA.2228@.TK2MSFTNGP03.phx.gbl...
>> However, now that I am going back and reading over the posts, I don't see
>> anywhere that he said he had already altered the table changing a column
>> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
>> column with a 5000 byte column to get the original CREATE TABLE to fail.
>> Has he indicated he has done other ALTERs?
> Here is what I am referring to:
> I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
> My interpretation was that he had done that. Maybe I'm wrong. It's
> ambiguous.
> A
>|||> Yes, that is what I'm referring to, and he explicitly says it is the
> CREATE table and not ALTER.
Ahh. You should know by now that I lack the ability to read entire
sentences. :-)
And you would think the caps would make words stand out more, but for me it
obscures them... I tend to focus on the meat *between* the keywords...
A

Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one
.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?

> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidate
s
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Number char(20)
CUSTNMBR char(45)
VENDORID char(45)
Attn_Name char(30)
Contact_Name char(30)
ContactPhone char(20)
BusinessName char(30)
AddressLine1 char(30)
AddressLine2 char(30)
City char(25)
State char(3)
ZipCode char(10)
Phone char(20)
Fax char(20)
ICC_Number char(20)
FedID_Number char(15)
Contracted tinyint
Start_Date datetime
InsAgentName char(30)
InsAgentAddress1 char(30)
InsAgentAddress2 char(30)
InsAgentCity char(25)
InsAgentState char(3)
InsAgentZipCode char(10)
InsAgentPhone char(20)
InsAgentFax char(20)
InsAgentContact char(30)
CargoInsName char(28)
CargoInsAmount decimal(19, 5)
CargoInsExpirationDate datetime
AutoLibInsName char(30)
AutoLibInsAmount decimal(19, 5)
AutoLibInsExpirationDate datetime
Agent_YN tinyint
Broker_YN tinyint
Carrier_YN tinyint
Shipper_YN tinyint
Consignee_YN tinyint
BillTo_YN tinyint
CreatedDate datetime
CreatedUserID char(20)
Billing_Rate decimal(19, 5)
Billing_Rate_Code char(5)
Rating char(5)
Color char(20)
Flagged tinyint
FlagDate datetime
FlagUserID char(20)
FlagReason char(50)
Active tinyint
Notes char(3000)
PayCode char(5)
PayRateFlat decimal(19, 5)
PayRatePercent decimal(19, 5)
PayRateLoaded decimal(19, 5)
PayRateUnloaded decimal(19, 5)
DefaultSalesperson char(20)
Directions char(2000)
FSType char(10)
FSRateAmount decimal(19, 5)
Weight decimal(19, 5)
AgentPayAccount int
CarrierPayAccount int
DropPayAmount decimal(19, 5)
PickupPayAmount decimal(19, 5)
ISType char(10)
ISRateAmount decimal(19, 5)
Latitude int
Longitude int|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.

> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of column
s
> you provided below can't possibly be 8508 bytes unless (a) the column you'
re
> trying to add is a lot longer than you're letting on, (b) some of those da
ta
> types are not correct, or (c) you've left out some columns.
>
>
>
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHA
R
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> I can create this table from scratch with the columns/sizes I listed
> above.
> The table is created with no problem. I have to up the size of one of my
> CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
I also strongly recommend you familiarize yourself with the reasons we have
CHAR and VARCHAR. CHAR(2000) is not exactly a great choice here.
http://databases.aspfaq.com/databas...se-columns.html
Asql

Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004
> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?

> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.
|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)
|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Numberchar(20)
CUSTNMBRchar(45)
VENDORIDchar(45)
Attn_Namechar(30)
Contact_Namechar(30)
ContactPhonechar(20)
BusinessNamechar(30)
AddressLine1char(30)
AddressLine2char(30)
Citychar(25)
Statechar(3)
ZipCodechar(10)
Phonechar(20)
Faxchar(20)
ICC_Numberchar(20)
FedID_Numberchar(15)
Contractedtinyint
Start_Datedatetime
InsAgentNamechar(30)
InsAgentAddress1char(30)
InsAgentAddress2char(30)
InsAgentCitychar(25)
InsAgentStatechar(3)
InsAgentZipCodechar(10)
InsAgentPhonechar(20)
InsAgentFaxchar(20)
InsAgentContactchar(30)
CargoInsNamechar(28)
CargoInsAmountdecimal(19, 5)
CargoInsExpirationDatedatetime
AutoLibInsNamechar(30)
AutoLibInsAmountdecimal(19, 5)
AutoLibInsExpirationDatedatetime
Agent_YNtinyint
Broker_YNtinyint
Carrier_YNtinyint
Shipper_YNtinyint
Consignee_YNtinyint
BillTo_YNtinyint
CreatedDatedatetime
CreatedUserIDchar(20)
Billing_Ratedecimal(19, 5)
Billing_Rate_Codechar(5)
Ratingchar(5)
Colorchar(20)
Flaggedtinyint
FlagDatedatetime
FlagUserIDchar(20)
FlagReasonchar(50)
Activetinyint
Noteschar(3000)
PayCodechar(5)
PayRateFlatdecimal(19, 5)
PayRatePercentdecimal(19, 5)
PayRateLoadeddecimal(19, 5)
PayRateUnloadeddecimal(19, 5)
DefaultSalespersonchar(20)
Directionschar(2000)
FSTypechar(10)
FSRateAmountdecimal(19, 5)
Weightdecimal(19, 5)
AgentPayAccountint
CarrierPayAccountint
DropPayAmountdecimal(19, 5)
PickupPayAmountdecimal(19, 5)
ISTypechar(10)
ISRateAmountdecimal(19, 5)
Latitudeint
Longitudeint
|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.

> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.

> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A

Alter table db size getting increased.

Hi,
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:

> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>

Alter table and size changes

Question that I hope someone can direct me.
I do alter table command to (mostly) add columns to an existing table,
but sometimes to change the size of an existing column (such as char(30)
changing to char(50)).
I remember reading that changing the sze will actually leave the 30
character area and add at the end the 50 character, so the table is
taking up 80 characters instead of 50 (if this is wrong let me know).
So I am thinking I need to do a command that compares the size of the
existing table with the size of the table if it had been created
correctly using the create table command. How can I do that or is it
necessary?
Thanks.
Darin
*** Sent via Developersdex http://www.examnotes.net ***Hi, Darin
This happens only for fixed-length datatypes (for example char, but not
varchar). You can look at the xoffset column in sysobjects to see the
difference.
Try this, for example:
CREATE TABLE T1 (X char(1000), Y char(500))
ALTER TABLE T1 ALTER COLUMN X char(2000)
CREATE TABLE T2 (A char(2000), B char(500))
SELECT o.name as TableName, c.name as ColumnName, c.length, c.xoffset
FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.name IN ('T1','T2')
DROP TABLE T1, T2
You will get something like this:
TableName ColumnName length xoffset
-- -- -- --
T1 X 2000 1504
T1 Y 500 1004
T2 A 2000 4
T2 B 500 2004
This may indicate that the row size for T1 is about 3506 bytes, whereas
the row size for T2 is about 2506 bytes.
For more informations, see:
http://www.nigelrivett.net/SQLAdmin...leProblems.html
However, I think you should worry about this problem only if are using
large columns with fixed-length data types. Usually, the large columns
are variable-length data types, i.e. varchar(50), not char(50), so this
is rarely a problem with a big impact.
Razvansql

Alter Table

Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.As you are removing some of the fields using alter it can work for you provided that it follows the mentioned condition in the warning.

Contact your DBA regarding the warning message .|||

Quote:

Originally Posted by balasach82

Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.

try doing a

SELECT only, selected, fields, here INTO NEWTABLE from DAILYTABLE..

alter column that has pk/index

Hi,
I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
many thanks.maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:
> Hi,
> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a cursor.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to have
> to drop and recreate indexes due to time involved in rebuilding.
> many thanks.

Alter a fulltext index column from size 2000 to max

Thanks in advance for the help, I am a bit of a newbie.

How do I alter a column from varchar (2000) to varchar (max) when it is a fulltext indexed column. Keep in mind that this script will be deployed so the database name may change.

thnx

You can alter the data type only if there are no dependencies (indexes, constraints, references, computed column, partitioned etc) with few exceptions. To alter a column enabled for full-text index, you need to drop the fulltext index, alter the column and recreate the fulltext index.

|||

Like this:

IF EXISTS (SELECT * FROM [dbo].[sysobjects]

WHERE ID = object_id(N'[dbo].tblNews') AND

OBJECTPROPERTY(id, N'tblNews') = 1)

DROP FULLTEXT INDEX ON tblNews;

GO

ALTER TABLE tblNews ALTER COLUMN whatsNew VARCHAR(max) null;

GO

--HOW DO I RECREATE THE FULL-TEXT INDEX?

--DOES IT COME FROM THE CATALOGUE?

GO

|||Yep.

Then

CREATE FULLTEXT INDEX ON tblNews(whatsNew) KEY INDEX <unique index on tblNews>

Allocating memory

If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
David
According to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings *do not* require a restart in order to take affect
(see
http://msdn.microsoft.com/library/de...onfig_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory
size for SQL server.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David C wrote:

>If I want to change SQL Server to use a fixed memory size, do I have to
>stop/start SQL Server...or even the whole server to do so? Thanks.
>David
>
>
|||Can I ask the reason for this change? While there certainly times when it
is appropriate setting it to a fixed size is rarely the correct thing to do
for best overall effeciency.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23MTGnEeHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> If I want to change SQL Server to use a fixed memory size, do I have to
> stop/start SQL Server...or even the whole server to do so? Thanks.
> David
>
|||While setting the min and max server memory can be used to limit the memory
allocation SQL Server uses, it would still be in Dynamic Allocation Mode.
These two settings are only targets not fixed amounts. If you want to use a
fixed memory size, use the Set Working Set Size parameter. This is the ONLY
STATIC setting.
However, I would question why anyone would want to limit the DBMS from doing
its job, reducing the number of I/O operations by keeping the most
frequently used queries and data pages in memory...to the maximum allowable.
Sincerely,
Anthony Thomas

"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:eaXxz9eHFHA.904@.tk2msftngp13.phx.gbl...
According to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings do not require a restart in order to take affect (see
http://msdn.microsoft.com/library/de...onfig_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory size
for SQL server.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
David C wrote:
If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
David
|||One of our clients is using SBS 2003 and the Exchange "store" at times
is sucking up almost all of the RAM and (apparently) it is bringing SQL
to it's knees as customers SQL response literally stops.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I don't know that much about exchange and it's memory usage but is there a
way to limit Exchange instead? If you did set the Fixed memory size it
might depend some on which service is started first. You can try it and see
what happens but don't take too much or Exchange will crawl as well.
Andrew J. Kelly SQL MVP
"David" <daman@.biteme.com> wrote in message
news:e9mFphmHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> One of our clients is using SBS 2003 and the Exchange "store" at times
> is sucking up almost all of the RAM and (apparently) it is bringing SQL
> to it's knees as customers SQL response literally stops.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||The server has 1.5GB RAM. What if I set the SQL memory to 512 (min) and
1536 (max)? Wouldn't that force SQL to have at least 512? Customer
could care less if mail is a little slow.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||No that may not do what you want. The Min setting states that when SQL
Server grabs that much it will not release any below that amount back to the
OS. If Exchange gets the memory first then Sql Server may never reach the
MIN value. The only way to grab it and hold it right off the bat is to use
the Fixed amount. And if Exchange is that much of a hog it may never
release any back to Sql Server so the MAX setting would be useless.
Andrew J. Kelly SQL MVP
"David" <daman@.biteme.com> wrote in message
news:%23tbRt$nHFHA.576@.TK2MSFTNGP15.phx.gbl...
> The server has 1.5GB RAM. What if I set the SQL memory to 512 (min) and
> 1536 (max)? Wouldn't that force SQL to have at least 512? Customer
> could care less if mail is a little slow.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||It may be time for a call to Microsoft PSS to sort out your Exchange
issue. Doesn't sound like SQL is the problem here and Exchange has had
its share of issues over the past few years.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Andrew J. Kelly wrote:

>No that may not do what you want. The Min setting states that when SQL
>Server grabs that much it will not release any below that amount back to the
>OS. If Exchange gets the memory first then Sql Server may never reach the
>MIN value. The only way to grab it and hold it right off the bat is to use
>the Fixed amount. And if Exchange is that much of a hog it may never
>release any back to Sql Server so the MAX setting would be useless.
>
>

Allocating memory

If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
DavidAccording to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings *do not* require a restart in order to take affect
(see
http://msdn.microsoft.com/library/d... />
g_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory
size for SQL server.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David C wrote:

>If I want to change SQL Server to use a fixed memory size, do I have to
>stop/start SQL Server...or even the whole server to do so? Thanks.
>David
>
>|||Can I ask the reason for this change? While there certainly times when it
is appropriate setting it to a fixed size is rarely the correct thing to do
for best overall effeciency.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23MTGnEeHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> If I want to change SQL Server to use a fixed memory size, do I have to
> stop/start SQL Server...or even the whole server to do so? Thanks.
> David
>|||While setting the min and max server memory can be used to limit the memory
allocation SQL Server uses, it would still be in Dynamic Allocation Mode.
These two settings are only targets not fixed amounts. If you want to use a
fixed memory size, use the Set Working Set Size parameter. This is the ONLY
STATIC setting.
However, I would question why anyone would want to limit the DBMS from doing
its job, reducing the number of I/O operations by keeping the most
frequently used queries and data pages in memory...to the maximum allowable.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:eaXxz9eHFHA.904@.tk2msftngp13.phx.gbl...
According to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings do not require a restart in order to take affect (see
http://msdn.microsoft.com/library/d... />
g_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory size
for SQL server.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
David C wrote:
If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
David|||One of our clients is using SBS 2003 and the Exchange "store" at times
is sucking up almost all of the RAM and (apparently) it is bringing SQL
to it's knees as customers SQL response literally stops.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know that much about exchange and it's memory usage but is there a
way to limit Exchange instead? If you did set the Fixed memory size it
might depend some on which service is started first. You can try it and see
what happens but don't take too much or Exchange will crawl as well.
Andrew J. Kelly SQL MVP
"David" <daman@.biteme.com> wrote in message
news:e9mFphmHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> One of our clients is using SBS 2003 and the Exchange "store" at times
> is sucking up almost all of the RAM and (apparently) it is bringing SQL
> to it's knees as customers SQL response literally stops.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||The server has 1.5GB RAM. What if I set the SQL memory to 512 (min) and
1536 (max)? Wouldn't that force SQL to have at least 512? Customer
could care less if mail is a little slow.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||No that may not do what you want. The Min setting states that when SQL
Server grabs that much it will not release any below that amount back to the
OS. If Exchange gets the memory first then Sql Server may never reach the
MIN value. The only way to grab it and hold it right off the bat is to use
the Fixed amount. And if Exchange is that much of a hog it may never
release any back to Sql Server so the MAX setting would be useless.
Andrew J. Kelly SQL MVP
"David" <daman@.biteme.com> wrote in message
news:%23tbRt$nHFHA.576@.TK2MSFTNGP15.phx.gbl...
> The server has 1.5GB RAM. What if I set the SQL memory to 512 (min) and
> 1536 (max)? Wouldn't that force SQL to have at least 512? Customer
> could care less if mail is a little slow.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||It may be time for a call to Microsoft PSS to sort out your Exchange
issue. Doesn't sound like SQL is the problem here and Exchange has had
its share of issues over the past few years.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Andrew J. Kelly wrote:

>No that may not do what you want. The Min setting states that when SQL
>Server grabs that much it will not release any below that amount back to th
e
>OS. If Exchange gets the memory first then Sql Server may never reach the
>MIN value. The only way to grab it and hold it right off the bat is to use
>the Fixed amount. And if Exchange is that much of a hog it may never
>release any back to Sql Server so the MAX setting would be useless.
>
>

Allocating memory

If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
DavidThis is a multi-part message in MIME format.
--020409090600020109050001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
According to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings *do not* require a restart in order to take affect
(see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory
size for SQL server.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David C wrote:
>If I want to change SQL Server to use a fixed memory size, do I have to
>stop/start SQL Server...or even the whole server to do so? Thanks.
>David
>
>
--020409090600020109050001
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>According to SQL BOL, the "min server memory" and "max server
memory" sp_configure settings <b>do not</b> require a restart in order
to take affect (see <a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp</a>). ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp</a>).
These are the 2 settings you'll need to change to set a fixed memory
size for SQL server.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
David C wrote:
<blockquote cite="mid%23MTGnEeHFHA.3612@.TK2MSFTNGP09.phx.gbl"
type="cite">
<pre wrap="">If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
David
</pre>
</blockquote>
</body>
</html>
--020409090600020109050001--|||Can I ask the reason for this change? While there certainly times when it
is appropriate setting it to a fixed size is rarely the correct thing to do
for best overall effeciency.
--
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23MTGnEeHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> If I want to change SQL Server to use a fixed memory size, do I have to
> stop/start SQL Server...or even the whole server to do so? Thanks.
> David
>|||While setting the min and max server memory can be used to limit the memory
allocation SQL Server uses, it would still be in Dynamic Allocation Mode.
These two settings are only targets not fixed amounts. If you want to use a
fixed memory size, use the Set Working Set Size parameter. This is the ONLY
STATIC setting.
However, I would question why anyone would want to limit the DBMS from doing
its job, reducing the number of I/O operations by keeping the most
frequently used queries and data pages in memory...to the maximum allowable.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:eaXxz9eHFHA.904@.tk2msftngp13.phx.gbl...
According to SQL BOL, the "min server memory" and "max server memory"
sp_configure settings do not require a restart in order to take affect (see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp).
These are the 2 settings you'll need to change to set a fixed memory size
for SQL server.
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
David C wrote:
If I want to change SQL Server to use a fixed memory size, do I have to
stop/start SQL Server...or even the whole server to do so? Thanks.
David