Now The SQL Server tempDB is arguably the most important database in any installed instance. A poorly configured tempdb will trump all the best efforts for design of good OLTP or datawarehouse systems.
The standard recommendation is to have one tempdb database file for each CPU. It’s also generally recognized that this rule of thumb us just that, a guess. For busy OLTP databases one tempdb for each cpu may make sense. But not always…
Last week I signed up to do a simple change to a production servers’s tempdb config. Specifically, drop 4 data files. I’ve recently added tempdb data files to existing databases… how hard can it be to drop them.
It turns out that I was right and not so right. My approach to deleting the tempdb data files was spot on; I just didn’t work quite the way I anticipated.
I this post I will run through a few scenarios of adding a tempdb data file and then attempting to drop the file.
Just to set the record… the tempdb file I am creating is not a fixed size and does allow for autogrowth. That is NOT the point of this exercise. Please visit other sites for info on configuring your tempdb for your application.
Let’s first add a tempdb file to our instance:
ADD FILE (
NAME = tempdb2
, FILENAME = N‘D:\tempdb\tempdb2.ndf’
, SIZE = 512MB
, FILEGROWTH = 50MB )
Now let’s remove the file:
ALTER DATABASE tempdb REMOVE FILE tempdb2
Since I’m running this test on a test server (translation, no one else is banging on it) the above code worked fine. I got the following confirmation:
The file “D:\tempdb\tempdb2.ndf” has been modified in the system catalog. The new path will be used the next time the database is started.
The file ‘tempdb2′ has been removed.
The next scenario will add a new file to tempdb, write out temp table, then attempt to drop the file:
add file (
NAME = tempdb2
, FILENAME = N‘D:\tempdb\tempdb2.ndf’
, size = 512MB
, filegrowth = 50MB )
– Use tempdb, before trying to remove the old table.
SELECT *
INTO #t
from SYS.OBJECTS
– Try to delete the file just added
ALTER DATABASE tempdb REMOVE FILE tempdb2
– It still worked!
The file “D:\tempdb\tempdb2.ndf” has been modified in the system catalog. The new path will be used the next time the database is started.
The file ‘tempdb2′ has been removed.
I anticipated the above test to generate an error message telling me that the tempdb file could not be deleted because it was in use. After all, that’s what I got on the production server…
Maybe the small temp table wasn’t enough to use the new file.
Let’s create a larger temp table and see what happens:
add file (
NAME = tempdb2
, FILENAME = N‘D:\tempdb\tempdb2.ndf’
, size = 512MB
, filegrowth = 50MB )
–drop table #test
– Let’s put significantly more data in tempdb:
CREATE TABLE #test ( id int identity (1, 1),
data char(8000) )
declare @i int
set @i = 1
while @i <= 50000
begin
insert into #test values (convert(char, @i))
select @i = @i + 2
end
I have a rather lengthy query that I use to see how much space is in each database file. I’ll post it here, but it really has more data/columns than is needed for this post:
–drop table #sfs
–drop table #fixed_drives
–drop table #output_table
–drop table #databases
–drop table #dbf
–drop table #fg
—————————————
– Save result set from showfilestats
—————————————
CREATE TABLE #sfs ( fileid tinyint
, filegroupid tinyint
, totalextents int
, usedextents int
, dbfilename sysname
, physfile varchar(255) );
——————————————–
– Save result set from sys.database_files
——————————————–
CREATE TABLE #dbf ( [file_id] int
, file_guid uniqueidentifier
, [type] tinyint
, type_desc nvarchar(60)
, data_space_id int
, [name] sysname
, physical_name nvarchar(260)
, [state] tinyint
, state_desc nvarchar(60)
, size int
, max_size int
, growth int
, is_media_ro bit
, is_ro bit
, is_sparse bit
, is_percent_growth bit
, is_name_reserved bit
, create_lsn numeric(25, 0)
, drop_lsn numeric(25, 0)
, read_only_lsn numeric(25, 0)
, read_write_lsn numeric(25, 0)
, diff_base_lsn numeric(25, 0)
, diff_base_guid uniqueidentifier
, diff_base_time datetime
, redo_start_lsn numeric(25, 0)
, redo_start_fork_guid uniqueidentifier
, redo_target_lsn numeric(25, 0)
, redo_target_fork_guid uniqueidentifier
, back_lsn numeric(25, 0) );
——————————————–
– Save result set from sys.filegroups select * from sys.filegroups
——————————————–
CREATE TABLE #fg ( [name] sysname
, data_space_id int
, [type] char(2)
, type_desc nvarchar(60)
, is_default bit
, [filegroup_id] uniqueidentifier
, log_filegroup_id int
, is_read_only bit );
– Populate #disk_free_space with data
CREATE TABLE #fixed_drives (DriveLetter char(1) NOT NULL
, FreeMB int NOT NULL );
INSERT INTO #fixed_drives
EXEC master..xp_fixeddrives;
CREATE TABLE #output_table (DatabaseName sysname
, FG_Name sysname
, GB_Allocated numeric(8, 2)
, GB_Used numeric(8, 2)
, GB_Available numeric(8, 2)
, DBFilename sysname
, PhysicalFile sysname
, Free_GB_on_Drive numeric(8, 2) );
SELECT name AS DBName
INTO #databases
FROM sys.databases
WHERE database_id <= 4
AND state_desc = ‘ONLINE’;
DECLARE @dbname sysname;
SELECT @dbname = ( SELECT TOP (1) DBName FROM #databases);
DELETE FROM #databases WHERE DBName = @dbname;
WHILE @dbname IS NOT NULL
BEGIN
– Get the file group data
INSERT INTO #sfs
EXEC(‘USE ‘+ @dbname + ‘; DBCC showfilestats;’);
INSERT INTO #dbf
EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.database_files;’);
INSERT INTO #fg
EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.filegroups;’);
– Wrap it up!
INSERT INTO #output_table (DatabaseName
, FG_Name
, GB_Allocated
, GB_Used
, GB_Available
, DBFilename
, PhysicalFile
, Free_GB_on_Drive )
SELECT @dbname AS DATABASE_NAME
, fg.name AS [File Group Name]
, CAST(((sfs.totalextents * 64.0) / 1024000.0) AS numeric(8, 2)) AS GB_Allocated
, CAST(((sfs.usedextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Used
, CAST((((sfs.totalextents - sfs.usedextents) * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Available
, sfs.dbfilename
, sfs.physfile
, CAST((fd.FreeMB / 1000.0) AS NUMERIC(8, 2)) AS Free_GB_on_Drive
FROM #sfs sfs
INNER JOIN #dbf dbf
ON dbf.[file_id] = sfs.fileid
INNER JOIN #fg fg
ON fg.data_space_id = sfs.filegroupid
INNER JOIN #fixed_drives fd
ON fd.DriveLetter = SUBSTRING(sfs.physfile, 1, 1);
SELECT @dbname = ( SELECT TOP (1) DBName FROM #databases);
IF @dbname IS NOT NULL
DELETE FROM #databases WHERE DBName = @dbname;
TRUNCATE TABLE #sfs;
TRUNCATE TABLE #dbf;
TRUNCATE TABLE #fg;
END
SELECT CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
, DatabaseName
, FG_Name
, GB_Allocated
, GB_Used
, GB_Available
, DBFilename
, PhysicalFile
, Free_GB_on_Drive
FROM #output_table
ORDER BY DatabaseName
, FG_Name
OK, this time we have confirmed that we have data in tempdb. Let’s see if we can drop it:
ALTER DATABASE tempdb REMOVE FILE tempdb2
– Cool, we have data in tempdb2!!!
The file “D:\tempdb\tempdb2.ndf” has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 1
The file ‘tempdb2′ cannot be removed because it is not empty.
This is the error I got with my production server. It turns out, if you actually read the error message (which I can’t say I truly did) it does indicate that the system catalog has been updated BUT the change will not take affect until the service is restarted.
By simply restarting the SQL Server service the tempdb file(s) will be removed on startup.
I’ve seen some posts online that attempted to use DBCC to empty the tempdb data file. For me it didn’t work.
Of course the above approach does require a restart of SQL Server. Besides the obvious issue of the database going offline for 30 seconds or so (make sure no jobs are running) restarting the service will zero out all of the server counters. This may be an issue if your using DMVs to monitor performance.
It’s been quite a while since I did a post… Hopefully this one is worthy of being remembered.
Stay curious… keep learning…
Leave a Reply
Using Gravatars in the comments - get your own and be recognized!
XHTML: These are some of the tags you can use:
<a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>