Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers). I created view on client size, for all databases:

CREATE VIEW [dbo].[view] AS 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

and on server side I used loop to query this view. But column "DataSpaceUsedInMB" works only for default database for my login.

share|improve this question
    
how do you loop through the view? –  Travis Gan Jun 20 '13 at 15:12
    
I created view on all databases and then loop on central server through name of databases like SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];, but it not work –  Cicik Jun 21 '13 at 8:47
add comment

3 Answers

up vote 1 down vote accepted

@cicik, if you are looping through the databases at your local server like you mentioned, assuming you already have the list of remote databases stored somewhere in a local table, you could loop through the databases (e.g. cursor) and execute,

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

This should put the remote database context at the database you specified, and hence it works for FILEPROPERTY function and sys.database_files dmv that return values only for the current database.

Also, you might want to consider extracting the list of databases on the remote server and run the script on each execution at the remote server (client side) so you won't have to create the view on each database, and won't miss any new databases created without your knowledge.

UPDATE If you want to use script to dynamically extract the data from the linked server without adding the view on every database,

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];

Make sure your login has the access to all the databases on the remote server.

share|improve this answer
    
no, I tried this but it not work –  Cicik Jun 21 '13 at 8:10
    
@Cicik I tested on my test machine and it works even for a login with public role and select permission on dbo.[view]. What is it that doesn't work? Does it returns any result? –  Travis Gan Jun 21 '13 at 14:21
    
@Cicik I have added some script to extract the information dynamically without having to create the view in every database. –  Travis Gan Jun 21 '13 at 15:25
    
Really thanks for advice, it help me solve my problem. –  Cicik Jun 24 '13 at 13:26
add comment

From BOL on FILEPROPERTY: "Returns the specified file name property value when a file name in the current database and a property name are specified. Returns NULL for files that are not in the current database."

You will have to run your query under the context of each database in order to find the SpaceUsed. You could do something like this:

First create a table to store the data (or a temp table if you'd like):

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

Then you will have to populate this table with the data (I'm going to use sp_msforeachdb for simplicity. You could also use a 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

Then you could repopulate the table how ever often you like. You should be able to query this table through a linked server connection to find the data you are looking for.

share|improve this answer
2  
msforeachdb is UNRELIABLE and undocumented as it can skip databases. See Aaron Bertrand post sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/… –  Kin Jun 20 '13 at 15:37
    
see comment above, problem is linked server. –  Cicik Jun 21 '13 at 8:23
add comment

But column "DataSpaceUsedInMB" works only for default database for my login.

You have to use : sys.master_files :

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

You are using sys.database_files :

Contains a row per file of a database as stored in the database itself. This is a per-database view.

So, SELECT * FROM sys.master_files will list the files for each database in the instance whereas SELECT * FROM sys.database_files will list the files for the specific database context.

Note: If you're not seeing rows from sys.master_files, it could be a permissions issue as BOL states:

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

Whereas for sys.database_files just requires membership in the public role.

Update : As highlighted, you can use your existing script with a way to loop all the databases and store that result in a table and then create a view (if you want).

You can use below script :

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

UPDATE (6/21/2013) :

this works on local, but NOT through LINKED SERVER, and that is core of my my problem. as @TravisGan mentioned problem is FILEPROPERTY function to find used space, because it works only for default database when connecting linked server. Anyway I have no idea how to execute this script through linked server. Is there any other way to find space in database file?

IF you want to execute query on multiple servers, then Powershell or SQLCMD is your best option.

Using linked servers, you have to use OPENROWSET or OPENQUERY.

IF you just have couple of servers then creating a linked server to the main will work fine as below :

Note : SQL2005 is a linked server.

 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

enter image description here

share|improve this answer
    
I initially thought that, but the problem is his use of FILEPROPERTY function that only return the current database. So it won't work even to replace with sys.master_files –  Travis Gan Jun 20 '13 at 15:12
    
@TravisGan the OP can use a while loop or undocumented ms_foreachDB or Aarons version of that as well. –  Kin Jun 20 '13 at 15:29
    
Right, if he is looping the database that way, sys.database_files should work as well. –  Travis Gan Jun 20 '13 at 15:33
    
@TravisGan Updated the my answer with sys.database_files + a loop and cursor. Thanks –  Kin Jun 20 '13 at 15:39
    
this works on local, but NOT through LINKED SERVER, and that is core of my my problem. as @TravisGan mentioned problem is FILEPROPERTY function to find used space, because it works only for default database when connecting linked server. Anyway I have no idea how to execute this script through linked server. Is there any other way to find space in database file? –  Cicik Jun 21 '13 at 8:21
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.