lundi 2 mars 2015

Tempdb growing, however it has a lot of free space


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.





Aucun commentaire:

Enregistrer un commentaire