Results 1 to 4 of 4

Thread: Data File Size

  1. #1
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106

    Question Unanswered: Data File Size

    SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
    SELECT cntr_value/1024 size_in_mb ,
    (SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
    FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
    ) a

    I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
    The above query i am using is for logfile size. Can any one help me with datafile size plz.
    I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
    May be i am unaware of a simple stored-procedure that can do this for me.

    Howdy!

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    use master 
    go
    sp_helptext sp_spaceused
    go
    Maybe you will get some ideas from here .... am a little busy ... so just help yourself ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You can use code from sp_spaceused to make your own logic ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Quote Originally Posted by Enigma
    Code:
    use master 
    go
    sp_helptext sp_spaceused
    go
    Maybe you will get some ideas from here .... am a little busy ... so just help yourself ...
    Thanx for guiding; i would try.

    Howdy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •