locked
Database Growth RRS feed

  • Question


  • How to investige database grwoth? 

    We found that one of our Db growing fast? so we wnat to find out that what causing this

    We are using Sql Server 2016 Ent SP2

    Friday, September 13, 2019 12:09 AM

Answers

  • That's not the only way for a database to grow, a reindex job could cause space to be allocated. The growth activity should be part of the default trace or system health xEvent session, or viewed from the standard reports as noted in other answers here. HTH
    But that space would show as free space not actually data space used. I mean I assume when you are looking at growth you would first see how much free space it their and if at all it can be recovered.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by VijayKSQL Monday, September 23, 2019 5:16 PM
    Monday, September 16, 2019 6:51 AM
  • The OP does not make it clear if they are asking about allocated space or used space. By default I tend to assume that when someone is seeing "growth", they are only seeing the allocated space. 
    • Marked as answer by VijayKSQL Monday, September 23, 2019 5:16 PM
    Friday, September 20, 2019 11:50 PM

All replies

  • Hello Friend,

    One way would be to create a job to keep up with the growth of the tables. It would be good for you to know exactly which object (s) is growing. This should already give you a sense of what is going on.

    Now the process itself, you should monitor the database and look for data load jobs, what kind of process it occurs.
    Has this increase started some time ago? Is this base old? Has there been an increase in users?
    Try filtering at times of day to understand if a few moments occur or if it is a continuous process.

    Follow the script so you can keep track of the size of the tables:

    USE [YourDBName] -- replace your dbname
    GO
    SELECT
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.rows AS RowCounts,
    CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
    CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    GROUP BY t.Name, s.Name, p.Rows
    ORDER BY s.Name, t.Name
    GO



    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Friday, September 13, 2019 12:59 AM
  • Hi VijayKSQL,

    Please using SSMS database report to get the details of the data file and log file space usage. You can also get details of SQL Server Database Growth and Shrink Events there. Right-click on a database >Go to Reports > Standard Reports > Disk Usage.

    Or you can use below T-SQL to check auto growth events in SQL Server database.

    DECLARE @current_tracefilename VARCHAR(500);
    DECLARE @0_tracefilename VARCHAR(500);
    DECLARE @indx INT;
    SELECT @current_tracefilename = path
    FROM sys.traces
    WHERE is_default = 1;
    SET @current_tracefilename = REVERSE(@current_tracefilename);
    SELECT @indx = PATINDEX('%\%', @current_tracefilename);
    SET @current_tracefilename = REVERSE(@current_tracefilename);
    SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
    SELECT DatabaseName, 
           te.name, 
           Filename, 
           CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds, 
           StartTime, 
           EndTime, 
           (IntegerData * 8.0 / 1024) AS 'ChangeInSize MB', 
           ApplicationName, 
           HostName, 
           LoginName
    FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
         INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
    WHERE(trace_event_id >= 92
          AND trace_event_id <= 95)
    ORDER BY t.StartTime;
    Please refer to Get details of SQL Server Database Growth and Shrink Events to get more information.

    Best regards,
    Cathy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 13, 2019 1:58 AM
  • We found that one of our Db growing fast? so we wnat to find out that what causing this

    It's because data was inserted, that cause database growth. You can use the script Detailed list of all tables and their size to track table sizes over time by inserting the result into a table.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Friday, September 13, 2019 10:39 AM
    Friday, September 13, 2019 6:17 AM
  • Its the data nothing else and if you want to find out what data I believe that is very very difficult to track and would not be fruitful. To start with check if database autogrowth is set to MB not percentage and value is not too high causing unnecessary data file growth.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, September 13, 2019 10:41 AM
  • That's not the only way for a database to grow, a reindex job could cause space to be allocated. The growth activity should be part of the default trace or system health xEvent session, or viewed from the standard reports as noted in other answers here. HTH
    Friday, September 13, 2019 2:46 PM
  • That's not the only way for a database to grow, a reindex job could cause space to be allocated. The growth activity should be part of the default trace or system health xEvent session, or viewed from the standard reports as noted in other answers here. HTH
    But that space would show as free space not actually data space used. I mean I assume when you are looking at growth you would first see how much free space it their and if at all it can be recovered.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by VijayKSQL Monday, September 23, 2019 5:16 PM
    Monday, September 16, 2019 6:51 AM
  • The OP does not make it clear if they are asking about allocated space or used space. By default I tend to assume that when someone is seeing "growth", they are only seeing the allocated space. 
    • Marked as answer by VijayKSQL Monday, September 23, 2019 5:16 PM
    Friday, September 20, 2019 11:50 PM