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.
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.
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.
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:
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.