Wyświetlanie informacji o wszystkich plikach baz danych w SQL Server

90

Czy można wyświetlić informacje o plikach (MDF / LDF) wszystkich baz danych na serwerze SQL?

Chciałbym otrzymać listę pokazującą, która baza danych używa jakich plików na dysku lokalnym.

Co próbowałem:

  • exec sp_databases wszystkie bazy danych
  • select * from sys.databases pokazuje wiele informacji o każdej bazie danych - ale niestety nie pokazuje plików używanych przez każdą bazę danych.
  • select * from sys.database_filespokazuje pliki mdf / ldf masterbazy danych - ale nie inne bazy danych
sql-server  sql-server-2008  tsql  database-management 
— M4N
źródło

Odpowiedzi:

123

Możesz użyć sys.master_files .

Zawiera wiersz na plik bazy danych przechowywany w bazie danych master. To jest pojedynczy widok obejmujący cały system.

— Mikael Eriksson
źródło
4
Dzięki, to (połączone z sys.databases) jest tym, czego szukałem!
— M4N,
1
wybierz * z sys.master_files
— Cosmin
2
@ M4N Jeśli chcesz tylko uzyskać nazwę bazy danych, możesz również zadzwonić DB_NAME(database_id)zamiast dołączaćsys.databases
— Cleptus
84

Jeśli chcesz uzyskać lokalizację bazy danych, możesz sprawdzić lokalizację wszystkich DB .
możesz użyć sys.master_filesdo pobrania lokalizacji bazy danych i sys.databsenazwy bazy danych

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id
— Mehdi Haghshenas
źródło
18

Używam skryptu, aby uzyskać puste miejsce w każdym pliku:

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files
'
Select * From ##temp
drop table ##temp

Rozmiar jest wyrażony w KB.

— Jānis
źródło
Co Use [?]ma zrobić? Daje błąd, że nie może znaleźć tej procedury składowanej. Usunięcie go pokazuje tylko wielokrotnie bazy danych systemu.
— Abel
4

Utworzyłem to zapytanie:

SELECT 
    db.name AS                                   [Database Name], 
    mf.name AS                                   [Logical Name], 
    mf.type_desc AS                              [File Type], 
    mf.physical_name AS                          [Path], 
    CAST(
        (mf.Size * 8
        ) / 1024.0 AS DECIMAL(18, 1)) AS         [Initial Size (MB)], 
    'By '+IIF(
            mf.is_percent_growth = 1, CAST(mf.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
        (mf.growth * 8
        ) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth], 
    IIF(mf.max_size = 0, 'No growth is allowed', IIF(mf.max_size = -1, 'Unlimited', CAST(
        (
                CAST(mf.max_size AS BIGINT) * 8
        ) / 1024 AS VARCHAR(30))+' MB')) AS      [MaximumSize]
FROM 
     sys.master_files AS mf
     INNER JOIN sys.databases AS db ON
            db.database_id = mf.database_id
— Alexandru-Codrin Panaite
źródło
3

Możesz także spróbować tego. 

 select db_name(dbid) dbname, filename from sys.sysaltfiles
— Joe Pi
źródło
3

Wykonywanie następującego sql (zadziała tylko wtedy, gdy nie masz wielu plików mdf / ldf dla tej samej bazy danych)

SELECT
    db.name AS DBName,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'ROWS' and db.database_id = mf.database_id ) as DataFile,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'LOG' and db.database_id = mf.database_id ) as LogFile
FROM sys.databases db

zwróci to wyjście

DBName       DataFile                     LogFile
--------------------------------------------------------------------------------
master       C:\....\master.mdf           C:\....\mastlog.ldf
tempdb       C:\....\tempdb.mdf           C:\....\templog.ldf
model        C:\....\model.mdf            C:\....\modellog.ldf

i reszta baz danych

Jeśli Twoje bazy danych TempDB mają wiele MDF (tak jak moje), ten skrypt zakończy się niepowodzeniem. Możesz jednak użyć

WHERE db.database_id > 4

na końcu i zwróci wszystkie bazy danych z wyjątkiem baz systemowych.

— adeel41
źródło
Zdaję sobie sprawę, że jest to mały zbiór danych, ale to nie jest powód, aby używać skorelowanych podzapytań. Mogą być dobre w Oracle, ale są poważnymi zabójcami wydajności w SQL Server, ponieważ powodują przetwarzanie wiersz po wierszu. Twój skrypt będzie wysyłał zapytanie do tabeli sys.master_files dwukrotnie o każdy wiersz w tabeli sys.databases.
— Davos,
2
Oprócz komentarza Davos ... Ten skrypt zakończy się również niepowodzeniem z błędami, jeśli masz wiele plików danych lub plików dziennika dla dowolnej bazy danych. (np. podzapytanie zwróciło więcej niż 1 wartość.)
— Arkaine55
@Davos Wiem, co mówisz, ale to zależy od tego, jak często wykonujesz to zapytanie, w przeciwnym razie jest to optymalizacja wstępna, której prawdopodobnie nie potrzebujesz.
— adeel41
2
Generalnie zgadzam się, że wczesna optymalizacja jest zła, ale mówię, że skorelowane podzapytania są po prostu złym wzorcem, którego nigdy nie powinno się używać. Zawsze są wyjątki od reguły „nigdy”, ale to nie jest jeden z tych przypadków. Wiem, że to niewielkie i może naprawdę nie mieć tutaj znaczenia, ale nie o to chodzi. To jest publiczne forum, z którego korzystają początkujący, aby uczyć się dobrych praktyk, więc musisz zapewnić im kodeks roli.
— Davos
1
Zapytanie zakończy się błędem, jeśli w jednej z baz danych używanych jest wiele plików danych. Oto wersja zapytania wykorzystująca zamiast tego sprzężenia. Twoje zdrowie! SELECT nazwa_db jako DBName, db.database_id, mfr.Physical_Name AS DataFile, mfl.Physical_Name AS LogFile FROM sys.databases db JOIN sys.master_files mfr ON db.database_id = mfr.database_id AND mfr.type_desc = 'ROWS' JOIN sys .master_files mfl ON db.database_id = mfl.database_id AND mfl.type_desc = 'LOG' ZAMÓWIENIE PRZEZ db.database_id
— Robert
2

Za pomocą tego skryptu możesz wyświetlić wszystkie nazwy baz danych i używane pliki (z wyjątkiem systemowych baz danych).

select name,physical_name from sys.master_files where database_id > 4
— Jorge Cribb
źródło
1

Ten skrypt zawiera listę większości tego, czego szukasz, i miejmy nadzieję, że może zostać zmodyfikowany do twoich potrzeb. Zauważ, że tworzy tam stałą tabelę - możesz chcieć to zmienić. Jest to podzbiór większego skryptu, który podsumowuje również informacje o kopiach zapasowych i zadaniach na różnych serwerach.

IF OBJECT_ID('tempdb..#DriveInfo') IS NOT NULL
 DROP TABLE #DriveInfo
CREATE TABLE #DriveInfo
 (
    Drive CHAR(1)
    ,MBFree INT
 ) 

INSERT  INTO #DriveInfo
      EXEC master..xp_fixeddrives


IF OBJECT_ID('[dbo].[Tmp_tblDatabaseInfo]', 'U') IS NOT NULL 
   DROP TABLE [dbo].[Tmp_tblDatabaseInfo]
CREATE TABLE [dbo].[Tmp_tblDatabaseInfo](
      [ServerName] [nvarchar](128) NULL
      ,[DBName] [nvarchar](128)  NULL
      ,[database_id] [int] NULL
      ,[create_date] datetime NULL
      ,[CompatibilityLevel] [int] NULL
      ,[collation_name] [nvarchar](128) NULL
      ,[state_desc] [nvarchar](60) NULL
      ,[recovery_model_desc] [nvarchar](60) NULL
      ,[DataFileLocations] [nvarchar](4000)
      ,[DataFilesMB] money null
      ,DataVolumeFreeSpaceMB INT NULL
      ,[LogFileLocations] [nvarchar](4000)
      ,[LogFilesMB] money null
      ,LogVolumeFreeSpaceMB INT NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Tmp_tblDatabaseInfo] 
SELECT 
      @@SERVERNAME AS [ServerName] 
      ,d.name AS DBName 
      ,d.database_id
      ,d.create_date
      ,d.compatibility_level  
      ,CAST(d.collation_name AS [nvarchar](128)) AS collation_name
      ,d.[state_desc]
      ,d.recovery_model_desc
      ,(select physical_name + ' | ' AS [text()]
         from sys.master_files m
         WHERE m.type = 0 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('')) AS DataFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 0 and m.database_id = d.database_id)  AS DataFilesMB
      ,NULL
      ,(select physical_name + ' | ' AS [text()]
         from sys.master_files m
         WHERE m.type = 1 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('')) AS LogFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 1 and m.database_id = d.database_id)  AS LogFilesMB
      ,NULL
FROM  sys.databases d  

WHERE d.database_id > 4 --Exclude basic system databases
UPDATE [dbo].[Tmp_tblDatabaseInfo] 
   SET DataFileLocations = 
      CASE WHEN LEN(DataFileLocations) > 4 THEN  LEFT(DataFileLocations,LEN(DataFileLocations)-2) ELSE NULL END
   ,LogFileLocations =
      CASE WHEN LEN(LogFileLocations) > 4 THEN  LEFT(LogFileLocations,LEN(LogFileLocations)-2) ELSE NULL END
   ,DataFilesMB = 
      CASE WHEN DataFilesMB > 0 THEN  DataFilesMB * 8 / 1024.0   ELSE NULL END
   ,LogFilesMB = 
      CASE WHEN LogFilesMB > 0 THEN  LogFilesMB * 8 / 1024.0  ELSE NULL END
   ,DataVolumeFreeSpaceMB = 
      (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( DataFileLocations,1))
   ,LogVolumeFreeSpaceMB = 
      (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( LogFileLocations,1))

select * from [dbo].[Tmp_tblDatabaseInfo]
— Gerard
źródło
Doskonały skrypt, który można kopiować / wklejać i używać tak, jak jest. Jednak jedno pytanie, widzę, LogVolumeFreeSpaceMBże zawsze pokazuje tę samą ilość dla wszystkich plików, w moim przypadku 44756. Czy można uzyskać faktyczne wolne miejsce? Czy może to liczba maksymalnego wolnego miejsca na dysku, na którym znajduje się LDF?
— Abel
Cześć Abel - Minęło trochę czasu odkąd napisałem i nie jestem pewien twojego problemu. Istnieje alternatywa dla XP_FIXEDDRIVES o nazwie sys.dm_os_volume_stats, więc możesz spróbować jej użyć. Jeśli problem polega na tym, że rozmiary plików bazy danych nie zmieniają się w czasie, jest to normalne, ponieważ SQL Server przydziela dużą porcję pustego miejsca na dysku do użytku, a następnie nie rozszerza tego pliku, dopóki nie zostanie zapełniony. Rozszerzy plik skokowo z kwotą określoną przez ustawienia pliku dla tej konkretnej bazy danych
— Gerard
0

Zapytanie zakończy się błędem, jeśli w jednej z baz danych używanych jest wiele plików danych (np. Plików typu „.ndf”).

Oto wersja zapytania wykorzystująca sprzężenia zamiast zapytań podrzędnych.

Twoje zdrowie!

SELECT
    db.name AS DBName,
    db.database_id,
    mfr.physical_name AS DataFile,
    mfl.physical_name AS LogFile
FROM sys.databases db
    JOIN sys.master_files mfr ON db.database_id=mfr.database_id AND mfr.type_desc='ROWS'
    JOIN sys.master_files mfl ON db.database_id=mfl.database_id AND mfl.type_desc='LOG'
ORDER BY db.database_id
— Robert
źródło
0

Poniższy skrypt może posłużyć do uzyskania następujących informacji: 1. Informacje o rozmiarze bazy danych 2. FileSpaceInfo 3. AutoGrowth 4. Model odzyskiwania 5. Informacje o log_reuse_backup

CREATE TABLE #tempFileInformation
(
DBNAME          NVARCHAR(256),
[FILENAME]      NVARCHAR(256),
[TYPE]          NVARCHAR(120),
FILEGROUPNAME   NVARCHAR(120),
FILE_LOCATION   NVARCHAR(500),
FILESIZE_MB     DECIMAL(10,2),
USEDSPACE_MB    DECIMAL(10,2),
FREESPACE_MB    DECIMAL(10,2),
AUTOGROW_STATUS NVARCHAR(100)
)
GO

DECLARE @SQL VARCHAR(2000)

SELECT @SQL = '
 USE [?]
            INSERT INTO #tempFileInformation
            SELECT  
                DBNAME          =DB_NAME(),     
                [FILENAME]      =A.NAME,
                [TYPE]          = A.TYPE_DESC,
                FILEGROUPNAME   = fg.name,
                FILE_LOCATION   =a.PHYSICAL_NAME,
                FILESIZE_MB     = CONVERT(DECIMAL(10,2),A.SIZE/128.0),
                USEDSPACE_MB    = CONVERT(DECIMAL(10,2),(A.SIZE/128.0 - ((A.SIZE - CAST(FILEPROPERTY(A.NAME,''SPACEUSED'') AS INT))/128.0))),
                FREESPACE_MB    = CONVERT(DECIMAL(10,2),(A.SIZE/128.0 -  CAST(FILEPROPERTY(A.NAME,''SPACEUSED'') AS INT)/128.0)),
                AUTOGROW_STATUS = ''BY '' +CASE is_percent_growth when 0 then cast (growth/128 as varchar(10))+ '' MB - ''
                                                                  when 1 then cast (growth as varchar(10)) + ''% - '' ELSE '''' END
                                                                  + CASE MAX_SIZE WHEN 0 THEN '' DISABLED '' 
                                                                                  WHEN -1 THEN '' UNRESTRICTED''
                                                                                  ELSE '' RESTRICTED TO '' + CAST(MAX_SIZE/(128*1024) AS VARCHAR(10)) + '' GB '' END
                                                                + CASE IS_PERCENT_GROWTH WHEn 1 then '' [autogrowth by percent]'' else '''' end
    from sys.database_files A
    left join sys.filegroups fg on a.data_space_id = fg.data_space_id
    order by A.type desc,A.name
    ;
    '

    --print @sql

    EXEC sp_MSforeachdb @SQL
    go

    SELECT dbSize.*,fg.*,d.log_reuse_wait_desc,d.recovery_model_desc
    FROM #tempFileInformation fg
    LEFT JOIN sys.databases d on fg.DBNAME = d.name
    CROSS APPLY
    (
        select dbname,
                sum(FILESIZE_MB) as [totalDBSize_MB],
                sum(FREESPACE_MB) as [DB_Free_Space_Size_MB],
                sum(USEDSPACE_MB) as [DB_Used_Space_Size_MB]
            from #tempFileInformation
            where  dbname = fg.dbname
            group by dbname
    )dbSize


go
DROP TABLE #tempFileInformation
— Vinod Narwal
źródło
0

po prostu dodając moje 2 centy.

jeśli konkretnie szukamy całkowitego wolnego miejsca tylko w plikach danych lub tylko w plikach dziennika we wszystkich bazach danych, możemy użyć kolumny "data_space_id". 1 jest dla plików danych, a 0 dla plików dziennika.

KOD:

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    spacetype sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];

Insert Into ##temp (DatabaseName, Name,spacetype, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,   ***data_space_id*** , physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2))/1024 as nvarchar) SizeGB,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2)/1024 as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2))/1024 as nvarchar) As FreeSpaceGB
    From sys.database_files'


select  
    databasename
    , sum(##temp.FreeSpace) 
from 
    ##temp 
where 
    ##temp.spacetype = 1  
group by 
    DatabaseName

drop table ##temp
— Raj Nair
źródło
0

Jeśli zmienisz nazwę bazy danych, MS SQL Server nie zmieni nazw plików źródłowych.

Poniższe zapytanie podaje aktualną nazwę bazy danych i nazwę pliku logicznego (która może być oryginalną nazwą bazy danych w momencie jej utworzenia), a także odpowiednie nazwy plików fizycznych.

Uwaga: Usuń komentarz do ostatniej linii, aby zobaczyć tylko rzeczywiste pliki danych

select  db.database_id, 
        db.name "Database Name", 
        files.name "Logical File Name",
        files.physical_name
from    sys.master_files files 
        join sys.databases db on db.database_id = files.database_id 
--                           and files.type_desc = 'ROWS'

Odniesienie:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15

— ssh
źródło
-3

Możesz skorzystać z poniższych:

SP_HELPDB [Master]
GO
— Iza
źródło
Zawiera tylko informacje o jednej określonej bazie danych. Pytanie dotyczy WSZYSTKICH baz danych.
— Thronk
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.