Solved

database hang

Posted on 2008-06-21
6
659 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now