Do I need to use "dbcc shrinkfile" command to shrink the transaction log?

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?
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...
> 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...
> 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).
> 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...
> 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.)
> Need your help ! thx a lot!
> Kennethsql

distribution database size and other file questions

I think my distribution database blew up in size a long time ago for a
specific problem, and I don't know if its large size (34 GB) will cause
other problems. Is there a way I can flush out old irrelevent data and then
shrink it to a smaller size? The published DB is about 110 GB and has pull
subscriptions to 2 other servers.
Also, what types of RAID disks should the distribution database be placed
on? What about the temp db, log files, and main published database files?
The published database has a separate .ndf for non clustered indexes. Are
these better to be on the same disk or different disks?
Is there anywhere someone can point me to in order to find out more
information about this stuff? I have searched high and low, and can't find a
good place to research this info and apply the information to our particular
setting.
Thanks in advance
--Kristy
As the distribution database involves high write activity it should be raid
10. temp db and logs should be on raid 10 as well. If the database has over
20% of its io being write, it should be raid 10, otherwise make it raid 5.
Ideally the ndf will be on a separate physical disk on a separate array
(raid 5 as its high read activity normally).
Can you shrink the distribution db as it is? Also you might want to issue
the following select * from distribution.dbo.msreplication_status to tell
you how many commands are in the queue. If its small you should be able to
shrink the db, if it is large you have to work on getting these commands to
the subscriber database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
>I think my distribution database blew up in size a long time ago for a
> specific problem, and I don't know if its large size (34 GB) will cause
> other problems. Is there a way I can flush out old irrelevent data and
> then
> shrink it to a smaller size? The published DB is about 110 GB and has pull
> subscriptions to 2 other servers.
> Also, what types of RAID disks should the distribution database be placed
> on? What about the temp db, log files, and main published database files?
> The published database has a separate .ndf for non clustered indexes. Are
> these better to be on the same disk or different disks?
> Is there anywhere someone can point me to in order to find out more
> information about this stuff? I have searched high and low, and can't find
> a
> good place to research this info and apply the information to our
> particular
> setting.
> Thanks in advance
> --Kristy
>
|||Thanks for the info. Is there a place you can point me to that will teach me
how to determine this on my own? I have your Transactional and Snapshot
book; is it in there?
We only have RAID 10 and RAID 1 set up on our server. But I am moving the
files around because I see a lot of things that indicate file placement
causing performance problems.
I check on distribution.dbo.msreplication_status. I know I tried shrinking
it many months before, but nothing worked.
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> As the distribution database involves high write activity it should be
raid
> 10. temp db and logs should be on raid 10 as well. If the database has
over
> 20% of its io being write, it should be raid 10, otherwise make it raid 5.
> Ideally the ndf will be on a separate physical disk on a separate array
> (raid 5 as its high read activity normally).
> Can you shrink the distribution db as it is? Also you might want to issue
> the following select * from distribution.dbo.msreplication_status to tell
> you how many commands are in the queue. If its small you should be able to
> shrink the db, if it is large you have to work on getting these commands
to[vbcol=seagreen]
> the subscriber database.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
pull[vbcol=seagreen]
placed[vbcol=seagreen]
files?[vbcol=seagreen]
Are[vbcol=seagreen]
find
>
|||My mistake it is msdistribution_status.
There are some white papers on the Microsoft web site - try this one
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%238I%2302OBHHA.2276@.TK2MSFTNGP03.phx.gbl...
> Thanks for the info. Is there a place you can point me to that will teach
> me
> how to determine this on my own? I have your Transactional and Snapshot
> book; is it in there?
> We only have RAID 10 and RAID 1 set up on our server. But I am moving the
> files around because I see a lot of things that indicate file placement
> causing performance problems.
> I check on distribution.dbo.msreplication_status. I know I tried shrinking
> it many months before, but nothing worked.
> --Kristy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> raid
> over
> to
> pull
> placed
> files?
> Are
> find
>