December 5, 2008 at 3:27 am
Hi,
We have a 2000 box replicating to a 2005 box using transactional replication, the replication works fine but the table sizes on the replicated 2005 box bear no relation to the table sizes on the 2000 box. The database on the 2000 box is 10gb while the db on the 2005 box is 75gb (there are reporting tables which account for 10gb), this has filled up the drive and is preventing backups and maintenance plans from working. Yesterday I dropped the most affected tables from the subscription (sp_dropsubscription then sp_droparticle) then added them again. Running the snapshot reader dropped and recreated the replicated tables and the size issue seemed to have been resolved. The most affected table has 2million rows and yesterday was 18gb pre-drop, after the drop it had gone down to 500mb - job done I had thought!!! I came in this morning and the table size had gone right back up to 18gb (with 40gb reserved in indexes!!!), the same increase has happened with all the other tables.
Can anyone shed some light on what might be going on?
The 2005 box is set to simple recovery mode, transaction log is 11mb. Backups have been failing for a while so there may be some issue with checkpoints. Shrinkfile and shrinkdb operations are not affecting the table sizes.
Any help would be greatly appreciated!!!
Liam
December 5, 2008 at 4:52 am
Do you have some reindexing or other maintenance happening on the SQL 2000 box at night? A lot of maintenance is logged, so it may be causing numerous transactions to be replicated and you are getting page splits in the subscriber database causing all of your index fragmentation.
December 5, 2008 at 5:44 am
Thanks for your help.
There is a daily reindex job on the 2000 box which takes 7 minutes to run. The largest table TB_X has 2.3million rows, on disk usage report (2005) this is 40gb total (data 18gb and indexes 22gb). Running a DBCC SHOWCONTIG on this table shows the following results.
Table: 'TB_X' (266484028); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 2312793
- Extents Scanned..............................: 289114
- Extent Switches..............................: 289121
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.99% [289100:289122]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.15%
- Avg. Bytes Free per Page.....................: 8013.9
- Avg. Page Density (full).....................: 0.99%
The average page density value is the issue. The reindex job on the 2000 box specifies 10% free space and running DBCC SHOWCONTIG for this table on the 2000 gives avg page density of 89% as per the the daily reindex job. I've found some other info about GUIDs causing fragmentation which may be part of the problem. I am running a REINDEX on TB_X now and will let you know how this changes the contig results. The table itself looks to be massively fragmented as well.
Is there any way to stop this happening?
Liam
December 5, 2008 at 5:49 am
You mentioned GUID's. Do you have a clustered index on a GUID on the table? If you do, I would recommend you try to find another field you can create a clustered index on (or even not having a clustered index). Since GUID's are unordered, your data will constantly be getting inserted in the middle of the index and cause fragmentation. In SQL 2005, this was "fixed" by a new function NewSequentialID() rather than NewID() so the new values end up at the end of the index.
December 5, 2008 at 6:13 am
There are no indexes based on the GUID on the 2000 or 2005 box on this table or any others but this doesn't mean it's not part of the problem as SQL uses these as key values for replication.
REINDEX job has worked and it looks pretty good now 600mb reserved!!!
TABLE level scan performed.
- Pages Scanned................................: 26020
- Extents Scanned..............................: 3256
- Extent Switches..............................: 3257
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.85% [3253:3258]
- Logical Scan Fragmentation ..................: 0.05%
- Extent Scan Fragmentation ...................: 0.89%
- Avg. Bytes Free per Page.....................: 798.9
- Avg. Page Density (full).....................: 90.13%
Is there any way to stop this ballooning happening or do I need to run REINDEX jobs immediately after the 2000 box has reindexed? There is daily reindexing job on the 2005 box but it has been failing due to lack of space 😀
I think it must be something to do with the GUID columns!
December 5, 2008 at 6:21 am
Do you have a small fillfactor or large pad_index size on the 2005 server? If by default you have a fillfactor set with a low value on the server it could account for the space increases.
Look at the reserved space for indexes and the actual size used by those indexes
select used_page_count*8 as used_pages_kb, reserved_page_count*8 as reserved_pages_kb, *
from sys.dm_db_partition_stats where object_id = object_id('your table name')
Also, could you run a trace overnight to see what was going on?
December 5, 2008 at 6:38 am
I think I may have just solved it, the maintenance reindex job on the 2005 box specified 90% free space!!! I am running the reindex job again now and will let you know the results!!
D'OH!!!
December 5, 2008 at 7:08 am
Sorted, thanks for your help guys!! Clustered indexes had inflated the table size which threw me off the scent about indexing being the root cause.
I had made that change earlier in the week but obviously hadn't read the option properly!!! I won't make that mistake again!
December 5, 2008 at 7:57 am
That's always been a pet peeve.
GUI asks for the amount of free space.
fillfactor is the other way around
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply