How to verify SQL Server Tempdb size via Script?

In my client place we have around 20 SQL Server Machine and i want to monitor Tempdb size (.mdf/ldf)in all the machine using script. 10 servers in 2005 and 10 servers in 2008 SQL Server. Please let me know the script. Thanks.
PKTGAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lofConnect With a Mentor Commented:
it may be much simpler

either
exec sp_helpdb tempdb

or
select * from tempdb.sys.dm_db_file_space_usage
0
 
Shannon_LowderConnect With a Mentor Commented:
I believe the following code may have what you're looking for:

--exec usp_getmdfsize 'tempdb'
Create procedure usp_getmdfsize @dbname varchar(128)
as
declare @query varchar(200)
set @query ='begin use ' + @dbname + ' End begin DBCC showfilestats end'
create table #mytable(Fileid int, FileGroup int, Totalextents bigint,UsedExtents bigint,Name varchar(256),Filename varchar(600))
insert #mytable exec (@query)
select Name as DatabaseName,Filename,(TotalExtents*64)/1024 as TotalMB,(Usedextents*64)/1024 as UsedMB,
Free= ((TotalExtents*64) - (Usedextents*64))/1024,
FreePercent=(((TotalExtents*64*1.0) - (Usedextents*64*1.0))/(TotalExtents*64*1.0))*100
from #mytable
drop table #mytable
go

The first size is your mdf, the second is your ldf.  If I've misunderstood your request, please, let me know.  I'm here to help!
0
All Courses

From novice to tech pro — start learning today.