Solved

database hang

Posted on 2008-06-21
6
675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 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