Hello, everybody,
I am in need of storing long pieces of text in database, length between 2000 and 4000 characters (nvarchar limit).
If I have a column in a table:
my_text nvarchar(2000)
does this mean that in database the size used will always be 2000*2=4000 bytes at least (+ rounding), independent on what the real length of the text stored is?
Anoter alternative - storing texts in files is expected to become unmanageable very soon, so I would like to keep to the db.
Or, would it be reasonable to have such a table:
my_text_1 nvarchar(500),
my_text_2 nvarchar(500),
my_text_3 nvarchar(500),
my_text_4 nvarchar(500)
Or declaration already means the space for a column is reserved even if it is nullable?
My DBs are MS SQL Server 2000/2005.As I understand it the amount of space used is limited to the size of the data you have in the nvarchar field. So "hello world" will take up the same amouint of space in an nvarchar(20) as it does in an nvarchar(200).|||But why use unicode? Do you really need it?
Showing posts with label space. Show all posts
Showing posts with label space. Show all posts
Wednesday, March 28, 2012
How much space will it Take??
friends,
I m in a process of makin a website tat will store the vital
statistics of users.Users can turn out to be in millions!!!
Can u help me to kno how much space will tat take and how much space
should i book online to keep my site in wrkin condition.
Ur help sought.
Thanx
samraatWithout table definitions (Including indexes) that would be impossible.
100 milllion users at an average of 8 bytes per row is small
100 million users at an average of 7800 bytes per row with 70 indexes most of them compound could be nasty.
Like I said, without definitions the permetations are endless.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||samabhik,
> I m in a process of makin a website tat will store the vital
> statistics of users.Users can turn out to be in millions!!!
> Can u help me to kno how much space will tat take and how much
> space should i book online to keep my site in wrkin condition.
Once you have your schema, you can calculate how much space you will
need. Refer to the topic Estimating Database Size in Books Online
for an example.
Linda
I m in a process of makin a website tat will store the vital
statistics of users.Users can turn out to be in millions!!!
Can u help me to kno how much space will tat take and how much space
should i book online to keep my site in wrkin condition.
Ur help sought.
Thanx
samraatWithout table definitions (Including indexes) that would be impossible.
100 milllion users at an average of 8 bytes per row is small
100 million users at an average of 7800 bytes per row with 70 indexes most of them compound could be nasty.
Like I said, without definitions the permetations are endless.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||samabhik,
> I m in a process of makin a website tat will store the vital
> statistics of users.Users can turn out to be in millions!!!
> Can u help me to kno how much space will tat take and how much
> space should i book online to keep my site in wrkin condition.
Once you have your schema, you can calculate how much space you will
need. Refer to the topic Estimating Database Size in Books Online
for an example.
Linda
How much space used in each transaction log file
Is there a way to know how much space is free in each transaction log file of the same database?
Example:
A database with 3, 1GB files for transaction log: A, B and C created in this sequence.
From what I have read, since SQL Server 2005 writes to a single transaction log only, I guess if the transaction log is using 2.5GB than A and B are full and B is only half full. Is this correct?
Thanks.Transaction log usage is the same for sql2k/5. It writes sequentially. To determine the used space for tran log, do this:
dbcc sqlperf(logspace)
How much space Transaction Log is used ?
We have a Transaction Log of size 10GB and we would like
to know how much size of it is actually used. This is
because we would like to shrink the TL size.
ThanksThere's more than one way to find this out. One technique is to use dbcc
sqlperf(logspace)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a38801c3eb8c$53b33c70$a001280a@.phx.gbl...
to know how much size of it is actually used. This is
because we would like to shrink the TL size.
ThanksThere's more than one way to find this out. One technique is to use dbcc
sqlperf(logspace)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a38801c3eb8c$53b33c70$a001280a@.phx.gbl...
quote:
> We have a Transaction Log of size 10GB and we would like
> to know how much size of it is actually used. This is
> because we would like to shrink the TL size.
> Thanks
How much space Transaction Log is used ?
We have a Transaction Log of size 10GB and we would like
to know how much size of it is actually used. This is
because we would like to shrink the TL size.
ThanksThere's more than one way to find this out. One technique is to use dbcc
sqlperf(logspace)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a38801c3eb8c$53b33c70$a001280a@.phx.gbl...
> We have a Transaction Log of size 10GB and we would like
> to know how much size of it is actually used. This is
> because we would like to shrink the TL size.
> Thanks
to know how much size of it is actually used. This is
because we would like to shrink the TL size.
ThanksThere's more than one way to find this out. One technique is to use dbcc
sqlperf(logspace)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a38801c3eb8c$53b33c70$a001280a@.phx.gbl...
> We have a Transaction Log of size 10GB and we would like
> to know how much size of it is actually used. This is
> because we would like to shrink the TL size.
> Thanks
How much space should I give to tempdb for rebuilding index?
Hi, all.
I scheduled a schedule for index rebuilding for a database which is about
60GB.
I know that tempdb is required for index rebuilding, but don't know how much
space would be adequate.
How much space should the tempdb have for index rebuilding?
Any help would be appreciated.> I know that tempdb is required for index rebuilding, but don't know how
much
> space would be adequate.
That depends on how you rebuild the index. If you use DBCC INDEXDEFRAG or
DBCC DBREINDEX, then tempdb is not used. And this is what most are using.
Below section in the updated books online has some info:
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:ODELj%230pDHA.3504@.TK2MSFTNGP11.phx.gbl...
> Hi, all.
> I scheduled a schedule for index rebuilding for a database which is about
> 60GB.
> I know that tempdb is required for index rebuilding, but don't know how
much
> space would be adequate.
> How much space should the tempdb have for index rebuilding?
> Any help would be appreciated.
>
>sql
I scheduled a schedule for index rebuilding for a database which is about
60GB.
I know that tempdb is required for index rebuilding, but don't know how much
space would be adequate.
How much space should the tempdb have for index rebuilding?
Any help would be appreciated.> I know that tempdb is required for index rebuilding, but don't know how
much
> space would be adequate.
That depends on how you rebuild the index. If you use DBCC INDEXDEFRAG or
DBCC DBREINDEX, then tempdb is not used. And this is what most are using.
Below section in the updated books online has some info:
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:ODELj%230pDHA.3504@.TK2MSFTNGP11.phx.gbl...
> Hi, all.
> I scheduled a schedule for index rebuilding for a database which is about
> 60GB.
> I know that tempdb is required for index rebuilding, but don't know how
much
> space would be adequate.
> How much space should the tempdb have for index rebuilding?
> Any help would be appreciated.
>
>sql
How much space on disk does a varchar use?
How much space on disk does a varchar use? Is it the defined length or the
actual length used? If the odd entry (1%) in a large table needs a comment o
f
say 40 characters, would an unused varchar field only take a minimum amount
of space, or should the comment be in a linked table (which of course also
has overheads as indexes are then needed), or would it be best to use/misuse
a text field?
--
Many thanks,
John AustinJohn Austin wrote:
> How much space on disk does a varchar use? Is it the defined length or the
> actual length used? If the odd entry (1%) in a large table needs a comment
of
> say 40 characters, would an unused varchar field only take a minimum amoun
t
> of space, or should the comment be in a linked table (which of course also
> has overheads as indexes are then needed), or would it be best to use/misu
se
> a text field?
From Books Online:
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must
be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n
must be a value from 1 through 8,000. Storage size is the actual length
in bytes of the data entered, not n bytes. The data entered can be 0
characters in length. The SQL-92 synonyms for varchar are char varying
or character varying.
Remarks
When n is not specified in a data definition or variable declaration
statement, the default length is 1. When n is not specified with the
CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the
database, unless a specific collation is assigned using the COLLATE
clause. The collation controls the code page used to store the character
data.
Sites supporting multiple languages should consider using the Unicode
nchar or nvarchar data types to minimize character conversion issues. If
you use char or varchar:
Use char when the data values in a column are expected to be
consistently close to the same size.
Use varchar when the data values in a column are expected to vary
considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, just what I needed to know - and sorry, I should have searched
books online more thoroughly!
--
John Austin
"Tracy McKibben" wrote:
> John Austin wrote:
> From Books Online:
> char and varchar
> Fixed-length (char) or variable-length (varchar) character data types.
> char[(n)]
> Fixed-length non-Unicode character data with length of n bytes. n must
> be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
> synonym for char is character.
> varchar[(n)]
> Variable-length non-Unicode character data with length of n bytes. n
> must be a value from 1 through 8,000. Storage size is the actual length
> in bytes of the data entered, not n bytes. The data entered can be 0
> characters in length. The SQL-92 synonyms for varchar are char varying
> or character varying.
> Remarks
> When n is not specified in a data definition or variable declaration
> statement, the default length is 1. When n is not specified with the
> CAST function, the default length is 30.
> Objects using char or varchar are assigned the default collation of the
> database, unless a specific collation is assigned using the COLLATE
> clause. The collation controls the code page used to store the character
> data.
> Sites supporting multiple languages should consider using the Unicode
> nchar or nvarchar data types to minimize character conversion issues. If
> you use char or varchar:
> Use char when the data values in a column are expected to be
> consistently close to the same size.
>
> Use varchar when the data values in a column are expected to vary
> considerably in size.
> If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
> a char column defined as NULL is handled as varchar.
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
actual length used? If the odd entry (1%) in a large table needs a comment o
f
say 40 characters, would an unused varchar field only take a minimum amount
of space, or should the comment be in a linked table (which of course also
has overheads as indexes are then needed), or would it be best to use/misuse
a text field?
--
Many thanks,
John AustinJohn Austin wrote:
> How much space on disk does a varchar use? Is it the defined length or the
> actual length used? If the odd entry (1%) in a large table needs a comment
of
> say 40 characters, would an unused varchar field only take a minimum amoun
t
> of space, or should the comment be in a linked table (which of course also
> has overheads as indexes are then needed), or would it be best to use/misu
se
> a text field?
From Books Online:
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must
be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n
must be a value from 1 through 8,000. Storage size is the actual length
in bytes of the data entered, not n bytes. The data entered can be 0
characters in length. The SQL-92 synonyms for varchar are char varying
or character varying.
Remarks
When n is not specified in a data definition or variable declaration
statement, the default length is 1. When n is not specified with the
CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the
database, unless a specific collation is assigned using the COLLATE
clause. The collation controls the code page used to store the character
data.
Sites supporting multiple languages should consider using the Unicode
nchar or nvarchar data types to minimize character conversion issues. If
you use char or varchar:
Use char when the data values in a column are expected to be
consistently close to the same size.
Use varchar when the data values in a column are expected to vary
considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, just what I needed to know - and sorry, I should have searched
books online more thoroughly!
--
John Austin
"Tracy McKibben" wrote:
> John Austin wrote:
> From Books Online:
> char and varchar
> Fixed-length (char) or variable-length (varchar) character data types.
> char[(n)]
> Fixed-length non-Unicode character data with length of n bytes. n must
> be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
> synonym for char is character.
> varchar[(n)]
> Variable-length non-Unicode character data with length of n bytes. n
> must be a value from 1 through 8,000. Storage size is the actual length
> in bytes of the data entered, not n bytes. The data entered can be 0
> characters in length. The SQL-92 synonyms for varchar are char varying
> or character varying.
> Remarks
> When n is not specified in a data definition or variable declaration
> statement, the default length is 1. When n is not specified with the
> CAST function, the default length is 30.
> Objects using char or varchar are assigned the default collation of the
> database, unless a specific collation is assigned using the COLLATE
> clause. The collation controls the code page used to store the character
> data.
> Sites supporting multiple languages should consider using the Unicode
> nchar or nvarchar data types to minimize character conversion issues. If
> you use char or varchar:
> Use char when the data values in a column are expected to be
> consistently close to the same size.
>
> Use varchar when the data values in a column are expected to vary
> considerably in size.
> If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
> a char column defined as NULL is handled as varchar.
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
How much space on disk does a varchar use?
How much space on disk does a varchar use? Is it the defined length or the
actual length used? If the odd entry (1%) in a large table needs a comment of
say 40 characters, would an unused varchar field only take a minimum amount
of space, or should the comment be in a linked table (which of course also
has overheads as indexes are then needed), or would it be best to use/misuse
a text field?
--
Many thanks,
John AustinJohn Austin wrote:
> How much space on disk does a varchar use? Is it the defined length or the
> actual length used? If the odd entry (1%) in a large table needs a comment of
> say 40 characters, would an unused varchar field only take a minimum amount
> of space, or should the comment be in a linked table (which of course also
> has overheads as indexes are then needed), or would it be best to use/misuse
> a text field?
From Books Online:
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must
be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n
must be a value from 1 through 8,000. Storage size is the actual length
in bytes of the data entered, not n bytes. The data entered can be 0
characters in length. The SQL-92 synonyms for varchar are char varying
or character varying.
Remarks
When n is not specified in a data definition or variable declaration
statement, the default length is 1. When n is not specified with the
CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the
database, unless a specific collation is assigned using the COLLATE
clause. The collation controls the code page used to store the character
data.
Sites supporting multiple languages should consider using the Unicode
nchar or nvarchar data types to minimize character conversion issues. If
you use char or varchar:
Use char when the data values in a column are expected to be
consistently close to the same size.
Use varchar when the data values in a column are expected to vary
considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, just what I needed to know - and sorry, I should have searched
books online more thoroughly!
--
John Austin
"Tracy McKibben" wrote:
> John Austin wrote:
> > How much space on disk does a varchar use? Is it the defined length or the
> > actual length used? If the odd entry (1%) in a large table needs a comment of
> > say 40 characters, would an unused varchar field only take a minimum amount
> > of space, or should the comment be in a linked table (which of course also
> > has overheads as indexes are then needed), or would it be best to use/misuse
> > a text field?
> From Books Online:
> char and varchar
> Fixed-length (char) or variable-length (varchar) character data types.
> char[(n)]
> Fixed-length non-Unicode character data with length of n bytes. n must
> be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
> synonym for char is character.
> varchar[(n)]
> Variable-length non-Unicode character data with length of n bytes. n
> must be a value from 1 through 8,000. Storage size is the actual length
> in bytes of the data entered, not n bytes. The data entered can be 0
> characters in length. The SQL-92 synonyms for varchar are char varying
> or character varying.
> Remarks
> When n is not specified in a data definition or variable declaration
> statement, the default length is 1. When n is not specified with the
> CAST function, the default length is 30.
> Objects using char or varchar are assigned the default collation of the
> database, unless a specific collation is assigned using the COLLATE
> clause. The collation controls the code page used to store the character
> data.
> Sites supporting multiple languages should consider using the Unicode
> nchar or nvarchar data types to minimize character conversion issues. If
> you use char or varchar:
> Use char when the data values in a column are expected to be
> consistently close to the same size.
>
> Use varchar when the data values in a column are expected to vary
> considerably in size.
> If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
> a char column defined as NULL is handled as varchar.
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
actual length used? If the odd entry (1%) in a large table needs a comment of
say 40 characters, would an unused varchar field only take a minimum amount
of space, or should the comment be in a linked table (which of course also
has overheads as indexes are then needed), or would it be best to use/misuse
a text field?
--
Many thanks,
John AustinJohn Austin wrote:
> How much space on disk does a varchar use? Is it the defined length or the
> actual length used? If the odd entry (1%) in a large table needs a comment of
> say 40 characters, would an unused varchar field only take a minimum amount
> of space, or should the comment be in a linked table (which of course also
> has overheads as indexes are then needed), or would it be best to use/misuse
> a text field?
From Books Online:
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must
be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n
must be a value from 1 through 8,000. Storage size is the actual length
in bytes of the data entered, not n bytes. The data entered can be 0
characters in length. The SQL-92 synonyms for varchar are char varying
or character varying.
Remarks
When n is not specified in a data definition or variable declaration
statement, the default length is 1. When n is not specified with the
CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the
database, unless a specific collation is assigned using the COLLATE
clause. The collation controls the code page used to store the character
data.
Sites supporting multiple languages should consider using the Unicode
nchar or nvarchar data types to minimize character conversion issues. If
you use char or varchar:
Use char when the data values in a column are expected to be
consistently close to the same size.
Use varchar when the data values in a column are expected to vary
considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, just what I needed to know - and sorry, I should have searched
books online more thoroughly!
--
John Austin
"Tracy McKibben" wrote:
> John Austin wrote:
> > How much space on disk does a varchar use? Is it the defined length or the
> > actual length used? If the odd entry (1%) in a large table needs a comment of
> > say 40 characters, would an unused varchar field only take a minimum amount
> > of space, or should the comment be in a linked table (which of course also
> > has overheads as indexes are then needed), or would it be best to use/misuse
> > a text field?
> From Books Online:
> char and varchar
> Fixed-length (char) or variable-length (varchar) character data types.
> char[(n)]
> Fixed-length non-Unicode character data with length of n bytes. n must
> be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
> synonym for char is character.
> varchar[(n)]
> Variable-length non-Unicode character data with length of n bytes. n
> must be a value from 1 through 8,000. Storage size is the actual length
> in bytes of the data entered, not n bytes. The data entered can be 0
> characters in length. The SQL-92 synonyms for varchar are char varying
> or character varying.
> Remarks
> When n is not specified in a data definition or variable declaration
> statement, the default length is 1. When n is not specified with the
> CAST function, the default length is 30.
> Objects using char or varchar are assigned the default collation of the
> database, unless a specific collation is assigned using the COLLATE
> clause. The collation controls the code page used to store the character
> data.
> Sites supporting multiple languages should consider using the Unicode
> nchar or nvarchar data types to minimize character conversion issues. If
> you use char or varchar:
> Use char when the data values in a column are expected to be
> consistently close to the same size.
>
> Use varchar when the data values in a column are expected to vary
> considerably in size.
> If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
> a char column defined as NULL is handled as varchar.
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
How much space does the long character data type use.
We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.
There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>
|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>
>.
>
|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...[vbcol=seagreen]
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> data types.
> Row Data" in BOL.
|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
of[vbcol=seagreen]
space[vbcol=seagreen]
image[vbcol=seagreen]
in[vbcol=seagreen]
Explorer.[vbcol=seagreen]
2000.[vbcol=seagreen]
is a[vbcol=seagreen]
up[vbcol=seagreen]
space?[vbcol=seagreen]
space as
>
>.
>
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.
There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>
|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>
>.
>
|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...[vbcol=seagreen]
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> data types.
> Row Data" in BOL.
|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
of[vbcol=seagreen]
space[vbcol=seagreen]
image[vbcol=seagreen]
in[vbcol=seagreen]
Explorer.[vbcol=seagreen]
2000.[vbcol=seagreen]
is a[vbcol=seagreen]
up[vbcol=seagreen]
space?[vbcol=seagreen]
space as
>
>.
>
How much space does the long character data type use.
We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
>> One of the tables we use have a COMMENT field which is a
>> Long Character datatype. In SQL the data type show up
>> as 'Text'. How does this data type utilize disk space?
>> Would the size grow based on the number of characters
>> entered in the field or does the system reserve space as
>> it does with other data type. How much space does it
>> reserve when a field is the long character datatype.
>>
>
>.
>|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> >--Original Message--
> >There is a 16 byte overhead on the text, ntext, and image
> data types.
> >
> >If your data in these columns is small, look at "Text in
> Row Data" in BOL.
> >
> >--
> >
> >Barry McAuslin
> >
> >Look inside your SQL Server files with SQL File Explorer.
> >Go to http://www.sqlfe.com for more information.
> >
> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> >> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> >> One of the tables we use have a COMMENT field which is a
> >> Long Character datatype. In SQL the data type show up
> >> as 'Text'. How does this data type utilize disk space?
> >> Would the size grow based on the number of characters
> >> entered in the field or does the system reserve space as
> >> it does with other data type. How much space does it
> >> reserve when a field is the long character datatype.
> >>
> >>
> >
> >
> >.
> >|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
>> Hi Barry,
>> Thanks you for replying.
>> What is puzzling me is that when I run sp_help on the
>> table I see that the EMPLID field is CHAR with a length
of
>> 11 which I can understand because you cannot enter more
>> than 11 characters in the EMPLID field. However the
>> COMMENT field is TEXT with a length of 16 but you can
>> enter much more than 16 characters.
>> How is this possible and how does it affect the disk
space
>> utilization?
>> >--Original Message--
>> >There is a 16 byte overhead on the text, ntext, and
image
>> data types.
>> >
>> >If your data in these columns is small, look at "Text
in
>> Row Data" in BOL.
>> >
>> >--
>> >
>> >Barry McAuslin
>> >
>> >Look inside your SQL Server files with SQL File
Explorer.
>> >Go to http://www.sqlfe.com for more information.
>> >
>> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> >> We currently use PeopleSoft HRMS on MS SQL SERVER
2000.
>> >> One of the tables we use have a COMMENT field which
is a
>> >> Long Character datatype. In SQL the data type show
up
>> >> as 'Text'. How does this data type utilize disk
space?
>> >> Would the size grow based on the number of characters
>> >> entered in the field or does the system reserve
space as
>> >> it does with other data type. How much space does it
>> >> reserve when a field is the long character datatype.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?
>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
>> One of the tables we use have a COMMENT field which is a
>> Long Character datatype. In SQL the data type show up
>> as 'Text'. How does this data type utilize disk space?
>> Would the size grow based on the number of characters
>> entered in the field or does the system reserve space as
>> it does with other data type. How much space does it
>> reserve when a field is the long character datatype.
>>
>
>.
>|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> >--Original Message--
> >There is a 16 byte overhead on the text, ntext, and image
> data types.
> >
> >If your data in these columns is small, look at "Text in
> Row Data" in BOL.
> >
> >--
> >
> >Barry McAuslin
> >
> >Look inside your SQL Server files with SQL File Explorer.
> >Go to http://www.sqlfe.com for more information.
> >
> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> >> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> >> One of the tables we use have a COMMENT field which is a
> >> Long Character datatype. In SQL the data type show up
> >> as 'Text'. How does this data type utilize disk space?
> >> Would the size grow based on the number of characters
> >> entered in the field or does the system reserve space as
> >> it does with other data type. How much space does it
> >> reserve when a field is the long character datatype.
> >>
> >>
> >
> >
> >.
> >|||Hi Dan,
Thanks a lot. This clears up my question.
>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
>> Hi Barry,
>> Thanks you for replying.
>> What is puzzling me is that when I run sp_help on the
>> table I see that the EMPLID field is CHAR with a length
of
>> 11 which I can understand because you cannot enter more
>> than 11 characters in the EMPLID field. However the
>> COMMENT field is TEXT with a length of 16 but you can
>> enter much more than 16 characters.
>> How is this possible and how does it affect the disk
space
>> utilization?
>> >--Original Message--
>> >There is a 16 byte overhead on the text, ntext, and
image
>> data types.
>> >
>> >If your data in these columns is small, look at "Text
in
>> Row Data" in BOL.
>> >
>> >--
>> >
>> >Barry McAuslin
>> >
>> >Look inside your SQL Server files with SQL File
Explorer.
>> >Go to http://www.sqlfe.com for more information.
>> >
>> >"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>> >news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>> >> We currently use PeopleSoft HRMS on MS SQL SERVER
2000.
>> >> One of the tables we use have a COMMENT field which
is a
>> >> Long Character datatype. In SQL the data type show
up
>> >> as 'Text'. How does this data type utilize disk
space?
>> >> Would the size grow based on the number of characters
>> >> entered in the field or does the system reserve
space as
>> >> it does with other data type. How much space does it
>> >> reserve when a field is the long character datatype.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
How much space does MS SQL 7.0 need for reorganizing data
We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg
How much space does MS SQL 7.0 need for reorganizing data
We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg
How much space does backup need?
Hello everyone:
I have a backup file about 9gig to be resored. The hard drive has less than 11gig space free. Is it enough for restore? Thanks.
ZYTMaybe and maybe not.
If the db has free space in the files of more that about 1 gb, you will probably fail.
Give it a try and see. Worst case is that you fail and have to find some place else to restore it.|||betcha you got a lot of old backups eating up the drive...also betcha you use maint plans|||Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.
I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.|||Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.
I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.
Hi, Vich:
Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.
ZYT|||Hi, Vich:
Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.
ZYT
Again; I do not know internals. I vaguely recall that the EM Restore Screen issues a "Checking for space" message while it's "thinking".
By "Hard drive has DB only, but data files and log files are in same hard drive", I gather you mean the drive is for the DB and all DB related files (log, DB backup, etc).
So; the very best case is that it fits. The DB may start out with a very small log file, but it will very quickly grow and then you'll be in trouble.
But Dude; you're nuts to be squeezing your main DB disk like this unless it's just an unaviodable emergency, and even then, your emergency may become a disaster.
For example; if your disk is highly fragmented then as free space nears it's end, even if there is theoretically enough space, the fragments may be too small. Also; you kill the chance for a decent defrag if you leave it so tight. Also; as databases and log files grow, they're sure to reach capacity quickly.
In other words, even if by some chance it does work, not for long.
Even with my shallow knowledge of all this, I believe that your solution will be to assign your log and backup files (for this and other databases) to another volume, preferably a dedicated volume. A simple mirrored volume is recommended. Ideally each DB will have it's own volume, but even sharing it will be better than having on the DB volume. If you have one huge production DB, you're probably best moving only it's log file there since sharing logs on volumes cause disk head thrashing that is very costly (like 10 times) to performance.
Let me know if you want instructions for this. I had to do it recently and have it all written into scripts. The others here (far more experienced thatn I) could bless the process before proceeding.|||Run this command on the backup file, to see what you are dealing with:
restore filelistonly from disk = 'your backup file goes here'
The output will list the size of each file.
I have a backup file about 9gig to be resored. The hard drive has less than 11gig space free. Is it enough for restore? Thanks.
ZYTMaybe and maybe not.
If the db has free space in the files of more that about 1 gb, you will probably fail.
Give it a try and see. Worst case is that you fail and have to find some place else to restore it.|||betcha you got a lot of old backups eating up the drive...also betcha you use maint plans|||Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.
I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.|||Are you talking about adding this as a new DB or is it a restore to replace an existing one? Is it a shared drive or DB only? Are the log files on their own volume? If not, maybe put one (or more) there to free up some space - of course use care not to disturb some production log file that's happy on some dedicated volume. If it's not an existing DB (you're actually adding a 9+ GB database), it sure seems risky to run with that little of free space even if it were to succeed, considering that will impact all the stuff already on that disk. Also; suggest starting with a nicely defragged disk.
I don't know internals much so can't offer an answer to your question, but these questions do still seem relevant.
Hi, Vich:
Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.
ZYT|||Hi, Vich:
Thanks for advise. I am restoring as a new DB. Hard drive has DB only, but data files and log files are in same hard drive. Does SQL Server calculate space before restoring, or during restoring. I worry SQL Server finds the space in not enough on the half way of restoring. That may bring server to be died. Is this corret thought.
ZYT
Again; I do not know internals. I vaguely recall that the EM Restore Screen issues a "Checking for space" message while it's "thinking".
By "Hard drive has DB only, but data files and log files are in same hard drive", I gather you mean the drive is for the DB and all DB related files (log, DB backup, etc).
So; the very best case is that it fits. The DB may start out with a very small log file, but it will very quickly grow and then you'll be in trouble.
But Dude; you're nuts to be squeezing your main DB disk like this unless it's just an unaviodable emergency, and even then, your emergency may become a disaster.
For example; if your disk is highly fragmented then as free space nears it's end, even if there is theoretically enough space, the fragments may be too small. Also; you kill the chance for a decent defrag if you leave it so tight. Also; as databases and log files grow, they're sure to reach capacity quickly.
In other words, even if by some chance it does work, not for long.
Even with my shallow knowledge of all this, I believe that your solution will be to assign your log and backup files (for this and other databases) to another volume, preferably a dedicated volume. A simple mirrored volume is recommended. Ideally each DB will have it's own volume, but even sharing it will be better than having on the DB volume. If you have one huge production DB, you're probably best moving only it's log file there since sharing logs on volumes cause disk head thrashing that is very costly (like 10 times) to performance.
Let me know if you want instructions for this. I had to do it recently and have it all written into scripts. The others here (far more experienced thatn I) could bless the process before proceeding.|||Run this command on the backup file, to see what you are dealing with:
restore filelistonly from disk = 'your backup file goes here'
The output will list the size of each file.
Monday, March 26, 2012
How much memory is safe to reserve for SQL?
I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
Gb of RAM. One of the databases is taking 1GB of space and it has 8
connections running at all times. I want to limit the amount of memory
that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
server. How much memory is advise to let SQL use in this case? 50%, 60%
of the total memory of the server?Hi,
If the server is dedicated for SQL Server then I will go with dynamic memory
allocation. If you have any other application running in the same server
apart from SQL Server then probably limit the SQL Server memort by 1 GB or
so...
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156132604.617900.25730@.h48g2000cwc.googlegroups.com...
>I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?
>|||Thank you for your advise. The server actually runs a point of sale
system and some customize software too. My concern is that the memory
usage is always high, generally running at 70 of the memory. I read
that SQL is not very good releasing memory after a service runs, and
that it will only release it when a programa needs it. I don't want to
wait until Windows sends a low memory warning for SQL release the
memory.
If you recommend 1 GB, then my next question is: Can the SQL server
service be reprogram to restart one a day in the middle of the day? so,
it will release the memory that is hugging?
WR wrote:
> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?|||Hi,
Why do you want an outage in the middle of the day. Let SQL Server takes 1
GB RAM and use it for its own purpose.
The rest of the memory will be used for operating system and other
applications.You do not require a service restart.
Incase if you are experiancing slow performance; try monitoring the using
the memory related counters
mentioned in below URL.
[url]http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp[
/url]
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> Thank you for your advise. The server actually runs a point of sale
> system and some customize software too. My concern is that the memory
> usage is always high, generally running at 70 of the memory. I read
> that SQL is not very good releasing memory after a service runs, and
> that it will only release it when a programa needs it. I don't want to
> wait until Windows sends a low memory warning for SQL release the
> memory.
> If you recommend 1 GB, then my next question is: Can the SQL server
> service be reprogram to restart one a day in the middle of the day? so,
> it will release the memory that is hugging?
>
> WR wrote:
>|||Hello,
Thank you again for your information. I gets I was having too much
cafeine that day before I sent the email. What I meant to say what to
restart SQL once a day in the middle of the night.
Any way, I will follow your advise to your advise and monitor the
memory, still I would like to be able to not to have SQL taking all
that memory at all times. (I know, I am hard head) ;-)
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Why do you want an outage in the middle of the day. Let SQL Server takes 1
> GB RAM and use it for its own purpose.
> The rest of the memory will be used for operating system and other
> applications.You do not require a service restart.
> Incase if you are experiancing slow performance; try monitoring the using
> the memory related counters
> mentioned in below URL.
> http://www.sql-server-performance.c...ver.as
p
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "WR" <wilsonrey@.gmail.com> wrote in message
> news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...sql
Gb of RAM. One of the databases is taking 1GB of space and it has 8
connections running at all times. I want to limit the amount of memory
that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
server. How much memory is advise to let SQL use in this case? 50%, 60%
of the total memory of the server?Hi,
If the server is dedicated for SQL Server then I will go with dynamic memory
allocation. If you have any other application running in the same server
apart from SQL Server then probably limit the SQL Server memort by 1 GB or
so...
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156132604.617900.25730@.h48g2000cwc.googlegroups.com...
>I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?
>|||Thank you for your advise. The server actually runs a point of sale
system and some customize software too. My concern is that the memory
usage is always high, generally running at 70 of the memory. I read
that SQL is not very good releasing memory after a service runs, and
that it will only release it when a programa needs it. I don't want to
wait until Windows sends a low memory warning for SQL release the
memory.
If you recommend 1 GB, then my next question is: Can the SQL server
service be reprogram to restart one a day in the middle of the day? so,
it will release the memory that is hugging?
WR wrote:
> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?|||Hi,
Why do you want an outage in the middle of the day. Let SQL Server takes 1
GB RAM and use it for its own purpose.
The rest of the memory will be used for operating system and other
applications.You do not require a service restart.
Incase if you are experiancing slow performance; try monitoring the using
the memory related counters
mentioned in below URL.
[url]http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp[
/url]
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> Thank you for your advise. The server actually runs a point of sale
> system and some customize software too. My concern is that the memory
> usage is always high, generally running at 70 of the memory. I read
> that SQL is not very good releasing memory after a service runs, and
> that it will only release it when a programa needs it. I don't want to
> wait until Windows sends a low memory warning for SQL release the
> memory.
> If you recommend 1 GB, then my next question is: Can the SQL server
> service be reprogram to restart one a day in the middle of the day? so,
> it will release the memory that is hugging?
>
> WR wrote:
>|||Hello,
Thank you again for your information. I gets I was having too much
cafeine that day before I sent the email. What I meant to say what to
restart SQL once a day in the middle of the night.
Any way, I will follow your advise to your advise and monitor the
memory, still I would like to be able to not to have SQL taking all
that memory at all times. (I know, I am hard head) ;-)
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Why do you want an outage in the middle of the day. Let SQL Server takes 1
> GB RAM and use it for its own purpose.
> The rest of the memory will be used for operating system and other
> applications.You do not require a service restart.
> Incase if you are experiancing slow performance; try monitoring the using
> the memory related counters
> mentioned in below URL.
> http://www.sql-server-performance.c...ver.as
p
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "WR" <wilsonrey@.gmail.com> wrote in message
> news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...sql
How much memory is safe to reserve for SQL?
I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
Gb of RAM. One of the databases is taking 1GB of space and it has 8
connections running at all times. I want to limit the amount of memory
that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
server. How much memory is advise to let SQL use in this case? 50%, 60%
of the total memory of the server?Hi,
If the server is dedicated for SQL Server then I will go with dynamic memory
allocation. If you have any other application running in the same server
apart from SQL Server then probably limit the SQL Server memort by 1 GB or
so...
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156132604.617900.25730@.h48g2000cwc.googlegroups.com...
>I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?
>|||Thank you for your advise. The server actually runs a point of sale
system and some customize software too. My concern is that the memory
usage is always high, generally running at 70 of the memory. I read
that SQL is not very good releasing memory after a service runs, and
that it will only release it when a programa needs it. I don't want to
wait until Windows sends a low memory warning for SQL release the
memory.
If you recommend 1 GB, then my next question is: Can the SQL server
service be reprogram to restart one a day in the middle of the day? so,
it will release the memory that is hugging?
WR wrote:
> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?|||Hi,
Why do you want an outage in the middle of the day. Let SQL Server takes 1
GB RAM and use it for its own purpose.
The rest of the memory will be used for operating system and other
applications.You do not require a service restart.
Incase if you are experiancing slow performance; try monitoring the using
the memory related counters
mentioned in below URL.
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> Thank you for your advise. The server actually runs a point of sale
> system and some customize software too. My concern is that the memory
> usage is always high, generally running at 70 of the memory. I read
> that SQL is not very good releasing memory after a service runs, and
> that it will only release it when a programa needs it. I don't want to
> wait until Windows sends a low memory warning for SQL release the
> memory.
> If you recommend 1 GB, then my next question is: Can the SQL server
> service be reprogram to restart one a day in the middle of the day? so,
> it will release the memory that is hugging?
>
> WR wrote:
>> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
>> Gb of RAM. One of the databases is taking 1GB of space and it has 8
>> connections running at all times. I want to limit the amount of memory
>> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
>> server. How much memory is advise to let SQL use in this case? 50%, 60%
>> of the total memory of the server?
>|||Hello,
Thank you again for your information. I gets I was having too much
cafeine that day before I sent the email. What I meant to say what to
restart SQL once a day in the middle of the night.
Any way, I will follow your advise to your advise and monitor the
memory, still I would like to be able to not to have SQL taking all
that memory at all times. (I know, I am hard head) ;-)
Hari Prasad wrote:
> Hi,
> Why do you want an outage in the middle of the day. Let SQL Server takes 1
> GB RAM and use it for its own purpose.
> The rest of the memory will be used for operating system and other
> applications.You do not require a service restart.
> Incase if you are experiancing slow performance; try monitoring the using
> the memory related counters
> mentioned in below URL.
> http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "WR" <wilsonrey@.gmail.com> wrote in message
> news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> > Thank you for your advise. The server actually runs a point of sale
> > system and some customize software too. My concern is that the memory
> > usage is always high, generally running at 70 of the memory. I read
> > that SQL is not very good releasing memory after a service runs, and
> > that it will only release it when a programa needs it. I don't want to
> > wait until Windows sends a low memory warning for SQL release the
> > memory.
> > If you recommend 1 GB, then my next question is: Can the SQL server
> > service be reprogram to restart one a day in the middle of the day? so,
> > it will release the memory that is hugging?
> >
> >
> > WR wrote:
> >> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> >> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> >> connections running at all times. I want to limit the amount of memory
> >> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> >> server. How much memory is advise to let SQL use in this case? 50%, 60%
> >> of the total memory of the server?
> >
Gb of RAM. One of the databases is taking 1GB of space and it has 8
connections running at all times. I want to limit the amount of memory
that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
server. How much memory is advise to let SQL use in this case? 50%, 60%
of the total memory of the server?Hi,
If the server is dedicated for SQL Server then I will go with dynamic memory
allocation. If you have any other application running in the same server
apart from SQL Server then probably limit the SQL Server memort by 1 GB or
so...
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156132604.617900.25730@.h48g2000cwc.googlegroups.com...
>I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?
>|||Thank you for your advise. The server actually runs a point of sale
system and some customize software too. My concern is that the memory
usage is always high, generally running at 70 of the memory. I read
that SQL is not very good releasing memory after a service runs, and
that it will only release it when a programa needs it. I don't want to
wait until Windows sends a low memory warning for SQL release the
memory.
If you recommend 1 GB, then my next question is: Can the SQL server
service be reprogram to restart one a day in the middle of the day? so,
it will release the memory that is hugging?
WR wrote:
> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?|||Hi,
Why do you want an outage in the middle of the day. Let SQL Server takes 1
GB RAM and use it for its own purpose.
The rest of the memory will be used for operating system and other
applications.You do not require a service restart.
Incase if you are experiancing slow performance; try monitoring the using
the memory related counters
mentioned in below URL.
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> Thank you for your advise. The server actually runs a point of sale
> system and some customize software too. My concern is that the memory
> usage is always high, generally running at 70 of the memory. I read
> that SQL is not very good releasing memory after a service runs, and
> that it will only release it when a programa needs it. I don't want to
> wait until Windows sends a low memory warning for SQL release the
> memory.
> If you recommend 1 GB, then my next question is: Can the SQL server
> service be reprogram to restart one a day in the middle of the day? so,
> it will release the memory that is hugging?
>
> WR wrote:
>> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
>> Gb of RAM. One of the databases is taking 1GB of space and it has 8
>> connections running at all times. I want to limit the amount of memory
>> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
>> server. How much memory is advise to let SQL use in this case? 50%, 60%
>> of the total memory of the server?
>|||Hello,
Thank you again for your information. I gets I was having too much
cafeine that day before I sent the email. What I meant to say what to
restart SQL once a day in the middle of the night.
Any way, I will follow your advise to your advise and monitor the
memory, still I would like to be able to not to have SQL taking all
that memory at all times. (I know, I am hard head) ;-)
Hari Prasad wrote:
> Hi,
> Why do you want an outage in the middle of the day. Let SQL Server takes 1
> GB RAM and use it for its own purpose.
> The rest of the memory will be used for operating system and other
> applications.You do not require a service restart.
> Incase if you are experiancing slow performance; try monitoring the using
> the memory related counters
> mentioned in below URL.
> http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "WR" <wilsonrey@.gmail.com> wrote in message
> news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> > Thank you for your advise. The server actually runs a point of sale
> > system and some customize software too. My concern is that the memory
> > usage is always high, generally running at 70 of the memory. I read
> > that SQL is not very good releasing memory after a service runs, and
> > that it will only release it when a programa needs it. I don't want to
> > wait until Windows sends a low memory warning for SQL release the
> > memory.
> > If you recommend 1 GB, then my next question is: Can the SQL server
> > service be reprogram to restart one a day in the middle of the day? so,
> > it will release the memory that is hugging?
> >
> >
> > WR wrote:
> >> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> >> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> >> connections running at all times. I want to limit the amount of memory
> >> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> >> server. How much memory is advise to let SQL use in this case? 50%, 60%
> >> of the total memory of the server?
> >
How much memory is allotted to a single user connection?
Hi, everybody!
I wonder how much memory space is allotted to a single user connection.
I know MS SQL Server 6.5 allots 37KB of memory space to a single connection.
And I heard that MS SQL 2000 allots 500KB of memory space to a single
connection.
Is it true?
Thanks in advance.Where did u get this stat from ... Will be interested to see the same ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>|||When in doubt, always first check the BOL. Under the section "Memory used by
SQL Server 2000 Objects Specifications", the BOL indicates that:
The memory used by a use connection uses 12 KB + (3 * Network Packet Size),
where Network Packet Size is controlled by the 'network packet size'
configuration option. The default is 4KB.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>
I wonder how much memory space is allotted to a single user connection.
I know MS SQL Server 6.5 allots 37KB of memory space to a single connection.
And I heard that MS SQL 2000 allots 500KB of memory space to a single
connection.
Is it true?
Thanks in advance.Where did u get this stat from ... Will be interested to see the same ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>|||When in doubt, always first check the BOL. Under the section "Memory used by
SQL Server 2000 Objects Specifications", the BOL indicates that:
The memory used by a use connection uses 12 KB + (3 * Network Packet Size),
where Network Packet Size is controlled by the 'network packet size'
configuration option. The default is 4KB.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>
Friday, March 23, 2012
How much allocated space is actually used?
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOW
FILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't se
parate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/feat...10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SH
OWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't
separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOW
FILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't se
parate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/feat...10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SH
OWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't
separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
How much allocated space is actually used?
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
Check out sp_spaceused in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/featu...0894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
Check out sp_spaceused in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/featu...0894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
How much allocated space is actually used?
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> > Hi,
> >
> > This is SQL 2000.
> > 3G has been allocated to store data for a database. Restoring this database
> > takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> > restored.
> > So is there any way to tell how much allocated space is actually used by a
> > database?
> >
> > Thanks in advance,
> >
> > Bing
>
>
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> > Hi,
> >
> > This is SQL 2000.
> > 3G has been allocated to store data for a database. Restoring this database
> > takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> > restored.
> > So is there any way to tell how much allocated space is actually used by a
> > database?
> >
> > Thanks in advance,
> >
> > Bing
>
>
Sunday, February 19, 2012
How I can get the database-property "space available" using osql or isql?
If you see the properties of a database, you can see the "space available" attribute.
Can anyone hint on how I can get it using isql or osql (CLI) ?
Is there any query or stored procedure to get this attribute value for the specified or all the databases?would sp_spaceused help at all??
Can anyone hint on how I can get it using isql or osql (CLI) ?
Is there any query or stored procedure to get this attribute value for the specified or all the databases?would sp_spaceused help at all??
Subscribe to:
Posts (Atom)