DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
Jerry
Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>
|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>

DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to
sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC
SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for sever
al import tables.
> I've run the DataSizer tool and it calculates the following for a table wi
th 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the follo
wing information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table s
ize est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the
calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compar
e to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get
:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-)
.
Btw, I always select from sysindexes (after updateusage) when I want to get
these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To b
e honest, I don't
remember the difference between dpages and used by heart either, so I always
look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I
was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered in
dex on the table then
> dropped the clustered index to recompact the heap pages. The 137 million
rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than
before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>

DataSizer for 2000

Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for
>> several import tables.
>> I've run the DataSizer tool and it calculates the following for a table
>> with 3 INT fields and one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the
>> following information via sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table
>> size est and table size actual). Is the DataSizer tool accurate for SQL
>> 2000? Am I performing the calculation incorrectly?
>> Thanks
>> Jerry
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
>> DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for several import tables.
>> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and
>> one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the following information via
>> sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table size est and table size
>> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
>> incorrectly?
>> Thanks
>> Jerry
>>
>

datalength( '' + space(5) + '' ) = 7 !??

Hello,
What configuration could cause a database to substitute a space character
for empty strings ?
Two databases on the same server. In one the statement select '' + '123' +
'' yields " 123 " and on the other the same statement yields "123".
Thanks for any insight
APSET ANSI_PADDING
--
HTH
Ryan Waight, MCDBA, MCSE
"Antoine Perret" <antoine@.bow.ch> wrote in message
news:eUi$EfYjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What configuration could cause a database to substitute a space character
> for empty strings ?
> Two databases on the same server. In one the statement select '' + '123' +
> '' yields " 123 " and on the other the same statement yields "123".
> Thanks for any insight
> AP
>|||Thank-you sir!|||Hi Antoine,
The database that yields " 123 " is probably on a compatibility level of 60
or 65.
Run
EXEC sp_dbcmptlevel [ [ @.dbname = ] name ]
to check.
--
Jacco Schalkwijk
SQL Server MVP
"Antoine Perret" <antoine@.bow.ch> wrote in message
news:eUi$EfYjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What configuration could cause a database to substitute a space character
> for empty strings ?
> Two databases on the same server. In one the statement select '' + '123' +
> '' yields " 123 " and on the other the same statement yields "123".
> Thanks for any insight
> AP
>|||Yes that is it.
Thanks
Funny I don't recall that feature on older versions...

datafile usage

If a database consists of more than one datafile, how does SQL Server use the space in these datafiles ?, does it fill up the first one then move to the next and so forth, or does it use up pages across all the files evenly ?.In a group, the files are all filled proportionnally to their size.

For example : a group with file1 = 100 MB and file2 = 200 MB
If you add 3 MB, 1 MB is added to file1, and 2 MB to file2.|||Thanks for the information|||You're welcome ! :)

Datafile Full

Hi NG,
We have a datafile on drive d: and the log file on drive e: and now the
problem is drive d: is running out of space. We are planning to buy a new
hardisk drive f: what should I do next after we install the new hardisk?
Should I create a new datafile and include that in the primary filegroup and
set the new datafile as default. Is that all I need to do?
Thank you in advance.
its 1 option, yes.
just disable the file growth option for the first file (the one on the d
you can also create a new file group and move some tables from the primary
file group to the new one.
"JackCool" <jackcool@.yahoo.com> wrote in message
news:upMbY3aKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> We have a datafile on drive d: and the log file on drive e: and now the
> problem is drive d: is running out of space. We are planning to buy a new
> hardisk drive f: what should I do next after we install the new hardisk?
> Should I create a new datafile and include that in the primary filegroup
> and set the new datafile as default. Is that all I need to do?
> Thank you in advance.
>

Datafile Full

Hi NG,
We have a datafile on drive d: and the log file on drive e: and now the
problem is drive d: is running out of space. We are planning to buy a new
hardisk drive f: what should I do next after we install the new hardisk?
Should I create a new datafile and include that in the primary filegroup and
set the new datafile as default. Is that all I need to do?
Thank you in advance.its 1 option, yes.
just disable the file growth option for the first file (the one on the d
you can also create a new file group and move some tables from the primary
file group to the new one.
"JackCool" <jackcool@.yahoo.com> wrote in message
news:upMbY3aKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> We have a datafile on drive d: and the log file on drive e: and now the
> problem is drive d: is running out of space. We are planning to buy a new
> hardisk drive f: what should I do next after we install the new hardisk?
> Should I create a new datafile and include that in the primary filegroup
> and set the new datafile as default. Is that all I need to do?
> Thank you in advance.
>

Databasefile can not automatically grow

Hi!
I have a database (12Gb) that is configured to automatically grow with 10 %,
unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
When the file becomes full the event log alarms with the message: Could not
allocate space for object '' in database '' because the 'PRIMARY' filegroup
is full.
Why does not the file automatically grow?
Someone out there having the same experience?
/Christofer Fransson
Run UPDATESTATISTICS command (see in the BOL)
I'd check a size of TEMPDB as well
"christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
> Hi!
> I have a database (12Gb) that is configured to automatically grow with 10
> %,
> unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> When the file becomes full the event log alarms with the message: Could
> not
> allocate space for object '' in database '' because the 'PRIMARY'
> filegroup
> is full.
> Why does not the file automatically grow?
> Someone out there having the same experience?
>
> /Christofer Fransson
|||The tempdb is not a problem it has enough space.
What does the update statistics do in this case?
"Uri Dimant" wrote:

> Run UPDATESTATISTICS command (see in the BOL)
> I'd check a size of TEMPDB as well
>
> "christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
> in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
>
>
|||BOL says
Updates information about the distribution of key values for one or more
statistics groups (collections) in the specified table or indexed view. To
create statistics on columns
Look , I will try to explain what happened as I'm understanding it
Your database is set up in Autgow method by 10 percent for example. Each
process/transaction is filling the database and when this (Autogrow) feature
kicks in ,SQL Server needs to allocate free space and new datapages in the
datafile
Once it found it marks it as 'occupied'. While your database growing it
does take some time and process has to wait and at this time as all new
datapage is 'occupied' , the system throws the error
I hope you get the idea
"christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
in message news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...[vbcol=seagreen]
> The tempdb is not a problem it has enough space.
> What does the update statistics do in this case?
> "Uri Dimant" wrote:
|||Verify that 'Maximum Size' for the Data File is not set to the Current Size
of the Data File.
"christofer fransson" wrote:

> Hi!
> I have a database (12Gb) that is configured to automatically grow with 10 %,
> unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> When the file becomes full the event log alarms with the message: Could not
> allocate space for object '' in database '' because the 'PRIMARY' filegroup
> is full.
> Why does not the file automatically grow?
> Someone out there having the same experience?
>
> /Christofer Fransson
|||Have you tried manually growing the file?
Could there be an NT restriction eg a quota
Paul
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236J7vBUPGHA.2888@.tk2msftngp13.phx.gbl...
> BOL says
> Updates information about the distribution of key values for one or more
> statistics groups (collections) in the specified table or indexed view. To
> create statistics on columns
> Look , I will try to explain what happened as I'm understanding it
> Your database is set up in Autgow method by 10 percent for example. Each
> process/transaction is filling the database and when this (Autogrow)
> feature kicks in ,SQL Server needs to allocate free space and new
> datapages in the datafile
> Once it found it marks it as 'occupied'. While your database growing it
> does take some time and process has to wait and at this time as all new
> datapage is 'occupied' , the system throws the error
> I hope you get the idea
>
>
>
> "christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
> in message news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...
>
|||Paul
http://www.sql-server-performance.co...e_settings.asp
"Paul Cahill" <anon@.anon.com> wrote in message
news:ewZOlZUPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Have you tried manually growing the file?
> Could there be an NT restriction eg a quota
> Paul
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236J7vBUPGHA.2888@.tk2msftngp13.phx.gbl...
>
|||What is the timing?
Basically, at 12GB your growth will be 1.2GB. As was pointed out, SS has to
'initialize' each block in that growth. It takes time to write 1.2GB. WHile
that is happening, processes can't write to the db.
How long it will take to do the initialization depends on your box and disk
speeds.
How long have you waited to see if the problem 'fixes itself'? If less than
1/2 hour (again depending on box and disks), you may not have given it enough
time.)
I suggest a different growth pattern of , say 16MB at a time instead of
exponential % growth.
Joseph R.P. Maloney, CSP,CCP,CDP
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Verify that 'Maximum Size' for the Data File is not set to the Current Size
> of the Data File.
> "christofer fransson" wrote:

Databasefile can not automatically grow

Hi!
I have a database (12Gb) that is configured to automatically grow with 10 %,
unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
When the file becomes full the event log alarms with the message: Could not
allocate space for object '' in database '' because the 'PRIMARY' filegroup
is full.
Why does not the file automatically grow'
Someone out there having the same experience?
/Christofer FranssonRun UPDATESTATISTICS command (see in the BOL)
I'd check a size of TEMPDB as well
"christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
> Hi!
> I have a database (12Gb) that is configured to automatically grow with 10
> %,
> unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> When the file becomes full the event log alarms with the message: Could
> not
> allocate space for object '' in database '' because the 'PRIMARY'
> filegroup
> is full.
> Why does not the file automatically grow'
> Someone out there having the same experience?
>
> /Christofer Fransson|||The tempdb is not a problem it has enough space.
What does the update statistics do in this case?
"Uri Dimant" wrote:
> Run UPDATESTATISTICS command (see in the BOL)
> I'd check a size of TEMPDB as well
>
> "christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
> in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
> > Hi!
> >
> > I have a database (12Gb) that is configured to automatically grow with 10
> > %,
> > unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> > When the file becomes full the event log alarms with the message: Could
> > not
> > allocate space for object '' in database '' because the 'PRIMARY'
> > filegroup
> > is full.
> >
> > Why does not the file automatically grow'
> >
> > Someone out there having the same experience?
> >
> >
> > /Christofer Fransson
>
>|||BOL says
Updates information about the distribution of key values for one or more
statistics groups (collections) in the specified table or indexed view. To
create statistics on columns
Look , I will try to explain what happened as I'm understanding it
Your database is set up in Autgow method by 10 percent for example. Each
process/transaction is filling the database and when this (Autogrow) feature
kicks in ,SQL Server needs to allocate free space and new datapages in the
datafile
Once it found it marks it as 'occupied'. While your database growing it
does take some time and process has to wait and at this time as all new
datapage is 'occupied' , the system throws the error
I hope you get the idea
"christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
in message news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...
> The tempdb is not a problem it has enough space.
> What does the update statistics do in this case?
> "Uri Dimant" wrote:
>> Run UPDATESTATISTICS command (see in the BOL)
>> I'd check a size of TEMPDB as well
>>
>> "christofer fransson" <christoferfransson@.discussions.microsoft.com>
>> wrote
>> in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
>> > Hi!
>> >
>> > I have a database (12Gb) that is configured to automatically grow with
>> > 10
>> > %,
>> > unrestricted file growth and a lot of space left on disk. SQL2000 ENT
>> > ed.
>> > When the file becomes full the event log alarms with the message: Could
>> > not
>> > allocate space for object '' in database '' because the 'PRIMARY'
>> > filegroup
>> > is full.
>> >
>> > Why does not the file automatically grow'
>> >
>> > Someone out there having the same experience?
>> >
>> >
>> > /Christofer Fransson
>>|||Verify that 'Maximum Size' for the Data File is not set to the Current Size
of the Data File.
"christofer fransson" wrote:
> Hi!
> I have a database (12Gb) that is configured to automatically grow with 10 %,
> unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> When the file becomes full the event log alarms with the message: Could not
> allocate space for object '' in database '' because the 'PRIMARY' filegroup
> is full.
> Why does not the file automatically grow'
> Someone out there having the same experience?
>
> /Christofer Fransson|||Have you tried manually growing the file?
Could there be an NT restriction eg a quota
Paul
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236J7vBUPGHA.2888@.tk2msftngp13.phx.gbl...
> BOL says
> Updates information about the distribution of key values for one or more
> statistics groups (collections) in the specified table or indexed view. To
> create statistics on columns
> Look , I will try to explain what happened as I'm understanding it
> Your database is set up in Autgow method by 10 percent for example. Each
> process/transaction is filling the database and when this (Autogrow)
> feature kicks in ,SQL Server needs to allocate free space and new
> datapages in the datafile
> Once it found it marks it as 'occupied'. While your database growing it
> does take some time and process has to wait and at this time as all new
> datapage is 'occupied' , the system throws the error
> I hope you get the idea
>
>
>
> "christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
> in message news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...
>> The tempdb is not a problem it has enough space.
>> What does the update statistics do in this case?
>> "Uri Dimant" wrote:
>>
>> Run UPDATESTATISTICS command (see in the BOL)
>> I'd check a size of TEMPDB as well
>>
>> "christofer fransson" <christoferfransson@.discussions.microsoft.com>
>> wrote
>> in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
>> > Hi!
>> >
>> > I have a database (12Gb) that is configured to automatically grow with
>> > 10
>> > %,
>> > unrestricted file growth and a lot of space left on disk. SQL2000 ENT
>> > ed.
>> > When the file becomes full the event log alarms with the message:
>> > Could
>> > not
>> > allocate space for object '' in database '' because the 'PRIMARY'
>> > filegroup
>> > is full.
>> >
>> > Why does not the file automatically grow'
>> >
>> > Someone out there having the same experience?
>> >
>> >
>> > /Christofer Fransson
>>
>|||Paul
http://www.sql-server-performance.com/database_settings.asp
"Paul Cahill" <anon@.anon.com> wrote in message
news:ewZOlZUPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Have you tried manually growing the file?
> Could there be an NT restriction eg a quota
> Paul
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236J7vBUPGHA.2888@.tk2msftngp13.phx.gbl...
>> BOL says
>> Updates information about the distribution of key values for one or more
>> statistics groups (collections) in the specified table or indexed view.
>> To create statistics on columns
>> Look , I will try to explain what happened as I'm understanding it
>> Your database is set up in Autgow method by 10 percent for example. Each
>> process/transaction is filling the database and when this (Autogrow)
>> feature kicks in ,SQL Server needs to allocate free space and new
>> datapages in the datafile
>> Once it found it marks it as 'occupied'. While your database growing it
>> does take some time and process has to wait and at this time as all new
>> datapage is 'occupied' , the system throws the error
>> I hope you get the idea
>>
>>
>>
>> "christofer fransson" <christoferfransson@.discussions.microsoft.com>
>> wrote in message
>> news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...
>> The tempdb is not a problem it has enough space.
>> What does the update statistics do in this case?
>> "Uri Dimant" wrote:
>>
>> Run UPDATESTATISTICS command (see in the BOL)
>> I'd check a size of TEMPDB as well
>>
>> "christofer fransson" <christoferfransson@.discussions.microsoft.com>
>> wrote
>> in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
>> > Hi!
>> >
>> > I have a database (12Gb) that is configured to automatically grow
>> > with 10
>> > %,
>> > unrestricted file growth and a lot of space left on disk. SQL2000 ENT
>> > ed.
>> > When the file becomes full the event log alarms with the message:
>> > Could
>> > not
>> > allocate space for object '' in database '' because the 'PRIMARY'
>> > filegroup
>> > is full.
>> >
>> > Why does not the file automatically grow'
>> >
>> > Someone out there having the same experience?
>> >
>> >
>> > /Christofer Fransson
>>
>>
>|||What is the timing?
Basically, at 12GB your growth will be 1.2GB. As was pointed out, SS has to
'initialize' each block in that growth. It takes time to write 1.2GB. WHile
that is happening, processes can't write to the db.
How long it will take to do the initialization depends on your box and disk
speeds.
How long have you waited to see if the problem 'fixes itself'? If less than
1/2 hour (again depending on box and disks), you may not have given it enough
time.)
I suggest a different growth pattern of , say 16MB at a time instead of
exponential % growth.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Absar Ahmad" wrote:
> Verify that 'Maximum Size' for the Data File is not set to the Current Size
> of the Data File.
> "christofer fransson" wrote:
> > Hi!
> >
> > I have a database (12Gb) that is configured to automatically grow with 10 %,
> > unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> > When the file becomes full the event log alarms with the message: Could not
> > allocate space for object '' in database '' because the 'PRIMARY' filegroup
> > is full.
> >
> > Why does not the file automatically grow'
> >
> > Someone out there having the same experience?
> >
> >
> > /Christofer Fransson

Databasefile can not automatically grow

Hi!
I have a database (12Gb) that is configured to automatically grow with 10 %,
unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
When the file becomes full the event log alarms with the message: Could not
allocate space for object '' in database '' because the 'PRIMARY' filegroup
is full.
Why does not the file automatically grow'
Someone out there having the same experience?
/Christofer FranssonBOL says
Updates information about the distribution of key values for one or more
statistics groups (collections) in the specified table or indexed view. To
create statistics on columns
Look , I will try to explain what happened as I'm understanding it
Your database is set up in Autgow method by 10 percent for example. Each
process/transaction is filling the database and when this (Autogrow) feature
kicks in ,SQL Server needs to allocate free space and new datapages in the
datafile
Once it found it marks it as 'occupied'. While your database growing it
does take some time and process has to wait and at this time as all new
datapage is 'occupied' , the system throws the error
I hope you get the idea
"christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
in message news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...[vbcol=seagreen]
> The tempdb is not a problem it has enough space.
> What does the update statistics do in this case?
> "Uri Dimant" wrote:
>|||Verify that 'Maximum Size' for the Data File is not set to the Current Size
of the Data File.
"christofer fransson" wrote:

> Hi!
> I have a database (12Gb) that is configured to automatically grow with 10
%,
> unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> When the file becomes full the event log alarms with the message: Could no
t
> allocate space for object '' in database '' because the 'PRIMARY' filegrou
p
> is full.
> Why does not the file automatically grow'
> Someone out there having the same experience?
>
> /Christofer Fransson|||Have you tried manually growing the file?
Could there be an NT restriction eg a quota
Paul
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236J7vBUPGHA.2888@.tk2msftngp13.phx.gbl...
> BOL says
> Updates information about the distribution of key values for one or more
> statistics groups (collections) in the specified table or indexed view. To
> create statistics on columns
> Look , I will try to explain what happened as I'm understanding it
> Your database is set up in Autgow method by 10 percent for example. Each
> process/transaction is filling the database and when this (Autogrow)
> feature kicks in ,SQL Server needs to allocate free space and new
> datapages in the datafile
> Once it found it marks it as 'occupied'. While your database growing it
> does take some time and process has to wait and at this time as all new
> datapage is 'occupied' , the system throws the error
> I hope you get the idea
>
>
>
> "christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
> in message news:C8840E12-8AC3-46AD-9342-5BFD7FAA1362@.microsoft.com...
>|||Paul
http://www.sql-server-performance.c...se_settings.asp
"Paul Cahill" <anon@.anon.com> wrote in message
news:ewZOlZUPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Have you tried manually growing the file?
> Could there be an NT restriction eg a quota
> Paul
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236J7vBUPGHA.2888@.tk2msftngp13.phx.gbl...
>|||What is the timing?
Basically, at 12GB your growth will be 1.2GB. As was pointed out, SS has to
'initialize' each block in that growth. It takes time to write 1.2GB. WHile
that is happening, processes can't write to the db.
How long it will take to do the initialization depends on your box and disk
speeds.
How long have you waited to see if the problem 'fixes itself'? If less than
1/2 hour (again depending on box and disks), you may not have given it enoug
h
time.)
I suggest a different growth pattern of , say 16MB at a time instead of
exponential % growth.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Verify that 'Maximum Size' for the Data File is not set to the Current Siz
e
> of the Data File.
> "christofer fransson" wrote:
>|||Run UPDATESTATISTICS command (see in the BOL)
I'd check a size of TEMPDB as well
"christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
> Hi!
> I have a database (12Gb) that is configured to automatically grow with 10
> %,
> unrestricted file growth and a lot of space left on disk. SQL2000 ENT ed.
> When the file becomes full the event log alarms with the message: Could
> not
> allocate space for object '' in database '' because the 'PRIMARY'
> filegroup
> is full.
> Why does not the file automatically grow'
> Someone out there having the same experience?
>
> /Christofer Fransson|||The tempdb is not a problem it has enough space.
What does the update statistics do in this case?
"Uri Dimant" wrote:

> Run UPDATESTATISTICS command (see in the BOL)
> I'd check a size of TEMPDB as well
>
> "christofer fransson" <christoferfransson@.discussions.microsoft.com> wrote
> in message news:CC9B7757-7D25-4D3D-BBE5-452DAAD71F2E@.microsoft.com...
>
>

Database used space info

I want to get the Database used space info (Count in the
Gray bar NOT the blue) in daily bases. How can I find this
info to put it into a query/script to run it every day. I
don't want the log info, just the dataspace used. Anyone
has an idea or a script to share.
Thanks for any help.Undocumented command DBCC SHOWFILESTATS. I think that is how Enterprise
Mangler populates the graph. I originally found this by reverse engineering
a third-party monitoring tool. I wrote a quick wrapper and dumped the
results to a table (WITH TABLERESULTS works) and report on percentages, etc.
As always, use undocumented DBCC commands at your own risk.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"George" <anonymous@.discussions.microsoft.com> wrote in message
news:011101c3d614$c6dc9260$a401280a@.phx.gbl...
> I want to get the Database used space info (Count in the
> Gray bar NOT the blue) in daily bases. How can I find this
> info to put it into a query/script to run it every day. I
> don't want the log info, just the dataspace used. Anyone
> has an idea or a script to share.
> Thanks for any help.|||I think that is it......
Thanks.
>--Original Message--
>Undocumented command DBCC SHOWFILESTATS. I think that is
how Enterprise
>Mangler populates the graph. I originally found this by
reverse engineering
>a third-party monitoring tool. I wrote a quick wrapper
and dumped the
>results to a table (WITH TABLERESULTS works) and report
on percentages, etc.
>As always, use undocumented DBCC commands at your own
risk.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>
>"George" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011101c3d614$c6dc9260$a401280a@.phx.gbl...
>> I want to get the Database used space info (Count in the
>> Gray bar NOT the blue) in daily bases. How can I find
this
>> info to put it into a query/script to run it every day.
I
>> don't want the log info, just the dataspace used. Anyone
>> has an idea or a script to share.
>> Thanks for any help.
>
>.
>|||This should help.
http://www.databasejournal.com/features/msaccess/article.ph
p/1467791
Best Regards
Thirumal Reddy Maram
>--Original Message--
>I want to get the Database used space info (Count in the
>Gray bar NOT the blue) in daily bases. How can I find
this
>info to put it into a query/script to run it every day. I
>don't want the log info, just the dataspace used. Anyone
>has an idea or a script to share.
>Thanks for any help.
>.
>

Database Tuning Advisor - how does the max. space parameter work?

I think that the min. space required for physical design structures may be
reported incorrectly. Please see the thread at the following URL:
http://groups.google.ca/group/SQL-Se...f3426d5a?hl=en
No it is not.I have tried my best to explain in the other thread based
on my interpretation of your posting