Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||for data files look at
dbcc showfilestats
This is undocumented dbcc command.
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
For log files
look at
dbcc sqlperf(logspace)
Regards
Amish shah
http://shahamishm.tripod.com
*** Sent via Developersdex http://www.codecomments.com ***|||Uri Dimant schrieb:
> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you[vbcol=seagreen]
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...|||Amish Shah schrieb:
> for data files look at
> dbcc showfilestats
> This is undocumented dbcc command.
> It shows total size and used size in extents.
> If you multiply it by 64 you will get size in KB.
> For log files
> look at
> dbcc sqlperf(logspace)
>
> Regards
> Amish shah
> http://shahamishm.tripod.com
> *** Sent via Developersdex http://www.codecomments.com ***
Thank you Amish,
that really does the job but how do i get this information into a
table?
like:
select into table ( dbcc showfilestat )
thats what i would like for capacity planning and charting.
Thank you very much, Ulli|||Hi Ulli
You have to create your own table. By looking at the output of dbcc
showfilestats you can determine the number of columns and an appropriate
datatype.
Once you have the table created, you can do the following:
INSERT INT MyFileStatsTable
EXEC ('DBCC showfilestats')
HTH
Kalen Delaney, SQL Server MVP
<ulli77@.web.de> wrote in message
news:1154272978.472934.27330@.b28g2000cwb.googlegroups.com...
> Amish Shah schrieb:
>
>
> Thank you Amish,
> that really does the job but how do i get this information into a
> table?
> like:
> select into table ( dbcc showfilestat )
> thats what i would like for capacity planning and charting.
> Thank you very much, Ulli
>
Showing posts with label datafiles. Show all posts
Showing posts with label datafiles. Show all posts
Wednesday, March 28, 2012
How to determine the free space in a datafile
Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||Uri Dimant schrieb:
> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I'm trying to figure out the amount of free space in the datafiles but
> > since sys.dm_db_file_space_usage works only for temp, I dont know which
> > table to join on sys.database_files or sys.master_files.
> >
> > Can anyone help me please?
> >sql
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||Uri Dimant schrieb:
> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I'm trying to figure out the amount of free space in the datafiles but
> > since sys.dm_db_file_space_usage works only for temp, I dont know which
> > table to join on sys.database_files or sys.master_files.
> >
> > Can anyone help me please?
> >sql
Monday, March 26, 2012
How to determine programatically what datafiles are associated with a database
I'm writing a data collection script to gather database and data file
information. This information will be used for reporting and analysis. I
want to be able to tell what data files are associated with each of the
databases. sysfiles doesn't include the database name and sysaltfiles
doesn't include all of the datafiles.
Query I'm using:
use master
select f.file_name, f.name, ...
from master.dbo.sysaltfiles a ,
dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid
and db.name = 'master'
I can probably infer the database name from the name column but I'd rather
not go there!
Please don't tell me to go somewhere and point and click. I'm monitoring
almost 70 databases and this data needs to be collected on a daily basis.
Message posted via http://www.webservertalk.comevelyn,
use sp_msforeachdb:
exec sp_msforeachdb '
select db.name, f.name
from master.dbo.sysaltfiles a ,
?.dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid '
hth
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eebc4237a7204ec3a8b8ca5fab4890a5@.SQ
webservertalk.com...
> I'm writing a data collection script to gather database and data file
> information. This information will be used for reporting and analysis. I
> want to be able to tell what data files are associated with each of the
> databases. sysfiles doesn't include the database name and sysaltfiles
> doesn't include all of the datafiles.
> Query I'm using:
> use master
> select f.file_name, f.name, ...
> from master.dbo.sysaltfiles a ,
> dbo.sysfiles f ,
> master.dbo.sysdatabases db
> where a.name=f.name
> and a.fileid = f.fileid
> and a.dbid = db.dbid
> and db.name = 'master'
> I can probably infer the database name from the name column but I'd rather
> not go there!
> Please don't tell me to go somewhere and point and click. I'm monitoring
> almost 70 databases and this data needs to be collected on a daily basis.
> --
> Message posted via http://www.webservertalk.com|||Try this:
exec sp_MSforeachdb
'
use [?]
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
'|||For this query add brackets around the question mark. i.e. [?]|||What does sp_msforeachdb do? It is not in my online book. I hesitate to
run something in 70 production databases without knowing the possible
ramifications.
Message posted via http://www.webservertalk.com|||I figured out what the procedure does.
Perhaps I'm not clear I want the database name and all associated data file
names. The following query gives me table names.
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
Message posted via http://www.webservertalk.com|||it is undocumented and unsupported, meaning that ms might choose not to
include it in future versions of sql server. however, it is widely used. you
can check the definition (in master db) with:
exec sp_helptext 'sp_msforeachdb'
nothing fancy (builds a cursor internallly), but very handy.
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f3affb3bea874a3fbf0edb3bcc8b8868@.SQ
webservertalk.com...
> What does sp_msforeachdb do? It is not in my online book. I hesitate to
> run something in 70 production databases without knowing the possible
> ramifications.
> --
> Message posted via http://www.webservertalk.com|||Hi
The database may have two or more log files. You also need to see them
Look at this script helps you.
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
fileid < (SELECT MAX(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename) FROM sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:960a4cbd69a241458a0b54a9fd0b14ea@.SQ
webservertalk.com...
> I figured out what the procedure does.
> Perhaps I'm not clear I want the database name and all associated data
file
> names. The following query gives me table names.
> select so.name as Table_Name,
> sfg.groupname as GroupName,
> sf.name as LogicalName,
> sf.filename as PhysicalFileName
> from dbo.sysobjects so
> inner join sysindexes si
> on so.id = si.id
> inner join sysfilegroups sfg
> on si.groupid = sfg.groupid
> inner join sysfiles sf
> on sf.groupid = sfg.groupid
> group by so.name,
> sfg.groupname,
> sf.name,
> sf.filename
> --
> Message posted via http://www.webservertalk.com
information. This information will be used for reporting and analysis. I
want to be able to tell what data files are associated with each of the
databases. sysfiles doesn't include the database name and sysaltfiles
doesn't include all of the datafiles.
Query I'm using:
use master
select f.file_name, f.name, ...
from master.dbo.sysaltfiles a ,
dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid
and db.name = 'master'
I can probably infer the database name from the name column but I'd rather
not go there!
Please don't tell me to go somewhere and point and click. I'm monitoring
almost 70 databases and this data needs to be collected on a daily basis.
Message posted via http://www.webservertalk.comevelyn,
use sp_msforeachdb:
exec sp_msforeachdb '
select db.name, f.name
from master.dbo.sysaltfiles a ,
?.dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid '
hth
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eebc4237a7204ec3a8b8ca5fab4890a5@.SQ
webservertalk.com...
> I'm writing a data collection script to gather database and data file
> information. This information will be used for reporting and analysis. I
> want to be able to tell what data files are associated with each of the
> databases. sysfiles doesn't include the database name and sysaltfiles
> doesn't include all of the datafiles.
> Query I'm using:
> use master
> select f.file_name, f.name, ...
> from master.dbo.sysaltfiles a ,
> dbo.sysfiles f ,
> master.dbo.sysdatabases db
> where a.name=f.name
> and a.fileid = f.fileid
> and a.dbid = db.dbid
> and db.name = 'master'
> I can probably infer the database name from the name column but I'd rather
> not go there!
> Please don't tell me to go somewhere and point and click. I'm monitoring
> almost 70 databases and this data needs to be collected on a daily basis.
> --
> Message posted via http://www.webservertalk.com|||Try this:
exec sp_MSforeachdb
'
use [?]
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
'|||For this query add brackets around the question mark. i.e. [?]|||What does sp_msforeachdb do? It is not in my online book. I hesitate to
run something in 70 production databases without knowing the possible
ramifications.
Message posted via http://www.webservertalk.com|||I figured out what the procedure does.
Perhaps I'm not clear I want the database name and all associated data file
names. The following query gives me table names.
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
Message posted via http://www.webservertalk.com|||it is undocumented and unsupported, meaning that ms might choose not to
include it in future versions of sql server. however, it is widely used. you
can check the definition (in master db) with:
exec sp_helptext 'sp_msforeachdb'
nothing fancy (builds a cursor internallly), but very handy.
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f3affb3bea874a3fbf0edb3bcc8b8868@.SQ
webservertalk.com...
> What does sp_msforeachdb do? It is not in my online book. I hesitate to
> run something in 70 production databases without knowing the possible
> ramifications.
> --
> Message posted via http://www.webservertalk.com|||Hi
The database may have two or more log files. You also need to see them
Look at this script helps you.
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
fileid < (SELECT MAX(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename) FROM sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:960a4cbd69a241458a0b54a9fd0b14ea@.SQ
webservertalk.com...
> I figured out what the procedure does.
> Perhaps I'm not clear I want the database name and all associated data
file
> names. The following query gives me table names.
> select so.name as Table_Name,
> sfg.groupname as GroupName,
> sf.name as LogicalName,
> sf.filename as PhysicalFileName
> from dbo.sysobjects so
> inner join sysindexes si
> on so.id = si.id
> inner join sysfilegroups sfg
> on si.groupid = sfg.groupid
> inner join sysfiles sf
> on sf.groupid = sfg.groupid
> group by so.name,
> sfg.groupname,
> sf.name,
> sf.filename
> --
> Message posted via http://www.webservertalk.com
Subscribe to:
Posts (Atom)