SQL Server Performance

Database Space Monitoring.

Discussion in 'SQL Server 2005 General DBA Questions' started by rembersuritesh, Mar 27, 2007.

  1. rembersuritesh New Member

    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.

    Ritesh



  2. MohammedU New Member

    You 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
  3. rembersuritesh New Member

    Thank you very much for the srcipt Mohammed!!

    Regards
    Ritesh Kumar

Share This Page