April 4, 2012 at 1:51 am
I was taking this view as base to calculate total space and available space for both data files and log files. Now it seems it gives different results in different database contexts, thus raising questions to my solution.
select SUM((b.size/128.0-CAST(FILEPROPERTY(b.name,'SpaceUsed') AS INT)/128.0)) as from Database1.sys.database_files b WHERE b.type = 0
The above query i am using to checking data available space in data file of database Database1
but when same query i use in different database like this:
use Database2
select SUM((b.size/128.0-CAST(FILEPROPERTY(b.name,'SpaceUsed') AS INT)/128.0)) as from Database1.sys.database_files b WHERE b.type = 0
It gives me different value.
Am i doing it right way? Is there any other query to get total space available to data file and log file as well as used space in both data file as well as log file.
April 4, 2012 at 9:47 am
It is because you are still referencing the view in Database1 by fully qualifying it. If you want the USE keyword to do anything, you have to change Database1.sys.database_files to sys.database_files.
Jared
CE - Microsoft
April 4, 2012 at 9:58 am
You can get the same information by using the sys.master_files which returns data for ALL database and ALL files regardless of the database context you are working in. So to get the information for ONLY the database you are in you would use:
Select * from sys.master_files where database_id = DB_ID();
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2012 at 3:16 am
SQLKnowItAll (4/4/2012)
It is because you are still referencing the view in Database1 by fully qualifying it. If you want the USE keyword to do anything, you have to change Database1.sys.database_files to sys.database_files.
What I am want to know is why it is giving different results when I check the details for same database Database1 from Database1 and Database2 ? It is reflecting different results for FILEPROPERTY(name,'SpaceUsed') with Database1.sys.database_files in both Database1 and Database2.
If you should run the query in same database to get this result, then what do the values reflect which are returned by executing this query from different database context ?
April 5, 2012 at 5:22 am
sqlnaive (4/5/2012)
SQLKnowItAll (4/4/2012)
It is because you are still referencing the view in Database1 by fully qualifying it. If you want the USE keyword to do anything, you have to change Database1.sys.database_files to sys.database_files.What I am want to know is why it is giving different results when I check the details for same database Database1 from Database1 and Database2 ? It is reflecting different results for FILEPROPERTY(name,'SpaceUsed') with Database1.sys.database_files in both Database1 and Database2.
If you should run the query in same database to get this result, then what do the values reflect which are returned by executing this query from different database context ?
You are not querying for different databases!!!
USE DB1
SELECT * FROM DB1.dbo.Table1
Is the same as
USE DB2
SELECT * FROM DB1.dbo.Table1
because of the DB1.dbo.Table1... It does not matter which database you "USE" because you are still referencing the table DB1 explicitly in the FROM clause. If you changed that query to:
USE DB1
SELECT * FROM dbo.Table1
Is NOT the same as
USE DB2
SELECT * FROM dbo.Table1
because the tables are not fully qualified in the FROM clause and they are referencing the database contained in the scope created by the USE command.
Jared
CE - Microsoft
April 5, 2012 at 7:21 am
Exactly and thats what my point is...
USE DB1
SELECT * FROM DB1.dbo.Table1
and
USE DB2
SELECT * FROM DB1.dbo.Table1
gives same result [as they both refer to smae table in DB1]. Same way the following should give same result:
use Database1
GO
select SUM((b.size/128.0-CAST(FILEPROPERTY(b.name,'SpaceUsed') AS INT)/128.0)) as from Database1.sys.database_files b WHERE b.type = 0 -- First Query
use Database2
GO
select SUM((b.size/128.0-CAST(FILEPROPERTY(b.name,'SpaceUsed') AS INT)/128.0)) as from Database1.sys.database_files b WHERE b.type = 0 -- Second Query
However this is not giving same result.
April 5, 2012 at 7:28 am
sqlnaive (4/5/2012)
Exactly and thats what my point is...USE DB1
SELECT * FROM DB1.dbo.Table1
and
USE DB2
SELECT * FROM DB1.dbo.Table1
gives same result [as they both refer to smae table in DB1]. Same way the following should give same result:
use Database1
GO
select SUM((b.size/128.0-CAST(FILEPROPERTY(b.name,'SpaceUsed') AS INT)/128.0)) as from Database1.sys.database_files b WHERE b.type = 0 -- First Query
use Database2
GO
select SUM((b.size/128.0-CAST(FILEPROPERTY(b.name,'SpaceUsed') AS INT)/128.0)) as from Database1.sys.database_files b WHERE b.type = 0 -- Second Query
However this is not giving same result.
Are you getting NULL for the second result?
Jared
CE - Microsoft
April 5, 2012 at 7:38 am
the issue is with FILEPROPERTY, its specific to the database context its running in
From BOL
file_name
Is an expression that contains the name of the file associated with the current database for which to return property information. file_name is nchar(128).
so this works
use db1
go
select fileproperty(name, 'SpaceUsed') from db1.sys.database_files
go
this doesnt as your not passing a name of DB2 into fileproperty
use db2
go
select fileproperty(name, 'SpaceUsed') from db1.sys.database_files
go
April 5, 2012 at 7:40 am
anthony.green (4/5/2012)
the issue is with FILEPROPERTY, its specific to the database context its running inFrom BOL
file_name
Is an expression that contains the name of the file associated with the current database for which to return property information. file_name is nchar(128).
so this works
use db1
go
select fileproperty(name, 'SpaceUsed') from db1.sys.database_files
go
this doesnt as your not passing a name of DB2 into fileproperty
use db2
go
select fileproperty(name, 'SpaceUsed') from db1.sys.database_files
go
+1
Jared
CE - Microsoft
April 6, 2012 at 2:58 am
Thanks Anthony for pointing that out. I closely checked and founf that there were few files having same name for both databases and that's why it was giving some value which was not correct. Now I understood it and will remember forever.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply