How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
MikeNevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
MikeNevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>sql

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
Mike
Nevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a numbe
r
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point an
d
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before addin
g
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) 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.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
----
----
----
----
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
----
----
----
----
-- -- -- --
-- --
--
----
----
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
----
----
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
----
----
No rowguidcol column defined.
Data_located_on_filegroup
----
----
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database
.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "W
e
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will
be
> permitted to be added. For example, if I wish to add a field to CRM as te
xt
> and set it to have a limit of 6K, then CRM will only allow me to add a num
ber
> of fields adding up to 2K. The thing is also is that CRM will not allow y
ou
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point
and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before add
ing
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKI
NG
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you wi
ll
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDA
TE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ----
---
> ----
---
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length P
rec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ----
---
> ----
---
> -- -- -- --
> -- --
> --
> ----
---
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replicatio
n
> ----
---
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ----
---
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ----
---
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
>|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>sql

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) 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.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
--
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) 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.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to enter
> > the database and will truncate the rest (from my understanding anyway).
> > However, CRM has set its own restrictions on the database and says that "We
> > will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we will
> > calculate
> > the size of the fields in the table and determine if anymore columns will be
> > permitted to be added. For example, if I wish to add a field to CRM as text
> > and set it to have a limit of 6K, then CRM will only allow me to add a number
> > of fields adding up to 2K. The thing is also is that CRM will not allow you
> > to DELETE or reconfigure any columns, therefore, if I said to make the new
> > field to be 1K, it will not allow me. Basically CRM breaks at this point and
> > I can no longer add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size is
> > before hand so I can allocate certain fields more appropriately before adding
> > them to know the size of each field. Is there an store procedure to get
> > these values from SQL Server, so I can know if I am on the brink of BREAKING
> > CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:
> Keener wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its
> > database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to
> > enter the database and will truncate the rest (from my understanding
> > anyway). However, CRM has set its own restrictions on the database
> > and says that "We will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we
> > will calculate
> > the size of the fields in the table and determine if anymore columns
> > will be permitted to be added. For example, if I wish to add a field
> > to CRM as text and set it to have a limit of 6K, then CRM will only
> > allow me to add a number of fields adding up to 2K. The thing is
> > also is that CRM will not allow you to DELETE or reconfigure any
> > columns, therefore, if I said to make the new field to be 1K, it will
> > not allow me. Basically CRM breaks at this point and I can no longer
> > add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size
> > is before hand so I can allocate certain fields more appropriately
> > before adding them to know the size of each field. Is there an store
> > procedure to get these values from SQL Server, so I can know if I am
> > on the brink of BREAKING CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
Keener
Hi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) 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.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener
|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) 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.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine table size?

Is there a stored procedure or command for determining the allocation size of
a table and related info?
thanks
Try,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:

> Is there a stored procedure or command for determining the allocation size of
> a table and related info?
> thanks

How to determine table size?

Is there a stored procedure or command for determining the allocation size o
f
a table and related info?
thanksTry,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:

> Is there a stored procedure or command for determining the allocation size
of
> a table and related info?
> thanks

How to determine table size?

Is there a stored procedure or command for determining the allocation size of
a table and related info?
thanksTry,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:
> Is there a stored procedure or command for determining the allocation size of
> a table and related info?
> thankssql

How to determine space used by a file

Nags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay

>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...[vbcol=seagreen]
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
>
> 4 Gig file pre
> in that. We want
> file group is less
> allocate another 4 Gig|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
>|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
preallocate[vbcol=seagreen]
free[vbcol=seagreen]
> in
than[vbcol=seagreen]
> 1
> Its
each[vbcol=seagreen]
>|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> idea.
amount[vbcol=seagreen]
> of
to[vbcol=seagreen]
> space.
in[vbcol=seagreen]
not[vbcol=seagreen]
> to
> preallocate
> free
> than
requirement.[vbcol=seagreen]
> each
>|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
and[vbcol=seagreen]
> year
> and
> group
their[vbcol=seagreen]
3[vbcol=seagreen]
groups[vbcol=seagreen]
preallocated[vbcol=seagreen]
> has
> the
of[vbcol=seagreen]
> amount
be[vbcol=seagreen]
> to
> in
autogrow)[vbcol=seagreen]
> not
<[vbcol=seagreen]
> requirement.
in[vbcol=seagreen]
>

How to determine space used by a file

Nags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay

>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>
My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...[vbcol=seagreen]
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
> 4 Gig file pre
> in that. We want
> file group is less
> allocate another 4 Gig
|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
>
|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.[vbcol=seagreen]
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
preallocate[vbcol=seagreen]
free[vbcol=seagreen]
> in
than[vbcol=seagreen]
> 1
> Its
each
>
|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the[vbcol=seagreen]
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> idea.
amount[vbcol=seagreen]
> of
to[vbcol=seagreen]
> space.
in[vbcol=seagreen]
not[vbcol=seagreen]
> to
> preallocate
> free
> than
requirement.
> each
>
|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like[vbcol=seagreen]
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
and[vbcol=seagreen]
> year
> and
> group
their[vbcol=seagreen]
3[vbcol=seagreen]
groups[vbcol=seagreen]
preallocated[vbcol=seagreen]
> has
> the
of[vbcol=seagreen]
> amount
be[vbcol=seagreen]
> to
> in
autogrow)[vbcol=seagreen]
> not
<[vbcol=seagreen]
> requirement.
in
>

How to determine server size!

Hi!
How can I determine the size of my server in sql server? Please help!
Thanks!
Measurnig tape?
Seriously though...size of what? Databases, harddrives, RAM, eveything?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jenny" <sosa48@.hotmail.com> wrote in message
news:1163711468.558168.104810@.f16g2000cwb.googlegr oups.com...
> Hi!
> How can I determine the size of my server in sql server? Please help!
> Thanks!
>
|||Yes, the size of everything, so I can get one solid number of the whole
size of the server.
Thank you!
Kevin3NF wrote:[vbcol=seagreen]
> Measurnig tape?
> Seriously though...size of what? Databases, harddrives, RAM, eveything?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jenny" <sosa48@.hotmail.com> wrote in message
> news:1163711468.558168.104810@.f16g2000cwb.googlegr oups.com...
|||Find out how many jigabytes you have and multiply that number times three.
"Jenny" wrote:

> Yes, the size of everything, so I can get one solid number of the whole
> size of the server.
> Thank you!
> Kevin3NF wrote:
>
|||SQL Server, I assume...2000 or 2005?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jenny" <sosa48@.hotmail.com> wrote in message
news:1163714621.207065.296280@.k70g2000cwa.googlegr oups.com...
> Yes, the size of everything, so I can get one solid number of the whole
> size of the server.
> Thank you!
> Kevin3NF wrote:
>
|||Yes, SQL Server 2005.
Thanks!
Kevin3NF wrote:[vbcol=seagreen]
> SQL Server, I assume...2000 or 2005?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jenny" <sosa48@.hotmail.com> wrote in message
> news:1163714621.207065.296280@.k70g2000cwa.googlegr oups.com...
|||A really rough hack at getting the size of all MDF, NDF and LDF files is:
Select sum(size), 'MB' from sysaltfiles
I don't have much experience in SQL 2005, so I assume this is the size of
the lhysical file, not the data in the file
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jenny" <sosa48@.hotmail.com> wrote in message
news:1163784398.735132.27890@.h48g2000cwc.googlegro ups.com...
> Yes, SQL Server 2005.
> Thanks!
>
> Kevin3NF wrote:
>

How to determine server size!

Hi!
How can I determine the size of my server in sql server? Please help!
Thanks!Measurnig tape?
Seriously though...size of what? Databases, harddrives, RAM, eveything?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jenny" <sosa48@.hotmail.com> wrote in message
news:1163711468.558168.104810@.f16g2000cwb.googlegroups.com...
> Hi!
> How can I determine the size of my server in sql server? Please help!
> Thanks!
>|||Yes, the size of everything, so I can get one solid number of the whole
size of the server.
Thank you!
Kevin3NF wrote:[vbcol=seagreen]
> Measurnig tape?
> Seriously though...size of what? Databases, harddrives, RAM, eveything?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jenny" <sosa48@.hotmail.com> wrote in message
> news:1163711468.558168.104810@.f16g2000cwb.googlegroups.com...|||Find out how many jigabytes you have and multiply that number times three.
"Jenny" wrote:

> Yes, the size of everything, so I can get one solid number of the whole
> size of the server.
> Thank you!
> Kevin3NF wrote:
>|||SQL Server, I assume...2000 or 2005?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jenny" <sosa48@.hotmail.com> wrote in message
news:1163714621.207065.296280@.k70g2000cwa.googlegroups.com...
> Yes, the size of everything, so I can get one solid number of the whole
> size of the server.
> Thank you!
> Kevin3NF wrote:
>|||Yes, SQL Server 2005.
Thanks!
Kevin3NF wrote:[vbcol=seagreen]
> SQL Server, I assume...2000 or 2005?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Jenny" <sosa48@.hotmail.com> wrote in message
> news:1163714621.207065.296280@.k70g2000cwa.googlegroups.com...|||A really rough hack at getting the size of all MDF, NDF and LDF files is:
Select sum(size), 'MB' from sysaltfiles
I don't have much experience in SQL 2005, so I assume this is the size of
the lhysical file, not the data in the file
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Jenny" <sosa48@.hotmail.com> wrote in message
news:1163784398.735132.27890@.h48g2000cwc.googlegroups.com...
> Yes, SQL Server 2005.
> Thanks!
>
> Kevin3NF wrote:
>sql

How to determine compiled proc size?

How do I determine the amount of server memory a stored procedure (or any
other object) takes up?http://www.sql-server-performance.com/rd_data_cache.asp gives some
information regarding querying the syscacheobjects system table and what
each row in the table means. You should be able to use that to derive
the number of pages of memory used with many kinds of server objects.
Good luck,
Tony Sebion
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:

> How do I determine the amount of server memory a stored procedure (or any
> other object) takes up?|||There's also an undocumented DBCC command: DBCC MEMOBJLIST but it requires
starting the service with the T3654 flag and it doesn't show what's in AWE
memory.
"Tony Sebion" wrote:

> http://www.sql-server-performance.com/rd_data_cache.asp gives some
> information regarding querying the syscacheobjects system table and what
> each row in the table means. You should be able to use that to derive
> the number of pages of memory used with many kinds of server objects.
> Good luck,
> Tony Sebion
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:
>
>

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:

>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:

>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?