select * from sysprocesses where dbid in (select dbid from sysdatabases where name='dbname')

--或者

SELECT * FROM

[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT

   [DBID]

FROM

   [Master].[dbo].[SYSDATABASES]

WHERE

   NAME='dbname'

)

SELECT  [Spid] = session_id ,

            ecid ,

            [Database] = DB_NAME(sp.dbid) ,

            [User] = nt_username ,

            [Status] = er.status ,

            [Wait] = wait_type ,

            [Individual Query] = SUBSTRING(qt.text,

                                           er.statement_start_offset / 2,

                                           ( CASE WHEN er.statement_end_offset = -1

                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))

                                                       * 2

                                                  ELSE er.statement_end_offset

                                             END - er.statement_start_offset )

                                           / 2) ,

            [Parent Query] = qt.text ,

            Program = program_name ,

            hostname ,

            nt_domain ,

            start_time

    FROM    sys.dm_exec_requests er

            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

    WHERE   session_id > 50 -- Ignore system spids.

            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.

ORDER BY    1 ,

 DBCC FREEPROCCACHE

 DBCC FREESESSIONCACHE

 DBCC FREESYSTEMCACHE('All')

 DBCC DROPCLEANBUFFERS 

--内存使用情况

SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)') 

-- 内存状态

DBCC MemoryStatus 

--查看最小最大内存

SELECT

cfg.name AS [Name],

cfg.configuration_id AS [Number],

cfg.minimum AS [Minimum],

cfg.maximum AS [Maximum],

cfg.is_dynamic AS [Dynamic],

cfg.is_advanced AS [Advanced],

cfg.value AS [ConfigValue],

cfg.value_in_use AS [RunValue],

cfg.description AS [Description]

FROM

sys.configurations AS cfg 

--设置最小最大内存 

sp_configure 'show advanced options', 1 

go

sp_configure 'min server memory', 0

RECONFIGURE

GO 

sp_configure 'max server memory', 2147483647

RECONFIGURE

GO 

sp_configure 'max server memory', 256

RECONFIGURE

GO

sp_configure 'show advanced options', 0 

CREATE proc [dbo].reclaimmemory  --强制释放内存

as

begin

 DBCC FREEPROCCACHE

 DBCC FREESESSIONCACHE

 DBCC FREESYSTEMCACHE('All')

 DBCC DROPCLEANBUFFERS

exec sp_configure 'max server memory', 256

EXEC ('RECONFIGURE' )

WAITFOR DELAY '00:00:05'

EXEC  sp_configure 'max server memory', 2147483647

EXEC ('RECONFIGURE' )

GO

end