Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped
to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/pr...oads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodin...ions/books.mspx
========================================
==========
"Prasad" <ekke_nikhil@.yahoo.co.uk> bl
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by
connecting through the
dedicated administrator connection (DAC), looking at the source code from th
e view sys.databases and
then do a select directly against the physical system table that the view sy
s.databases uses.
I don't know what you want to achieve in the end, but best bet is probably t
o disregard objects that
lives in the database with id 32767.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005
, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of
the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea
to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databas
es ?
> I also wanted information as to how do I query the system tables, it giv
es me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Some tables are flagged as user tables even though they are shipped with the
product. The backup
history tables are such examples.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.g
bl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing a
s told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a
System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>sql

Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
--
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
=================================================="Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and
then do a select directly against the physical system table that the view sys.databases uses.
I don't know what you want to achieve in the end, but best bet is probably to disregard objects that
lives in the database with id 32767.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databases ?
> I also wanted information as to how do I query the system tables, it gives me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing as told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
>> any database.
>> - Breaking changes to database engine features in SQL Server 2005
>> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
>> [2] To query a system table, you will need to prefix the table with the
>> appropriate database that you are going to query. E.g.,
>> SELECT * FROM msdb..backupfile
>> - Querying the SQL Server System Catalog
>> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
>> - Querying the SQL Server System Catalog FAQ
>> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
>> --
>> Martin Poon
>> Microsoft MVP - SQL Server
>> ----
>> - SQL Server 2005 books online
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> - SQL Server 2000 books online
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> ==================================================>> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
>> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
>> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
>> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs to
>> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>>
>

Database ID

I am new to DMVs and DMFs. If I run
select * from sys.dm_db_index_usage_stats
How do I know which database relates to the database_Id? likewise the
Object_Id and Index_ID? Is there a lookup table somewhere I could join on?
thanks
Adam,
you could use something like this:
use yourdatabasename -- (the database you are interested in examining)
go
select db_name(ius.database_id) as DatabaseName,
object_name(ius.object_id) as ObjectName,
sysind.name as IndexName
from sys.dm_db_index_usage_stats ius
inner join sys.indexes sysind on ius.index_id = sysind.index_id
For the moment it'll work fine, but you must be in the correct database for
the object_name function to return a valid name, hence the USE statement.
After SP2 has been released you'll be able to incorporate the database into
the object_name function to ensure you don't nee a use statement at the top.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Database ID

I am new to DMVs and DMFs. If I run
select * from sys.dm_db_index_usage_stats
How do I know which database relates to the database_Id? likewise the
Object_Id and Index_ID? Is there a lookup table somewhere I could join on?
thanksAdam,
you could use something like this:
use yourdatabasename -- (the database you are interested in examining)
go
select db_name(ius.database_id) as DatabaseName,
object_name(ius.object_id) as ObjectName,
sysind.name as IndexName
from sys.dm_db_index_usage_stats ius
inner join sys.indexes sysind on ius.index_id = sysind.index_id
For the moment it'll work fine, but you must be in the correct database for
the object_name function to return a valid name, hence the USE statement.
After SP2 has been released you'll be able to incorporate the database into
the object_name function to ensure you don't nee a use statement at the top.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Database ID

I am new to DMVs and DMFs. If I run
select * from sys.dm_db_index_usage_stats
How do I know which database relates to the database_Id? likewise the
Object_Id and Index_ID? Is there a lookup table somewhere I could join on?
thanksAdam,
you could use something like this:
use yourdatabasename -- (the database you are interested in examining)
go
select db_name(ius.database_id) as DatabaseName,
object_name(ius.object_id) as ObjectName,
sysind.name as IndexName
from sys.dm_db_index_usage_stats ius
inner join sys.indexes sysind on ius.index_id = sysind.index_id
For the moment it'll work fine, but you must be in the correct database for
the object_name function to return a valid name, hence the USE statement.
After SP2 has been released you'll be able to incorporate the database into
the object_name function to ensure you don't nee a use statement at the top.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;