June 12, 2012 at 3:49 pm
Long story short...
TempDB filled up the drive.
Moved tempdb.mdf and tried to move tempdb.ldf but fat fingered the logical file name. Restarted the server and it started bouncing from one cluster node back to the other failing to come online. Tried to run net start mssqlserver /m /c /t3608 etc etc to put it in single user mode but found out there was an application connecting with the SA account blocking me from connecting. Finally after disabling TCP/IP I was able to get in. Now that we fixed the tempdb .mdf and .ldf file locations I would like to know how to find out what caused tempdb to grow after the fact. Please advise. We NEED to know what caused this. Thank you!
June 12, 2012 at 6:35 pm
Is there anything in the SQL error logs or Windows event logs that would give any indication what caused the uncontrolled growth?
Another thought if you know of any processes or workload happening around the time this occurred? (I noticed you mentioned an application was blocking you from using single user mode .. was that app causing growth of tempdb?)
I would interested in learning about what caused it?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 13, 2012 at 1:38 am
You will be lucky trying to find out what caused this unless you have some sort of custom monitoring tool which polls your server seeing that is going on.
If the server is fairly low usage, there is a possability that the default trace may still contain some of the growth events for tempdb, but I wouldnt hold my breath
Change below path to your instance's install directory.
SELECT *
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
You would of stood a better chance at finding out what caused it before you stopped and moved TempDB as you would of been able to query open transactions etc, but as TempDB is recreated on each restart of the services you have lost that ability.
June 13, 2012 at 7:35 am
Well it looks like there is no LOG directory and no .trc files anywhere. Why would the vendor set it up this way?
Before restarting the server and having tempdb recreated, I saved the original tempdb. Can I mount that mdf and ldf on my local server and take a look?
June 13, 2012 at 7:39 am
It wont help as you wont be able to query the data in any of the objects as SQL issues a unique number to the end of a temp table eg #temp________________5478547854 and you cant manually go into object explorer and right click select top 1000 rows on a tempdb object.
You can disable the default trace as its a user option to have it enabled or disabled, by default it is in the same location as the SQL Error logs, so if you know where these are you should get the default trace logs, do a windows explorer search for log.trc.
The information you needed was in the DMV's which are cleared out when SQL is restarted.
June 13, 2012 at 12:21 pm
I noticed you mentioned you couldn't find the error logs, that's odd. Is it possible the vendor that configured the SQL Server placed on a different drive that what normally is expected in your organization?
Have you tried checking your SQL Server configuration manager looking at the SQL Service, if you look at the tabs you you can see what start up parameters are used as well as location of error log.
Like wise you can check the regedit -- registry and traverse down HK_KEY_localmachine and find Micrsoft SQL Server, and the Error log path will be there.
Once you find it, the default trace file is located in that same folder.
Maybe you can identify when it started happening if you can find the error logs...root case will be bigger challenge... but maybe get you in the ball park? Maybe this will lead you somewhere. Since you want to avoid again it would be good to keep looking, until you hit a dead end.
Let me know how it goes?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
June 14, 2012 at 5:43 am
Vertigo44 (6/13/2012)
Well it looks like there is no LOG directory and no .trc files anywhere.
select * from sys.traces can give you trace file path
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 14, 2012 at 5:49 am
Try these queries
Determining the Amount of Free Space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
Determining the Longest Running Transaction
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS ,
(SUM(user_object_reserved_page_count)*1.0/128) AS
FROM sys.dm_db_file_space_usage;
Obtaining the space consumed by internal objects in the current session for both running and completed tasks
CREATE VIEW all_task_usage
AS
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
SELECT R1.session_id,
R1.internal_objects_alloc_page_count
+ R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+ R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
You can also set the profiler with filter on tempdb database ans see what running in it
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 14, 2012 at 7:32 am
this might be nice to collect off somewhere...
SELECT
cast(SUM(unallocated_extent_page_count) as bigint) as 'Free Unallocated',
cast(SUM(version_store_reserved_page_count) as bigint) as 'version store resv pages',
cast(SUM(user_object_reserved_page_count) as bigint) as 'user obj resv pages',
cast(SUM(internal_object_reserved_page_count) as bigint) as 'int obj resv pages',
cast(SUM(mixed_extent_page_count) as bigint) as 'mixed ext pages'
FROM sys.dm_db_file_space_usage
--
SELECT
R1.session_id,
R4.client_net_address,
R4.num_reads,
R4.num_writes,
R3.text
FROM sys.dm_db_task_space_usage AS R1
LEFT OUTER JOIN sys.dm_exec_connections AS R4 ON R1.session_id = R4.session_id
LEFT OUTER JOIN sys.dm_exec_requests AS R2 ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE R1.session_id > 50;
June 14, 2012 at 7:38 am
Rewrote that Create View sample code to be a CTE :w00t:
;WITH all_task_usage
AS (
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS tsk_intobj_alloc_pgcnt,
SUM(internal_objects_dealloc_page_count) AS tsk_intobj_dealloc_pgcnt
FROM sys.dm_db_task_space_usage
GROUP BY session_id
)
SELECT dssu.session_id,
dssu.internal_objects_alloc_page_count + atu.tsk_intobj_alloc_pgcnt AS ses_intobj_alloc_pgcnt,
dssu.internal_objects_dealloc_page_count + atu.tsk_intobj_dealloc_pgcnt AS ses_intobj_dealloc_pgcnt
FROM sys.dm_db_session_space_usage AS dssu
INNER JOIN all_task_usage AS atu ON dssu.session_id = atu.session_id;
June 14, 2012 at 7:43 am
This code will tell you if the default trace is enabled:
SELECT * FROM sys.configurations WHERE configuration_id = 1568 AND value_in_use = 1
If it IS enabled then this code will tell the name of the current default trace file:
SELECT [path] FROM sys.traces where is_default = 1
You can then query that trace file data for Event Class 92 and 93 ( Data File Auto Grow and Log File Auto Grow).
There is an excellent artice on the default trace with code at this link:
June 14, 2012 at 7:44 am
That's a nice query! Thanks!
June 14, 2012 at 12:19 pm
Open up a separate copy the saved tempdb.mdf in a hex editor (like HxD[/url]) in read only mode, and see if there's anything you recognize in it!
Alternately, for those that didn't save the file, you can use HxD on the drive itself in read only mode, not on a specific file.
Beware: if you write data to the drive, you can really cause yourself trouble.
Advanced version: Shut the system down, then make a sector by sector image of the entire disk, then turn the system on again and look at the image later.
June 14, 2012 at 4:53 pm
If you were running default trace and have the files, you may be able to see some atcitivity in tempdb. You can look for processes with sort warnings or hash warning if the growth was related to large sorts. In general, with patience, poking around the trace file may yield some info. Also look around the time when the fast tempdb growth started.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply