Solved

database hang

Posted on 2008-06-21
6
669 Views
Last Modified: 2008-09-14
Hi
i use sql server 2000 as a backend for my hospital database. i have two databases. one databse is hanging up every 48 hours or sometimes every 24 hours. i have to restart the MSSQLSERVER servive everytime it will be ok again it reoccurs .no errors are reported in event viewer.users are able to connect to database but they cannot save anything into the database.

other database in the same server is working fine.
is there any issue with the logs.

any help
0
Comment
Question by:abhidarsh
  • 2
6 Comments
 
LVL 1

Expert Comment

by:sgerling
ID: 21839632
Could you provide a little more information on what exactly happens when you say it's hanging up?  

Also, have you checked for blocking?  This is a very common reason for SQL to lockup and would be resolved by restarting the service, although I would not recommend doing that for a simple blocking issue.
0
 

Author Comment

by:abhidarsh
ID: 21840068
users on the client side cannot save anything into the database.when the client complains me i would restart the service and again they are able to work normal.This problem suddenly started from one week. before everything was fine.

How do i check this blocking.Also when i restart the service i see series of information events in event viewer where i observed four events for other database (which works fine) related to locks and recovery . But i do not see any events related to problamatic database after i restart the service although it works fine for 24-48 hours.
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 250 total points
ID: 21840608
Restarting SQL Server is definitely not a blocking solution. All that is going to do is kill all processes doing any work and restart without any blocking chains. Check out this article which describes in detail blocking and has links for more details about SQL 2000 and SQL 2005 and the various methods for properly diagnosing and resolving blocking:

http://support.microsoft.com/kb/271509

Now this is not necessarily the cause of the issues, just commenting on that thought and it could very well be what is happening. Using the tools above you should be able to identify blocking causing it.

So the next time the app starts hanging you need to do some identification of the issue:

1.) Run SP_WHO2. Do you see any non 0/non-null Blocked columns? This means you have blocking as the above article will describe.

2.) Run SELECT * FROM MASTER.DBO.SYSPROCESSES WHERE SPID > 50 AND WAITTIME > 0
Do you see any results? This means there are processes waiting (processes with a spid of 50 and less are system processes and they wait quite a bit by design so don't look at those). What are the wait types if you do see them? This should point you in a direction (http://blogs.msdn.com/arvindsh/archive/2006/06/10/625364.aspx will link to information about wait types/times)

3.) Look at your CPU Utlization, Memory Availability, Disk Queue Length, Paging and other performance monitor counters (check out: http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx for more info on SQL Server specfic performance counters). Do you see anything that indicates any sort of issue there?

4.) Look at the SQL Server Error Logs for that timeframe, the Event Viewer for application and system errors and warnings.

Basically follow a methodical troubleshooting approach and the above items should help point to a cause.
0
 
LVL 1

Assisted Solution

by:sgerling
sgerling earned 250 total points
ID: 21840879
In addition the above, here is a stored procedure that should help you easily identify blocking.  Execute this code in your database and it will create a stored procedure.  Once that is complete, you can type the stored procedure name (block_checking) into Query Analyzer and execute it.


create proc blocking_check
as
declare @realblock int, @id int, @block int
 
select @block= 0
Select 	@realblock = min(s1.spid)
FROM
	master..sysprocesses s1, master..sysprocesses s2	
where s1.spid = s2.blocked and s1.blocked = 0
if @realblock > 0
begin	select @block = 1
	select @realblock
	dbcc inputbuffer(@realblock)
end
select @id = min(blocked) from master..sysprocesses
if @id > 0 and @id <> @realblock
begin	select @block = 1
	select @id
	dbcc inputbuffer(@id)
end
select @id = max(blocked) from master..sysprocesses
if @id > 0 and @id <> @realblock
begin	select @block = 1
	select @id
	dbcc inputbuffer(@id)
end
 
if @block= 0
begin
	select 'No blocking found'
	return
end
 
 
Select distinct 
	s1.spid,
	s1.blocked 'block by',
	s1.status, s1.program_name, s1.loginame
FROM
	master..sysprocesses s1, master..sysprocesses s2	
where s1.spid = s2.blocked
order by s1.blocked desc, s1.spid
 
select req_spid,object_name(rsc_objid) 'object', 
	INDEX_COL(object_name(rsc_objid), rsc_indid, 1) 'index',
	(case when rsc_type = 2 then 'Database'
		when rsc_type = 3 then 'File'
		when rsc_type = 4 then 'Index'
		when rsc_type = 5 then 'Table'
		when rsc_type = 6 then 'Page'
		when rsc_type = 7 then 'Key'
		when rsc_type = 8 then 'Extent'
		when rsc_type = 9 then 'Row ID'
		when rsc_type = 10 then 'Application'
		else '?' end) 'Mode', 
	(case when req_mode = 0 then '0'
	when req_mode in (1, 2) then 'Schema'
	when req_mode = 3 then 'Shared'
	when req_mode = 4 then 'Update'
	when req_mode = 5 then 'Exclusive'
	when req_mode = 6 then 'Intent Shared'
	when req_mode = 7 then 'Intent Update'
	when req_mode = 8 then 'Intent Exclusive'
	when req_mode = 9 then 'Shared Intent Update'
	when req_mode = 10 then 'Shared Intent Exclusive'
	when req_mode = 11 then 'Update Intent Exclusive'
	else convert(varchar(5),req_mode) end) 'type'
	,count(*) 'lock count' 
	from master..syslockinfo
where rsc_dbid = db_id() and rsc_type <> 2
group by
req_spid, object_name(rsc_objid), 
	INDEX_COL(object_name(rsc_objid), rsc_indid, 1),
	
	(case when rsc_type = 2 then 'Database'
		when rsc_type = 3 then 'File'
		when rsc_type = 4 then 'Index'
		when rsc_type = 5 then 'Table'
		when rsc_type = 6 then 'Page'
		when rsc_type = 7 then 'Key'
		when rsc_type = 8 then 'Extent'
		when rsc_type = 9 then 'Row ID'
		when rsc_type = 10 then 'Application'
		else '?' end), 
	(case when req_mode = 0 then '0'
	when req_mode in (1, 2) then 'Schema'
	when req_mode = 3 then 'Shared'
	when req_mode = 4 then 'Update'
	when req_mode = 5 then 'Exclusive'
	when req_mode = 6 then 'Intent Shared'
	when req_mode = 7 then 'Intent Update'
	when req_mode = 8 then 'Intent Exclusive'
	when req_mode = 9 then 'Shared Intent Update'
	when req_mode = 10 then 'Shared Intent Exclusive'
	when req_mode = 11 then 'Update Intent Exclusive'
	else convert(varchar(5),req_mode) end)
order by 1 desc
 
GO

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question