November 9, 2016 at 5:22 am
I need to resize the t-log by using the index size as the db is in simple recovery , can any one help to identify the index sizes in a database ?
November 9, 2016 at 5:24 am
Don't resize the log based on index size. Size it based on how large it needs to be for your regular operations. Track the used % of the log for a week or two (DBCC SQLPERF(LOGSPACE)) and use that to set the size of the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2016 at 8:04 am
ramyours2003 (11/9/2016)
I need to resize the t-log by using the index size as the db is in simple recovery , can any one help to identify the index sizes in a database ?
What Gail said really, but if you want to see index sizes this will be useful
SELECT DB_NAME() AS DBName
, s.name AS SchemaName
, OBJECT_NAME(o.OBJECT_ID) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, CASE i.[type]
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'NonClustered'
WHEN 3 THEN 'XML'
WHEN 4 THEN 'Spatial'
END AS IndexType
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
END AS FG_or_PartitionName
, p.partition_number AS PartitionNo
, p.[rows] AS [RowCnt]
, au.type_desc AS AllocType
, au.total_pages / 128 AS TotalMBs
, au.used_pages / 128 AS UsedMBs
, au.data_pages / 128 AS DataMBs
FROM sys.indexes i
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
ORDER BY TotalMBs DESC
OPTION (RECOMPILE);
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply