Example using undocumented procedure xp_readerrorlog

xp_readerrorlog_demo.sql

--
--	xp_readerrorlog @p1, @p2, @p3, @p4
--	based on http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql
--
--	@p1		0 = current, 1 = archive numeber 1 etc
--	@p2		Log type: 1/NULL=SQL Server, 2=SQL Server Agent
--	@p3		First search string
--	@p4		Second search string
--	@p5		Search start time
--	@p6		Search end time
--	@p7		Sort order 'asc' or 'desc'

--	Search tempdb full spids after restart
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
	DROP TABLE #temp;
CREATE TABLE #temp (LogDate datetime not null, Processinfo sysname not null, Text nvarchar(max));

INSERT	#temp
EXEC master.sys.xp_readerrorlog 1, 1, 'The transaction log for database ''tempdb'' is full';

SELECT	DISTINCT REPLACE(ProcessInfo, 'spid', '') AS spids
FROM	#temp
WHERE	ISNUMERIC(RIGHT(ProcessInfo, 1))=1
GO