Espaço usado pelo arquivo de banco de dados por meio do servidor vinculado

@cicik, se você estiver percorrendo os bancos de dados em seu servidor local como você mencionou, supondo que você já tenha a lista de bancos de dados remotos armazenados em algum lugar em uma tabela local, você pode percorrer os bancos de dados (por exemplo, cursor) e executar,

SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];

Isso deve colocar o contexto do banco de dados remoto no banco de dados especificado e, portanto, funciona para a função FILEPROPERTY e sys.database_files dmv que retornam valores apenas para o banco de dados atual.

Além disso, você pode querer considerar extrair a lista de bancos de dados no servidor remoto e executar o script em cada execução no servidor remoto (lado do cliente) para não precisar criar a exibição em cada banco de dados e não perder quaisquer novos bancos de dados criados sem o seu conhecimento.

ATUALIZAÇÃO Se você quiser usar o script para extrair dinamicamente os dados do servidor vinculado sem adicionar a exibição em todos os bancos de dados,

DECLARE @remotecmd nvarchar(max);

SET @remotecmd = N'
DECLARE @db     sysname,
        @cmd    nvarchar(max);

CREATE TABLE #dbspace 
(
    DataFileName nvarchar(128),
    DataPhysicalName nvarchar(260),
    DataTotalSizeInMB decimal(17,6),
    DataSpaceUsedInMB decimal(17,6),
    DataAvailableSpaceInMB decimal(17,6)
);

DECLARE dbcur CURSOR
LOCAL FAST_FORWARD
FOR SELECT name
    FROM sys.databases
    WHERE [state] = 0;

OPEN dbcur;
FETCH NEXT FROM dbcur INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = N''USE '' + QUOTENAME(@db) + N''; '';
SET @cmd = @cmd + 
N''
INSERT INTO #dbspace
SELECT 
    name AS [DataFileName], 
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB], 
    ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataSpaceUsedInMB], 
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB] 
FROM  [sys].[database_files] WHERE type = 0'';

EXEC (@cmd);

FETCH NEXT FROM dbcur INTO @db;
END

CLOSE dbcur;
DEALLOCATE dbcur;

SELECT 
    DataFileName,
    DataPhysicalName,
    DataTotalSizeInMB,
    DataSpaceUsedInMB,
    DataAvailableSpaceInMB
FROM #dbspace;

DROP TABLE #dbspace;'

EXEC (@remotecmd) AT [YourLinkedServer];

Certifique-se de que seu login tenha acesso a todos os bancos de dados no servidor remoto.

Mas a coluna "DataSpaceUsedInMB" funciona apenas para o banco de dados padrão para meu login.

Você tem que usar : sys.master_files :

Contém uma linha por arquivo de um banco de dados conforme armazenado no banco de dados mestre. Esta é uma visão única de todo o sistema.

Você está usando sys.database_files :

Contém uma linha por arquivo de um banco de dados conforme armazenado no próprio banco de dados. Esta é uma exibição por banco de dados.

Portanto, SELECT * FROM sys.master_fileslistará os arquivos para cada banco de dados na instância, enquanto SELECT * FROM sys.database_fileslistará os arquivos para o contexto de banco de dados específico.

Observação: se você não estiver vendo as linhas de sys.master_files, pode ser um problema de permissão, pois o BOL afirma:

As permissões mínimas necessárias para ver a linha correspondente são CREATE DATABASE, ALTER ANY DATABASE ou VIEW ANY DEFINITION.

Considerando que para sys.database_files requer apenas associação na função pública.

Atualização: conforme destacado, você pode usar seu script existente com uma maneira de fazer um loop em todos os bancos de dados e armazenar o resultado em uma tabela e, em seguida, criar uma exibição (se desejar).

Você pode usar o script abaixo:

DECLARE
    @SqlStatement nvarchar(MAX)
    ,@DatabaseName sysname;


CREATE TABLE DatabaseSpace(
    DATABASE_NAME   sysname
    ,LOGICAL_NAME   sysname
    ,FILE_SIZE_MB   decimal(12, 2)
    ,SPACE_USED_MB  decimal(12, 2)
    ,FREE_SPACE_MB  decimal(12, 2)
    ,FILE_NAME      sysname
    );

DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases;

OPEN DatabaseList;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @SqlStatement = N'USE '
        + QUOTENAME(@DatabaseName)
        + CHAR(13)+ CHAR(10)
        + N'INSERT INTO DatabaseSpace
    SELECT
        [DATABASE_NAME] = DB_NAME()
        ,[LOGICAL_NAME] = f.name
        ,[FILE_SIZE_MB] = CONVERT(decimal(12,2),round(f.size/128.000,2))
        ,[SPACE_USED_MB] = CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))
        ,[FREE_SPACE_MB] = CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))
        ,[FILENAME] = f.name
    FROM sys.database_files f;';

    EXECUTE(@SqlStatement);

END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

SELECT * FROM DatabaseSpace;

--DROP TABLE DatabaseSpace;;
--GO

ATUALIZAÇÃO (21/06/2013):

isso funciona no local, mas NÃO através do LINKED SERVER, e esse é o cerne do meu problema. como @TravisGan mencionou, o problema é a função FILEPROPERTY para encontrar o espaço usado, porque funciona apenas para o banco de dados padrão ao conectar o servidor vinculado. De qualquer forma, não tenho ideia de como executar esse script por meio do servidor vinculado. Existe alguma outra maneira de encontrar espaço no arquivo de banco de dados?

SE você deseja executar a consulta em vários servidores, o Powershell ou SQLCMD é sua melhor opção.

Usando servidores vinculados, você deve usar OPENROWSET ou OPENQUERY .

SE você tiver apenas alguns servidores, criar um servidor vinculado ao principal funcionará bem, conforme abaixo:

Nota: SQL2005 é um servidor vinculado.

 create view test_view as    
SELECT      db.name AS DatabaseName,
            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
            SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
            SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
FROM        SQL2005.master.dbo.sysdatabases AS db
INNER JOIN  SQL2005.master.dbo.sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE       db.name NOT IN('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb')      
GROUP BY    db.name

select * from test_view

De BOL em FILEPROPERTY: "Retorna o valor da propriedade de nome de arquivo especificado quando um nome de arquivo no banco de dados atual e um nome de propriedade são especificados. Retorna NULL para arquivos que não estão no banco de dados atual."

Você terá que executar sua consulta no contexto de cada banco de dados para encontrar o SpaceUsed. Você poderia fazer algo assim:

Primeiro crie uma tabela para armazenar os dados (ou uma tabela temporária, se desejar):

IF EXISTS (
    SELECT *
    FROM sys.tables
    WHERE NAME LIKE '%dbsize%'
    )
DROP TABLE dbsize

CREATE TABLE dbsize (
 DataFileName VARCHAR(50)
,DataPhysicalName VARCHAR(500)
,DataTotalSizeInMB INT
,DataSpaceUsedInMB DECIMAL(30, 2)
,DataAvailableSpaceInMB DECIMAL(30, 2)
)
GO

Então você terá que preencher esta tabela com os dados (vou usar sp_msforeachdb para simplificar. Você também pode usar um cursor).

INSERT INTO dbsize (
DataFileName
,DataPhysicalName
,DataTotalSizeInMB
,DataSpaceUsedInMB
,DataAvailableSpaceInMB
)
EXEC sp_msforeachdb 'use [?]; 

SELECT name AS ''DataFileName'', 
physical_name AS ''DataPhysicalName'',
ROUND(size / 128, 1) AS ''DataTotalSizeInMB'', 
ROUND(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS 
''DataSpaceUsedInMB'', 
ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS 
''DataAvailableSpaceInMB'' FROM  [sys].[database_files] WHERE type = 0'

SELECT *
FROM dbsize

Então você pode repovoar a tabela quantas vezes quiser. Você deve ser capaz de consultar esta tabela por meio de uma conexão de servidor vinculada para localizar os dados que está procurando.