SQL Server Performance

Database growth

Discussion in 'SQL Server 2005 General DBA Questions' started by california6, Apr 26, 2007.

  1. california6 New Member

    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

  2. satya Moderator

  3. california6 New Member

    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
  4. MohammedU New Member

    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.
  5. california6 New Member

    MoahmmedU,<br /><br />So good to see you <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Welcome back..<br /><br />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.<br /><br />Would greatly appreciate if you could please advice as to why it's returning "0" value in %used.<br /><br />Thanks again for the answer,<br />Did you get some sleep? <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Cali
  6. california6 New Member

    MohammedU,

    Is this working for you? Appreciate if you could please let me know.

    Thanks,
    Ayub
  7. MohammedU New Member

    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.
  8. california6 New Member

    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




  9. california6 New Member

    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=':)' /><br /><br />Thanks for all your help MohammedU. You are simply great.<br /><br /><br />select c.srvname, b.Name as Dataabase, --a.name as 'DB File', a.Filename AS 'Data File Path'<br />sum(maxsize*8/1024) 'File Max MB', <br />sum(size*8/1024) as 'UsedMB',<br />sum(convert(numeric(20,5), a.size))/sum(convert(numeric(20,5),maxsize))*100 as '%Used' <br />from sysaltfiles a, sysdatabases b , sysservers c where maxsize &gt;-1 <br />and (size*100/maxsize)&gt;30<br />and (size*8/1024) &lt;300<br />and a.dbid = b.dbid<br />group by c.srvname , b.Name<br />order by b.Name
  10. MohammedU New Member

    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.
  11. california6 New Member

    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
  12. california6 New Member

    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,
  13. MohammedU New Member

    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.
  14. california6 New Member

    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
  15. MohammedU New Member

    Modify the following script as needed...<br /><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 />select @@servername as servername, dbname, sum(total) Total, sum(used) Used, sum(free) Free<br /> from #results<br />group by dbname<br />order by Total desc<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 />**********************/<br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  16. california6 New Member

    Thank you MohammedU for this script. I will look into this.

    Many thanks for your help.
    Cali

Share This Page