September 7, 2011 at 8:16 am
Hello, my tempdb got to size 190 GB and I think because of that I am running out of space on my drive for production databases. How can I make my temp smaller? Also when users are using other databases that on the same drive, they get error Primary filegroup is full, create disk space by deleting undeneeded files,dropping objects in filegroup,or setting autogrowth on for existing files in the filegroup.
Please help me.
Thjank you
September 7, 2011 at 8:19 am
It's often a good thing if you put your tembdb on a dedicated disk, for such reasons and general performance. Though, I don't know what you expect to what size your databases might grow, but 190 GB isn't that gigantic.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
September 7, 2011 at 8:25 am
How can I fix this problem? Primary filegroup is full,
September 7, 2011 at 8:32 am
Make room on the drive so it can grow.
See if you have long pending opened tran you can close (might be you)
Add a file on another group.
See if you have user tables (maybe from tests) you can delete.
Very last option could be to see if shrink helps on tempdb.
Even worst than that would be to cycle the server.
September 7, 2011 at 8:41 am
I shrink tempdb and on drive still the same size,but when I check on properties for data and log, it got smaller, what else I need to do for temp get smaller.Should I re start agent?
If I add another file do will this slow performance, do I need to re start agent or server?
Thank you
September 7, 2011 at 8:45 am
Don't mess with it unless you're sure this is a 1 time occurence.
Tempdb just needs space to process stuff and there's nothing you can do except give him the space.
A new file won't slow performance unless that drive is WAY slower than the current one.
September 7, 2011 at 8:49 am
I have 24Gb now, is this enough??
September 7, 2011 at 8:51 am
I have water in my pool, do I have enough?
Only you can monitor that and answer this.
September 7, 2011 at 8:51 am
How can I check long pending opened tran ??
Thank you
September 7, 2011 at 8:56 am
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
September 7, 2011 at 9:23 am
You can monitor the tempdb usage per active session using this script:
http://www.sqlservercentral.com/scripts/tempdb/72007/
The script returns the tempdb usage for each active session and can't be used to identify the offending query after the database has grown. However, a database file does not grow to 190 GB in 1 second, so you can record the query output to a table and find the most tempdb intensive queries.
Once found the culprit, you can fix the root cause of the excessive tempdb usage. Take into consideration that tempdb can grow tremendously when the optimizer picks the wrong execution plan and has to allocate internal objects such as huge bitmaps.
Before you can shrink a tempdb full of internal objects, you have to flush the system cache with DBCC FREESYSTEMCACHE('All').
Unfortunately, in SQL 2005 there's nothing more specific. You will experience a bit of performance hit right after issuing this command, but it's a resonable tradeoff compared to cycling the service.
Hope this helps
Gianluca
-- Gianluca Sartori
September 7, 2011 at 9:49 am
Thank you, I ran it and under o open transactions is 0, anything else I should pay attention to?
Also, if I re start sql agent my temp db, should get smaller right?
Also, how can I check why my temp db got so big?
Thank you
September 7, 2011 at 9:54 am
If you think this is a fluke you can shrink it.
But if it grows again then you know this is what the system needs to have.
As a first round of checking I'd look at thte default trace to see when the growth happened and try to correlate with big jobs / data loads.
Might be a single query that did this too. That takes further monitoring.
Sql Monitor from Red-Gate is what I'd use here.
September 7, 2011 at 9:56 am
Krasavita (9/7/2011)
Thank you, I ran it and under o open transactions is 0, anything else I should pay attention to?
As stated in my reply: no sessions, no results from the query.
Also, if I re start sql agent my temp db, should get smaller right?
Not quite. If you restart SQL Server, your tempdb gets recreated from scratch (from model, actually).
Also, how can I check why my temp db got so big?
Follow the advice in my post.
-- Gianluca Sartori
September 7, 2011 at 10:01 am
DBCC FREESYSTEMCACHE('All').
How long this will run, do you think?
Thank you
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply