2

On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior.

While monitoring growth using the following query as basis, I've noticed tempdb has grown quite frequently in the last few hours:

DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT databasename,
       e.name   AS eventname,
       cat.name AS [CategoryName],
       starttime,
       e.category_id,
       loginname,
       loginsid,
       spid,
       hostname,
       applicationname,
       servername,
       textdata,
       objectname,
       eventclass,
       eventsubclass
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC 

Gives several lines of growth of the data files:

tempdb  Data File Auto Grow Database    2015-03-02 09:50:33.187 2

However, when I look at the space occupied by tempdb I'm not seeing where the space crunch is:

USE tempdb 
GO 
SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Gives the following output:

DbName  FileName    CurrentSizeMB   FreeSpaceMB
tempdb  tempdev     7500.000000     7492.625000
tempdb  templog     156.132812      93.820312
tempdb  tempdev2    7250.000000     7245.625000
tempdb  tempdev3    7250.000000     7245.312500
tempdb  tempdev4    7366.500000     7360.875000

The person maintaining the CRM has asked for us to shrink the tempdb.
My colleague has, in the past, obliged. However I'm unwilling to do so without an explanation. Especially considering that the shrinking has become an almost weekly occurence.

Can anyone give me an indication as to why the tempdb has so many growth events, and how to handle this properly?
I'm currently considering asking for more storage, and increasing the tempdb drive by 50%.
However this feels like treating the symptom, not the cause.

2

What is the autogrow model for the TempDB files? Tiny increments will incur many small autogrowths whereas large increments will incur fewer, but more intensive, growth routines.

Presumably TempDB is growing because queries that are running on the server are constructing a lot of temporary tables, table variables, or cursors and so on?

Is it known what queries are running, and do you have historical baseline to compare the autogrowths and sizes to?

If things have suddenly changed, and the organisation has not grown the amount of actual data it uses significantly, I would guess that a developer or reporting user has changed the way in which they run their queries.

I think we need a bit more information from you, regarding the above.

  • Actually, looking into the growth settings (thanks for this btw, I should have thought of this), things get a bit weirder. I am counting 110 growth events of 250MB each. Which accounts for 80% of the current tempdb size. – Reaces Mar 2 '15 at 14:08
  • Well, from what's been said so far, all I can conclude is that TempDB is doing what TempDB does (!) Are you able to contact the developers or reporting users to see if their methods have changed, specifically with regard to recent rewrites of queries so that they use more of TempDB? You're right to be suspicious of "just shrinking it", by the way - you need to understand what is "normal", if what you are seeing today fits with that, and how to improve things in the future if diskspace is likely to become an issue. – EMarkM Mar 2 '15 at 14:20
  • There is no real baseline to speak off, and my position here is temporary and doesn't really have the time allotted to set up a proper baseline. As such I don't think it's going to be a part of the picture any time soon. I'm inclined to believe that, judging by the fact that the CRM database itself is not growing, there are reports being ran that should be handled differently. Oddly enough, the combined size of the databases on that server is less than the current size of the tempdb... – Reaces Mar 2 '15 at 14:30
  • It's certainly not unheard of for a TempDB to be bigger than the "actual" data it is supporting, and from the fact that the files are growing but now showing free space it just looks to me like a lot of intensive queries are using it. As a temp, you may not be in a position to offer advice, or you may find that staff at the organisation "don't want to hear it", but if you do get the chance, try to encourage the permanent staff to start baselining their servers from both a Windows and SQL point of view - it's impossible for you to satisfy their requirements properly if you don't have the info. – EMarkM Mar 2 '15 at 14:34
  • I'm going to create an incident ticket to create some time to talk about this shrinking behavior. And next time I see the request pass (it's been done now by an out-sourced colleague... once again) I'll stop it from being executed and make sure we get some information about the reports being ran that cause this. – Reaces Mar 2 '15 at 14:38
2

While I had the same problem, I ran into a different solution.

The Issue: tempdb data files at 99% unused space. Disk space is near zero. I free some space on the disk, and within 24 hours the data files have grown to consume all the disk space again. They still report 99% unused space.

Solution: We had checkDB running in a job overnight on a daily schedule. checkDB created a snapshot in the tempdb which was too large for tempdb to handle and kept failing (which we were also trying to resolve why the job was failing separately). Turns out, both issues were linked, tempdb data files filled and auto grew with the checkDB job which would fail as it ran out of disk space. The database being checked was over a TB.

Hope this adds to the above solution for future Googlers.

  • Do you know why this snapshot happens in Tempdb and not in snapshot datafiles/streams? Is this using snapshot isolation? – eckes Jan 4 '18 at 12:47
  • Far from the expert here, but I understand that if there is not enough space for the snapshot in the datafile drive, then it will commit the snapshot to memory, if there is not enough space in memory, then it will create the snapshot in tempdb's drive. – bp_ Jan 8 '18 at 20:27
  • Looks like my above comment may not be entirely accurate. I suggest you skip the novice middleman and go to the pro: CheckDB From Every Angle – bp_ Jan 8 '18 at 20:50
  • Ah Great, a worktable is an obvious reason. Thanks @bp_ – eckes Jan 9 '18 at 6:00
2

This is an old question, but looks like it deserves a more general answer.

The standard advice regarding tempdb sizing on SQL server is to make it big enough to handle whatever routine activity your server throws at it, and to stop micro-managing growths and shrinks.

SQL uses tempdb for tons of stuff: in-memory sorts, table reindexing, explicitly created temp tables, database snapshots, etc. SQL should use the space within tempdb as needed, and will free it up internally when complete. The overall file sizes shouldn't need to change all the time. There is zero reason to keep reclaiming that space back to the OS, SQL will likely need it again soon. The only way to determine if the amount of tempdb use is "normal" is to monitor it over time.

As a production DBA, I would refuse a request like this from an application team to shrink tempdb, at least without a whole lot of further discussion. This goes for "vendor recommendations" as well; even if the vendor is Microsoft - the MS CRM team (and the SharePoint team, and the System Center team, etc.) is not the Microsoft SQL product team.

Your post doesn't say how big your user databases are, but in my experience as a production DBA, 30GB of tempdb data files is not big at all. In fact, if you are still seeing tempdb data file growths even at 30GB, then contrary to their request, you need to make it bigger, not smaller. Tempdb sizes of 100GB or more are not uncommon if your user dbs are very large (100s of GB or TB).

Give SQL the disk space it needs to do its job.

Your tempdb log file, on the other hand, is massively undersized. In your case, I'd probably make it 25% the total size of the data files or more.

All that said, in your case I'd probably try something like:

  • 4x data files, each 20GB in size. Growth rate of 1GB.
  • 1x log file, 20GB in size, growth rate of 4GB.

Continue monitoring, see if that is a sufficient size, or if they continue to grow.

There are some activities (poorly written queries, reindexing of huge tables, etc.) that could blow up the log and would require more targeted attention to what exactly caused the problem.

  • 1
    Well this is an old question come back from the dead! I went back and checked what I ended up doing to resolve the issue. What I have in place now is 4x data files, 18.5GB each, with a growth rate of 250MB. And a log file of 15GB with a growth rate of 250MB. It would probably be better to increase it a tiny bit still, but this CRM is due to be moved to CRM online somewhere this year so I'm just going to leave it for now. Thank you for the belated attention! – Reaces Jan 4 '18 at 6:39
  • @Reaces Looks pretty good, actually. No reason to change now, but worth knowing that a larger value for log file growth is recommended to keep the VLFs (the internal divisions within the log file) reasonably sized: Tran Log VLFs: Too many or too few? – BradC Jan 4 '18 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.