quote:

---

Originally posted by Michael Valentine Jones
Maybe it is getting inflated by some process like reindexing?

---

quote:

---

Maybe it is getting inflated by some process like reindexing?

---

quote:

---

Originally posted by elwoos

Thanks to all for the replies, very helpful as always.

quote:

---

Maybe it is getting inflated by some process like reindexing?

---

I must admit I hadn't really thought of reindexing being a potential cause. I will have a look at that.

I've looked at the size of the TLog backups for the last two days and they are mostly less than 1MB!! Though there seem to be peaks at odd times. In particular, between 0645 and 0715, the Tlog backups then for the last two days were 192Mb. The ones before and after were around 350Kb!!! There are likely to be very few people if any using the system at that time of day. There may also be a much smaller peak around lunch time.

I think I have sussed it now. There is a DTS package which populates some tables overnight for reporting. That drops indexes before truncating the tables, then recreates the indexes when the tables have been populated. The tables aren't massive but there appear to be over 20 indexes recreated.

---

quote:

---

Originally posted by elwoos
I think I'll try running this package just before the full daily backup but after the TLog backups (I only run them from 0715 to 1900 daily) have finished to see if that sorts it.

---

quote:

---

Originally posted by elwoos

Just in case anyone is wondering why I didn't know about this one. I was forced to give our "developer" sys admin rights so that he can "do the work he needs to when I am on leave"!

---

quote:

---

Do your reporting table need to be in your 'live' database?

---

quote:

---

been there, done that, got the restore scripts ready

---

quote:

---

Maybe it is getting inflated by some process like reindexing?

---

quote:

---

Its probably deleting all existing rows, and then inserting the data

---

quote:

---

An option would be to use DTS to insert into a temporary table, in a different database using SIMPLE recovery model

---

DELETE D
FROM	DestinationDB.dbo.[MyTable] AS D
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	SourceDB.dbo.[MyTable] S
	WHERE
		        D.[MyPK] = S.[MyPK]

UPDATE D
SET
--	[MyPK] = S.[MyPK],
	[MyStringCol1] = S.[MyStringCol1],
	[MyIntCol2] = S.[MyIntCol2],
	[MyTextCol3] = S.[MyTextCol3]
FROM	DestinationDB.dbo.[MyTable] AS D
	JOIN SourceDB.dbo.[MyTable] AS S
		 ON D.[MyPK] = S.[MyPK]

WHERE
		(
			   (D.[MyStringCol1] COLLATE Latin1_General_BIN <> S.[MyStringCol1] 
				OR (D.[MyStringCol1] IS NULL AND S.[MyStringCol1] IS NOT NULL) 
				OR (D.[MyStringCol1] IS NOT NULL AND S.[MyStringCol1] IS NULL))
			OR (D.[MyIntCol2] <> S.[MyIntCol2] 
				OR (D.[MyIntCol2] IS NULL AND S.[MyIntCol2] IS NOT NULL) 
				OR (D.[MyIntCol2] IS NOT NULL AND S.[MyIntCol2] IS NULL))
--			OR (D.[MyPK] <> S.[MyPK] 
--				OR (D.[MyPK] IS NULL AND S.[MyPK] IS NOT NULL) 
--				OR (D.[MyPK] IS NOT NULL AND S.[MyPK] IS NULL))
			OR (DATALENGTH(D.[MyTextCol3]) <> DATALENGTH(S.[MyTextCol3]) 
				OR CONVERT(varchar(8000), D.[MyTextCol3]) COLLATE Latin1_General_BIN <> CONVERT(varchar(8000), S.[MyTextCol3]) 
				OR (D.[MyTextCol3] IS NULL AND S.[MyTextCol3] IS NOT NULL) 
				OR (D.[MyTextCol3] IS NOT NULL AND S.[MyTextCol3] IS NULL))
		)


INSERT INTO DestinationDB.dbo.[MyTable]
(
	[MyPK],
	[MyStringCol1],
	[MyIntCol2],
	[MyTextCol3]
)
SELECT	S.*
FROM	SourceDB.dbo.[MyTable] AS S
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	DestinationDB.dbo.[MyTable] AS D
		WHERE	D.[MyPK] = S.[MyPK]
	)

quote:

---

Originally posted by Kristen

Here's a snippet of code that I use to make the "UpSert" from the Source table to the Destination table (I actually use a routine that outputs the code, for all columns, rather than typing this guff manually!)

Hopefully it may give you some ideas. Note that the TEXT columns are only compared on LENGTH and first 8,000 characters - so items of identical length and difference only outside the first 8,000 characters would slip through the net.

...
INSERT INTO DestinationDB.dbo.[MyTable]
(
	[MyPK],
	[MyStringCol1],
	[MyIntCol2],
	[MyTextCol3]
)
SELECT	S.*
FROM	SourceDB.dbo.[MyTable] AS S
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	DestinationDB.dbo.[MyTable] AS D
		WHERE	D.[MyPK] = S.[MyPK]
	)

Kristen

---