Monitoring tempdb free space in SQL Server 2000

Question

Monitoring tempdb free space in SQL Server 2000

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
February 6, 2008 at 1:59 pm

#70796

Is there a way to monitor the free space of tempdb data and log files in SQL Server 2000?
I have set up the data files on a ***fixed*** size large enough to comfortably accommodate current workloads, but we are doing a large data migration over the weekend, and I would like to be able to monitor space usage over time.
I'm aware of the wonderful tools available in SQL 2005, but unfortunately I'm dealing with SQL 2000 here. 🙁
Thanks for any feedback,
Marios
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 1

Monitor meaning record size or alert you if it gets to some size?

You can run perfmon, log the size in a CSV and chart it, load into a table, etc. That might be the easy way to keep track of growth over time.

If you need an alert, you can set an alert on a performance metric. That way you can be emailed when it gets to a certain size.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Thanks Steve,

I'm interested in tracking size over time, specifically tempdb used space over time. I did not have alerts in mind, but that sounds like a good idea.

As I mentioned, my tempdb data files are fixed with no autogrowth allowed. Therefore, I am interested in measuring the tempdb *allocated* space at any given time vs. the total space available, which is constant.

I am not aware of any perfmon counters or profiler-trace events available to do that in SQL 2000.

The only way I can think of getting some info on this is by using ::fn_virtualfilestats (total_bytes_write) data to extrapolate on the tempdb space allocated at any given point. I am not sure if that will work.

Is it possible to measure the amount of empty space in a database file in sql 2000?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

Can we set an alert once tempdb *allocated* space (not the actual size of the file, which in my case stays constant) reaches a certain threshold?

I don't think it's possible in SQL 2000.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

rajankjohn SSCertifiable Points: 5061 More actions · Answer 4

I have used two methods. one - call sp_spaceused or use the builtin "SpaceAvailableInMB" method in sql dmo "DBFile" object. Call the dmo script from a recurring schedule.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 5

perfmon doesnt have a counter to check the "used space" for data files. It is there for log files only.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Rajan John (2/6/2008)
I have used two methods. one - call sp_spaceused or use the builtin "SpaceAvailableInMB" method in sql dmo "DBFile" object. Call the dmo script from a recurring schedule.

Thanks Rajan,

Can you provide me with some more detail on this, maybe an example?

I'm not familiar with using SQL DMO.

Much appreciated

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 7

Sorry, forgot that was for log space only.

One thing you could do is track the space in tempdb using a proc and either store it in an admin table somewhere, or write it out to perfmon as one of the user settable counters.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

Steve Jones - Editor (2/7/2008)
Sorry, forgot that was for log space only.
One thing you could do is track the space in tempdb using a proc and either store it in an admin table somewhere, or write it out to perfmon as one of the user settable counters.

Thanks, I will proby do something along these lines.

We also have a database-management tool from IDERA that helps us monitor data/log empty space in real time.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 9

For data i used dbcc showfilestats

use tempdb dbcc showfilestats

-- For DB Size : (totalextents*64)/1024

-- FOr DB Used : (usedextents*64)/1024

-- For DB Free Space :

--((totalextents*64)/1024)-((usedextents*64)/1024)

FOr log i used

dbcc sqlperf(logspace)

"-=Still Learning=-"

Lester Policarpio

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

Lester Policarpio (2/7/2008)
For data i used dbcc showfilestats
use tempdb dbcc showfilestats
-- For DB Size : (totalextents*64)/1024
-- FOr DB Used : (usedextents*64)/1024
-- For DB Free Space :
--((totalextents*64)/1024)-((usedextents*64)/1024)
FOr log i used
dbcc sqlperf(logspace)

Thanks! This is very useful.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 11

You're Welcome 🙂

"-=Still Learning=-"

Lester Policarpio

Andrey Right there with Babe Points: 780 More actions · Answer 12

why not

((totalextents-usedextents)*64)/1024)

instead of

((totalextents*64)/1024)-((usedextents*64)/1024)

?

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 13

Yeah that can also work 🙂

"-=Still Learning=-"

Lester Policarpio

John Morgan-454160 SSC Veteran Points: 201 More actions · Answer 14

Hello Steve,

I have two SQL severs, one is used live and the other is used to log ship 3 DB's (LIVE, DEV and Training) to as a back up. My question is, the size of my tempdb on the live server is 27GB, while on the standby server it is 8MB. I have no maintenance plans running on the tempdb database, should I setup a back up and optimise plan?

Cheers

John M