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