locked
tempdb files growth RRS feed

  • Question

  • Once I tried adding rowversion column in a big table in production db and I ran out of tempd drive space. But then I missed to see which of the data or log or both files increased. Now if I try to replicate the same case in local pc I cannot see any of the tempdb files growing but the data and log files of the db I am working grow.

    What actually makes the tempdb files grow and how to replicate in local server?


    • Edited by Curendra Tuesday, June 9, 2020 10:23 AM
    Tuesday, June 9, 2020 10:23 AM

All replies

  • It's not apparent to me why adding a timestamp column to a big table would cause tempdb to explode. But since the entire table has to be rebuilt, it takes a lot of log space, so the log file for the database could grow a lot, unless it was already sized for the operation.

    But, hm, what is the database had some form of snapshot enabled? I don't really expect DDL statements to maintain the version store, since they take a Sch-M lock, but I have been wrong before.

    So you could try enabling read_committed_snapshot on your database and redo the test and see what happens.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 9, 2020 9:19 PM
  • Hi Curendra,

    I created one big table with 80,000 rows in my testing environment and added one column rowversion using below statement.

    ALTER TABLE show_extent

    ADD Rowversion [Rowversion] NOT NULL

    I used below query to compare the file size of data and log files and found that only log file increased around 5% and no obvious increase of tempdb files.

    --query log file usage

    DBCC SQLPERF(LOGSPACE)

    --query data file usage

    DBCC SHOWFILESTATS

    In my situation, all files are located in the same C driver and there are no index in this table.

    In your situation, tempdb grew very large might due to other reasons in your production environment.

    You could use below query to find out that tempdb data file increase is due to User Object or Internal Object and check further.

    select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,

    SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

    SUM (version_store_reserved_page_count)*8  as version_store_kb,

    SUM (unallocated_extent_page_count)*8 as freespace_kb,

    SUM (mixed_extent_page_count)*8 as mixedextent_kb

    FROM sys.dm_db_file_space_usage

    Of course, the fastest way to reduce the size of tempdb is restart the SQL Server service but it will never be a perfect way for production database.

    Below are some suggestions with tempdb configuration which maybe helpful to you:

    1. Set the number of data files to be the same as the number of logical CPUs.
    2. Splitting tempdb into multiple data files of equal size can provide higher parallel efficiency for operations using tempdb.
    3. Make sure all data file self-increase are the same, and not to use increment by percentage.

    If you would like to replicate in local server, I advise you to do full backup and restore.

    How to Backup And Restore Database In SQL Server?- Step-By-Step Tutorial

    Best regards,

    Melissa

    -------------------------------------------

    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


    Wednesday, June 10, 2020 6:05 AM
  • Thanks for the replies.

    Actually there are two instances of sql servers. I was looking at the wrong tempdb files. 

    Now I can see tempdb files growing for bulk inserts or adding rowversion column for bulk data.


    • Edited by Curendra Friday, June 12, 2020 3:14 AM
    Friday, June 12, 2020 3:14 AM
  • If your database is in Full Recovery mode, your tempdb will grow with data movements. In your local PC, the database might be in Simple recovery mode and hence there will not be any change in Tempdb file.
    Friday, June 12, 2020 3:22 AM
  • Hi Curendra,

    There are THREE different recovery models of SQL Server: Simple, Full and Bulk-logged.

    It is recommended to temporarily switch to bulk-logged recovery model right before performing bulk insert operations and then immediately switch back to the full recovery model since it could reduce log space usage by using minimal logging for most bulk-logged operations.

    Based on above, your tempdb may not grow very fast and large.

    If you could ignore some data loss in case of disaster, you could even choose the simple recovery which maintains only a minimal amount of information in the transaction log.

    Best regards,

    Melissa

    -------------------------------------------

    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, June 12, 2020 8:59 AM
  • If your database is in Full Recovery mode, your tempdb will grow with data movements. In your local PC, the database might be in Simple recovery mode and hence there will not be any change in Tempdb file.

    Hi,

    I was looking at the wrong tempdb files which I have already mentioned.

    However I also tried what you suggested but found that tempdb files were growing even for simple recovery mode. So I think the recovery mode affects the log file of that db only and the tempdb files grow for any bulk operations irrespective of the recovery mode of the database.

    Saturday, June 13, 2020 3:39 AM
  • However I also tried what you suggested but found that tempdb files were growing even for simple recovery mode. So I think the recovery mode affects the log file of that db only and the tempdb files grow for any bulk operations irrespective of the recovery mode of the database.

    Yeah, the recovery mode of the database has absolutely no effect on how much space you need in tempdb.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 13, 2020 8:43 AM
  • Hi Curendra,

    I found one good article about adding rowversion and tempdb space maybe helpful to you.

    Row Versioning Concurrency in SQL Server

    In order to avoid running out of tempdb space, you may add more space or change the location of tempdb to a larger driver.

    Please find examples from below:

    /* Re-sizing TempDB */

    USE [master];

    GO

    ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE=2GB, FILEGROWTH = 100);

    GO

    /* Adding three additional files */

    USE [master];

    GO

    ALTER DATABASE [tempdb] ADD FILE

        (NAME = N'tempdev2', FILENAME = N'R:\TEMPDB_DATA\tempdev2.ndf' , SIZE = 2GB , FILEGROWTH = 100);

    ALTER DATABASE [tempdb] ADD FILE

        (NAME = N'tempdev3', FILENAME = N'R:\TEMPDB_DATA\tempdev3.ndf' , SIZE = 2GB , FILEGROWTH = 100);

    ALTER DATABASE [tempdb] ADD FILE

        (NAME = N'tempdev4', FILENAME = N'R:\TEMPDB_DATA\tempdev4.ndf' , SIZE = 2GB , FILEGROWTH = 100);

    GO

    Best regards,

    Melissa

    -------------------------------------------

    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

    Monday, June 15, 2020 2:01 AM