July 9, 2017 at 10:14 pm
Hi All ,
I would like to know how to findthe root cause of database growth – DATA FILE ?
One of the db in production serverwas growing quite a lot these few days but I don’t know the cause…
If I run sql profiler I can onlyrun lets say 30 mins as I am afraid it will degrace server perfomance
Any feedback is much muchappreciated
Cheers…..
July 10, 2017 at 12:35 am
Find a script which indicates the size of every index in the database.
Run it repeatedly and see which index(es) grow the most.
If it does not give you any clear indication - look for a frequently updated HEAP table (a table with no clustered index).
Frequent UPDATEs on a "heap" table almost certainly cause enormous data file growth.
_____________
Code for TallyGenerator
July 10, 2017 at 10:47 pm
Sergiy - Monday, July 10, 2017 12:35 AMFind a script which indicates the size of every index in the database.Run it repeatedly and see which index(es) grow the most.If it does not give you any clear indication - look for a frequently updated HEAP table (a table with no clustered index).Frequent UPDATEs on a "heap" table almost certainly cause enormous data file growth.
Hi
Thanks for the reply
I notice there are many heaps in that database . One heap has a high index fragmentation . 99% , I rebuilt the index but it fills up the drive that already has little space 🙁
What should I do then ?
Cheers....
July 11, 2017 at 1:51 am
Create a clustered index.
Do you have a copy of the database restored in a test environment?
_____________
Code for TallyGenerator
July 11, 2017 at 7:12 am
WhiteLotus - Sunday, July 9, 2017 10:14 PMHi All ,
I would like to know how to findthe root cause of database growth – DATA FILE ?
One of the db in production serverwas growing quite a lot these few days but I don’t know the cause…
If I run sql profiler I can onlyrun lets say 30 mins as I am afraid it will degrace server perfomance
Any feedback is much muchappreciated
Cheers…..
My script below will show sizes of clustered indexes, heaps and non clustered indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT DB_NAME() AS DBName
, s.name AS SchemaName
, OBJECT_NAME(o.OBJECT_ID) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, 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
, i.is_disabled AS IsDisabled
, i.data_space_id
, 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]
--, p.data_compression_desc AS CompressionType
, 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 au.total_pages desc
OPTION (RECOMPILE);
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2017 at 5:40 pm
Sergiy - Tuesday, July 11, 2017 1:51 AMCreate a clustered index.Do you have a copy of the database restored in a test environment?
Thanks for the reply but what kind of clustered index that I need to create ? will it help ?
Cheers...
July 11, 2017 at 9:54 pm
Optimal definition of a clustered index for a table depends on the table definition and the typical workload which the table is involved in.
Creating a clustered index on a busy table may change the system behaviour quite significantly, so it must go through proper development cycle, with all the proper testing done.
But first you need to figure out which table needs a clustered index.
Try the script posted above, or one of others similar scripts posted here, to figure out which heap is your problem.
_____________
Code for TallyGenerator
July 12, 2017 at 4:27 am
WhiteLotus - Tuesday, July 11, 2017 5:40 PMSergiy - Tuesday, July 11, 2017 1:51 AMCreate a clustered index.Do you have a copy of the database restored in a test environment?Thanks for the reply but what kind of clustered index that I need to create ? will it help ?
Cheers...
Don't!
Just use my script above to find growing indexes or heaps
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 12, 2017 at 6:11 am
Perry Whittle - Wednesday, July 12, 2017 4:27 AMDon't!
Just use my script above to find growing indexes or heaps
And then what?
OK, you've found that heap which is causing all the problems.
How do you fix it?
_____________
Code for TallyGenerator
July 12, 2017 at 10:02 pm
Perry Whittle - Tuesday, July 11, 2017 7:12 AMWhiteLotus - Sunday, July 9, 2017 10:14 PMHi All ,
I would like to know how to findthe root cause of database growth – DATA FILE ?
One of the db in production serverwas growing quite a lot these few days but I don’t know the cause…
If I run sql profiler I can onlyrun lets say 30 mins as I am afraid it will degrace server perfomance
Any feedback is much muchappreciated
Cheers…..
My script below will show sizes of clustered indexes, heaps and non clustered indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDGO
SELECT DB_NAME() AS DBName
, s.name AS SchemaName
, OBJECT_NAME(o.OBJECT_ID) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, 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
, i.is_disabled AS IsDisabled
, i.data_space_id
, 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]
--, p.data_compression_desc AS CompressionType
, 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 au.total_pages desc
OPTION (RECOMPILE);
Thanks Mate
July 13, 2017 at 9:57 am
Sergiy - Wednesday, July 12, 2017 6:11 AMPerry Whittle - Wednesday, July 12, 2017 4:27 AMDon't!
Just use my script above to find growing indexes or heapsAnd then what?
OK, you've found that heap which is causing all the problems.
How do you fix it?
If if stays as a heap then rebuild it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply