July 12, 2012 at 2:11 am
My live server tempdb size and maxsize are almost equal now. Does it mean that sql has no more space in tempdb for any transaction that requires it? In other words, will such transactions fail?
Or does it simply means that sql has acquired the max size specified for tempdb and this space is reserved for tempdb now and can be used by transactions?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 12, 2012 at 2:26 am
Depends, could be either, if the space is used by temp objects, sorts etc and an extra temp object comes in and theres not enough space to store the temp object you will get the insufficent space alerts, but if TempDB is clear and has space available, then you wont.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
The above will loop through each file in all databases and report on size, space used, free space.
July 12, 2012 at 9:15 am
I would typically size tempdb to what I have determined through monitoring to be the max size needed so that it doesn't grow. Then you monitor free space to determine if it needs to be grown any larger.
All that to say that, in most cases, if tempdb is up to the max size setting, it will have free space, and free space is really what you are concerned with.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply