Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Exclamation Unanswered: Server data spaces. Need help quick please

    Gurus help needed.
    Gurus.. Please help for Data file used space
    I want to select the information for 'database used' and 'datafile used' spaces from the system tables. Which table or query can I use?

    I updated statiscs, ran sp_updateusdage command but found the data displayed by sp_spaceused command without parameters lot differing from the data displayed from Enterprise Manager/View/Taskpad for the same database. Though this is simple in other databse vendors, it looks a bit difficult in SQLServer2000 which is known for user friendly.

    For example I found fo a databse
    Enterprise Manager shows database free: 3.13 MB

    sp_spaceused shows:

    Database: size: 4.00 MB, Unallocated: 2.48 MB

    Reserved: 528 KB
    Data: 144 KB
    Index: 128 KB
    Unused: 104 KB

    In this case what should I believe like 3.13 MB or 2.48 MB?

    What is the datafile used size here? Is it 144 KB? Then the Enterprise manager taskpad shows for datafile as free: 2.38 MB.

    I am totally confused on these numbers.

    Instead of executing commands to know the information, I am trying to write SQL statements to execute and store this data into a file and send me e-mail.

    Can any one help please with this jugglary?

    Thanks
    Vinnie

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Server data spaces. Need help quick please

    Originally posted by vkaramched
    Gurus help needed.
    Gurus.. Please help for Data file used space
    I want to select the information for 'database used' and 'datafile used' spaces from the system tables. Which table or query can I use?

    I updated statiscs, ran sp_updateusdage command but found the data displayed by sp_spaceused command without parameters lot differing from the data displayed from Enterprise Manager/View/Taskpad for the same database. Though this is simple in other databse vendors, it looks a bit difficult in SQLServer2000 which is known for user friendly.

    For example I found fo a databse
    Enterprise Manager shows database free: 3.13 MB

    sp_spaceused shows:

    Database: size: 4.00 MB, Unallocated: 2.48 MB

    Reserved: 528 KB
    Data: 144 KB
    Index: 128 KB
    Unused: 104 KB

    In this case what should I believe like 3.13 MB or 2.48 MB?

    What is the datafile used size here? Is it 144 KB? Then the Enterprise manager taskpad shows for datafile as free: 2.38 MB.

    I am totally confused on these numbers.

    Instead of executing commands to know the information, I am trying to write SQL statements to execute and store this data into a file and send me e-mail.

    Can any one help please with this jugglary?

    Thanks
    Vinnie
    This stored procedure

    CREATE PROCEDURE CP_LCSQL_FileSizing AS
    DECLARE @dbid INT
    DECLARE @MaxId INT
    DECLARE @dbName SYSNAME
    SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
    SET @dbid = 1
    WHILE @dbid <= @MaxId
    BEGIN
    SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)
    IF (@dbname IS NOT NULL)
    BEGIN
    EXEC ('SET QUOTED_IDENTIFIER OFF
    insert into db_Statistics SELECT "' + @dbname + '" AS DB_Name,
    RTRIM(name) AS DB_DevName,
    RTRIM(filename) AS DB_Filename,
    RTRIM(size) AS DBSize, CURRENT_TIMESTAMP as Stat_Time FROM [' + @dbname + '].dbo.sysfiles')
    SET @dbid = @dbid + 1
    END
    ELSE
    SET @dbid = @dbid + 1
    END

    The stored procedure stores the data in a table (db_Statistics) in the master database. I have it autorun the first day of the month and then pick up data at a later point. Note that this returns the actual size of the datafile, not the unused space.

    Hope this helps.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134
    Hello,

    Thanks for the reply. I really want the free/unused size of data file and database. I could get the sizes of database/datafile and logfile details(log file unused space too). I appreciate your reply but would you please tell me how to get the unused/free space details? I could see these details through Enterprise Manager, therefore I think there may be a way to get that info from system tables. Even otherwise as I gave the details, Enterprise manager data is lot different from sp_spaceused data even though I ran dbcc updateusage. I am not sure which is correct. I really appreciate anyone's help in this at the earliest.

    Thanks for any help
    Vinnie

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by vkaramched
    Hello,

    Thanks for the reply. I really want the free/unused size of data file and database. I could get the sizes of database/datafile and logfile details(log file unused space too). I appreciate your reply but would you please tell me how to get the unused/free space details? I could see these details through Enterprise Manager, therefore I think there may be a way to get that info from system tables. Even otherwise as I gave the details, Enterprise manager data is lot different from sp_spaceused data even though I ran dbcc updateusage. I am not sure which is correct. I really appreciate anyone's help in this at the earliest.

    Thanks for any help
    Vinnie
    Are you dividing the space used in the EM by 1024?
    My master says 9.31 MB which if you do SELECT 9.31*1024 in the Analyzer comes out as 9533.44 KB and my SP_SPACEUSED comes out

    reserved data index_size unused
    ------------------ ------------------ ------------------ ------------------
    9520 KB 7232 KB 1264 KB 1024 KB

    9520-9533.44=13.44 KB If you are sweating 13K on disk space, you need to do a some upgrades on your server.

    And the other one you can do is go into the EM, look at the properties of the SP_SPACEUSED and create a new procedure that inserts the returned data into a table witha date stamp as opposed to writing it to the analyzer.

    I think that the best suggestion is to use a modified spaceused procedure and ignore what the EM says for the most part. And I'll bet if you do parse out to bytes in what you store and then do your division by 1024, you will come up with the same sizes in both the query analyzer and the EM.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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