none
Verfügbarer Speicherplatz einer Datenbank RRS feed

  • Frage

  • Hallo,

    ich möchte per Abfrage den verfügbaren Speicherplatz einer Datenbank ermitteln. Gut, das geht über sys.sysfiles und fileproperty(<Name>,'SpaceUsed'). Im Internet gibt es da einige Vorschläge dazu.

    -- Get free space in filegroups of all databases
    -- ---------------------------------------------
    -- Create temporary table for resultset
    CREATE TABLE #GetFileGroupSize(
      DatabaseName varchar(255),
      GroupName varchar(255),
      FilegroupSize_MB decimal(12, 2),
      FilegroupSpaceUsed_MB decimal(12, 2),
      FilegroupFreeSpace_MB decimal(12, 2),
      FilegroupFreeSpace_Percent decimal(12, 2)
    );
    
    -- Execute query against all databases and insert the results into the temporary table
    INSERT INTO #GetFileGroupSize
    EXEC sp_MSForEachDB '
    USE [?];
    SELECT
      DB_NAME() AS DatabaseName,
      groups.name AS GroupName,
      CONVERT(decimal(12, 2), ROUND(SUM(files.size/128.000), 2)) AS FilegroupSize_MB,
      CONVERT(decimal(12, 2), ROUND(SUM(fileproperty(files.name, ''SpaceUsed'')/128.000), 2)) AS FilegroupSpaceUsed_MB,
      CONVERT(decimal(12, 2), ROUND(SUM(files.size/128.000) - SUM(fileproperty(files.name, ''SpaceUsed'')/128.000), 2)) AS FilegroupFreeSpace_MB,
      CONVERT(decimal(12, 2), ROUND((SUM(files.size/128.000) - SUM(fileproperty(files.name, ''SpaceUsed'')/128.000))/(SUM(files.size/128.000))*100, 2)) AS FilegroupFreeSpace_Percent  
    FROM
      sys.sysfiles files, sys.filegroups groups
    WHERE
      files.groupid = groups.data_space_id
      -- Uncomment the following lines if you are only interested in the default filegroup
      --  AND
      --  groups.is_default = 1
    GROUP BY
      groups.name;'
    
    -- Return resultset from temporary table
    SELECT * FROM #GetFileGroupSize ORDER BY FilegroupFreeSpace_Percent;
    
    -- Cleanup temporary table
    

    Wenn ich dann aber mit dem Management Studio die Datenbankeigenschaften abfrage, dann habe ich dort unter "verfügbaren Speicherplatz" eine Differenz.

    SQL-Abfrage:
    FilegroupSize_MB           = 3756.13
    FilegroupSpaceUsed_MB   = 3170.25
    FilegroupFreeSpace_MB    =  585.88

    Management Studio  Datenbankeigenschaften 
    verfügbaren Speicherplatz = 365,99 MB

    Da ich schon einmal unter Win XP an die 4GB Grenze für Express gestossen bin, möchte ich sicher handeln können. Wie ist die Angabe vom  Management Studio zu bewerten?

    Gruss Klaus.


    KR

    Montag, 4. November 2013 12:40

Antworten

  • Wenn ich dann aber mit dem Management Studio die Datenbankeigenschaften abfrage, dann habe ich dort unter "verfügbaren Speicherplatz" eine Differenz.

    Hallo Klaus,

    Du hast da keine Differenz, denn Deine Abfrage ermittelt nicht den "verfügbaren Speicherplatz", sondern den belegt und dazu den frei Speicherplatz.

    Führe mal auf der Datenbank den Befehl

    exec sp_spaceused

    aus (siehe sp_spaceused (Transact-SQL)), im ersten Resultset gibt es die Spalte "unallocated space", das ist der "verfügbare Speicherplatz".

    Wo hast Du übrigens die Abfrage her? Beim Datenbankgrößen-Limit für die Express Edition wird nicht nur "SpaceUsed" sondern auch "SpaceUnused" berücksichtigt, was Du aber nicht über "FileProperty" ermitteln kannst.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Als Antwort markiert KlaRa1 Dienstag, 5. November 2013 10:54
    Dienstag, 5. November 2013 10:19

Alle Antworten

  • Wenn ich dann aber mit dem Management Studio die Datenbankeigenschaften abfrage, dann habe ich dort unter "verfügbaren Speicherplatz" eine Differenz.

    Hallo Klaus,

    Du hast da keine Differenz, denn Deine Abfrage ermittelt nicht den "verfügbaren Speicherplatz", sondern den belegt und dazu den frei Speicherplatz.

    Führe mal auf der Datenbank den Befehl

    exec sp_spaceused

    aus (siehe sp_spaceused (Transact-SQL)), im ersten Resultset gibt es die Spalte "unallocated space", das ist der "verfügbare Speicherplatz".

    Wo hast Du übrigens die Abfrage her? Beim Datenbankgrößen-Limit für die Express Edition wird nicht nur "SpaceUsed" sondern auch "SpaceUnused" berücksichtigt, was Du aber nicht über "FileProperty" ermitteln kannst.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Als Antwort markiert KlaRa1 Dienstag, 5. November 2013 10:54
    Dienstag, 5. November 2013 10:19
  • Hallo,

    vielen Dank für die Antwort.

    >Beim Datenbankgrößen-Limit für die Express Edition wird nicht nur "SpaceUsed" sondern auch
    >"SpaceUnused" berücksichtigt, was Du aber nicht über "FileProperty" ermitteln kannst.

    use xy_db;
     select
     getdate(),a.FILEID,
     [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
     [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
     [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
     NAME = a.NAME, FILENAME = a.FILENAME, DB_NAME()
     from dbo.sysfiles a
     

    Ich habe hier mal eine einfachere Variante der Abfrage. Die ist etwas übersichtlicher.  FILE_SIZE_MB müsste eigentlich der Wert für das Datenbankgrößen-Limit der Express Edition sein.

    Die Quelle der ersten Abfrage war:
    http://www.schmoeker.org/pages/wissenswertes/sql-server/freien-speicher-aller-datenbanken-ermitteln.php

    Gruss Klaus.


    KR


    • Bearbeitet KlaRa1 Dienstag, 5. November 2013 10:53
    Dienstag, 5. November 2013 10:52