Hi There
As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.
However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.
So i want to write a job that checks the percentage space used for all databases.
My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @.DBNAME' and execute it the cursor context stays local you do not actually change database context.
So how do i loop though databases, i have found
sp_msforeachdb, however this is undocumented in BOL.
Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.
Obviously i would rather use documented methods.
So bottom line what tsql could i use to check the percentage of file space use for all databases?
Thanx
In sql server 2005 you can do this:
select * from master.dbo.sysdatabases
returns the list of databases.
for each database you can do this:
select * from [database_name].dbo.sysfiles
This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:
select sum ( size ) * 8192 from [database_name].dbo.sysfiles
The downside is that this is not documented and not recommended by microsoft as the database names may change.
I'm not sure if you can do this in sql2000 - you'd have to try.
|||If this is sql server 2005 then you can use something like
select name , (size/max_size) [PERCENT] from sys.master_files
to get the file space used percentage for all the database files. You need to special case for some specific values of max_size like (0,-1). Look up the documentation for sys.master_files at http://msdn2.microsoft.com/en-us/library/ms186782.aspx for sql server 2005. Let me know if that works for you.Let me know if that works for you.
|||Here you go; try this...
dbcc sqlperf(logspace)
|||HI Guys
Ok i have tried you rsuggestions but this does not work, neither solution works because ia m interested in % file space used. In my case maxsize in both tables is -1 because they have unrestricted file growth, therefore i cannot calculate percentage used.
I want to monitor the file percentage used so that i can grow filegroups at specified times i do not want data files to auto grow during production hours. I would imagine many DBA's would want to monitor this, but how ? Like i said DBCC showfilestats works perfectly but is is undocumented? I also dont know how it works since sys.master_files and [database].dbo.sysfiles do not have an accurate maxsize(-1) so i cannot calculate it ?
Anyone?
