Compress Db

Hi ,
My database in SQL server keep on increasing daily
after trasactional data keeps on updates to the database
and the database size now already more than 10 gigabytes.
1) How can I compress this database to make the size
smaller ?
2) What is the advisible way to maintain such huge
database ?
3) Beside indexing the tables , any other way to make the
database perform well ?
Please guide me.
TQ
Hi
Have you try DBCC SHRINKFILE or DBCC SHRINKDATABASE?
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>
|||Hi,
Can you identify the usage of Data and Log first using the command
sp_helpdb <dbname>
see the value of the column size, if the trasnaction log size is big then
use the below command to identify the exact usage.
dbcc sqlperf(logspace)
If the size of the log is huge and if the utilization percentage is very
less then you can shrink the transaction based on the URI's post.
steps:-
1. Perform a transaction log backup (backup log command)
2. DBCC SHRINKFILE('logical_ldf_name','truncateonly')
Now the LDF file will be shrinked. verify using dbcc sqlperf(logspace)
Note:
You can also check the data and index utlization using:- If you have a small
value do not shrink the MDF file.
use <dbname>
go
sp_spaceused
If the unused value is very high , then you can shrink the mdf file as well.
Thanks
Hari
MCDBA
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>
|||If the log is the file which is huge, then either backup the log regularly
so the space can be re-used, instead of letting it grow (backup log... in
BOL).
Or
you can set the recovery mode of the database to SIMPLE RECOVERY. In this
case SQL Server truncates the log every minute, but you will only be able to
restore the database ( if a crash occurs) to the most recent whole(or
differential) database backup...
If I might, take a look at the book Admin 911 by Brian Knight... It will go
a long way to get you in good shape.
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
"travis_5579@.hotmai.com" <anonymous@.discussions.microsoft.com> wrote in
message news:57c401c4747a$0c5e3830$a601280a@.phx.gbl...
> Hi ,
> My database in SQL server keep on increasing daily
> after trasactional data keeps on updates to the database
> and the database size now already more than 10 gigabytes.
> 1) How can I compress this database to make the size
> smaller ?
> 2) What is the advisible way to maintain such huge
> database ?
> 3) Beside indexing the tables , any other way to make the
> database perform well ?
> Please guide me.
> TQ
>