SQL Server: what is cleared by reboot? Weird things happened lately on one of my SQL Servers:


Select * from View never ends, but the SQL queries inside the view runs well separately.
ALTER procedure never ends.
DELETE procedure cause Error:1222, which suggests a lock from something
Multiple SQL jobs never end once started.(killed manually after run for 3+ days)


I had a feeling that something was not right with the SQL server, so I rebooted the server and everything was fixed. 

I still don't know what went wrong. It is like SQL Server wore out. I am wondering if anyone had similar experience or can point me some reading about what happen during the process of rebooting.
You can run this script if it happens again:

SELECT pr.spid, pr.loginame, pr.status, pr.blocked, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.blocked &lt;&gt; 0
    and pr.spid &gt;= 50


It should give you a list of processes which were blocked. After that you can try to find the process which blocks all of these processes. You can try (if you have the right for it) to kill the process using kill yourSPID.

But beware, just try the kill if the spid is above or equal to 50! Otherwise it's an internal process!

After a restart every process is either rolled forward or rolled back. Your tempdb is recreated and all internal processes are restarted.