November 14, 2007 at 6:35 am
My tempdb database has 4 data files and 1 log file. I need to monitor the growth of each data and log file to make sure they don't fill up completely. The Taskpad view in SQL 2000 shows me what I am looking for however I want to be able to schedule a job that would collect the same data automatically for a certain period of time. Is there a way to collect and present that data using TSQL?
November 14, 2007 at 3:01 pm
I believe this simple query will work:
from db in question:
select name, (size * 8)/1024 from sysfiles
will give you size in MB
November 14, 2007 at 3:45 pm
Take a look at the script on the link below. It will return a detailed analysis of all database files on a server.
Get Server Database File Information
November 14, 2007 at 4:41 pm
Great! Thanks for the tip. That query shows me what I'm looking to gather.
November 14, 2007 at 6:42 pm
Hi,
Just excute DBCC SQLPERF(LOGSPACE)
Regards,
Ahmed
November 14, 2007 at 7:06 pm
I've tried that before and it only show me the total for all the tempdb. My tempdb has multiple files and I want to know the stats of each of the files. Thanks though.
November 14, 2007 at 7:18 pm
Hi,
Check this TSQL
Declare @dbName sysname
declare @strSQL sysname
-- Get Databases names for Online ones.
Declare @nextExtension Float
Declare cDB Cursor For Select name From master..sysdatabases Where status&512=0
Create Table #spHelpFile([DbName] sysname null,[DLFileName] sysname,fileid smallint ,[filename] nchar(240) ,[filegroup] sysname null,[Size] nvarchar(18),[MaxSize] nvarchar(18),growth nvarchar(18),usage varchar(9))
Open cDB
Fetch Next From cDB Into @dbName
While @@Fetch_Status =0
Begin
Set @strSQL = 'use ' + @dbName + ' exec sp_helpfile'
Insert into #spHelpFile ([DLFileName],fileid,[filename],[filegroup],[Size],[MaxSize],growth,usage)
Exec(@strSQL)
Declare @st varchar (400)
Set @st='Update #spHelpFile
Set [DbName]= ''' + @dbName + ''' ,[Size]= Convert(Float,Rtrim(Ltrim(Replace([Size],''KB'','''')))),[MaxSize]=Convert(bigint,Case When [MaxSize]=''Unlimited'' Then ''0'' Else Rtrim(Ltrim(Replace([MaxSize],''KB'',''''))) End) Where [DbName] Is Null'
exec(@st)
Update #spHelpFile
Set growth=Case
When Charindex('%',growth)=0 Then Convert(Decimal(10,2),Convert(Float,Replace(growth,'KB',''))) Else (Convert(Float,Replace(growth,'%','')) * [Size])/100
End
Fetch Next From cDB Into @dbName
End
Close cDB
Deallocate cDB
Select
substring([DbName],1,40) As [Database Name]
,substring([DLFileName],1,50) As [Data/Log File Name]
,Case
When [filegroup]='PRIMARY' Then 'Data' Else 'Log'
End As [File Type]
,Convert(bigint,Convert(Float,[Size])) As [Size (KB)]
,[MaxSize] As [MaxSize (KB)]
,Growth As [Next Extension]
,Substring([filename],1,1) As Drive
From
#spHelpFile
Drop table #spHelpFile
Regards,
Ahmed
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply