Post reply

Space Used for database files

  • October 26, 2004 at 11:15 am

    #102251

    In EM, you can display the space used for each database files (.MDF, .NDF, .LDF) using Task Pad. Is there a way to find the space used for each database file of a database using transact-sql ?

    Thanks,

    Jimmy

  • October 26, 2004 at 12:02 pm

    #528038

    sp_helpfile will show you the files sizes.

    --
    Adam Machanic
    whoisactive

  • October 26, 2004 at 1:54 pm

    #528056

    I tested. But sp_helpfile only provides allocated space.

  • October 26, 2004 at 2:05 pm

    #528058

    Grasshoper - Thanks for the reply. I think you misunderstood what I want. Sp_Spaceused does not provide space used for a given .MDF or .LDF file.

  • October 26, 2004 at 3:07 pm

    #528065

    I just found out an undocumented command that provides the info I need:

    DBCC ShowFileStats

    and space used = UsedExtents * 64.0 / 1024.0

  • October 27, 2004 at 12:35 am

    #528091

    Try SELECT * FROM SYSFILES

    may help u

  • October 27, 2004 at 6:07 am

    #528141

    Why not see what EM fires - set up a profiler trace on a db and then use EM to display the db in TaskPad view - it'll show you exactly what EM fires - all T-SQL commands (including dbcc showfilestats).

  • October 27, 2004 at 6:30 am

    #528144

    For getting actual file used if you are using single file you can use command

     

    dbcc sqlperf(logspace)

  • October 27, 2004 at 8:41 am

    #528167

    We have a job that runs and populates a table and we query the table to generate a weekly report.  The job executes the following statement...

    insert into yourDB.dbo.yourTableName (db, fileID, filesize)

     exec sp_MSforeachdb @command1 =

      'select db_name(dbid), sf.fileid, sf.size

      from sysdatabases sd, ?..sysfiles sf

      where db_name(dbid) = ''?'''

  • October 27, 2004 at 8:57 am

    #528174

    FILEPROPERTY will return used space. Use the following code to get the total size (MB) and the used space (MB) for each file in the current db:

     DECLARE @bytesperpage dec(18,0)

     , @pagesperMB dec(18,3)

     -- get number of bytes per page

     SELECT @bytesperpage = low

     FROM master.dbo.spt_values

     WHERE number = 1 and type = 'E'

     -- get number of page for 1 MB

     SELECT @pagesperMB = 1048576 / @bytesperpage

                                  

     SELECT a.name AS LogicalFileName,

      CASE WHEN groupid=0 THEN 1 ELSE 0 END AS IsLogFile,

      CAST(a.size/@pagesperMB as dec(16,3)) as FileSizeMB,

      CAST(CAST(FILEPROPERTY(a.name,'SpaceUsed') as int)/@pagesperMB as dec(16,3)) AS UsedSpaceMB

     FROM sysfiles a

     

     

  • October 27, 2004 at 2:16 pm

    #528218

    Thank you all for the help.

    Grasshoper - Your solution using FILEPROPERTY function is the best ! Please allow me to futher simplify as followed:

    SELECT FILEPROPERTY(DbFileLogicalName, 'Spaceused') * 8.0 / 1024.0

    Thank you so much,

    Jimmy

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply