Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Causes For Latch Wait Time

I setup a SQL Agent to send me an email when the Average Latch Wait Time is greater than 300ms. Now I receive an email every 15 seconds stating that the current ALWT is 3916ms. That value never changes with the emails. However, the perfmon shows nothing at all (shows zero).

I also have a Buffer cache hit ratio of 2848.00.

These numbers are when there is NOBODY on the DB at all It is just sitting there. When I reboot the server, as soon as SQL starts it starts to send the emails again.

Server: Intel Xeon Quad Core 2.66
RAM: 4GB (with /3GB in the boot.ini)
RAID 1: Data (DB and logs)
CPU Utilization: 0-1%
RAM Utilization: 527MB
OS: Server 2003 R2 With SP2
SQL: 2005 Standard with SP2

How can I determine if the ALWT is really 3916?

I executed 'Select * from sysprocesses where SPID>50 and waittime>0'

Which showed;

program_name=DatabaseMail90 - Id<3780>

Any assistance is greatly appreciated.

Thank You


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Latch Time
Hi All,

This might be a bit of a question like "how long is a peice of string" however here goes.....

What is considered to be a high average latch time?

We have a SQL2000 machine which has:
700-900 ms average latch time
100% Buffer Cache Hit Ratio
No memory pages / second
Very low (almost always below 5%) cpu usage
Next to no disk transfers / sec
0 average lock time

I am surprised that the latch time is so high. Any ideas, whether I should be looking at something in particular?

Cheers in advance.

View Replies !   View Related
WARNING: EC 259683c0, 0 Waited 32100 Sec. On Latch 807133b0. Not A BUF Latch.
During a bulk insert I am receiving this error message in the sql log. It appears to have caused the server to reboot the other night..

I am running SQL 2000 8.00.760 (Service Pack 3)

2004-04-07 12:57:52.82 spid8 WARNING: EC 259683c0, 0 waited 32100 sec. on latch 807133b0. Not a BUF latch.
2004-04-07 12:57:52.82 spid8 Waiting for type 0x4, current count 0xa, current owning EC 0x70899570.

Any ideas???



View Replies !   View Related
Time Out Occurred Waiting For Buffer Latch??
Hi Guys,

One of your SQLSERVER 7, Server throws the below error message into the log file. It won't go away until the server get rebooted. When this error start showing up.. customers have hard time connecting to the Server, eventually we have to reboot the server.

Time out occurred while waiting for buffer latch type 1, bp 0x18b9200, page (1:50859), stat 0xb, object ID 17:1954106002:1, waittime 500. Continuing to wait.

Any help is greatly appericated.


View Replies !   View Related
Could Not Read And Latch Page (1:1370) With Latch Type SH
Ben writes "Dbcc checkdb showed a database to have consistency errors, however running repair_allow_data_loss resulted in the following error

Server: Msg 8966, Level 16, State 1, Line 2
Could not read and latch page (1:1370) with latch type SH. sysindexes failed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please could you tell me how to fix it, and how it occurred in the first place?


View Replies !   View Related
Cumulative Wait Time On Server Replies ???
Hello all,I've got a query which suddently became very slow. It now takes about 10secs instead of 2 secs.I've got to identical DB (one is for test and the other is production). Thequery is slow only in production.When running this query in both DB and looking at execution plan,statistics, etc, the onle difference is the Cumulative wait time on serverreplies.In test DB, I get the value: 2200And in production DB: 1.22344e+009What does this mean concretly? What do I have to do to solve this problem?TIA.YannickPS I'm using SS2000 SP3 on NT4.0

View Replies !   View Related
Perflib 2002 SQLCTR60.DLL Wait Time
The following message is constantly appearing in the event viewer application log ;

Perflib 2002 The open procedure for service "MSSQLServer" in DLL "SQLCTR60.DLL"
has taken longer than the established wait time to complete.
The wait time in milliseconds is shown in the data.

Can anyone please advise ?

View Replies !   View Related
Help On Buffer Latch Timeout
Time out occurred while waiting for buffer latch type 2,bp 0x18b7d40, page 1:11558916), stat 0xb, object ID 9:1842105603:2, EC 0x5862D9C8 : 0, waittime 300. Not continuing to wait.

What does this mean any reason and fix for it..?


View Replies !   View Related
What Is The Latch Waits/sec Value On Your Servers....
Hi DBAs,

just wondering what are the values on your SQL servers for these counters:

Average latch wait time (ms)
Latch Waits/sec

We set up two servers, both are quad machines (4 cpus) with SANs devices as a HD space (RAID5) that operate via fiberchanels (can't be faster).
Both server have 4 Gb of RAM. Both have SQL2000 Ent and Win2000 Advance Server. The only difference between them is that the first one is clustered the second one is not, so.

The clastered Server has values:

Average Latch Wait time - 2782.285
Latch Waits/sec - 62.065

The non clastered Server has:

Average Latch Wait time - 550.108
Latch Waits/sec - 21

There are two similar databases located on them and the same application (two separate instalations) with a similar load utilizes the DB.

I wounder whe could be a reason for such a different values?


View Replies !   View Related
MSG 845, Buffer Latch Type 2
I have been looking at Books Online and I'm trying to figure out how I can resolve this error.

MSG 845, Level 17, State 1
Time out occured while waiting for buffer latch type 2 for page.....


View Replies !   View Related
Error 845: Buffer Latch Timeout
More of an FYI in case anyone has run into this problem...We were running into the infamous "timeout on buffer latch type 3" error andwas caused by the autogrow in tempdb. Apparently when SQL Server is gettinghit pretty hard and tempdb is taking a timeout to auto-grow, SQL servercraps its pants and times-out. This combined with the fact that tempdbshrinks down to its last set size after a restart, you'll hit this errorpretty regularly after a SQL Server bounce. (If tempdb auto-grow from a set1GB to 10GB, after the restart tempdb will be 1GB again. - Just how gay isthat?) Anyway, we just manually set tempdb to a respectable level to resolvethe problem.

View Replies !   View Related
DBCC CheckDb Latch Errors
we''re currently performing a dry run of migrating some large (100GB+) SQL Server 2000 databases from a SQL 2000 cluster onto a new 2005 mirrorred database pair.  The new configuration has completely separate database servers and a new dedicated SAN.
Upon restoring our largest database we are seeing the following errors recorded in the log after setting up mirroring and then trying to run DBCC CHECKDB:
"Unable to read and latch page (1:1323852) with latch type SH. 21(The device is not ready.) failed."
We are currently investigating the SAN hardware logs to determine if this might be a physical issue as it would seem to indicate, however has anyone any experience of this or insight into what may cause this error?
Thanks in advance!

View Replies !   View Related
Recovering Data From Broken Tables (latch Errors)

Is it possible to generate a script, that extracts every bit of data it can, from tables which suffers from latch errors;

Msg 7985, Level 16, State 2, Line 1

System table pre-checks: Object ID 4. Could not read and latch page (1:403) with latch type SH. Check statement terminated due to unrepairable error.

So bad news on that one. There are no backup available that I know off, so all bets are off on that part of the recovery process.

The "good news" part of the problem, is that it is possible to extract some data out of the table, before SQL server 2005 returns the error. So what I want to do, is get every bit out I can, doing it somehow automatically, so I don't have to run bcp on 180 tables :).

Presumeably, this is quite possible, but how?

NB. If I do a complete basic SELECT * FROM tbl1, it returns data, then errors out, but what about the data after the last row SELECT returns? Is it possible, that there might be data in the table that are recoverable after the 'broken row(s)'?

View Replies !   View Related
I am running into problems while running a large procedure, and i think it may have something to do with a PAGEIOLATCH_SH wait problem.
My server, whose sole purpose is to run this one procedure, is doing plenty of disk i/o, and the CPU’s bouncing around, so I assume it’s working. But when I look at its process info, it seems to be sleeping a lot of the time on PAGEIOLATCH_SH. No other users are in the DB, so I'm quite confused. I don't find much info on this anywhere, so any insight would be very appreciated.

View Replies !   View Related
Wait Type
If I get pageiolatch_sh wait type periodically, what does it mean ? Is
tuning required ?


View Replies !   View Related
Event Wait
I'm new to SQL Sever 2005 and I'm trying to do what Informatica (Power Center - ETL) is trying does.

I have created a work flow and it is scheduled to run at every night 1:00 AM .The process is to load a flat file ( into the database from a shared location.The flat file is transfered from a 3rd party and once the file transfer is complete it will create a indicator file (0 byte eg: CRV.DONE file) which indicates the transfer is complete.

In my workflow I will be waiting for the CRV.DONE indicator file and once it is avaiable I will start loading the and once the load is completed I will delete CRV.DONE file and be ready for the next day load.

Please let me know if there is any way in SQL Server 2005 to achieve it.Thanks

View Replies !   View Related
Wait For A Response?
Hi.  We are migrating a mainframe datacom database to SQL Server.  One of our client-server applications already uses SQL Server.  This application uses a middleware product to query and update the datacom database being migrated.  We are considering using Service Broker to replace the middleware. 

In many cases the client does not need a response provided the message is queued and will eventually get delivered.  However, in some cases the client would like to wait for the message to be processed before proceeding.  Is there an easy way to both submit and optionally wait for a response - with data - in a single stored procedure?  If client does not want to continue to wait, is there a way to use a procedure to check for the returned message later? 

We have not used Service Broker before and are doing for a "sanity" check before proceeding.  We do not want to tightly couple the two databases at this time.  


View Replies !   View Related
Wait Problem...
I have installed performance dashboard on 2 different servers. The first server have User Session CPU Time 71% and Wait Time =28%, The other server have Cpu Time of 20% and Wait Time of 79%. Have I understand that stands in SQL Server Waits And Queues that I have some typ of wait problem in my second server? 

Then I tries to run this

'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)),
'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats

First Server
%signal waits                           %resource waits
--------------------------------------- ----------------------------
0.07                                    99.93
Second Server
%signal waits                           %resource waits
--------------------------------------- ----------------------------
0.12                                    99.88
Messy in my head€¦ Help please

My second server have

CLR with 50% in the historial wait. Rest in Sleep

My first server have
99% in sleep wait category.


View Replies !   View Related
&"Waiting For Buffer Latch&" Error
Does anybody know what might cause the following message to show up inthe SQL Server Error Log?:Time out occurred while waiting for buffer latch type 2, bp0x12260f80, page (5:77914), stat 0x40d, object ID 7:421576540:0,waittime 500. Continuing to wait.I've read several articles about what to do about this situation onSQL Server 2000, but I'm running SQL Server 7.0. Specifically, I'mrunning version 7.00.842. Is there a way to resolve this problemwithout upgrading to some flavor of SQL Server 2000?

View Replies !   View Related
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.

Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.

View Replies !   View Related
Sql Server Wait Interface?
Oracle has a 'wait interface' for monitoring bottlenecks, etc... does SQLServer have utilities like this?

View Replies !   View Related
Sysprocesses Says: Wait Forever
I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.

Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

What should I do?

View Replies !   View Related
I have an ASP.NET web application that hangs on a single database UPDATE command for 5+ minutes. I can see this occur in SQL Profiler. This is a one row UPDATE statement on a small table (~600 rows). There are no JOINs or sub queries. There are no other users using the system. During this 5+ minutes, I can see the job in Enterprise Manager with a wait type of NETWORKIO. Since both IIS And SQL Server are running on the same system, the network shouldn't be an issue. Any ideas?

View Replies !   View Related
Using BACKUP From OSQL And Wait For It's End
I'm writing a small vbscript to backup a db and some related files, so I used a WSShell calling OSQL to run a Sql BACKUP command, then after it's finished I XCOPY the resulting file plus some other related files. But the problem is that OSQL ends it's execution as soon as the BACKUP command is sent to SqlServer, not when the backup itself ends.
Anyone knows how to synchronize the two? How to wait, inside OSLQ, for the end of the BACKUP execution?

View Replies !   View Related
&#34;wait Type&#34; Exchange
What is a "wait type" exchange. It is listed under wait type.

View Replies !   View Related
How To Wait For File Before Continuing
Good morning,

I will start off with the default warning message: I am a beginner.  That said, I have an SSIS process that calls an external executable to transform a data file through a homegrown C program.  (This will eventually be converted, but for the moment needs to remain.) The end of the run creates a *.done file.  How do I use the SSIS tasks to pause/wait, checking periodically, for the existence of this file before continuing with processing tasks?  I apologize if this is easy, but I am stumped.

Thanks in advance,

SQL 2005
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

View Replies !   View Related
Check Condition And Wait

I have a data-flow-task that imports data to sqlserver.

Now I want to check, if a special column of an imported record is null.

If yes, I have to wait 10 minutes and jump to the data-flow-task again. (Cjeck and wait).

How can I do this with the integration services?




View Replies !   View Related
SSIS Job To Wait And Run After AS400 Job

We have a few SSIS jobs that we are currently manually kicking off after we are sure that certain AS400 jobs have run.  We want to completely automate this process, so that we don't have to babysit.  What is the most efficient way to do this?  In the past (on SQL Server 7 no less) I've seen the 400 job setting a flag to 'Y' in a 400 file, FTPing it down to a flat file, and then the SQL job running every five minutes checking the flag.  When it was 'Y', the SQL job would run.  We do not have the option of using FTP here.  Any suggestions would be appreciated!  After the job runs, we'd like it to kick off a report as well

View Replies !   View Related
Wait For Incoming CSV Files
are there any tasks in SSIS that watches a folder and waits for incoming files?

View Replies !   View Related
Async_network_io Wait During Replication
I have re-initialize some subscription.  After generated snapshot successfully, the synchronization view showing 'The process is running and is waiting for a response from the server.'  and I found the replication process is under 'async_network_io wait'.  The job has run for more than 8 hrs.  Any idea to improve it?  Thanks in advaise.

View Replies !   View Related
Need To Exec TSQL And Then Wait 60 Sec And Run Again
 I am tring to fiqure out how i can run a TSQL and the have it start again 60 sec after it compleats, without me have to push the button.  I just need it to loop over and over until my data is deleted.  I have to do it this way so my site will still allow customers to login and I need the break so they can.  Any help would be great.

View Replies !   View Related
SQL Start /wait Command?
So, I'm fairly new to SQL, and I'm working with a SQL2k5 Database with pre-made packages and what-not.  This database was setup before I started this job, and now I'm trying to improve part of the processing in SQL, and so far so good, but I can't figure a couple things out. 

The main problem is when I start a SQL command to launch a DTS package from a .sql file, how can I make it wait for the package to complete or fail before moving onto the next part of the .sql script?  Hope it's a simple question, I've just taught myself enough SQL to get by in a couple of weeks.


View Replies !   View Related
Wait Type Descriptions/Meanings
Does anyone know where I could find a dictionary of the waittypes that SQL 7 displays in the process monitor?


View Replies !   View Related
Current Acitvity - Wait Types
When I run some of the quiries, in current activity, wait type would say:
PAGEIOLATCH_SH what does it means ? Is there a source I can see all
types of wait types ?

Help is much appreciated.

View Replies !   View Related
Poll Filesystem To Wait For File
Stored procedure that polls a specific folder for a given file
once a minute a given amount of time. If/when the file is found
the filename is prefixed with the current date/time and is left
in the same folder. If the file isn't found after the predefined
number of minutes the procedure throws an error. Executed like this:

EXEC database.dbo.WaitForFile
@FilePath = 'c: emp',
@FileName = 'file.txt',
@WaitMinutes = 60 --> Poll every minute for 60 minutes

This procedure depends on access to the system stored procedures
xp_fileExist and xp_cmdshell and proper permissions on the disks
where the files reside.

Procedure written by:
Henning Frettem (aka Lumbago, 2007-10-03

@FilePath VARCHAR(200),
@FileName VARCHAR(200),
@WaitMinutes INT

@FilePathFull VARCHAR(200),
@FileExists INT,
@Counter INT,
@ErrMsg VARCHAR(400),
@cmd VARCHAR(400)

SET @FilePathFull = @FilePath + @FileName
SET @FileExists = 0
SET @Counter = 0

EXEC master.dbo.xp_fileExist @FilePathFull, @FileExists OUTPUT

WHILE @FileExists = 0 AND @Counter < @WaitMinutes
SET @Counter = @Counter + 1
WAITFOR delay '00:01:00'

EXEC master.dbo.xp_fileExist @FilePathFull, @FileExists OUTPUT

IF @FileExists = 0
SET @ErrMsg = 'FileWait for ''' + @FilePathFull +
''' timed out after waiting for ' + CAST(@Counter AS VARCHAR(20)) +
' minutes'
RAISERROR (@ErrMsg, 16, 1)
ELSE IF @FileExists = 1
SET @cmd = 'rename ' + @FilePathFull + ' ' +
CONVERT(VARCHAR(20), GETDATE(), 112) + '_' +
REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '_' +
EXEC master.dbo.xp_cmdshell @cmd, no_output

"Real programmers don't document, if it was hard to write it should be hard to understand"

View Replies !   View Related
Chronic CXPACKET Wait Issue
CXPACKET wait type current makes up 63% of all the wait types which is causing latency. I need to identify the specific workloads responsible for waits so I can optimise or MAXDOP them. I already know how to retreived the top IO, CPU, Memory consuming queries but how do I identify the statements and order them by wait time?

Can someone point me in the direction of a command, DMV or will the top CPU list be adequate?


View Replies !   View Related
Moving Data From One Filegroup To Another.....but Wait, There Is More!!
OK, I know this is out there all over and yes I did a search for this topic; however, I am confused about tables with an image data type and with moving text file group to another filegroup.

Here is what I have:

I have a table storing imaged documents and has become very large.  I want to move the table to another filegroup.  The table is created like this:

USE [PD51_Data]
/****** Object:  Table [dbo].[SCANNEDDOCUMENTS]    Script Date: 05/13/2008 14:52:40 ******/
    [DocID] [int] IDENTITY(1,1) NOT NULL,
    [CaseID] [int] NOT NULL,
    [DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Doc] [image] NOT NULL,
    [DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TopicID] [int] NULL,
    [ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,PRIMARY KEY CLUSTERED
    [DocID] ASC


On a test DB, I moved the clustered and nonclustered indexes to a secondary filegroup no problem, but it still shows to be stored in the primary filegroup.  I read an article about having to create a new table in the secondary in order to move the images and text file group.  Has anyone come across this? 

Do I need to drop the clustered index and FK to move to a secondary filegroup?


Do I create a new table into the secondary filegroup and then add the Clustered index and constraints?

View Replies !   View Related
CLR_Auto_Event And CLR_Manual_Event Wait States
I'm hoping someone can clarify what I am seeing below:
I have a SQL Server  which the 2nd top wait state according to the SQL Server 2005 SP2 dashboard is "CLR_Auto_Event" and "CLR_Manual_Event".  This server dosen't have CLR Integration enabled in "Surface Area Configuration", and we do not have any CLR applications.  I've checked this dashboard many times and CLR never appeared before, and nothing has changed on the server interms of new databases, and we don't host any applications on the server.  I ran a short profiler trace and didn't see any CLR Assembly Loads.
Is there something I'm missing....?

View Replies !   View Related
Why Does UNION ALL Wait For A Row From Each Input Buffer?

It seems strange to me that once the UNION ALL component waits for at least one row from each input buffer before it puts anything into the output buffer (that's the behaviour that I have observed anyway).


Why does it do that?





[SSIS Team Followup]

View Replies !   View Related
Interesting Long PAGEIOLATCH_SH Wait From VB.NET 2003
I am developing a VB.NET app to handle the integration of a legacy PICK/Unidata accounting/ERP system to a warehouse management system (WMS) developed with a SQL2000 db back-end.

The integration app runs every 15 minutes, dumping data into a custom "integration db" that is located on the same SQL server instance as the production WMS db.

Here is how the app works:
Step 1: Download all changes from various files (tables) from legacy db via ODBC to dataset, then export to text files.
Step 2: Import text files via BULK INSERT into staging tables in the integration db, then copy the data using SPs into more permanent tables in the integration db.
Step 3: Using SPs, compare various tables between the integration db and the production db to determine what data needs to be transmitted to the WMS system. <--- this is where the failures have occured
Step 4: Take the records that have been flagged for download in step 3 and throw them out to the WMS system via XML files.

The volumes here are relatively light - a few thousand records at most.

So everything works great until it get to step 3. Occasionally, the SPs doing the cross db comparisons will take forever (5+ minutes) or occasionally the (non-cross-db) SPs in step 4 that help to compile the XML data will take 5+ minutes. Oddly there a times when this error happes very consistently - and if I pause the app before it gets to step 3 or 4 during these times I can run the same SPs from Query Analyzer, and they complete almost instantly.

I've tried READ UNCOMMITTED isolation levels, and NOLOCK tables hints and everything under the Sun I can dream up. These SPs are very straight forward, I've ripped all the begin/rollback/commit transaction statements out of them, yet still they hang. I've looked at the open transaction count of the processes before they run these SPs and they are zero, but during execution when they are hanging it shows that there are two open transactions. The longest wait type seem to be PAGEIOLATCH_SH to the production db ... but again I don't understand how that can cause this problem because running the same SP in query analyzer works so quickly ... ??!!

The only thing I can think of is that the BULK INSERT statements followed up by the cross-db statements using the same connection ( connection pool) are really cheesing off SQL server. Thanks for any help with this.

Scott Fitz
defacto DBA

View Replies !   View Related
Migrate To Sql Server 2005 Or Wait To 2008
I have a question. I work in BI (with sql server 2000) and this year (2008) my idea is migrate to 2005, but, consulting to expert in Microsoft technology, they suggested wait to 2008, sql 2005 have many problem, sql server 2008 was completed re-write.

The question is, what problem have sql server 2005?


View Replies !   View Related
How Do You Start A Query Running, But Don&#39;t Wait For It To Finish
Here's my dilema, I want to run a stored procedure that starts another stored procedure running, but does not wait for the stored procedure to complete execution.

The stored procedure should execute immediately, and leave the other procedure to complete running in the background. Is there any way to do this?

View Replies !   View Related

Copyright © 2005-08, All rights reserved