TempDB Log File Growing Exponentially But Remains 99% Empty?

• neil.baal 44846

  Old Hand

  Points: 354

  More actions

  January 30, 2017 at 4:08 am

  #339961

  Morning All,

  I have trawled the website looking for a previous post similar to the issue I am observing but cannot find one,

  We have a SQL 2014 SP2 server sat on a Server 2012 R2 OS,
  Every few days the TempDB log file is growing up to 64GB on disk, can't grow anymore as the disk space runs out, but when checked it
  is 99% empty,
  We shrink the log file, removing the white space and so the saga continues, within a few days the log file has grown again but is only 1% full,

  I have changed the log file to grow by 1GB chunks rather than a percentage, I have also tried capping the maximum log size growth to 20GB, this
  results in an error within the application, NAV CRM, where it states the TempDB log file is full, its not though it is 99% empty grrrrrr,

  Anyone with any ideas, one possibility is to split the log file as per the cores on the system, a best practice I have read but not sure that will solve the problem,
  will I just end up with 4 massive TempDB log files that are 99% empty?

  Any help will be greatly appreciated,

  Thanks,
  Neil

• Eirikur Eiriksson

  SSC Guru

  Points: 182951

  More actions

  January 30, 2017 at 4:21 am

  #1925563

  neil.baal 44846 - Monday, January 30, 2017 4:08 AM

  Morning All,

  I have trawled the website looking for a previous post similar to the issue I am observing but cannot find one,

  We have a SQL 2014 SP2 server sat on a Server 2012 R2 OS,
  Every few days the TempDB log file is growing up to 64GB on disk, can't grow anymore as the disk space runs out, but when checked it
  is 99% empty,
  We shrink the log file, removing the white space and so the saga continues, within a few days the log file has grown again but is only 1% full,

  I have changed the log file to grow by 1GB chunks rather than a percentage, I have also tried capping the maximum log size growth to 20GB, this
  results in an error within the application, NAV CRM, where it states the TempDB log file is full, its not though it is 99% empty grrrrrr,

  Anyone with any ideas, one possibility is to split the log file as per the cores on the system, a best practice I have read but not sure that will solve the problem,
  will I just end up with 4 massive TempDB log files that are 99% empty?

  Any help will be greatly appreciated,

  Thanks,
  Neil

  Quick question, what is the outcome of this query?
  😎

  SELECT
 SDB.name
,SDB.log_reuse_wait_desc
FROM sys.databases SDB
WHERE SDB.name = N'tempdb';

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  January 30, 2017 at 4:23 am

  #1925565

  neil.baal 44846 - Monday, January 30, 2017 4:08 AM

  Every few days the TempDB log file is growing up to 64GB on disk, can't grow anymore as the disk space runs out, but when checked it
  is 99% empty,

  What's happening is that something runs, fills up the TempDB log, fails (out of log space) and rolls back. Once the rollback completes the log is marked reusable, leaving it 99% empty.

  To pinpoint the cause, you need to catch whatever it is while running, if you try investigating after the fact, you will get nowhere.
  You can write a job that polls sys.dm_db_session_space_usage and/or sys.dm_db_task_space_usage, or you can use Extended events. Either way you need to catch it while it's running

  Anyone with any ideas, one possibility is to split the log file as per the cores on the system, a best practice I have read but not sure that will solve the problem,
  will I just end up with 4 massive TempDB log files that are 99% empty?

  That is NOT a best practice, completely the opposite. Multiple log files are not at all useful, SQL uses them sequentially.

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  January 30, 2017 at 4:26 am

  #1925566

  Eirikur Eiriksson - Monday, January 30, 2017 4:21 AM

  SELECT
 SDB.name
,SDB.log_reuse_wait_desc
FROM sys.databases SDB
WHERE SDB.name = N'tempdb';

  Edit: Mis-read

  Since the log is 99% empty, that's almost certain to read NOTHING

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• neil.baal 44846

  Old Hand

  Points: 354

  More actions

  January 30, 2017 at 4:48 am

  #1925570

  Eirikur Eiriksson - Monday, January 30, 2017 4:21 AM

  neil.baal 44846 - Monday, January 30, 2017 4:08 AM

  Morning All,

  I have trawled the website looking for a previous post similar to the issue I am observing but cannot find one,

  We have a SQL 2014 SP2 server sat on a Server 2012 R2 OS,
  Every few days the TempDB log file is growing up to 64GB on disk, can't grow anymore as the disk space runs out, but when checked it
  is 99% empty,
  We shrink the log file, removing the white space and so the saga continues, within a few days the log file has grown again but is only 1% full,

  I have changed the log file to grow by 1GB chunks rather than a percentage, I have also tried capping the maximum log size growth to 20GB, this
  results in an error within the application, NAV CRM, where it states the TempDB log file is full, its not though it is 99% empty grrrrrr,

  Anyone with any ideas, one possibility is to split the log file as per the cores on the system, a best practice I have read but not sure that will solve the problem,
  will I just end up with 4 massive TempDB log files that are 99% empty?

  Any help will be greatly appreciated,

  Thanks,
  Neil

  Quick question, what is the outcome of this query?
  😎

  SELECT
 SDB.name
,SDB.log_reuse_wait_desc
FROM sys.databases SDB
WHERE SDB.name = N'tempdb';

  Eirikur, the results of the query you sent are below:

  1 tempdb NOTHING

  Thanks,

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 30, 2017 at 5:01 am

  #1925573

  Log file auto growths are recorded in the default trace, so if this didn't happen too long ago, you can see when the growths occurred.  That might give you a clue to the type of work that's causing them.  If that doesn't help, you'll need to try one of the techniques Gail mentioned.

  John

• neil.baal 44846

  Old Hand

  Points: 354

  More actions

  February 2, 2017 at 9:20 am

  #1926384

  Thanks for all the responses,

  I've looked into this further and there was a long running job from NAV,

  Raised a call with Microsoft and they have said it is expected behaviour and that we set the initial size to around the 100GB mark,

  Another suggestion is the queries for the NAV jobs are optimised,

  Cheers
  Neil