September 23, 2013 at 4:14 am
Hi,
What is the recovery model of your TempDB? Default is SIMPLE, and it should be. Autogrowth of 10% is not the best option.
Citing Erland:
Do you have any databases that uses any form of snapshot isolation? You can see how much space the version store takes up in this DMV: sys.dm_db_file_space_usage.
you should have control over these stuff...
TempDB databases have to have more files on busy systems. And as yours rises quickly then you'd better add more (maybe will have to extend the storage for tempdb). There are many recommendations for them, and some are:
- Starting with number of files = 1/4 of CPU cores and then monitor the tempdb and see the need for addition of more. Consider the trace flag 1118 (What is Paul's recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There's no down-side to having it turned on - http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/)
- Once I applied this nice article: https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/ and I got very good results.
You should invest quite a lot time on your tempdb, it's important.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
September 24, 2013 at 8:55 am
Microsoft recommend unlimited growth for temp databases.
Initial size should be 50 MB for all temp dbs with 500MB file growth with unlimited Max Growth. hope it helps
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.
September 24, 2013 at 9:21 am
Angeltica (9/24/2013)
Microsoft recommend unlimited growth for temp databases.Initial size should be 50 MB for all temp dbs with 500MB file growth with unlimited Max Growth. hope it helps
This is totally incorrect. You can't make a blanket recommendation for tempDB size, this is something that needs to be considered carefully based on the workload of the individual server. A 50MB tempDB will grow almost immediately on most servers I'm involved with and I don't manage large databases. The same applies for growth, you have to determine what it should be for your environment, ideally you will create tempDB with the max size it needs so it NEVER has to grow. Autogrow should be just for emergencies or before you really know what the max size you need is. In the OP's case, tempDB is growing and filling the drive, and it may not even need that much space, so setting the initial size properly will likely solve this problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2013 at 11:37 am
This is something what is being configured by Microsoft script best practice configuration. Depending on environment (hardware) we do create n number of temp databases. Also, we do 15 min tran log backup, and full database backup. Through our maintenance and best practice config we never had any issues with huge databases log files. Well, presume it is matter of experience. If I get a spec for the box, I could suggest some config options.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.
September 24, 2013 at 12:18 pm
Angeltica (9/24/2013)
This is something what is being configured by Microsoft script best practice configuration.
To be honest, I wouldn't trust some of MS's recommendations. You cannot make a blanket statement as to how big TempDB should be. I've had servers where TempDB was over 200GB. In fact, I can't recall a server that I've worked on in the last 5 years that got a 50MB TempDB, even my desktop has it set at a couple hundred MB. The size of TempDB is defined by the workload on the server, not some statement in some old best practices guide.
Also, we do 15 min tran log backup, and full database backup.
TempDB can't be backed up, and backup frequency of user databases won't have any effect on TempDB size
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2013 at 12:49 pm
Don't get me wrong, backup is against general databases. I had problem with tempDb trying to DBCC one of the biztalk with transactions logs databases.
And again, based on hardware, script configs each instance. Unlimited growth is option what is applied for every single tempDB on instance. Based on number of processes number of temp dbs is configured. For Db of 1.5 TB we are having 200 GB drive for temp db. It normally used around 150 GB. One of the things is for sure, you should not restrict tempdb growth.
Also with same success you could say: bin your MCDBA certificate 🙂
P.S.: I've been working at some organizations where they were happily running their business without proper instance configurations. Yep, restart always helped. But it doesn't mean it was done in correct way.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.
September 24, 2013 at 3:22 pm
GilaMonster (9/24/2013)
To be honest, I wouldn't trust some of MS's recommendations.You cannot make a blanket statement as to how big TempDB should be.
+1 to both of those statements.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2013 at 3:24 pm
Angeltica (9/24/2013)
Microsoft recommend unlimited growth for temp databases.Initial size should be 50 MB for all temp dbs with 500MB file growth with unlimited Max Growth. hope it helps
Where's the MS link for that recommendation, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2013 at 1:01 am
Based on this article we are configuring out tempDB. But if it is expanding too much, you definitely have to have a look into code.
Here are some basic configurations:
ALTER DATABASE tempdb MODIFY FILE
(name = 'tempdev',
size = 50MB,
filegrowth = 500MB)
GO
ALTER DATABASE tempdb MODIFY FILE
(name = 'templog',
filegrowth = 50MB)
GO
--Create additional files for tempdb
--Files created at ratio of 1 file per processor core, up to a maximum of 8 files
-- as per recommendations made by Bob Ward at PASS 2011 conference
DECLARE@cpu_count int
DECLARE@file_count int
DECLARE@logical_name sysname
DECLARE@file_name nvarchar(520)
DECLARE@physical_name nvarchar(520)
DECLARE@alter_command nvarchar(max)
SELECT @physical_name = physical_name
FROM tempdb.sys.database_files
WHERE name = 'tempdev'
SELECT @file_count = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
SELECT @cpu_count = cpu_count
FROM sys.dm_os_sys_info
if @cpu_count > 8 set @cpu_count = 8
WHILE @file_count < @cpu_count
BEGIN
SELECT @logical_name = 'tempdev' + CAST(@file_count AS nvarchar)
SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')
SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' + @file_name + ''', SIZE = 50MB, FILEGROWTH = 500MB)'
EXEC sp_executesql @alter_command
SELECT @file_count = @file_count + 1
END
--Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.
September 25, 2013 at 1:27 am
Why are you altering model? If you're adding files to TempDB, add the files to TempDB, don't mess with model.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 1:32 am
Corrected. Thank you.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.
September 25, 2013 at 4:22 am
IgorMi (9/23/2013)
What is the recovery model of your TempDB? Default is SIMPLE, and it should be.
can we change it ? ??
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 25, 2013 at 6:05 am
Bhuvnesh (9/25/2013)
IgorMi (9/23/2013)
What is the recovery model of your TempDB? Default is SIMPLE, and it should be.can we change it ? ??
You're right! It cannot be different from SIMPLE. Maybe it can be changed with a trick, however a good notice from you. I thought it could be changed, without reasoning a bit for its purpose.
Thanks,
IgorMi
Igor Micev,My blog: www.igormicev.com
September 25, 2013 at 8:02 am
Angeltica (9/25/2013)
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/Based on this article we are configuring out tempDB. But if it is expanding too much, you definitely have to have a look into code.
Here are some basic configurations:
ALTER DATABASE tempdb MODIFY FILE
(name = 'tempdev',
size = 50MB,
filegrowth = 500MB)
GO
ALTER DATABASE tempdb MODIFY FILE
(name = 'templog',
filegrowth = 50MB)
GO
--Create additional files for tempdb
--Files created at ratio of 1 file per processor core, up to a maximum of 8 files
-- as per recommendations made by Bob Ward at PASS 2011 conference
DECLARE@cpu_count int
DECLARE@file_count int
DECLARE@logical_name sysname
DECLARE@file_name nvarchar(520)
DECLARE@physical_name nvarchar(520)
DECLARE@alter_command nvarchar(max)
SELECT @physical_name = physical_name
FROM tempdb.sys.database_files
WHERE name = 'tempdev'
SELECT @file_count = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
SELECT @cpu_count = cpu_count
FROM sys.dm_os_sys_info
if @cpu_count > 8 set @cpu_count = 8
WHILE @file_count < @cpu_count
BEGIN
SELECT @logical_name = 'tempdev' + CAST(@file_count AS nvarchar)
SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')
SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' + @file_name + ''', SIZE = 50MB, FILEGROWTH = 500MB)'
EXEC sp_executesql @alter_command
SELECT @file_count = @file_count + 1
END
--Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
That article isn't an MS article. It's by Paul Randal and he used to work for MS. I also don't see anywhere that he made the recommendation of only 50MB for the initial size of TempDB files. That's not a good idea, either. That means that TempDB has to grow every time you reboot the server.
I strongly recommend that the initial size of TempDB be set large enough so that no growth is actually expected whether you have multiple files or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2013 at 8:24 am
You do not need tempdb ndf file as large as you can make them. It is more than enough to put as 50 mb each. They will expand as much as needed during workload, just do not put hard growth restrictions.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply