Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
quote:
> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
quote:
> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
quote:
> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
quote:
> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
quote:
> Need your help ! thx a lot!
> Kenneth
No comments:
Post a Comment