September 28, 2011 at 6:09 am
Hi Experts,
We have a critical issue that our tempDB log file is full.The drive is having only 9MB free space now.
i tried to shrink but no use.Checked the and log_resuse_wait_desc showing active transaction but was not able to find any active transaction on tempdb.
Tried to add a new log file on another drive but failed with error tempdb full..
Please help as we are not able to do anything on the server now..
September 28, 2011 at 7:19 am
You'll need to grow tempdb to gain a little space for the growth operation.
You can restart SQL Server as well to clear the tempdb db and recreate it.
September 28, 2011 at 9:52 am
Steve Jones - SSC Editor (9/28/2011)
You'll need to grow tempdb to gain a little space for the growth operation.You can restart SQL Server as well to clear the tempdb db and recreate it.
Thanks for the reply..
How can i grow the tempdb??As metnioned its not allowing to add a new file and the drive where the existing file resides have only 9MB free space.
I know the second option but not possible now.
September 28, 2011 at 9:58 am
You can close some connections and those objects will drop their tempdb space. Or cancel the transactions.
You could try adding 5MB to the existing file and then grow on a second drive. I'd do this in one script:
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 6144KB )
GO
ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'tempdblog2', FILENAME = N'g:\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdblog2.ldf' , SIZE = 10240KB , FILEGROWTH = 10%)
September 28, 2011 at 10:01 am
Just a note here, the active transaction may not be in tempdb. It's most likely in your app database and is just using tempdb and therefore wont show up with dbcc opentran there.
Check sysprocesses for spids with open_tran > 0, or do dbcc opentran on your other databases.
September 28, 2011 at 10:33 am
Steve Jones - SSC Editor (9/28/2011)
You can close some connections and those objects will drop their tempdb space. Or cancel the transactions.You could try adding 5MB to the existing file and then grow on a second drive. I'd do this in one script:
USE [master]GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 6144KB )
GO
ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'tempdblog2', FILENAME = N'g:\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdblog2.ldf' , SIZE = 10240KB , FILEGROWTH = 10%)
I already tried but was getting the same error.
September 28, 2011 at 10:36 am
Derrick Smith (9/28/2011)
Just a note here, the active transaction may not be in tempdb. It's most likely in your app database and is just using tempdb and therefore wont show up with dbcc opentran there.Check sysprocesses for spids with open_tran > 0, or do dbcc opentran on your other databases.
Thansk for the reply
There are 100 of process running in that server and how can i find those using tempdb??
Some are related to replication ,sql monitor etc..Is it possible to drop all connection in a go???
September 28, 2011 at 10:42 am
Ratheesh.K.Nair (9/28/2011)
Derrick Smith (9/28/2011)
Just a note here, the active transaction may not be in tempdb. It's most likely in your app database and is just using tempdb and therefore wont show up with dbcc opentran there.Check sysprocesses for spids with open_tran > 0, or do dbcc opentran on your other databases.
Thansk for the reply
There are 100 of process running in that server and how can i find those using tempdb??
Some are related to replication ,sql monitor etc..Is it possible to drop all connection in a go???
Well thats probably not something you'd want to do...
A good place to start would be here:
SELECT * FROM MASTER..sysprocesses
WHERE open_tran > 0
That will give you all the spids that have currently open transactions, and would currently be using tempdb space. Look for anything with a last_batch of around the time the problem started (or before), or with a huge amount of cpu/physical_io. Then, run dbcc inputbuffer(spid#) on each one to see if anything looks out of the ordinary, like someone accidentally not closing a transaction after an update, or someone missing a where clause.
September 30, 2011 at 3:06 pm
Ratheesh.K.Nair (9/28/2011)
There are 100 of process running in that server and how can i find those using tempdb??
Run this:
--http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#EX1AE
SELECT
R1.session_id
, R1.user_objects_alloc_page_count
, R1.user_objects_dealloc_page_count
, R1.internal_objects_alloc_page_count
, R1.internal_objects_dealloc_page_count
, R3.[text]
, S.[program_name]
, S.login_name
, S.[status]
, S.cpu_time
, S.memory_usage
, S.total_scheduled_time
, S.total_elapsed_time
, S.last_request_start_time
, S.last_request_end_time
, S.reads
, S.writes
, S.logical_reads
FROM
sys.dm_db_task_space_usage AS R1
INNER JOIN
sys.dm_exec_sessions AS S
ON
R1.session_id = S.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
S.is_user_process = 1
AND
(
R1.user_objects_alloc_page_count > 0
OR R1.user_objects_dealloc_page_count > 0
OR R1.internal_objects_alloc_page_count > 0
OR R1.internal_objects_dealloc_page_count > 0
OR R3.[text] IS NOT NULL
);
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply