February 16, 2007 at 3:37 pm
Hi All,
Does anyone have a handy script to monitor the database spaces to keep track of the sizes to make sure they are not expanse out of control. Thanks for the helps in advance.
Minh Vu
February 18, 2007 at 12:01 am
You can make use of the following script..
set nocount on
declare @cmd varchar(500)
declare @db varchar(128)
create table #results(FileType varchar(4) NOT NULL,
[Name] sysname NOT NULL,
Total numeric(9,2) NOT NULL,
Used numeric(9,2) NOT NULL,
[Free] numeric(9,2) NOT NULL,
dbname sysname NULL)
create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)
create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
begin
/* Get data file(s) size */
declare dcur cursor local fast_forward
for
select CATALOG_NAME
from INFORMATION_SCHEMA.SCHEMATA
open dcur
fetch next from dcur into @db
while @@fetch_status=0
begin
set @cmd = 'use ' + @db + ' DBCC showfilestats'
insert #data
exec(@cmd)
insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Data',
left(right([FileName],charindex('\',reverse([FileName]))-1),
charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
-CAST(((UsedExtents*64)/1024.00) as numeric(9,2))),
@db
from #data
delete #data
fetch next from dcur into @db
end
close dcur
deallocate dcur
/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')
insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize),
dbname
from #log
select @@servername as servername, dbname, sum(total) Total, sum(used) Used, sum(free) Free
from #results
group by dbname
order by Total desc
drop table #data
drop table #log
drop table #results
return
end
MohammedU
Microsoft SQL Server MVP
February 20, 2007 at 3:41 am
You could also use these SQL Server stored procedures....
to check space on disk....
execute master..usp_diskspace
to check database sizes.....
sp_helpdb
February 20, 2007 at 6:12 am
"execute master..usp_diskspace" is not working???
February 20, 2007 at 6:57 am
execute the extende procedure below that returns the free disk space on drives on your server
EXECUTE master..xp_fixeddrives
February 20, 2007 at 9:23 am
Monitor Database Growth on Multiple SQL Servers
http://www.databasejournal.com/features/mssql/article.php/3339681
You can take the T-SQL part only. No need to use VBscript.
The core code is:
exec master.dbo.xp_fixeddrives -- for disk space
exec master.dbo.sp_msforeachdb
"select @@servername as Servername,'?' as Databasename,getdate() as TimeStamps, * from ?.dbo.sysfiles" -- for DB size
February 20, 2007 at 6:15 pm
Thank you so much for all of the helps.
They all worked for my case.
Minh Vu
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply