February 6, 2008 at 1:59 pm
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]
February 6, 2008 at 8:17 pm
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.
February 6, 2008 at 8:54 pm
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]
February 6, 2008 at 8:58 pm
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]
February 6, 2008 at 10:35 pm
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.
February 6, 2008 at 10:40 pm
perfmon doesnt have a counter to check the "used space" for data files. It is there for log files only.
February 7, 2008 at 8:57 am
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]
February 7, 2008 at 1:22 pm
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.
February 7, 2008 at 1:27 pm
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]
February 7, 2008 at 6:00 pm
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
February 7, 2008 at 6:51 pm
Lester Policarpio (2/7/2008)
For data i used dbcc showfilestatsuse 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]
February 7, 2008 at 7:41 pm
You're Welcome 🙂
"-=Still Learning=-"
Lester Policarpio
February 8, 2008 at 3:58 am
why not
((totalextents-usedextents)*64)/1024)
instead of
((totalextents*64)/1024)-((usedextents*64)/1024)
?
February 9, 2008 at 12:08 am
Yeah that can also work 🙂
"-=Still Learning=-"
Lester Policarpio
March 12, 2008 at 5:39 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply