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.