April 24, 2007 at 2:30 pm
We have written a client application for SQL Server 2000. 99.9% of the time everything runs just dandy. But, every so often, say 3 times a week, SQL Server just comes to a dead stop -- nobody can do anything, nothing happens. We've tried to trace things, but nothing is reported. Then within a few minutes everything is back to normal and going strong. This client has many transactions occurring all the time throughout the day so these few minutes are the cause for much grief.
Anybody have any suggestions where I could look for reasons this is happening?
April 25, 2007 at 2:36 am
at "freeze" time ...
- are there backups running ?
- are rebuilds going on ?
- did you use sqlprofiler to capture the load
- how about deadlock situations (are startupparameters -T1204 and -T3605 active ?)
- Can you see locking activity ?
- are antivirus applications running ? (they can eat up your cpu)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2007 at 1:42 pm
Alzdba, thanks for the input. There are no backups or rebuilds going. SQLProfiler doesn't seem to report anything (though I'm not sure what I should be tracing), and I don't know how to test for deadlock situations, or what startupparameters T1204 or T3605 mean.
There doesn't appear to be any locking activity and there is no antivirus application running.
Do you have suggestions on how I should configure sqlprofiler to try to trap what's happening?
April 25, 2007 at 11:50 pm
You can configure a trace to run in background and wait for the issue to occur.
this script starts a script for a certain time and creates tracefiles of max 50Mb each. So keep in mind to monitor the targetdrive for the files.
You can analyse the traceinfo afterward with sqlprofiler or by importing it into a sqlserver table.
-- show Traces info
SELECT * FROM :: fn_trace_getinfo(default)
goto finish
declare @StartStopEnd varchar(5)
declare @TraceID int
declare @RunDays smallint
declare @RunHours smallint
declare @RunMinutes smallint
declare @PlanNY char(1)
-- standard 1 hour 15 minutes
Select @RunDays = 0
, @RunHours = 1
, @RunMinutes = 15
Select @StartStopEnd = 'Start', @TraceID = 0, @PlanNY = 'N'
-- Select @StartStopEnd = 'Stop', @TraceID = 1
-- Select @StartStopEnd = 'End' --, @TraceID = xxx
if upper(@StartStopEnd )= 'START' goto StartTrace
else
begin
if upper(@StartStopEnd )= 'STOP' goto StopTrace
else
begin
if upper(@StartStopEnd )= 'END' goto CleanupTrace
else
begin
Print 'Ongeldige @StartStopEnd kode [' + @StartStopEnd + ']'
goto finish
end
end
end
goto finish
StartTrace:
-- Create a Queue
declare @rc int
-- declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = dateadd(dd,@RunDays,dateadd(hh,@RunHours,dateadd(mi,@RunMinutes,getdate())))
-- MB
set @maxfilesize = 50 -- Mb
Declare @TraceFileName nvarchar(300)
set @TraceFileName = 'C:\ALZDBA_SQL_Trace' + '_' + replace(@@servername,'\','_') + '_' + replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') -- + '.trc' wordt automatisch toegevoegd
if datalength(@TraceFileName) > 299
begin
print 'Filename to long ! [' + cast( datalength(@TraceFileName) as varchar(5)) +'] - [' + cast(@TraceFileName as varchar(300)) +']'
goto finish
end
print '-- Saving tracedata to : ' + cast(@TraceFileName as varchar(300)) + ' --'
print '-- **********************' + REPLICATE('*', datalength(@TraceFileName)/2) + ' --'
print ' '
print '-- Trace will end at ' + convert(varchar(23),@DateTime,121) + '. --'
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, @TraceFileName, @maxfilesize, @Datetime
if (@rc <> 0) goto error
-- Set the events
declare @on bit
set @on = 1
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 10, 3, @on -- DbId
exec sp_trace_setevent @TraceID, 10, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 10, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 10, 8, @on -- ClientHostname
exec sp_trace_setevent @TraceID, 10, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 10, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 10, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 10, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 10, 31, @on -- Error
exec sp_trace_setevent @TraceID, 10, 40, @on -- DatabaseUsername
-- 11 = RPC:Starting
exec sp_trace_setevent @TraceID, 11, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 11, 3, @on -- DbId
exec sp_trace_setevent @TraceID, 11, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 11, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 11, 8, @on -- ClientHostname
exec sp_trace_setevent @TraceID, 11, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 11, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 11, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 11, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 11, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 11, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 11, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 11, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 11, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 11, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 11, 31, @on -- Error
exec sp_trace_setevent @TraceID, 11, 40, @on -- DatabaseUsername
-- 12 = SQL:BatchCompleeted
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 31, @on -- Error
exec sp_trace_setevent @TraceID, 12, 40, @on
-- 13 = SQL:BatchStarting
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 15, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 31, @on -- Error
exec sp_trace_setevent @TraceID, 13, 40, @on
-- 14 = Login
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 31, @on -- Error
exec sp_trace_setevent @TraceID, 14, 40, @on
-- 15 = Logout
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 31, @on -- Error
exec sp_trace_setevent @TraceID, 15, 40, @on
-- 17 = ExistingConnection
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 15, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 31, @on -- Error
exec sp_trace_setevent @TraceID, 17, 40, @on
if @PlanNY = 'Y'
Begin
-- 68 = obtain the execution plans
exec sp_trace_setevent @traceid, 68, 1, @on --Execution Plan, TextData
-- exec sp_trace_setevent @traceid, 68, 2, @on --Execution Plan, BinaryData
exec sp_trace_setevent @traceid, 68, 3, @on --Execution Plan, DatabaseID
-- exec sp_trace_setevent @traceid, 68, 4, @on --Execution Plan, TransactionID
exec sp_trace_setevent @traceid, 68, 6, @on --Execution Plan, NTUserName
exec sp_trace_setevent @traceid, 68, 7, @on --Execution Plan, NTDomainName
exec sp_trace_setevent @traceid, 68, 8, @on --Execution Plan, ClientHostName
-- exec sp_trace_setevent @traceid, 68, 9, @on --Execution Plan, ClientProcessID
exec sp_trace_setevent @traceid, 68, 10, @on --Execution Plan, ApplicationName
exec sp_trace_setevent @traceid, 68, 11, @on --Execution Plan, SQLSecurityLoginName
exec sp_trace_setevent @traceid, 68, 12, @on --Execution Plan, SPID
exec sp_trace_setevent @traceid, 68, 13, @on --Execution Plan, Duration
exec sp_trace_setevent @traceid, 68, 14, @on --Execution Plan, StartTime
exec sp_trace_setevent @traceid, 68, 15, @on --Execution Plan, EndTime
exec sp_trace_setevent @traceid, 68, 16, @on --Execution Plan, Reads
exec sp_trace_setevent @traceid, 68, 17, @on --Execution Plan, Writes
exec sp_trace_setevent @traceid, 68, 18, @on --Execution Plan, CPU
-- exec sp_trace_setevent @traceid, 68, 21, @on --Execution Plan, EventSubClass
-- exec sp_trace_setevent @traceid, 68, 22, @on --Execution Plan, ObjectID
-- exec sp_trace_setevent @traceid, 68, 25, @on --Execution Plan, IntegerData
-- exec sp_trace_setevent @traceid, 68, 27, @on --Execution Plan, Eventclass
exec sp_trace_setevent @TraceID, 68, 26, @on
exec sp_trace_setevent @TraceID, 68, 31, @on -- Error
exec sp_trace_setevent @TraceID, 68, 40, @on
END
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, @status = 1
-- display trace id for future references
-- select TraceID=@TraceID
print '-- '
Print '-- TraceID for this trace [' + cast(@TraceID as varchar(10)) + '] --'
Print '-- ************************' + REPLICATE('*', datalength(cast(@TraceID as varchar(10)))) + '* --'
-- show Traces info
SELECT * FROM :: fn_trace_getinfo(default)
goto finish
StopTrace:
-- Set the trace status to stop
exec sp_trace_setstatus @TraceID, @status = 0
print 'Trace Stopped.'
goto finish
CleanupTrace:
-- Set the trace status to cleanup
exec sp_trace_setstatus @TraceID, @status = 2
print 'Trace Cleaned up.'
goto finish
error:
select ErrorCode=@rc
finish:
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2007 at 11:58 pm
another thought ...
At freeze time, is it only the sqlserver instance that's frozen or is the whole server frozen ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 26, 2007 at 10:26 pm
Alzdba, THANKS SO MUCH FOR THE SCRIPT!
I'm going to give it a try tomorrow during the times when the freeze sometimes occurs and I'll let you know what I find.
And, in answer to your last question, no, the whole server isn't frozen. At least not in the sense that I'm able to start a terminal session to it, and poke around some -- which is almost always too late. During those times when I can log in and try to snoop around, SQL starts going again before I can try anything.
Thanks again!
April 27, 2007 at 6:32 am
Hi JS,
Another thing you might look for is autogrowth of files. If you have a large data or log file, and this file is set to autogrow (the default) and the autogrow amount is 10% (also the default) you can find your SQL server pausing while it grows the file.
Say you have a 100GB file, with the default autogrow settings. When the file is full, the next attempt to write data there will cause the file to grow by 10GB before the insert can happen. This can take a while and it might cause the symptoms you are describing.
April 27, 2007 at 8:42 am
Thanks Jeff!
I had checked the auto-shrink wondering if that was it -- it wasn't -- but, you're right, the autogrowth is turned on and the file is big and it is set for the 10% growth. I'll check that today too!
April 27, 2007 at 10:12 am
Have you checked to see if there are any scheduled jobs that run during these lockups, both SQL and non-SQL jobs? Do these lockups coincide with anybody's daily routines? Can you run sp_who2 during these lockups? If so, do you see any blocking?
April 27, 2007 at 10:15 am
I would also recommend that you run SQLDiag at the time of freeze up. This will help determine blocking/locking issues and wait types as well as much other useful info.
April 27, 2007 at 10:35 am
I'm not familiar with SQLDiag. Is that an executable, a SPROC, something I need to download? It sounds like it would be a great tool.
April 27, 2007 at 11:22 am
It is an executable that gets installed with SQL Server. It will be in your SQL Server installation directory's Binn folder. Just run SQLDiag.exe. It takes just a minute or two and takes a snapshot of sorts inclusing blocking/locking, input buffer info, sp_who, etc.. The output file will be in the Log folder. If you run it and get a buffer overrun error, there is a Microsoft hot fix for this for SP4. If you get that error, I can post the KBase # here for you.
April 27, 2007 at 11:50 am
I thought this utility only came with SQL server enterprise edition.
John Cutsinger
Altegris Investments
April 27, 2007 at 11:58 am
I'm not sure. Books on line does not say this. Microsoft's MSDN page says it is new to SP3: http://msdn2.microsoft.com/en-us/library/aa214020(SQL.80).aspx
May 6, 2007 at 11:19 am
Hi There...
Maybe this link can help, it does report the
locking activity on your sql, simple sp that
can be run as a schedule trace.
How to monitor blocking in SQL Server 2005
and in SQL Server 2000
http://support.microsoft.com/kb/271509/EN-US/
And how is your Server HW/Specs?
If you have enough power (multiple processor/4,
Gigs of RAM/4G, SAN Storage/RAID,
you can run the windows perf monitor
as well to check the server activity (processor load,
memory used, disk queue, and related sql server perf
counter(running sql lock waittime,disk read/write
bytes per second, etc).
Test the sql trace above and the perf monitor on
the idle time to measure your system performance
on low usage, and set the appropriate time delay
for both tools to match your system condition and
capacity.
Have Fun !!!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply