Solved

database hang

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

724 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