TRansaction log backup size during index rebuild

[niall5098]

HI All,

i have a client who is running a third party index rebuild script that runs each sunday.

the script runs fine but it create massive tlog backups, for example the logs yesterday were 14GB at 6am, 42GB at 6:30am and 7GB at 7am.

the strange thing is if i run sp_spaceused on the database it shows the index_size as being 12GB. is that all indexes in the database or do i need to run a msforeach script?

if 12GB is the size of all the indexes, then i need to figure out (with help) why the log files are so large as it is impacting on the disk they reside.

Thanks in advance,

Niall

 

[TomKane]

Hi, yes the index_size on space used is the Total amount of space used by indexes. When you say index rebuild script is it a T-SQL script?

 

[TomKane]

Sorry, I mislead you it's the total index size for a table - this is a script I used when I'm looking at that sort of stuff... I think I got it from Pinal Dave's SQL Authority blog

USE total_test
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))--94796040 KB
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp