Feb 10

Stalking tempdb growth

Recently, our tempdb log went completely bonkers, growing from its normal 30 GB to fill its 68 GB drive (with all the subsequent freakings-out by SQL Server and required service bouncings which you would anticipate). While glaring at job durations and recently-released builds, I threw a couple of small jobs into SQL Server to monitor tempdb data and log usage so it could page me if it decided to grow the tempdb log excessively and so that I could see when growth started and what it might correlate to.

I made two small tables and a sproc to fill each and scheduled the sprocs to run every 10 minutes. I found two interesting things.

  1. Our tempdb data files could be much smaller. They’re currently 20 GB, and we have 8 of them, as we’re using the T1118 tempdb concurrency enhancement at the direction of Microsoft (it’s ostensibly for 2000 only, but we’re running 2005 and they have us using it), and they top out at 1.8 GB of data each.
  2. The tempdb checkpoint interval, which as far as I can tell I have no control over, as it has nothing to do with the server-wide recovery interval, appears to be 2-3 hours during the week and 6-7 hours on the weekend. When I graphed the log usage in Excel, the amount of data in the log very clearly repeatedly grew steadily to a point (13 GB during the week, 19 GB on the weekend), then plummeted to 0, then grew steadily back to that same point.

My tables for recording sizes:

CREATE TABLE dbo.tempdbDataSize (
 filename CHAR(8) NOT NULL,

CREATE TABLE dbo.tempdbLogSize (

If you haven’t changed your tempdb file names, you can use a CHAR(7) in tempdbDataSize. Since we have 8, I renamed the first one from “tempdev” to “tempdev1” for consistency and numbered them (tempdev1 – tempdev8). We have a busy system, so I’m recording my tempdb log sizes in gigabytes. You may need to use megabytes instead, in which case you’ll want to kick those TINYINTs up to SMALLINTs and remove the division by 1024 from the statement (below) which audits the log usage.

I found the code to get the file usage by turning on SQL Profiler and then opening the Shrink Files pane in SSMS (remember not to click OK in this pane if you try this yourself — shrinking tempdb while people are using it is Supremely Bad Idea).

The tempdb data size audit sproc:

CREATE PROCEDURE dbo.tempdbDataSize_audit AS


CREATE TABLE #tmpspc (
Fileid int,
FileGroup int,
TotalExtents int,
UsedExtents int,
Name sysname,
FileName nchar(520)

EXEC(‘USE tempdb INSERT INTO #tmpspc EXEC (”dbcc showfilestats”)’)
INSERT INTO tempdbDataSize (filename, usedMB)
SELECT name, UsedExtents * 64 / 1024
FROM #tmpspc
DROP TABLE #tmpspc

The tempdb log size audit sproc:

CREATE PROCEDURE dbo.tempdbLogSize_audit AS


DECLARE @sizeGb INT, @usedGb INT

CREATE TABLE #tmplogspc (
databaseName SYSNAME,
logSizeMB FLOAT,
SpaceUsedPercent FLOAT,
status BIT

INSERT INTO #tmplogspc
EXEC (‘dbcc sqlperf(logspace)’)
SELECT @sizeGb = logSizeMb / 1024, @usedGb = logSizeMB * spaceUsedPercent / 100 / 1024
FROM #tmplogspc
WHERE databaseName = ‘tempdb’
DROP TABLE #tmplogspc

DECLARE @subject VARCHAR(50)
IF @sizeGb >= 40
SET @subject = ‘Tempdb log size is ‘ + CAST(@sizeGb AS VARCHAR) + ‘ GB’
EXEC sendEmail @toEmails = ‘me@nullmydomain.com’, @subject = @subject
ELSE IF @sizeGB > (SELECT TOP 1 gb FROM tempdbLogSize ORDER BY time DESC)
SET @subject = ‘Tempdb grew to ‘ + CAST(@sizeGb AS VARCHAR) + ‘ GB’
EXEC sendEmail @toEmails = ‘me@nullmydomain.com’, @subject = @subject

INSERT INTO tempdbLogSize (gb, usedGb) VALUES (@sizeGb, @usedGb)

The sendEmail sproc is my custom wrapper for sp_send_dbmail — you can just drop in a sp_send_dbmail call.


My findings haven’t been particularly useful in diagnosing the mysterious tempdb log growth. As a bandaid to prevent further system unavailability, I changed my sproc that rebuilds indexes overnight to do its sorts in the user database rather than in tempdb. This causes them to take a little longer, as the happy parallelism of having the index and the rebuild spread across multiple physical disks is lost, but the tempdb log has behaved since. One of the first things I checked for was recent large growth in table size (you record rowcounts daily so that you can see growth over time, right?), figuring this would correspond to large growth in index size and thus tempdb log usage during rebuilds, and there wasn’t any, so this isn’t particularly helpful.

Jan 27

Installing SQL Server 2005 Full-Text Search After the Fact

You might expect that you could add full-text search to an existing SQL Server instance by firing up the install media. You’d be wrong. You must go into the Control Panel, into Add/Remove programs, click the Microsoft SQL Server 2005 component, and click its Change button. Don’t forget to re-apply your service packs!

Jan 21

SQL Server Perfmon Counters

If not otherwise specified, these numbers are from a Microsoft webcast.

SQL Server: Buffer Manager

  • Page life expectancy should be > 300
  • If Page lookup/sec / Batch Requests / sec is > 100, then you’re doing too much I/O.
  • Lazy writes/sec should be 0. > 20 is bad, indicates memory pressure.
  • Free pages should be >= 640.

Memory manager

  • Memory grants pending should be 0


  • Avg Latch Wait Time(ms) should be less than 10


  • Total wait time (ms) > 60,000 ms is bad.

SQL Server Statistics

  • Compilations/sec should be less than 20% of batch requests.
  • Recompiles should be less than 10% of batch requests.


Jan 17

Ghost Minstrel Bowl

Ghost Minstrel BowlA ghostly Kokopelli summons the wind in this wee dish made of paper suspended between layers of glass. The paper is specially formulated to withstand the steamy 1350 degrees inside a glass kiln. The food-safe bowl is 4.5 inches wide and 1 inch deep, perfect for sauces, spices, jewelry, and small soaps. Visit Jellybug Artworks for more pictures and a chance to own it!