SQL Server Performance Forum – Threads Archive

Database growth

Hello Folks, I am sorry for posting this again. Unfortunately this thing isn#%92t working and it’s confusing from one SQL Server ver to another. First: I would like to know, which system table hold’s user object size? In oracle you have dba_segments which lists all the user objects size. What’s the equivalent here on SQL Server? Second: The database growth question: Well MohammedU and other folks answered to this question and here’s the query what I am using. But this query is reporting in-correct percentages when ran on a SQL Server 2000 database which has 5 datafiles and 4 logfiles. Here#%92s what we would like to see from this query: Report the database Percentage > 80% AND space growth < 1GB to grow: But as I said in the above paragraph, when ran onto a SQL Server 2000 database, the percentage result was > 500%. That#%92s very weird. The query which has been massage and re-posted by couple of folks here looks as follows: select c.srvname as Servername, b.Name as Dataabase,
sum(size*8/1024) ‘Used Size’,
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) >80
and a.dbid = b.dbid
group by c.srvname, b.Name
order by b.Name Can somebody please look into this and advice what needs to be done in order to make this work. Please. Many thanks,
Cali

Run DBCC UPDATEUSAGE to correct the stats and run the statment again.
http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32 – system views map fyi. 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.

Hello Satya, I have done that. But still show’s in-correct number. Here’s how the results looks like: Query: select c.srvname as Servername, b.Name as Dataabase, –a.name as ‘DB File’, a.Filename AS ‘Data File Path’
sum(size*8/1024) as ‘UsedMB’,
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) >60
— sum(size*8/1024,2) <100
and a.dbid = b.dbid
group by c.srvname, b.Name
order by b.Name
Servername Database UsedMB FileMAX MB %USed Cali Live 12004 14000 167
The %Used 167 is crazy. I dont know, how SQL Server is calculating the %used from? The above database has 7 files associated with it:
1
1112840812800003200327700TSCNTemplate_dat 2
06402560001024328340TSCNTemplate_log 3
14082565120002048327700Live_1_Data 4
01282560001024328340live_1_Log 5
012805120001024328340live_2_Log 6
01285120001024328340Live_3_Log 7
1128001310721010813460Live_2_Data
Also in my above query — sum (size*8/1024,2) <100 isnt working either. Apprecaited if you could please advice as to how to proceed. This is really frustrating.. Also what table holds all the user object size and growth estimates? Thanks again for all your help.
Cali

May be your pecentage calculation is wrong… select c.srvname as Servername, b.Name as Dataabase, –a.name as ‘DB File’, a.Filename AS ‘Data File Path’
sum(size*8/1024) as ‘UsedMB’,
sum(maxsize*8/1024) ‘File Max MB’,
sum(a.size/maxsize)*100 as ‘%Used’
from sysaltfiles a, sysdatabases b, sysservers c where maxsize<>-1 and
(size*100/maxsize) >60
— sum(size*8/1024,2) <100
and a.dbid = b.dbid
group by c.srvname, b.Name
order by b.Name MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

MoahmmedU, So good to see you <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Welcome back.. Well thanks for the answer. I ran the query you posted. Unfortunately the %Used percetage returns a 0(zero) value now <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I am using this on SQL Server 2005 now. This thing is really killing and i really appreciate all your help on this. Would greatly appreciate if you could please advice as to why it’s returning "0" value in %used. Thanks again for the answer, Did you get some sleep? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Cali

MohammedU, Is this working for you? Appreciate if you could please let me know. Thanks,
Ayub

Check the followign…
For some reason sql is rounding it zero… select c.srvname, b.Name as Dataabase, –a.name as ‘DB File’, a.Filename AS ‘Data File Path’
sum(size*8/1024) as ‘UsedMB’,
sum(maxsize*8/1024) ‘File Max MB’,
sum(convert(numeric(20,5), a.size))/sum(convert(numeric(20,5),maxsize))*100 as ‘%Used’
from sysaltfiles a, sysdatabases b , sysservers c
where maxsize<>-1
and a.dbid = b.dbid
group by c.srvname , b.Name
order by b.Name
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

MohammedU, Thank you so much for the answer. I am sorry for coming back with one or the other issue. Please excuse me. But here’s what i see now: On SQL Server 2005, The SQL does not work. When i ran it, here’s what it complains: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
On SQL Server 2000: The SQL works without problem. Dont know why does not work on 2005. Any suggestions?
Also the SQL is working on 2000, which is great. But with the above query, it’s returning all the rows. But wanted to see only %USED > 50 percent only. dont know how to fix in the query? Same time i want to add a "AND" clause to the statement. %USED > 50 percent AND 100 MB free space left to grow on files. Really appreciated if you could please please look into this. Thank you again for all your help,
cali

I did some hit and try with the query and here it is: It’s working on SQL 2000 and SQL 2005. MohammedU, please take a look at it and give your blessings: <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks for all your help MohammedU. You are simply great. select c.srvname, b.Name as Dataabase, –a.name as ‘DB File’, a.Filename AS ‘Data File Path' sum(maxsize*8/1024) ‘File Max MB’, sum(size*8/1024) as ‘UsedMB’, sum(convert(numeric(20,5), a.size))/sum(convert(numeric(20,5),maxsize))*100 as ‘%Used’ from sysaltfiles a, sysdatabases b , sysservers c where maxsize >-1 and (size*100/maxsize)>30 and (size*8/1024) <300 and a.dbid = b.dbid group by c.srvname , b.Name order by b.Name

I don’t see any problem with this query but what is the purpose of this query…
You want see the info only for auto grow disabled files? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The purpose of this query is to report Database growth > 30 percent and less than 300 MB to grow over in database. But i do not know, if this query works for the files which do not have a max file growth specified. Also dont know, if this is working for Log files. Appreciated all your help. Thanks,
Cali

I dont see the script is taking the logfiles in consideration. Dont know, why the txn log files are not included. Any suggestions MohammedU? Thanks,

Why don’t you use the other script which posted on a different thread?
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I dont think, the other script takes txn log files too. But if you have the other script here, i can try. Thanks again,
Cali

Modify the following script as needed… 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 /********************************** **********************/ MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thank you MohammedU for this script. I will look into this. Many thanks for your help.
Cali

]]>