Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How to get the free temporary memory available in a server

For example:- I am running one SSIS package to upload data to tables. First it will move all the data to temporary table and then it will upload to actual Tables. So before executing this package is there any way to get the available remaining temporary memory size? including SQL Server ( Because there are some parallel jobs are running by SQL Server 2005 and using the temporary space). So if I can get the remaining memory size I can decide whether I can run the package or not. Please help me.

share|improve this question
3  
please go through the answers the people are giving to your previous questions and choose the best one. It will help you get better results. –  Elzo Valugi Jan 26 '12 at 7:39
add comment

migrated from stackoverflow.com Jan 26 '12 at 11:54

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

There is no such a thing in sql server as temporary memory. Temp tables and table variables created in tempdb and may use shared buffer pool of sql server as any other objects do

But

if you still insist on knowing everything about server memory use the following script:

set nocount on
declare @PI sysname
declare @PI_MemoryManager   sysname
declare @TotalServerMemoryKB            numeric
declare @TargetServerMemoryKB           numeric
declare @MemoryConnectionsKB            numeric
declare @MemoryLocksKB                  numeric
declare @MemoryOptimizerKB              numeric
declare @MemoryMinKB                    numeric
declare @MemoryMaxKB                    numeric
declare @MemorySortHashKB               numeric
declare @PhysicalRAMKB                  numeric
declare @qs_xp_msver table(
    [idx] [int] NULL,
    [name] [sysname] NOT NULL,
    [internal_value] [int] NULL,
    [char_value] [sysname] NULL
) 
insert into @qs_xp_msver (idx, name, internal_value, char_value)
exec    master.dbo.xp_msver 'PhysicalMemory'
select  @PI = case when convert(sysname,ServerProperty('ServerName')) like '%\%' then 'MSSQL$' + @@servicename else 'SQLServer' end
set     @PI_MemoryManager   = @PI + ':Memory Manager'
select  @TotalServerMemoryKB    = sum(case when lower(p.counter_name) = 'total server memory (kb)'      then convert(numeric,cntr_value) else 0 end)
    ,   @TargetServerMemoryKB   = sum(case when lower(p.counter_name) = 'target server memory (kb)'     then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryConnectionsKB    = sum(case when lower(p.counter_name) = 'connection memory (kb)'        then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryLocksKB          = sum(case when lower(p.counter_name) = 'lock memory (kb)'              then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryOptimizerKB      = sum(case when lower(p.counter_name) = 'optimizer memory (kb)'         then convert(numeric,cntr_value) else 0 end)
    ,   @MemorySortHashKB       = sum(case when lower(p.counter_name) = 'granted workspace memory (kb)' then convert(numeric,cntr_value) else 0 end)
from sys.dm_os_performance_counters p 
where object_name  = @PI_MemoryManager
and lower(counter_name) in
    (   
        'total server memory (kb)'
    ,   'target server memory (kb)'
    ,   'connection memory (kb)'
    ,   'lock memory (kb)'
    ,   'optimizer memory (kb)'
    ,   'granted workspace memory (kb)'
    )
and instance_name <> '_Total'
select  @MemoryMinKB        = sum(case when name like 'min%'        then convert(numeric,value_in_use) * 1024 else 0 end)
,       @MemoryMaxKB        = sum(case when name like 'max%'        then convert(numeric,value_in_use) * 1024 else 0 end)
from    
    sys.configurations with (readpast)
where   
    name in ('min server memory (MB)', 'max server memory (MB)')
declare @PhysicalRAM    numeric
select  @PhysicalRAM = ceiling(convert(numeric, 
            substring(char_value, charindex('(', char_value) + 1, charindex(')', char_value) - 1 - charindex('(', char_value) ) )
            / (1.0))
from    @qs_xp_msver 
if (@PhysicalRAM is null)
    set @PhysicalRAMKB = 0
else if (@PhysicalRAM < 0)
    set @PhysicalRAMKB = (convert(numeric,4096.0)*1024*1024 + @PhysicalRAM) /1024
else
    set @PhysicalRAMKB = @PhysicalRAM/1024
if (@PhysicalRAMKB < @MemoryMaxKB and @PhysicalRAMKB > 0 )      
    set @MemoryMaxKB = @PhysicalRAMKB
set nocount off
select  
        @TotalServerMemoryKB            as CurrentMemoryKB
    ,   @MemoryMinKB                    as MemoryMinKB
    ,   @MemoryMaxKB                    as MemoryMaxKB
    ,   @MemoryConnectionsKB            as MemoryConnectionsKB
    ,   @MemoryLocksKB                  as MemoryLocksKB
    ,   @MemoryOptimizerKB              as MemoryOptimizerKB
    ,   @MemorySortHashKB               as MemorySortHashKB
    ,   @PhysicalRAMKB                  as PhysicalRAMKB
    ,   @TotalServerMemoryKB            as TotalServerMemoryKB
    ,   @TargetServerMemoryKB           as TargetServerMemoryKB
share|improve this answer
    
OP is on about tempdb I suspect –  gbn Jan 26 '12 at 7:56
add comment

I assume you mean tempdb? There is no such concept as "temporary memory size"

If so..

You can subtract allocated data from tempdb data file size, but you won't see all objects because of "Metadata visibility" and the fact that temporary tables are local to a connection

And the data files should grow to accommodate your data anyway.

The idea of checking before importing is odd.
I've never felt the need in 14 years of working with SQL Server

share|improve this answer
    
+1 10 years @ sql and never felt worry about tempdb size (except of performance issues) –  Oleg Dok Jan 26 '12 at 8:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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