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
Showing posts with label space. Show all posts
Showing posts with label space. Show all posts
Wednesday, March 28, 2012
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:
>
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:
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:
Friday, March 23, 2012
How to change free space?
How to change free space? and When do i to need change free space?
oh yeah, Explain me of fillfactor so simple awayYour best place to start is BooksOnLine. Lookup Fill Factor and it will
point you to all sorts of good information. Also have a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:euLMQrXrFHA.460@.TK2MSFTNGP15.phx.gbl...
> How to change free space? and When do i to need change free space?
> oh yeah, Explain me of fillfactor so simple away
>
oh yeah, Explain me of fillfactor so simple awayYour best place to start is BooksOnLine. Lookup Fill Factor and it will
point you to all sorts of good information. Also have a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:euLMQrXrFHA.460@.TK2MSFTNGP15.phx.gbl...
> How to change free space? and When do i to need change free space?
> oh yeah, Explain me of fillfactor so simple away
>
Wednesday, March 21, 2012
How to Change Data Directory to D: drive
Hi everyone,
I have a problem here. I install SQL Server and set it's Data Folder to C:
drive.
Unfortunately my C drive is running out of space and I plan to add another
drive (D: drive).
I want to move my database to D: drive, but however I cannot see D: drive in
the SQL Server Enterprise Manager
when I try to add a new database.
Could anyone help me?
Best Regards,
Deili.Hi,
Execute the command XP_FIXEDDRIVES from Query Anayzer. If you can see D
drive then you can very well move the datbases to D drive. If you are not
able to
see please check the access prev. of D Drive.
How to Move.
1. Detach all user databases (SP_detach_db)
2. Move it to D Drive
3. Attach the dataabases (SP_attach_db)
How to chage the default data folder:
In Enterprise manager , right click above server -- Properties
select "database setting" option, there you can chage the
Default data and Log directory.
Thanks
Hari
MCDBA
"news.microsoft.com" <deili@.yahoo.com> wrote in message
news:OrfWGmw1DHA.2680@.TK2MSFTNGP11.phx.gbl...
in
I have a problem here. I install SQL Server and set it's Data Folder to C:
drive.
Unfortunately my C drive is running out of space and I plan to add another
drive (D: drive).
I want to move my database to D: drive, but however I cannot see D: drive in
the SQL Server Enterprise Manager
when I try to add a new database.
Could anyone help me?
Best Regards,
Deili.Hi,
Execute the command XP_FIXEDDRIVES from Query Anayzer. If you can see D
drive then you can very well move the datbases to D drive. If you are not
able to
see please check the access prev. of D Drive.
How to Move.
1. Detach all user databases (SP_detach_db)
2. Move it to D Drive
3. Attach the dataabases (SP_attach_db)
How to chage the default data folder:
In Enterprise manager , right click above server -- Properties
select "database setting" option, there you can chage the
Default data and Log directory.
Thanks
Hari
MCDBA
"news.microsoft.com" <deili@.yahoo.com> wrote in message
news:OrfWGmw1DHA.2680@.TK2MSFTNGP11.phx.gbl...
quote:
> Hi everyone,
> I have a problem here. I install SQL Server and set it's Data Folder to C:
> drive.
> Unfortunately my C drive is running out of space and I plan to add another
> drive (D: drive).
> I want to move my database to D: drive, but however I cannot see D: drive
in
quote:
> the SQL Server Enterprise Manager
> when I try to add a new database.
> Could anyone help me?
> Best Regards,
> Deili.
>
How to Change Data Directory to D: drive
Hi everyone,
I have a problem here. I install SQL Server and set it's Data Folder to C:
drive.
Unfortunately my C drive is running out of space and I plan to add another
drive (D: drive).
I want to move my database to D: drive, but however I cannot see D: drive in
the SQL Server Enterprise Manager
when I try to add a new database.
Could anyone help me?
Best Regards,
Deili.Hi,
Execute the command XP_FIXEDDRIVES from Query Anayzer. If you can see D
drive then you can very well move the datbases to D drive. If you are not
able to
see please check the access prev. of D Drive.
How to Move.
1. Detach all user databases (SP_detach_db)
2. Move it to D Drive
3. Attach the dataabases (SP_attach_db)
How to chage the default data folder:
In Enterprise manager , right click above server -- Properties
select "database setting" option, there you can chage the
Default data and Log directory.
Thanks
Hari
MCDBA
"news.microsoft.com" <deili@.yahoo.com> wrote in message
news:OrfWGmw1DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> I have a problem here. I install SQL Server and set it's Data Folder to C:
> drive.
> Unfortunately my C drive is running out of space and I plan to add another
> drive (D: drive).
> I want to move my database to D: drive, but however I cannot see D: drive
in
> the SQL Server Enterprise Manager
> when I try to add a new database.
> Could anyone help me?
> Best Regards,
> Deili.
>sql
I have a problem here. I install SQL Server and set it's Data Folder to C:
drive.
Unfortunately my C drive is running out of space and I plan to add another
drive (D: drive).
I want to move my database to D: drive, but however I cannot see D: drive in
the SQL Server Enterprise Manager
when I try to add a new database.
Could anyone help me?
Best Regards,
Deili.Hi,
Execute the command XP_FIXEDDRIVES from Query Anayzer. If you can see D
drive then you can very well move the datbases to D drive. If you are not
able to
see please check the access prev. of D Drive.
How to Move.
1. Detach all user databases (SP_detach_db)
2. Move it to D Drive
3. Attach the dataabases (SP_attach_db)
How to chage the default data folder:
In Enterprise manager , right click above server -- Properties
select "database setting" option, there you can chage the
Default data and Log directory.
Thanks
Hari
MCDBA
"news.microsoft.com" <deili@.yahoo.com> wrote in message
news:OrfWGmw1DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> I have a problem here. I install SQL Server and set it's Data Folder to C:
> drive.
> Unfortunately my C drive is running out of space and I plan to add another
> drive (D: drive).
> I want to move my database to D: drive, but however I cannot see D: drive
in
> the SQL Server Enterprise Manager
> when I try to add a new database.
> Could anyone help me?
> Best Regards,
> Deili.
>sql
Subscribe to:
Posts (Atom)