filestats

Hi ,
In 2k there is dbcc command dbcc showfilestats to see used & free extents in
filegroup , what is equivalent in Yukon or shall we continue using the same
..
Thanks
ARR
check out sys.dm.db.index_physical_stats in BooksOnLine.
Andrew J. Kelly SQL MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> Hi ,
> In 2k there is dbcc command dbcc showfilestats to see used & free extents
> in
> filegroup , what is equivalent in Yukon or shall we continue using the
> same
> .
> Thanks
> ARR
>
|||sys.dm_db_index_physical_stats does not report per file does it? Only per
object.
select fileproperty('filename', 'SpaceUsed') reports the amount of space
allocated but not free.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> check out sys.dm.db.index_physical_stats in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>
|||Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent of
dbcc showfilestats column UsedExtents * 8
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
|||And the equivalent of TotalExtents can be found in: select size from
sys.database_files or select size from sys.master_files
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
> of dbcc showfilestats column UsedExtents * 8
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>
|||Sorry I was thinking showcontig<g>.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
|||Last one to wrap this up, this would be the ultimate Yukon way of retrieving
the data today you get from showfilestats (which will remains an
undocumented DBCC command in Yukon as well).
select 'FileId' = file_id,
'FileGroup' = data_space_id,
'TotalExtents' = size/8,
'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
'Name' = name,
'FileName' = physical_name
from sys.database_files
where type = 0
and state = 0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> And the equivalent of TotalExtents can be found in: select size from
> sys.database_files or select size from sys.master_files
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks a lot .
This helped me alot .
Thanks
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:#Jr$nhvAFHA.4044@.TK2MSFTNGP14.phx.gbl...
> Last one to wrap this up, this would be the ultimate Yukon way of
retrieving
> the data today you get from showfilestats (which will remains an
> undocumented DBCC command in Yukon as well).
> select 'FileId' = file_id,
> 'FileGroup' = data_space_id,
> 'TotalExtents' = size/8,
> 'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
> 'Name' = name,
> 'FileName' = physical_name
> from sys.database_files
> where type = 0
> and state = 0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
equivalent[vbcol=seagreen]
space[vbcol=seagreen]
the
>

filestats

Hi ,
In 2k there is dbcc command dbcc showfilestats to see used & free extents in
filegroup , what is equivalent in Yukon or shall we continue using the same
.
Thanks
ARRcheck out sys.dm.db.index_physical_stats in BooksOnLine.
Andrew J. Kelly SQL MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> Hi ,
> In 2k there is dbcc command dbcc showfilestats to see used & free extents
> in
> filegroup , what is equivalent in Yukon or shall we continue using the
> same
> .
> Thanks
> ARR
>|||sys.dm_db_index_physical_stats does not report per file does it? Only per
object.
select fileproperty('filename', 'SpaceUsed') reports the amount of space
allocated but not free.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> check out sys.dm.db.index_physical_stats in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>|||Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent of
dbcc showfilestats column UsedExtents * 8
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>|||And the equivalent of TotalExtents can be found in: select size from
sys.database_files or select size from sys.master_files
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
> of dbcc showfilestats column UsedExtents * 8
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>|||Sorry I was thinking showcontig<g>.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>|||Last one to wrap this up, this would be the ultimate Yukon way of retrieving
the data today you get from showfilestats (which will remains an
undocumented DBCC command in Yukon as well).
select 'FileId' = file_id,
'FileGroup' = data_space_id,
'TotalExtents' = size/8,
'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
'Name' = name,
'FileName' = physical_name
from sys.database_files
where type = 0
and state = 0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> And the equivalent of TotalExtents can be found in: select size from
> sys.database_files or select size from sys.master_files
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
>|||Thanks a lot .
This helped me alot .
Thanks
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:#Jr$nhvAFHA.4044@.TK2MSFTNGP14.phx.gbl...
> Last one to wrap this up, this would be the ultimate Yukon way of
retrieving
> the data today you get from showfilestats (which will remains an
> undocumented DBCC command in Yukon as well).
> select 'FileId' = file_id,
> 'FileGroup' = data_space_id,
> 'TotalExtents' = size/8,
> 'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
> 'Name' = name,
> 'FileName' = physical_name
> from sys.database_files
> where type = 0
> and state = 0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
equivalent[vbcol=seagreen]
space[vbcol=seagreen]
the[vbcol=seagreen]
>