2

Weird things happened lately on one of my SQL Servers:

  1. Select * from View never ends, but the SQL queries inside the view runs well separately.
  2. ALTER procedure never ends.
  3. DELETE procedure cause Error:1222, which suggests a lock from something
  4. 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.

migrated from stackoverflow.com Jul 8 '15 at 7:53

This question came from our site for professional and enthusiast programmers.

  • Peraphs you've one or more tables locked. When you reboot the server lock are destroyed – Joe Taras Jun 18 '15 at 14:54
  • 1
    Memory cleared, TempDb re-initialization, lock destroyed, buffer cleans when you reboot SQL Server. You should consider updating stats on regular interval – Anuj Tripathi Jun 18 '15 at 15:15
4

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 <> 0
    and pr.spid >= 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.

  • No problem. Nice to help you. :-) – Ionic Jun 18 '15 at 17:31
1

SQL Server is a transactional based multi-user system. Rebooting can kill transactions maybe gracefully but at Reboot open transactions are usually rolled back. In the future rebooting should be done as a last resort.

  • Well not just rolled back. Under certain circumstances they are rolled forward too. – Ionic Jun 18 '15 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy