March 19, 2008 at 3:05 pm
I am on sql server 2000 and I want to know when the last checkpoint ran. I have been having a problem with tempdb growing. If I manually run checkpoint I can keep the log from growing. But if I leave it autoconfig ( recovery_interval = 0), The log keeps growing.
March 20, 2008 at 9:03 am
SQL Server 2000 doesn't issue CHECKPOINTs against tempdb.
Here's an excerpt from SQL Server 2000 I/O Basics at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
All databases, except for tempdb are checkpointed. Tempdb does not require recovery (it is recreated every time SQL Server starts) so flushing data pages to disk is not optimal for tempdb and SQL Server avoids doing so.
I'd recommend trying to find out what's causing tempdb to grow so much, and setting up a scheduled CHECKPOINT in the meantime
Hope that helps....
Brian K
March 20, 2008 at 9:43 am
Look for nasty queries, ones with lots of IO. Your high tempdb utilization is likely due to inefficient execution plans.
In SQL 2005 there are powerful tools to allow you great insight into what is going on. Not so in SQL 2000.
In SQL 2000 I would run a simple trace with the following events:
RPC:Completed
SP:StmtCompleted
SQL:BatchCompleted
Add the "Reads" column to your trace. Filter the Duration column to include only events that last longer than say 100 microsec (or a 1000 microsec, depending on how long you want to run your trace). Filter the Reads column to include events with only over, say, a 1,000 reads.
Collect your data for at least a few minutes, at a time when a lot of activity occurs on the server.
Write the data in a file (say 500 MB with file rollover enabled) and use the following command to insert into a table after trace is completed and stopped:
select * into trace_table
from ::fn_trace_gettable ('complete_path_trace_file_location_goes_here', default)
Sort the results by the Reads column and focus on queries with the most Reads. These will be queries in most need of tuning. Pass those on the Database Engine Tuning Advisor to get feedback on missing indexes. Also check whether these queries need to be re-written more efficiently.
When configuring your trace, make sure that the setting "Server processes trace data" is turned off - otherwise, your trace will put a lot of additional load on your server at a time when you can least afford it.
__________________________________________________________________________________
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply