Hi,
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi KannanIn sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
Showing posts with label shrink. Show all posts
Showing posts with label shrink. Show all posts
Thursday, March 22, 2012
Database Size
Hi,
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi KannanIn sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi KannanIn sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
Wednesday, March 21, 2012
Database Size
Hi,
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi Kannan
In sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
I have create Database with size 200 md and 40 mb for mdf and ldf
respectively (Fixed Size - not go grow or shrink) while creating database,
using wizard, little later i checked database size it became with default
size automatically.
How can I create database with Fixed size
Thanks
Mothi Kannan
In sql Enterprise Manager right click your database and use the data and log
tabs to set the sizes. be sure to uncheck the autogrow stuff..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mothi" <Mothi@.discussions.microsoft.com> wrote in message
news:2AAF530A-5462-4E60-8D28-D76D7429DFC4@.microsoft.com...
> Hi,
> I have create Database with size 200 md and 40 mb for mdf and ldf
> respectively (Fixed Size - not go grow or shrink) while creating database,
> using wizard, little later i checked database size it became with default
> size automatically.
> How can I create database with Fixed size
> Thanks
> Mothi Kannan
Database Shrink Problem
I have a database which was 120 MB before changed the data
types on some columns in some tables. I have changed
the 'char' data types to 'varchar' data type on many
columns expecting the database size to get smaller.
Instead, it went up from 120 MB to 131 MB (This is the
pure data part not the space allocation part).
Did I understand the data types wrong '
Thanks for any help..No - you have probably understood the types correctly, but changing from =char to varchar will have 2 effects:
1 An extra 2 bytes per varchar column will be allocated for the length =data of each column, thus the row will potentially increase in size and =may cause extra pages to be allocated as a result.
2. The char colukns are padded with spaces so to get rid of those you =will ned to do Update x set col=3Drtrim(col) on each column you have =changed.
Fnally having done 1 and 2 drop and recreate the clustered index (if =any) to force the rows to be re-allocate dto new pages with theire =reduced (hopefully) sizes. If no CI exists then create one and drop it =again to have the same effect.
Mike John
"calvin" <anonymous@.discussions.microsoft.com> wrote in message =news:008e01c3c96d$bc5a7950$a101280a@.phx.gbl...
> I have a database which was 120 MB before changed the data > types on some columns in some tables. I have changed > the 'char' data types to 'varchar' data type on many > columns expecting the database size to get smaller. > Instead, it went up from 120 MB to 131 MB (This is the > pure data part not the space allocation part).
> > Did I understand the data types wrong '
> > Thanks for any help..
> >|||Thanks Mike......
>--Original Message--
>No - you have probably understood the types correctly,
but changing from char to varchar will have 2 effects:
>1 An extra 2 bytes per varchar column will be allocated
for the length data of each column, thus the row will
potentially increase in size and may cause extra pages to
be allocated as a result.
>2. The char colukns are padded with spaces so to get rid
of those you will ned to do Update x set col=rtrim(col) on
each column you have changed.
>Fnally having done 1 and 2 drop and recreate the
clustered index (if any) to force the rows to be re-
allocate dto new pages with theire reduced (hopefully)
sizes. If no CI exists then create one and drop it again
to have the same effect.
>Mike John
>"calvin" <anonymous@.discussions.microsoft.com> wrote in
message news:008e01c3c96d$bc5a7950$a101280a@.phx.gbl...
>> I have a database which was 120 MB before changed the
data
>> types on some columns in some tables. I have changed
>> the 'char' data types to 'varchar' data type on many
>> columns expecting the database size to get smaller.
>> Instead, it went up from 120 MB to 131 MB (This is the
>> pure data part not the space allocation part).
>> Did I understand the data types wrong '
>> Thanks for any help..
>>
>.
>
types on some columns in some tables. I have changed
the 'char' data types to 'varchar' data type on many
columns expecting the database size to get smaller.
Instead, it went up from 120 MB to 131 MB (This is the
pure data part not the space allocation part).
Did I understand the data types wrong '
Thanks for any help..No - you have probably understood the types correctly, but changing from =char to varchar will have 2 effects:
1 An extra 2 bytes per varchar column will be allocated for the length =data of each column, thus the row will potentially increase in size and =may cause extra pages to be allocated as a result.
2. The char colukns are padded with spaces so to get rid of those you =will ned to do Update x set col=3Drtrim(col) on each column you have =changed.
Fnally having done 1 and 2 drop and recreate the clustered index (if =any) to force the rows to be re-allocate dto new pages with theire =reduced (hopefully) sizes. If no CI exists then create one and drop it =again to have the same effect.
Mike John
"calvin" <anonymous@.discussions.microsoft.com> wrote in message =news:008e01c3c96d$bc5a7950$a101280a@.phx.gbl...
> I have a database which was 120 MB before changed the data > types on some columns in some tables. I have changed > the 'char' data types to 'varchar' data type on many > columns expecting the database size to get smaller. > Instead, it went up from 120 MB to 131 MB (This is the > pure data part not the space allocation part).
> > Did I understand the data types wrong '
> > Thanks for any help..
> >|||Thanks Mike......
>--Original Message--
>No - you have probably understood the types correctly,
but changing from char to varchar will have 2 effects:
>1 An extra 2 bytes per varchar column will be allocated
for the length data of each column, thus the row will
potentially increase in size and may cause extra pages to
be allocated as a result.
>2. The char colukns are padded with spaces so to get rid
of those you will ned to do Update x set col=rtrim(col) on
each column you have changed.
>Fnally having done 1 and 2 drop and recreate the
clustered index (if any) to force the rows to be re-
allocate dto new pages with theire reduced (hopefully)
sizes. If no CI exists then create one and drop it again
to have the same effect.
>Mike John
>"calvin" <anonymous@.discussions.microsoft.com> wrote in
message news:008e01c3c96d$bc5a7950$a101280a@.phx.gbl...
>> I have a database which was 120 MB before changed the
data
>> types on some columns in some tables. I have changed
>> the 'char' data types to 'varchar' data type on many
>> columns expecting the database size to get smaller.
>> Instead, it went up from 120 MB to 131 MB (This is the
>> pure data part not the space allocation part).
>> Did I understand the data types wrong '
>> Thanks for any help..
>>
>.
>
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
orrebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
rebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
Manu
Manu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
sql
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
Manu
Manu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
sql
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
orrebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database Shrink
Hi,
Does database shrinking require all requisites that are necessary for compacting (like additional free space and closed database) ?
And is there any programmatic way to estimate optimized database size regarding current database size, like it was done by special tool in SQL Management Studio?
The various aspcects of SQL CE database maintenance are covered here: http://msdn2.microsoft.com/en-us/library/ms172411(SQL.90).aspx There is both Compact and Autoshrink features. No tools exists for estimating space to be recovered, but you can tune Autoshrink via your connection string. Autoshrink works "automatically", and does not require closed connections or additional free space.
Labels:
additional,
closed,
compacting,
database,
microsoft,
mysql,
necessary,
oracle,
requisites,
server,
shrink,
shrinking,
space,
sql
Subscribe to:
Posts (Atom)