June 15, 2015 at 9:22 am
Hi,
I'm having a growth rate problem on two SQL2014 Databases.
For example - the growth rate over the weekend was 18 gig for the MDF alone! This my main production DB so no one is in it over the weekend. Which makes things weird. I've checked jobs, packages, etc.
I have looked at the largest tables and compared them to the same stats last month and there doesn't seem to be any unusual growth in the largest tables. There is some new records but not enough to explain that kind of space.
When I started here I added a TON of indexes to this DB (to fix a bad locking and blocking issue) 0 wondering if that has something to do with it?
June 15, 2015 at 10:28 am
Do you rebuild your indexes over the weekend? Is there a lot of empty space in your datafile?
When you rebuild indexes, a new index structure is created effective doubling your space used for each table that is rebuilt. For example, if you have a clustered table which is 18GB, when you rebuild this table, a new clustered index is created is created and organized. During the rebuild operation you have both the original and new clustered index, both taking 18GB. once the rebuild operation is complete the existing clustered index is removed, leaving only the new one.
If you did not have enough free space in the data file to accommodate this extra usage, your file would grow and you would now have probably close to 18GB of empty space in your file.
June 15, 2015 at 12:10 pm
looking into this..you could be right Bob..thanks.
June 16, 2015 at 12:36 am
index rebuild job on weekend 🙂
June 16, 2015 at 4:24 am
The following query should show the file sizes of the data and log files and the amount of space used.
SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
ORDER BY groupid DESC
It should help in checking the free space.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply