SQL Server Performance Forum – Threads Archive

Group by

I have the following query which reports a database file size and percentage space usage of each file in a database. What i would like to see is, group this query by over all percentage usage and not see by each file usage. Is this possible? if yes, could you please share this? Here’s how my query looks like to calcuate each data file size: select c.srvname as Servername, b.Name as Dataabase, a.name as ‘DB File’,
a.Filename
AS ‘Data File Path’,size*8/1024 ‘Used MB’,
maxsize*8/1024 ‘File Max MB’,
(a.size*100/maxsize) as ‘%Used’
from sysaltfiles a, sysdatabases b, sysservers c where maxsize<>-1 and
(size*100/maxsize) >11
and a.dbid = b.dbid Thanks again,
Cali

You mean group by DB name? select c.srvname as Servername, b.Name as Dataabase, –a.name as ‘DB File’, a.Filename AS ‘Data File Path’
sum(size*8/1024) ‘Used MB’,
sum(maxsize*8/1024) ‘File Max MB’,
sum(a.size*100/maxsize) as ‘%Used’
from sysaltfiles a, sysdatabases b, sysservers c where maxsize<>-1 and (size*100/maxsize) >11
and a.dbid = b.dbid
group by c.srvname, b.Name
order by b.Name ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Dinakar, Yes this is what i wanted. Thank you so much, this is much better. But i noticed, the txn logspace is not included in the above calculation. Dont you think including txn log space usage will be important from calculation perspective? Many thanks,
Cali

Here is another script…<pre id="code"> set nocount on declare @cmd varchar(500) declare @db varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 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 NAME from sys.databases 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 dbname,FileType,[Name],Total,Used,[Free] from #results order by dbname,FileType 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 /********************************** **********************/ </pre id="code"> MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thank you MohammedU for the answer. Now i have a question: In order for my script and ndinakar script to work, I had to set the database Data file max size limit. If I dont do that, then the sysaltfiles table with column maxsize show’s a -1 value and the scripts doesnt pull any information upon execution. Now let’s say i have setup the maxsize limit for one of my database file and if later i add another database file without specifying maxsize to my database, what might happen here? Well the scripts are extracting the information based on maxsize limit and if we dont have that set, then it’s like unlimited growth and i dont see the script might work here: What do you say? I believe oracle has something called maxbytes and i was wondering, if SQL Server has anything like this here? I have setup a script to monitor tablespace – datafiles in oracle and here’s how the script looks like on Oracle: I wanted to implement the same script here for my SQL Server process too: select vd.name, ddf.cnt, ddf.tablespace_name, ds.db_mb, ddf.df_mb, ddf.df_mmb,
round(ddf.df_mmb – ds.db_mb,2) df_growable, round(ds.db_mb/ddf.df_mmb*100,0) db_mmb_pct
from v$database vd,
(select tablespace_name,
round(sum(bytes)/1024/1024) db_mb
from dba_segments
group by tablespace_name) ds,
(select count(tablespace_name) cnt,
tablespace_name,
round(sum(bytes)/1024/1024) df_mb,
round(sum(greatest(bytes,maxbytes))/1024/1024) df_mmb
from dba_data_files
group by tablespace_name) ddf
where ddf.tablespace_name = ds.tablespace_name (+)
and round(ds.db_mb / ddf.df_mmb,2) > 0.7
and round(ddf.df_mmb – ds.db_mb) < 20000 — 20GB
order by db_mmb_pct Also something weird i noticed with SQL Server 2005. If I add a datafile to my database and drop that datafile later, sysaltfiles table is stil populating with the new updates. sysaltfiles is still showing the file exist and my calculation scripts are not working properly. I tried to ran direct update against sysaltfiles to remove this drop database datafiles it says the following: Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed. I also did this: sp_configure ‘allow updates’,1
go
reconfigure with override
go Try to run a delete against the sysaltfiles and it pop-up the above message. Ad hoc updates to system catalogs are not allowed. I am running SQL Server 2005. So what do you suggest? any help would be greatly appreciated. Many thanks,
Cali

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. Only by using DAC you will be able to update system tables, which is not recommended at all. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

No more playing with system tables in 2005 [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Run DBCC UPDATEUAGE before running the scripts and see …. MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

My question is, why isnt SQL Server delete files which are no more associated with a database? These deleted files sitting in sysaltfiles table is causing lot of confusion and causing my scripts to report wrong results in terms of database size. How could i get rid of these files? Appreciated if you could provide some guidelines. Thanks,
cali

That is because MS not allowing updating system tables, try querying sysaltfiles directly to ensure they are populated with old values. See this linkhttp://www.4guysfromrolla.com/webtech/032906-1.shtml is any help. (As referred above you can update system tables but it will risk the support from MS if needed)_ Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>