March 26, 2012 at 6:01 am
Hi,
I need to get all files space used information in a remote server database. To get this in a local server database i can do this:
select
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from
dbo.sysfiles a
But i need execute the same in the remote server database, similar to:
select
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from
REMOTE_SERVER.remote_database.dbo.sysfiles a
Somebody can say me any idea?
Many thanks in advance.
March 26, 2012 at 6:20 am
Do you have the remote server set up as a linked server? Your syntax won't work if you don't. Another thing you could consider is to use Integration Services. That would be more secure since it doesn't require the extra surface area that a linked server creates.
John
March 26, 2012 at 7:50 am
Yes i have defined as linked server, but the query:
select
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from REMOTE_SERVER.remote_database.dbo.sysfiles a
always return 'null' value for [SPACE_USED_MB].
The integration services solution you mentioned is not valid solution for me. I need to get this information from a stored procedure.
Somebody know how i can get with T-SQL the space used information in all files in remote database?
Many thanks.
March 26, 2012 at 7:56 am
Does the account that you are using to make the linked server connection have access to the database in question?
John
March 26, 2012 at 8:22 am
Yes, it have.
But it is not the problem. I think that my problem is relationed with FILEPROPERTY function not with db link.
This function sintax is: FILEPROPERTY ( file_name , property ) where 'file_name' is an expression that contains the name of the file associated with the current database for which to return property information. FILEPROPERTY run if i execute the query in local database:
use LOCALDATABASE
GO;
select
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from dbo.sysfiles a
I would like to get the same funtionality but in a remote database. May not be the FILEPROPERTY the solution for my problem, but i don't find any other options to solve my problem.
Any idea?
Many thanks.
March 26, 2012 at 8:26 am
Try this script, it should work nicely - you can run it as-is replacing the "LinkedServer" name with a real linked server
IF OBJECT_ID ('tempdb.dbo.#DatabaseSizes') IS NOT NULL
BEGIN
DROP TABLE dbo.#DatabaseSizes
END
DECLARE @DB sysname
DECLARE @SQL nvarchar(max)
CREATE TABLE dbo.#DatabaseSizes
(
database_name nvarchar(500)
,total_size_mb nvarchar(15)
,unallocated_mb nvarchar(15)
,reserved_mb nvarchar(15)
,data_mb nvarchar(15)
,index_mb nvarchar(15)
,unused_mb nvarchar(15)
)
SET @DB = (
SELECT TOP 1 DB.name
FROM [LinkedServer].[master].[sys].[databases] DB
WHERE DB.state = 0
AND DB.is_read_only = 0
AND DB.is_in_standby = 0
AND DB.source_database_id IS NULL
ORDER BY DB.name )
WHILE @DB IS NOT NULL
BEGIN
SET @SQL =
'INSERT INTO dbo.#DatabaseSizes
SELECT
'''+@DB+''' AS database_name
,REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, ROUND ((A.total_size * CONVERT (bigint, 8192))/1048576.0, 0)), 1)), 4, 15)) AS total_size_mb
,(CASE
WHEN A.database_size >= B.total_pages THEN REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, ROUND (((A.database_size - B.total_pages) * CONVERT (bigint, 8192))/1048576.0, 0)), 1)), 4, 15))
ELSE ''0''
END) AS unallocated_mb
,REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, ROUND ((B.total_pages * CONVERT (bigint, 8192))/1048576.0, 0)), 1)), 4, 15)) AS reserved_mb
,REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, ROUND ((B.pages * CONVERT (bigint, 8192))/1048576.0, 0)), 1)), 4, 15)) AS data_mb
,REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, ROUND (((B.used_pages - B.pages) * CONVERT (bigint, 8192))/1048576.0, 0)), 1)), 4, 15)) AS index_mb
,REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, ROUND (((B.total_pages - B.used_pages) * CONVERT (bigint, 8192))/1048576.0, 0)), 1)), 4, 15)) AS unused_mb
FROM (
SELECT
SUM (CASE
WHEN DBF.type = 0 THEN DBF.size
ELSE 0
END) AS database_size
,SUM (DBF.size) AS total_size
FROM [LinkedServer].'+@DB+'.[sys].[database_files] AS DBF
WHERE DBF.type IN (0,1)
) A
CROSS JOIN
(
SELECT
SUM (AU.total_pages) AS total_pages
,SUM (AU.used_pages) AS used_pages
,SUM (CASE
WHEN IT.internal_type IN (202,204) THEN 0
WHEN AU.type <> 1 THEN AU.used_pages
WHEN P.index_id <= 1 THEN AU.data_pages
ELSE 0
END) AS pages
FROM [LinkedServer].'+@DB+'.[sys].[partitions] P
INNER JOIN [LinkedServer].'+@DB+'.[sys].[allocation_units] AU
ON AU.container_id = P.partition_id
LEFT JOIN [LinkedServer].'+@DB+'.[sys].[internal_tables] IT
ON IT.[object_id] = P.[object_id]
) B
'
EXEC (@SQL)
SET @DB = ( SELECT TOP 1 DB.name
FROM [LinkedServer].[master].[sys].[databases] DB
WHERE DB.state = 0
AND DB.is_read_only = 0
AND DB.is_in_standby = 0
AND DB.source_database_id IS NULL
AND DB.name > @DB
ORDER BY DB.name)
END
SELECT * FROM dbo.#DatabaseSizes
IF OBJECT_ID ('tempdb.dbo.#DatabaseSizes') IS NOT NULL
BEGIN
DROP TABLE dbo.#DatabaseSizes
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 26, 2012 at 8:29 am
Yes, FILEPROPERTY. From Books Online: "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."
I was going to suggest you find some other way of calculating the space used. One option would have been to look at the code for sp_spaceused and see if you could roll your own solution. Looks like you've struck lucky, though!
John
March 26, 2012 at 10:31 am
First thank all,
MyDoggieJessie, your solution is a good aproximation for my needs, but is valid only if database have a FILEGROUP.
In my case i have remote databases with multiple FILEGROUPS and i need to get all space used in all files to get space used in FILEGROUP. I need it because i want to monitor the FILEGROUPS that are fulled. The FILEPROPERTY give this funtionality.
Somebody can say me how i can monitor the remote database FILEGROUP used space information?
Many thanks.
March 27, 2012 at 1:43 am
I'm sorry, but you've totally lost me. You complain that it only works if the database has a filegroup (which, incidentally, all databases do), and then you say you want to use it to monitor filegroups. The FILEPROPERY function does not give information about filegroups. It gives, as its name suggests, information about files. If you want summary information for filegroups, group by data_space_id in sys.database_files. If you need to know the names of those filegroups, join to sys.data_spaces.
Perhaps you could show us what result set you expect to see for a given database?
John
March 27, 2012 at 3:23 am
Hi John,
Sorry if I have not explained well.
What I really need is to monitor the FILEGROUP that are FULL in a remote database. To do it i sum the space used in all files created in each FILEGROUP and then compare with the sum of the total space they have (or can have).
The solution proposed by MyDoggieJessie returns a row for database. For example:
used_pages total_pages pages
-------------------------------
158 148 65
So I say that this solution only helps me if i have one (and only one) FILEGROUP. The same happens if I run 'sp_spaceused':
database_name database_size unallocated space
-----------------------------------------------------------------
REMOTE_DATABASE_NAME 50.00 MB 13.77 MB
index_size unused reserved dates
--------------------------------------------------
1264 KB 520 KB 664 KB 80 KB
'REMOTE_DATABASE_NAME' is a remote database consists of some files create on two filegroups (PRIMARY and DATA_FILEGROUP1 filegroups). If we execute the following query:
select fileproperty (name, 'SpaceUsed') space_used, name
from sys.database_files
We obtain the following result:
space_used name
------------------
184 FILE2
59 FILE2_log
8 FILE21
8 FILE3
8 FILE22
8 FILE4
Comment that FILE2,FILE21,FILE22 and FILE3 are create on PRIMARY FILEGROUP. FILE4 is created on DATA_FILEGROUP1 filegroup.
I need get the result equivalent to "FILEPROPERTY" to sum the 'space_used'. In the example, suppose that:
FILE2 -> has a maximum size of 184.
FILE21 -> has a maximum size of 8.
FILE3 -> has a maximum size of 8.
FILE22 -> has a maximum size of 8.
SO, the maximum size of PRIMARY FILEGROUP is 208.
The space used in the PRIMARY FILEGROUP is the sum of all spaces used for the files created in this filegroup: 184 + 8 + 8 + 8 = 208. Then we can say that PRIMARY FILEGROUP is FULL (space used is equal to filegroup max size).
This is the functionality that i need (for each FILEGROUP of the remote database).
Someone could tell me as I could get?
Thank you.
March 27, 2012 at 4:09 am
You need to forget about FILEPROPERTY because it only works for the current database. You can use sp_spaceused '[TableName]' to get the details for each individual table. That may be a bit messy because you'll end up with a separate result set for each table. What you can do, as I suggested earlier, is to examine the definition of sp_spaceused to find out which tables and views it pulls the metadata from, and then write your own query to return space data for each table in a single result set. You never know, if you take the last few words from that last sentence and search the internet, somebody may have done it before.
Edit: if you use Integration Services instead of remote servers, you should be able to use FILEPROPERTY.
John
March 27, 2012 at 10:10 am
Ok, thanks John.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply