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
>