Detailed statistics on Data/Transaction Log Files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Detailed statistics on Data/Transaction Log Files

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-10 : 08:54:42

Meik writes "Hello,

I wonder if it is possible to retrieve statistics, such as

- free space, in KB
- used space, in KB

for the data and transaction log files of a specified database, using SQL-DMO, TSQL or Stored Procedures.

Until now I used the SQL-DMO to retrieve some of these information. However the SQLDMO.LogFile object does not provide and attribute for retrieving the available space, and the SQLDMO.DBFile object only supports the available space in MB as an long integer value (e.g. 0.94MB available data space will be 0MB retrieved by SQLDMO.DBFile).

I'd like to have the same statistics as shown in the Taskpad View of the Enterprise Manager.

Is there any way to do this?

Many thanks for your help...

Kind regards
Meik Schuetz

PS: SQLServer2000/MSDE, WIN2000PRO"

izaltsman
A custom title

1139 Posts

Posted - 2002-04-10 : 09:31:04

DBCC SQLPERF (LOGSPACE) will return enough info for you to estimate the stats you need.

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

mschuetz
Starting Member

4 Posts

Posted - 2002-04-10 : 09:55:30

izaltsman,

thanks for the quick reply, it has already been very usefull.

however, I wonder if there is something simular that will return these stats for "each" DATA and LOG file of a specified database.

You know some solution for this?

Kind regards
Meik Schuetz

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-10 : 11:15:10

I have a couple stored procs that do this for me. For general info on each file I loop through the databases and run sp_helpfile for each one.

SET @sqlcmd = 'USE ' + @dbname + ' EXEC sp_helpfile'
INSERT INTO #sp_helpfile
EXEC(@sqlcmd)

For more detailed info, I run dbcc showfilestats. This is an undocumented dbcc command that EM uses to display it's space info. I came a cross it while doing a profiler trace and viewing the space info in EM.

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 04/10/2002 11:16:07

mschuetz
Starting Member

4 Posts

Posted - 2002-04-10 : 11:36:55

Hi Jeff,

this looks very good! One more question...could you please explain the significance of the fields 'TotalExtents' and 'UsedExtents' when I use the DBCC showfilestats?

Kind regards
Meik Schuetz

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-10 : 11:50:41

From "Pages and Extents" in BOL

quote:

Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB. This means SQL Server 7.0 databases have 16 extents per MB

Jeff Banschbach
Consultant, MCDBA

mschuetz
Starting Member

4 Posts

Posted - 2002-04-10 : 12:22:07

Jeff,

that's it...thank you very much for your help

Greetings
Meik Schuetz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources