Sp tempdbuse--Check usage of all tempdbs
From SybaseWiki
create procedure sp__tempdbuse as
-- Procedure: sp__tempdbuse -- Created: May 2008 -- Author: Bob Holmes (Email: cambob@gmail.com) -- Usage: sp__tempdbuse
set nocount on
select name into #dblist from master..sysdatabases where name like "%tempdb%"
declare db_cursor cursor for select name from #dblist
declare @current char(20)
open db_cursor
fetch db_cursor into @current
create table #results (dbname char(20), totalspace char(10), freespace char(10),
pctused char(3))
while @@sqlstatus = 0
begin
select db_name(dbid) 'DatabaseName', segmap 'Segmap', name 'Segname', sum(size) 'Allocated', sum(curunreservedpgs(dbid,lstart,unreservedpgs)) 'Free' into #SegAlloc from master.dbo.sysusages , syssegments where dbid = db_id(@current) and segmap & power(2,segment) = power(2,segment) and syssegments.name != 'system'
and syssegments.name != 'logsegment'
group by dbid,segmap,name order by dbid,segmap,name
insert #results select DatabaseName, rtrim(convert(char(10),sum(Allocated)/512)), rtrim(convert(char(10),sum(Free)/512)), rtrim(convert(char(10),100 -(sum((Free)/512) * 100 / (sum
(Allocated)/512))))
from #SegAlloc group by DatabaseName,Segname order by DatabaseName,Segname
drop table #SegAlloc
fetch db_cursor into @current
end
select * from #results
close db_cursor
deallocate cursor db_cursor
return