SQL Server Performance Forum – Threads Archive
Database Space Monitoring.
Hi group, I have seen the article from Neeraj Nagpal thanks to neeraj greate article it is about the monitoring space on multiple server. is there way to create a script that has to look into the data file space and log file space for each database on a single sql server and if the database id running out of space then it should automatically increase the log file and data file size. Thanks in Advance. RiteshYou make use of the following script…just add the additional code to check the free space and based on your threashold run the ALTER DATABASE STATEMENT to increase the file size…<br /><br />set nocount on <br /><br /><br />declare @cmd varchar(500) <br />declare @db varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> <br /><br /><br />create table #results(FileType varchar(4) NOT NULL, <br /> [Name] sysname NOT NULL, <br /> Total numeric(9,2) NOT NULL, <br /> Used numeric(9,2) NOT NULL, <br /> [Free] numeric(9,2) NOT NULL, <br /> dbname sysname NULL) <br /><br /><br />create table #data(Fileid int NOT NULL, <br /> [FileGroup] int NOT NULL, <br /> TotalExtents int NOT NULL, <br /> UsedExtents int NOT NULL, <br /> [Name] sysname NOT NULL, <br /> [FileName] varchar(300) NOT NULL) <br /><br /><br />create table #log(dbname sysname NOT NULL, <br /> LogSize numeric(15,7) NOT NULL, <br /> LogUsed numeric(9,5) NOT NULL, <br /> Status int NOT NULL) <br /><br /><br />begin <br /><br /><br /> /* Get data file(s) size */ <br /> declare dcur cursor local fast_forward <br /> for <br /> select NAME from sys.databases<br /><br /> open dcur <br /><br /><br /> fetch next from dcur into @db <br /><br /><br /> while @@fetch_status=0 <br /> begin <br /><br /><br /> set @cmd = ‘use ‘ + @db + ‘ DBCC showfilestats’ <br /> insert #data <br /> exec(@cmd) <br /><br /><br /> insert #results(FileType,[Name],Total,Used,[Free],dbname) <br /> select ‘Data’, <br /><br /><br />left(right([FileName],charindex(”,reverse([FileName]))-1), <br /> charindex(‘.’,right([FileName], <br /> charindex(”,reverse([FileName]))-1))-1), <br /> CAST(((TotalExtents*64)/1024.00) as numeric(9,2)), <br /> CAST(((UsedExtents*64)/1024.00) as numeric(9,2)), <br /> (CAST(((TotalExtents*64)/1024.00) as numeric(9,2)) <br /> -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))), <br /> @db <br /> from #data <br /><br /><br /> delete #data <br /><br /><br /> fetch next from dcur into @db <br /><br /><br /> end <br /> close dcur <br /> deallocate dcur <br /><br /><br /> /* Get log file(s) size */ <br /> insert #log <br /> exec(‘dbcc sqlperf(logspace)’) <br /><br /><br /> insert #results(FileType,[Name],Total,Used,[Free],dbname) <br /> select ‘Log’,dbname+’_log’,LogSize, <br /> ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize), <br /> dbname <br /> from #log <br /><br /><br /> select dbname,FileType,[Name],Total,Used,[Free] <br /> from #results order by dbname,FileType <br /><br /><br /> drop table #data <br /> drop table #log <br /> drop table #results <br /><br /><br /> return <br /><br /><br />end <br /><br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
Thank you very much for the srcipt Mohammed!! Regards
Ritesh Kumar
]]>