August 20, 2009 at 3:01 am
Hi all
We using SQL 2008 here, our tempdb on the reporting server is almost 9Gb, sometimes when running queries or reports we get errors back saying that the tempdb log file is full, but the .ldf file is only about 200Mb, and the .mdf is almost 9 Gig, tried shrinking the database and .mdf file, but the size remains the same.
How can we reduce the size of our tempdb, surely its just used to store temp data and data for #temp tables.
Thanks
Ruan
August 20, 2009 at 4:16 am
Hi Ruan,
tempdb gets rebuilt everytime the SQL Server service starts up, meaning that it will reset the file to the initial size it was set up for. The problem with just restarting SQL is obviously the cache gets cleared, etc, etc.
Is there free space on the disk where the tempdb log is located? The most likely cause of the error is that the disk is full.
Also, the reason why you can't shrink tempdb is because there may be a used extent / page more to the end of the .mdf. You'll need to relook the initial size and growth increments for this file, as it can cause performance issues if the database has to grow constantly.
HTH!
Danie.
August 20, 2009 at 4:36 am
Hey Danie
Yes there is 25Gb free on that drive, which is more than enough, i have also stopped and started the SQL service, but the the size remains the same.
August 20, 2009 at 4:52 am
Oops sorry Ruan, I assumed that tempdb had grown to that size. Sounds like the initial size for the data file is set to 9GB. Also, it sounds like there might be a max size set on the log file or autogrowth has been disabled.
You can change the initial size of the data file by running:
alter database tempdb
modify file
(
name = 'tempdev',
size =
)
Again, you'll need to monitor how much tempdb is being used and maybe tweak the settings a bit. For the log file, run the following:
select [name], * 8 / 1024. as 'Current Size (MB)', max_size, growth, is_percent_growth from tempdb.sys.database_files
This will tell you what the current size (in MB), maximum file size and growth increment is. max_size could be -1, meaning unlimited growth. The default for the log file in tempdb is 10% growth with an unlimited max size.
August 20, 2009 at 5:10 am
Thats great, thanks Danie!
August 20, 2009 at 6:50 am
Also, you need to know, tempdb is used for a lot more than just temp tables. It's used for sorting data, frequently from ORDER BY statements. If you're using snapshot isolation, tempdb is under heavy use to provide the row versions. Table variables go into tempdb. Multiple Active Result Sets (MARS) makes heavy use of tempdb. Internal processes such as spool operations go into the tempdb. Cursors... It's a LONG list.
It sounds like your tempdb is large because you're using it. You're getting errors because it's trying to allocate space and can't do it fast enough. I'd suggest growing it, not shrinking it, first. Then, start evaluating your code & configuration to figure out why you're blasting tempdb so hard.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2009 at 7:16 am
To add to what Grant has said..
When you rebuild your indexes temp db is used extensively.
If your temp db size is not set to 9gb initial size then you need to find out why its growing to 9gb. And yes dont shrink it try and manage it.
"Keep Trying"
August 20, 2009 at 7:43 am
there is a maintenance plan that runs every night on the user db's to reorganize all indexes, there are 15 user db's on the server, 3 of which heavily get used.
It is basically our reporting server, and there is transactional replication setup between this and the production server.
Surely tempdb should get rid of data once the maintenance plans are finished and queries have completed?
August 20, 2009 at 8:16 am
ruancra (8/20/2009)
there is a maintenance plan that runs every night on the user db's to reorganize all indexes, there are 15 user db's on the server, 3 of which heavily get used.It is basically our reporting server, and there is transactional replication setup between this and the production server.
Surely tempdb should get rid of data once the maintenance plans are finished and queries have completed?
The only way that would happen is if you have auto-shrink enabled, a very bad idea. If your tempdb is growing out to this size, it's because it needs that much room. You can try to manage your processes & code better to minimize use, but just trying to force tempdb smaller will only lead to heart ache.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply