Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TRansaction log backup size during index rebuild

Status
Not open for further replies.

niall5098

IS-IT--Management
Jun 2, 2005
114
IE
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
 
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?
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top