SQL server 2008 R2 standard edition dead lock log.

You can track deadlocks by enabling trace flags 1204 and/or 1222.

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

The tempdb issue is a bit stranger. The query select * from doesn't make sense, adn should return an error. I suspect that there is an issue with the reporting of the query - how are you identifying the query?

"adn should return an error. "

you didn't see this kind of error before, please share. I see this 3 times already on SQL server 2005, SQL 2008 SQL 2008 R2 and whenever it happens, the Tempdb full and SQL server dead.

"I suspect that there is an issue with the reporting of the query - how are you identifying the query? "

from activity monitoring. sometimes from:

dbcc opentran(tempdb)
go

and dbcc inputbuffer (<SPID>)

I really upset about that, why it keep happening to my case !!!

so we turn it on by typing :

DBCC TRACEON (1222 ,-1)
go
DBCC TRACEON (1204 ,-1)
go
?

and it is an one off operation or we need to do this everytime we start SQL server? it seems that we can add it to the startup parameter of SQL server? how ?

Is there any way log this kind of error in the SQL server log so that I can find out what is before and after the error ?

"As to tempdb - you need to get some more detail around what is causing the issue. One initial suggestion would be to examine the default trace and look for the file growth events. There's a couple of good scripts for this here:
"

I find no evident on this.. the default one just shows the server has been restarted, just it

what should do the "Select * from " then stop there forever and make the tempdB full ?

Restarting the server will restart the default trace. What is returned fro select * from sys.traces?

sqlservr,

this one will return all the active query and which DB it run against? and if we only need to see tempdb one and which login do this, we do:

SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle))
AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0 and dbid = 2 and loginame= '<SQL server login name>'

?

and the result of this query not necessary equal to

dbcc opentran(tempdb)
go

?
 
So from time to time the dbcc opentran(tempdb) result is not quite userful as it only show the oldest transcation?

EugeneZ,

We already runing SQL server 2008 R2 +SP2+ CU4.

"the result set has visible chars limit, the process details maybe cut
 if somebody ran

"select * from                                                              tablename""

this one is good and what if, developer from time to time will parse the query in that way:

select * from
xxx
where
yyy=0
and zzz='#'

for easlier reading.

it still show up like that? it will be too trouble for checking that out, right?

"so, additionally to the above posts run sql profiler "

What for ? please instruct with what kind of parameter we need to add.

MattSQL,

it only means that if we restart the SQL server each time, log will be overwrite and the old deadlock message will gone ?

No it means the most recent default trace will not have the autogrowth events.

We've already covered the deadlock trace flags - now we're focusing on the tempdb issues.

Please tell me what is returned from select * from sys.traces?

can this query:

"SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0 "

log down since when this query runs and how long it runs ?

MattSQL,

I can't see what information you interest on that query, something like that:

"<SQL path> \MSSQL\Log\log_2450.trc"

what interest you ?

check this one
SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0

I'm interested becasue the tempdb growths will be recorded in the default trace, so I wanted to try and construct a query for you to begin investigating these. Depending on how busy your server is and how long ago the last tempdb growth occured this information may be lost. But this query will return some information about sessions that caused any file growth:

select * from fn_trace_gettable('<SQL path> \MSSQL\Log\log.trc',default)
where eventclass = 92

MattSQL,

result is empty, what it means then ?

sqlservr,

we do this

SELECT spid, loginame, hostname as "From host", DB_NAME(dbid) AS "Against database" ,
(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) as "Query executed"
FROM master..sysprocesses WHERE login open_tran > 0 and dbid = 2
order by hostname

and it only shows latest active query running against tempdb, right? but it don't see much !

An empty result from that query, means too much time has elapsed since the last tempdb growth and those entries are no longer available in the default trace.