September 15, 2009 at 12:54 pm
Hi
Pretty new to SQL admin and am running a SQL 2005 Standard server hosting Dynamics AX DB. The server has 2 x dual core 3GB CPU and 8GB RAM. The tempdb is on its own drive.
Afew time over the last few weeks the Temp DB has grown to the full 68GB drive capacity in a mater of 3-4 hours. The temp DB is set to the default settings with no restrictions and to grow 10%. I have read and understand that I need to set the max size but have the following questions:
What should the Min/Max be
What should the growth rate be set to.
If I restrict it what happens when it reaches this limit
and most of all...why should this suddenly happen what seemed to be an average day.
A lot to aks, hope yo ucan help.
Regards
Jason
September 15, 2009 at 1:09 pm
The single TempDB data file should be split to the number of files equal the number of CPUs on your server.
All of these files should have exactly the same initial size, which is close to disk size divided to number of these files.
In this configuration files don't need any time for space allocation, and all of them grab data simultaneously, which increase performance a lot.
September 15, 2009 at 1:18 pm
Two more things.
There are many reasons for TempDB files grow, it depends on particular case. For instance you may have a procedure, which use temporary tables in a code.
If TempDB files are getting full you can shrink TempDB or restart SQL Server services and the TempDB will be empty again.
For details on shrinking TEMPDB see: http://support.microsoft.com/default.aspx/kb/307487
September 15, 2009 at 1:22 pm
OK. Thanks for your advise. I will look at seting my db size to the disk space size. What concerns me is that what happens when the temp needs to grow past that size. It would have continued growing if it hadn't filled the space and i had to restart the service. What should I set the growth rate to?
September 15, 2009 at 1:28 pm
As I said, just lock the file size and set growing rate to 0.
Look at this info about the best practices:
www.sqlmag.com/Article/ArticleID/39158/sql_server_39158.html
September 15, 2009 at 1:31 pm
The tempdb data file should be set statically and use the filegrowth as an emergency backup. When trying to determine what could cause it, you could look into the server to try and find it. What jobs have been running? What procedures or apps might have been running? Does it happen at generally the same day/time each time?
If you are uncertain, you may want to setup an alert to notify you when the log file grows (prior to setting up the static size). When the file grows you could enable a trace or run profiler against the db and try to find what might be causing it at that time. After you have found the cause, then set the static file size.
This is a matter of preference. If you have separate controllers (raid or san), I would split the tempdb files between them (if you go the route of one physical file per core).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2009 at 3:59 pm
Zumao1
You may want to read this excellent article by Gail Shaw and alter your database backup mode and/or timing of log backups. The aricle contains a very clear explanation of how the recovery mode for the database effects log file size.
http://www.sqlservercentral.com/articles/64582/
Also scroll down and read Steve Jones simple but clear explanation as to the effect of log backups on log file size at
http://www.sqlservercentral.com/Forums/Topic788295-149-1.aspx
Edited at: 6:25 PM
May 30, 2011 at 8:49 am
Question: did you ever figure out the cause for this issue? Could it be because at the time, several companies were being copied or exported from Dynamics AX?
Dynamics AX has a strange (to me) way of handling several companies: their data is all in the same database. All database tables are shared by all the different companies, and in each table you have a field with the company code to use as a filter. Of course this will depend on how you plan the installation, as the table accesses are working all the time, but the application and sql server have handled the locks with no problems so far.
There are two things that I suspect fill the tempdb:
. One is the function from inside the application to generate a copy of the company. To copy a company, the app has to enter each table, filter the records by the company you want to copy, and insert records in that same table with the new company code (the new company you named when you started to generate the copy). Before inserting the records, I suspect that the data is being stored in tempdb.
. Another is the function to export a company, which follows a similar logic to the Copy Company function: it has to enter each table, filter by company code, and extract the records to a file.
If you have any insights regarding this issue, it is completely welcome.
Thanks, y'all!
May 30, 2011 at 8:52 am
I would run a server side trace to look for any rogue queries\procedures and monitor file growths, that would be a good starting point
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 31, 2011 at 12:29 am
1) the advise to make tempdb files equal to CPU cores is overblown. virtually no one out there has sufficient IO throughput to feed up 8, 16, 32, 64, .. tempdb files. if you aren't seeing lots of PFS/SGAM latch issues in tempdb, then a very few files is fine unless you have lots of spindles on tempdb LUN.
2) Here are some queries to see what is eating tempdb:
select t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc,t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
from (Select session_id, request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1, sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and (t1.request_id = t2.request_id)
and t1.session_id > 50
order by t1.task_alloc DESC
--You can use the sql_handle and plan_handle to get the SQL statement and the query plan as follows
--select text from sys.dm_exec_sql_text(@sql_handle)
select text from sys.dm_exec_sql_text(0x02000000FBD00C1E278ABAF4AF97298E219DC58A9BAE0D81)
-- you can show the query plan too.
--select * from sys.dm_exec_query_plan(@plan_handle)
select * from sys.dm_exec_query_plan(0x02000000FBD00C1E278ABAF4AF97298E219DC58A9BAE0D81)
-- RUN A TEMDDB INTENSIVE QUERY IN ANOTHER SESSION AND MONITOR THE
-- TEMPDB SPACE USAGE.ONCE THE ACTIVE TASKS ARE COMPLETED, THE SPACE USAGE
-- DROPS DOWN TO 0 BUT IT SHOWS IN THE SESSION.
--
-- identifying top 5 tasks that are largest consumers of space in temmpdb
--
select top 5 *
from sys.dm_db_task_space_usage
where session_id > 50
order by user_objects_alloc_page_count + internal_objects_alloc_page_count DESC
Select total_size = SUM (unallocated_extent_page_count) +
SUM (user_object_reserved_page_count) +
SUM (internal_object_reserved_page_count) +
SUM (version_store_reserved_page_count) +
SUM (mixed_extent_page_count),
SUM (unallocated_extent_page_count) as freespace_pgs,
SUM (user_object_reserved_page_count) as user_obj_pgs,
SUM (internal_object_reserved_page_count) as internal_obj_pgs,
SUM (version_store_reserved_page_count) as version_store_pgs,
SUM (mixed_extent_page_count) as mixed_extent_pgs
from sys.dm_db_file_space_usage
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply