How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:
> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
> > Database space can be allocated using EM. But sometimes people allocated a
> > huge size for data or log file, for instance, 10GB.
> >
> > When the database goes into running for production, maybe only needs maximun
> > 1GB
> > . So I want to reduce this size.
> >
> > Shrink only truncate log.
> >
> > How to do this with recreating a db. How to know the space used in db? For
> > instance, maybe only 300MB used although 10GB allocated.
> >
> > Does the huge space affect the db performance?
> >|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
> > Hi
> >
> > Check out DBCC SHRINKFILE see
> > http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> >
> > If you allocate the size too small the database will only spend time and
> > resources growing the database, therefore it should be a reasonable initial
> > size that avoids this. If the files are always growing and being shrunk you
> > may get fragmentation of the physical files may affect performance.
> >
> > If you are in full recovery mode and have regular log backups, the size of
> > the log file should be reasonably constant. You should make sure that the
> > initial size is also large enough to cover your normal usage. Read
> > http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> > information.
> >
> > Make sure that your filegrowth is not set to a percentage otherwise it would
> > grow exponentially.
> >
> > John
> >
> > "KentZhou" wrote:
> >
> > > Database space can be allocated using EM. But sometimes people allocated a
> > > huge size for data or log file, for instance, 10GB.
> > >
> > > When the database goes into running for production, maybe only needs maximun
> > > 1GB
> > > . So I want to reduce this size.
> > >
> > > Shrink only truncate log.
> > >
> > > How to do this with recreating a db. How to know the space used in db? For
> > > instance, maybe only 300MB used although 10GB allocated.
> > >
> > > Does the huge space affect the db performance?
> > >sql

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maxim
un
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initia
l
> size that avoids this. If the files are always growing and being shrunk yo
u
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it wou
ld
> grow exponentially.
> John
> "KentZhou" wrote:
>|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DB
CC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
>

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
.. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?
Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>
|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:

how to change model DB

Hi,
How to change model DB, so that new created DB can use file size & location
or other options copied from model DB?
Thanks,
Jack
just clarification: change the default values of the database options for
Model DB, so any newly created databases can use new value such as DB file
location & size.
Many thanks,
"Jack" <chongh.hc@.gmail.com> wrote in message
news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How to change model DB, so that new created DB can use file size &
> location or other options copied from model DB?
> Thanks,
> Jack
>
|||Jack
You simply need to Right Click on the Model Database in SQL Server
Enterprise Manager and update the desired properties such as the Data File
and Log File sizes. To set the Default database location you need to Right
Click on the SQL Server instance in SQL Server Enterprise Manager and Select
the Database Settings Tab and set the Default Data and Log directories.
- Peter Ward
WARDY IT Solutions
"Jack" wrote:

> just clarification: change the default values of the database options for
> Model DB, so any newly created databases can use new value such as DB file
> location & size.
> Many thanks,
> "Jack" <chongh.hc@.gmail.com> wrote in message
> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>
|||Excellent!
thanks, Peter!
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5039A552-9DB0-4C62-ACA1-24C85B045C2C@.microsoft.com...[vbcol=seagreen]
> Jack
> You simply need to Right Click on the Model Database in SQL Server
> Enterprise Manager and update the desired properties such as the Data File
> and Log File sizes. To set the Default database location you need to
> Right
> Click on the SQL Server instance in SQL Server Enterprise Manager and
> Select
> the Database Settings Tab and set the Default Data and Log directories.
>
> - Peter Ward
> WARDY IT Solutions
> "Jack" wrote:
sql

how to change model DB

Hi,
How to change model DB, so that new created DB can use file size & location
or other options copied from model DB?
Thanks,
Jackjust clarification: change the default values of the database options for
Model DB, so any newly created databases can use new value such as DB file
location & size.
Many thanks,
"Jack" <chongh.hc@.gmail.com> wrote in message
news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How to change model DB, so that new created DB can use file size &
> location or other options copied from model DB?
> Thanks,
> Jack
>|||Jack
You simply need to Right Click on the Model Database in SQL Server
Enterprise Manager and update the desired properties such as the Data File
and Log File sizes. To set the Default database location you need to Right
Click on the SQL Server instance in SQL Server Enterprise Manager and Select
the Database Settings Tab and set the Default Data and Log directories.
- Peter Ward
WARDY IT Solutions
"Jack" wrote:
> just clarification: change the default values of the database options for
> Model DB, so any newly created databases can use new value such as DB file
> location & size.
> Many thanks,
> "Jack" <chongh.hc@.gmail.com> wrote in message
> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > How to change model DB, so that new created DB can use file size &
> > location or other options copied from model DB?
> >
> > Thanks,
> >
> > Jack
> >
>
>|||Excellent!
thanks, Peter!
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5039A552-9DB0-4C62-ACA1-24C85B045C2C@.microsoft.com...
> Jack
> You simply need to Right Click on the Model Database in SQL Server
> Enterprise Manager and update the desired properties such as the Data File
> and Log File sizes. To set the Default database location you need to
> Right
> Click on the SQL Server instance in SQL Server Enterprise Manager and
> Select
> the Database Settings Tab and set the Default Data and Log directories.
>
> - Peter Ward
> WARDY IT Solutions
> "Jack" wrote:
>> just clarification: change the default values of the database options for
>> Model DB, so any newly created databases can use new value such as DB
>> file
>> location & size.
>> Many thanks,
>> "Jack" <chongh.hc@.gmail.com> wrote in message
>> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > How to change model DB, so that new created DB can use file size &
>> > location or other options copied from model DB?
>> >
>> > Thanks,
>> >
>> > Jack
>> >
>>

how to change model DB

Hi,
How to change model DB, so that new created DB can use file size & location
or other options copied from model DB?
Thanks,
Jackjust clarification: change the default values of the database options for
Model DB, so any newly created databases can use new value such as DB file
location & size.
Many thanks,
"Jack" <chongh.hc@.gmail.com> wrote in message
news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How to change model DB, so that new created DB can use file size &
> location or other options copied from model DB?
> Thanks,
> Jack
>|||Jack
You simply need to Right Click on the Model Database in SQL Server
Enterprise Manager and update the desired properties such as the Data File
and Log File sizes. To set the Default database location you need to Right
Click on the SQL Server instance in SQL Server Enterprise Manager and Select
the Database Settings Tab and set the Default Data and Log directories.
- Peter Ward
WARDY IT Solutions
"Jack" wrote:

> just clarification: change the default values of the database options for
> Model DB, so any newly created databases can use new value such as DB file
> location & size.
> Many thanks,
> "Jack" <chongh.hc@.gmail.com> wrote in message
> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>|||Excellent!
thanks, Peter!
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5039A552-9DB0-4C62-ACA1-24C85B045C2C@.microsoft.com...[vbcol=seagreen]
> Jack
> You simply need to Right Click on the Model Database in SQL Server
> Enterprise Manager and update the desired properties such as the Data File
> and Log File sizes. To set the Default database location you need to
> Right
> Click on the SQL Server instance in SQL Server Enterprise Manager and
> Select
> the Database Settings Tab and set the Default Data and Log directories.
>
> - Peter Ward
> WARDY IT Solutions
> "Jack" wrote:
>

how to change dts import/export windows size

Howdy!
Does any of you good folk know how to change window size of the DTS
Import/Export Wizard window?
Darn thing is very small and inconvinient to work with, no apparent way to
change it's size,
may be there is a registry tweak or something.
Thanks in advance,
I.B.Yes, It's inconvenient. No, there is no way to change the size of it, as
far as I know. Maybe in MS SQL SERVER 2004?
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Ilya Bari" <IBari@.SnappyDsl.net> wrote in message
news:4015c5fd@.news.snappydsl.net...
> Howdy!
> Does any of you good folk know how to change window size of the DTS
> Import/Export Wizard window?
> Darn thing is very small and inconvinient to work with, no apparent way to
> change it's size,
> may be there is a registry tweak or something.
> Thanks in advance,
> I.B.

How to calculate the index size ?

Hi:
I need to calculate the total size of all indexes, is the index_size
reported by sp_spaceused 'tablename' is what i need ?
Please help
Thanks
JCVoonThe following is a link to useful sps in what you are looking to do.
Specifically look at:
sp_columns_rowset
sp_MShelpindex
sp_MStablespace
sp_MSindexspace
In SQL Books there is an article called: Estimating the Size of a Table ,
which will give you some ideas
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1147078009.708121.91560@.i40g2000cwc.googlegroups.com...
> Hi:
> I need to calculate the total size of all indexes, is the index_size
> reported by sp_spaceused 'tablename' is what i need ?
> Please help
> Thanks
> JCVoon
>|||Jack Vamvas :
Thanks.
sp_MSindexspace is what i need, in fact the index_size reported by
sp_spaceused 'tablename' is sum of the Size reported by
sp_MSindexspace 'tablename' .
Regards
JCVoon

How to calculate the index size ?

Hi:
I need to calculate the total size of all indexes, is the index_size
reported by sp_spaceused 'tablename' is what i need ?
Please help
Thanks
JCVoonThe following is a link to useful sps in what you are looking to do.
Specifically look at:
sp_columns_rowset
sp_MShelpindex
sp_MStablespace
sp_MSindexspace
In SQL Books there is an article called: Estimating the Size of a Table ,
which will give you some ideas
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1147078009.708121.91560@.i40g2000cwc.googlegroups.com...
> Hi:
> I need to calculate the total size of all indexes, is the index_size
> reported by sp_spaceused 'tablename' is what i need ?
> Please help
> Thanks
> JCVoon
>|||Jack Vamvas :
Thanks.
sp_MSindexspace is what i need, in fact the index_size reported by
sp_spaceused 'tablename' is sum of the Size reported by
sp_MSindexspace 'tablename' .
Regards
JCVoon

How to calculate table and index size

Can anyone point me in the right direction on how to calculate table and
index sizes
TIA
RobEXEC sp_spaceused 'tablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"Rob Diamant" <rob@.usi.com> wrote in message
news:#4qxCfGsEHA.808@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on how to calculate table and
> index sizes
> TIA
> Rob
>

How to calculate table and index size

Can anyone point me in the right direction on how to calculate table and
index sizes
TIA
RobEXEC sp_spaceused 'tablename'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Rob Diamant" <rob@.usi.com> wrote in message
news:#4qxCfGsEHA.808@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on how to calculate table and
> index sizes
> TIA
> Rob
>

How to calculate table and index size

Can anyone point me in the right direction on how to calculate table and
index sizes
TIA
Rob
EXEC sp_spaceused 'tablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"Rob Diamant" <rob@.usi.com> wrote in message
news:#4qxCfGsEHA.808@.TK2MSFTNGP12.phx.gbl...
> Can anyone point me in the right direction on how to calculate table and
> index sizes
> TIA
> Rob
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
>> will record size be diferent if I save in each field value 0 versus if I
>> save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>>I should add that I am using SQL SErver 2000.
>> Thanks
>> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
>> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int.
>> Now, will record size be diferent if I save in each field value 0 versus
>> if I save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>>
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for help
I should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>
|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>
|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How To Calculate Database Size In Sql 2000

Hi , one of my database in SQL 2000 grew very inorganically , almost from 1.5 GB to 4 GB in one month . Is there a way i can find out which table is taking how much space in it .

Regds
Rahul ManglikHi,

In the Enterprise Manager
------------
1.select database
2.Right click->View->taskpad
3.In Taskpad, select table info

Regards,
Ram ASP

How to calculate actual size of column?

Hi,
I am wondering if any equivalent function available which
gives the actual length of column in bytes as VSIZE in
Oracle?
If not how to calcuate actual column size?
Thanks in advance.
RajIs the DATALENGTH function what you're looking for? For example:
SELECT DATALENGTH(au_lname)
FROM pubs..authors
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Raj" <vmresumes@.yahoo.com> wrote in message
news:8fdb01c345a2$356250c0$a401280a@.phx.gbl...
> Hi,
> I am wondering if any equivalent function available which
> gives the actual length of column in bytes as VSIZE in
> Oracle?
> If not how to calcuate actual column size?
> Thanks in advance.
> Raj

how to bypass report size limitation

I want to build a super long report that is organized by a train of
rectangles, and one rectangle follows another. Each rectangle has a page
break at end, so the report can be rendered in many pages, one rectangle
falls in one page. The problem is the size limit of a report is 160 inches.
That means, if I make 8.5 by 11 inches a rectangle, I can have only 14.5
pages in maximum.
How can I overcome this issue?I also have the same issue. Have you found a way around the size limit of 160
inches?
"how to bypass report size limitation" wrote:
> I want to build a super long report that is organized by a train of
> rectangles, and one rectangle follows another. Each rectangle has a page
> break at end, so the report can be rendered in many pages, one rectangle
> falls in one page. The problem is the size limit of a report is 160 inches.
> That means, if I make 8.5 by 11 inches a rectangle, I can have only 14.5
> pages in maximum.
> How can I overcome this issue?

How to bypass log file ?

Rob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
Thanks|||Rob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:

> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
>
>|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:

> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
>
>

How to bypass log file ?

I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
ThanksRob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
>
>