Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!
<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegr oups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David
|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. The
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious
Thanks heaps!

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegroups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/pr...oads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:[vbcol=seagreen]
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You ca
n
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. T
he
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/pr...nloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious
Thanks heaps!

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegroups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
--
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. The
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious :)
Thanks heaps!

Constant variables - round 2

Hi!
I'm forwarding this because I couldn't find another way to avoid
multiposting once I have forgotten to cross-post to other groups. Sorry,
anyway.
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there a way to create constants (i.e. constant variables) in stored
> procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> PL/SQL's
> "x_var constant integer := 999;"-type declarations.
> Thx,
> Agoston
>
No. TSQL Programming doesnt have any keyword for constant variable.
but if you want to store constants, it is recommended to keep them in
separate table and read it in the TSQL block. one level of security you can
provide is not to allow anyone to update the constants table.
In sql world, constants are data values. like string literals, numeric and
decimal values.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Y6k8PA6EHA.2572@.tk2msftngp13.phx.gbl...
> Hi!
> I'm forwarding this because I couldn't find another way to avoid
> multiposting once I have forgotten to cross-post to other groups. Sorry,
> anyway.
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
>

Constant variables

Hi!
Is there a way to create constants (i.e. constant variables) in stored
procedures? Basically I'm looking for the T-SQL counterpart of Oracle
PL/SQL's
"x_var constant integer := 999;"-type declarations.
Thx,
AgostonHi!
I'm forwarding this because I couldn't find another way to avoid
multiposting once I have forgotten to cross-post to other groups. Sorry,
anyway.
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there a way to create constants (i.e. constant variables) in stored
> procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> PL/SQL's
> "x_var constant integer := 999;"-type declarations.
> Thx,
> Agoston
>|||No. TSQL Programming doesnt have any keyword for constant variable.
but if you want to store constants, it is recommended to keep them in
separate table and read it in the TSQL block. one level of security you can
provide is not to allow anyone to update the constants table.
In sql world, constants are data values. like string literals, numeric and
decimal values.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Y6k8PA6EHA.2572@.tk2msftngp13.phx.gbl...
> Hi!
> I'm forwarding this because I couldn't find another way to avoid
> multiposting once I have forgotten to cross-post to other groups. Sorry,
> anyway.
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> > Is there a way to create constants (i.e. constant variables) in stored
> > procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> > PL/SQL's
> > "x_var constant integer := 999;"-type declarations.
> >
> > Thx,
> > Agoston
> >
> >
>

Constant table size?

Hi, thanks for taking a minute to help me out.

I've got a bunch of reports that have very specific formatting requirements (not mine, industry standards). Basically I need to have different headers and footers on each page, so I don't think I can use the report's headers and footers. Instead I'd like to use each table's (one for each page) set of headers and footers. This is fine for getting the data in there, but I'd like the table footer to be forced to be at the bottom of the page because having a footer halfway down just looks bad.

To summarize, is it possible to have a table footer be in the same place on a page (almost) regardless of how many rows there are returned.
pg1 pg2
header header
data data
data
footer footer

I'd prefer not to split each page into a different report and use those footers, so if there are any other hacks out there I'd really appreciate it

Thanks, JeffWell I do have an ugly way: add enough rows (of the data row's height) to the table footer to put the footer in the right position. Then add an expression to each one's visibility to toggle the first one off when 2 data rows are returned, and toggle the second when 3 data rows are returned, etc.

Any neater, less intensive ideas?
-Jeff

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas

"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff
choppertoo
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message555779.html

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaf
-
chopperto
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message555779.htm

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff

Constant re-indexing of db

We have a database that on a daily basis suffers from poor performance.

Our IT folks found through trial and error that running a complete DBCC reindex job on the database at those times acts as a "release valve" and the server / database start performing normally again.

I don't think that the OLAP activity on this database is anything major so not a whole lot of data is being added. Plus page splitting activity is minimal at the time so I don't think the indexes need to be re-built.

I was thinking that maybe the re-index job is causing deadlocks and therefore eliminating blocked processes. But sp_locks show nothing is being blocked prior to running the re-index job.

Can you throw ideas about how re-indexing or its byproduct effects could be affecting database performance?

Also, what should I trace? What additional logging can SQL put into the errorlog? What are performance impacts in production of performing TRaces of that kind?

This wouldn't be a denial of service type attack would it?

Please send all your suggestions and I'll look into them.

Thanks

...Ray

See if this helps: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||A side-effect of creating an index is to create fullscan statistics. You may actually be suffering from statistics that are not getting updated often enough, and thus be getting bad query plans since the optimizer is making decisions with stale information. Be sure that auto-create and auto-update statistics are enabled. If that is not good enough, try running sp_updatestats nightly. If you can isolate just which tables have bad stats, then you can frequently update the statistics on those tables. If sampled statistics (the default) don't give you the desired results, then consider updating statistics with FULLSCAN.

See this white paper regarding statistics in SQL Server 2005.

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Constant locking issue

My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
SQL Server loves memory. It will give back to the OS only when necessary.
My workstation and laptop each have 2GB and I suggest that you give your
server more RAM.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
|||The problem is, this morning I went back to check the SQL server memory
usage has gone up to 850M
over all memory usage by the whole server was very close to 1G
Even though there were less users in the morning
(Yesterday peak of usage, SQL server memory usage was around 798M)
This tells me something in SQL server holding on to memory that doesn't
release
SQL server is set to Dynamically configure SQL memory, minimum memory query
is set to 1024Kb
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#rTA1fOOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> SQL Server loves memory. It will give back to the OS only when necessary.
> My workstation and laptop each have 2GB and I suggest that you give your
> server more RAM.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> My SQL server has 1G Ram, and I noticed that it was using about 700MB of
it[vbcol=seagreen]
> constant. Next day I looked and usage was gone up to 798 constant.
> Is it possible that SQL server is not releasing the memory it is using,
> thus causing slow downs, and lock finally.
> My guess is that SQL server doesn't realy make use for windows page file.
> MC
>
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in
> offset
>

Constant locking issue

Hello,
I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The offset
of the latest long IO is: 0x000000004a9c00
Could you run a diagnostic on your disk subsystem?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The offset
of the latest long IO is: 0x000000004a9c00
|||I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
|||Your .MDF and .LDF files should be on separate drives. This is both for
performance as well as safety. If your E: drive blew, you'd be in trouble.
Also, RAID5 is slow for update. RAID10 is preferable, if you have space
and money.
Also, check my response re RAM. More is better.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
|||When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G[vbcol=seagreen]
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in
> offset
>
|||Of course I meant that they should be on separate physical drives. That
also applies if you are using RAID. What if 2 disks fail in your RAID5?
That said, have you been able to use System Monitor to look at the Avg Disk
Queue Lengths on each physical disk set - both read and write queues? The
figure should not be 2X the number of spindles.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:eYVKxqUOGHA.1360@.TK2MSFTNGP10.phx.gbl...
When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G[vbcol=seagreen]
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in
> offset
>

Constant locking issue

Hello,
I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The offset
of the latest long IO is: 0x000000004a9c00Could you run a diagnostic on your disk subsystem?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The offset
of the latest long IO is: 0x000000004a9c00|||I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||Your .MDF and .LDF files should be on separate drives. This is both for
performance as well as safety. If your E: drive blew, you'd be in trouble.
Also, RAID5 is slow for update. RAID10 is preferable, if you have space
and money.
Also, check my response re RAM. More is better.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> > Could you run a diagnostic on your disk subsystem?
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "MC" <webmaster@.ozoptics.com> wrote in message
> > news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> >
> > I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
> 2G
> > RAM)
> > We use this for Maximizer ECRM application, but lately (past 2-3month)
we
> > seem to get more SQL locking issues while running application.
> > When I try to kill the proccess that causing the lock, it still shows
the
> > proccess in Enterprise manager, but this leads to more locks somehow.
> > If I try to stop SQL server, at this stage, SQL server engine hangs then
> > times out (doesn't properly stop)
> > If I try to re-start the whole server, server itself hangs on the blue
> > screen trying to shut down
> > (I can re-start server, no problem, as long as these locks doesn't
occur)
> >
> > No CRM application changes.
> > Only changes on the server is constant windows patches, Symantec
> > Anti-Virus updates.
> > I don't know if any of those windows security update is the cause.
> > I donwloaded latest SQL 2000 updates and installed still seem to happen.
> > Nothing in the windows event log that tells me that I am having software
> or
> > hardware issues
> >
> > I noticed this error in SQL event log though, but I don't remember
> getting
> > this error on previous SQL crashes, due to locks
> > Any idea appreciated
> > MC
> >
> > SQL log
> >
> > SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> > 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf]
in
> > database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
> offset
> > of the latest long IO is: 0x000000004a9c00
> >
> >
>|||Of course I meant that they should be on separate physical drives. That
also applies if you are using RAID. What if 2 disks fail in your RAID5?
That said, have you been able to use System Monitor to look at the Avg Disk
Queue Lengths on each physical disk set - both read and write queues? The
figure should not be 2X the number of spindles.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:eYVKxqUOGHA.1360@.TK2MSFTNGP10.phx.gbl...
When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> > Could you run a diagnostic on your disk subsystem?
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "MC" <webmaster@.ozoptics.com> wrote in message
> > news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> >
> > I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
> 2G
> > RAM)
> > We use this for Maximizer ECRM application, but lately (past 2-3month)
we
> > seem to get more SQL locking issues while running application.
> > When I try to kill the proccess that causing the lock, it still shows
the
> > proccess in Enterprise manager, but this leads to more locks somehow.
> > If I try to stop SQL server, at this stage, SQL server engine hangs then
> > times out (doesn't properly stop)
> > If I try to re-start the whole server, server itself hangs on the blue
> > screen trying to shut down
> > (I can re-start server, no problem, as long as these locks doesn't
occur)
> >
> > No CRM application changes.
> > Only changes on the server is constant windows patches, Symantec
> > Anti-Virus updates.
> > I don't know if any of those windows security update is the cause.
> > I donwloaded latest SQL 2000 updates and installed still seem to happen.
> > Nothing in the windows event log that tells me that I am having software
> or
> > hardware issues
> >
> > I noticed this error in SQL event log though, but I don't remember
> getting
> > this error on previous SQL crashes, due to locks
> > Any idea appreciated
> > MC
> >
> > SQL log
> >
> > SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> > 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf]
in
> > database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
> offset
> > of the latest long IO is: 0x000000004a9c00
> >
> >
>

Constant locking issue

My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
>
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
>
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
>
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
>
> SQL log
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
>SQL Server loves memory. It will give back to the OS only when necessary.
My workstation and laptop each have 2GB and I suggest that you give your
server more RAM.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||The problem is, this morning I went back to check the SQL server memory
usage has gone up to 850M
over all memory usage by the whole server was very close to 1G
Even though there were less users in the morning
(Yesterday peak of usage, SQL server memory usage was around 798M)
This tells me something in SQL server holding on to memory that doesn't
release
SQL server is set to Dynamically configure SQL memory, minimum memory query
is set to 1024Kb
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#rTA1fOOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> SQL Server loves memory. It will give back to the OS only when necessary.
> My workstation and laptop each have 2GB and I suggest that you give your
> server more RAM.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> My SQL server has 1G Ram, and I noticed that it was using about 700MB of
it
> constant. Next day I looked and usage was gone up to 798 constant.
> Is it possible that SQL server is not releasing the memory it is using,
> thus causing slow downs, and lock finally.
> My guess is that SQL server doesn't realy make use for windows page file.
> MC
>
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> > Hello,
> >
> > I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
> 2G
> > RAM)
> > We use this for Maximizer ECRM application, but lately (past 2-3month)
we
> > seem to get more SQL locking issues while running application.
> > When I try to kill the proccess that causing the lock, it still shows
the
> > proccess in Enterprise manager, but this leads to more locks somehow.
> > If I try to stop SQL server, at this stage, SQL server engine hangs then
> > times out (doesn't properly stop)
> > If I try to re-start the whole server, server itself hangs on the blue
> > screen trying to shut down
> > (I can re-start server, no problem, as long as these locks doesn't
occur)
> >
> > No CRM application changes.
> > Only changes on the server is constant windows patches, Symantec
> > Anti-Virus updates.
> > I don't know if any of those windows security update is the cause.
> > I donwloaded latest SQL 2000 updates and installed still seem to happen.
> > Nothing in the windows event log that tells me that I am having software
> or
> > hardware issues
> >
> > I noticed this error in SQL event log though, but I don't remember
> getting
> > this error on previous SQL crashes, due to locks
> > Any idea appreciated
> > MC
> >
> > SQL log
> >
> > SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> > 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf]
in
> > database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
> offset
> > of the latest long IO is: 0x000000004a9c00
> >
> >
>

Constant locking issue

My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf
] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>SQL Server loves memory. It will give back to the OS only when necessary.
My workstation and laptop each have 2GB and I suggest that you give your
server more RAM.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
My SQL server has 1G Ram, and I noticed that it was using about 700MB of it
constant. Next day I looked and usage was gone up to 798 constant.
Is it possible that SQL server is not releasing the memory it is using,
thus causing slow downs, and lock finally.
My guess is that SQL server doesn't realy make use for windows page file.
MC
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf
] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||The problem is, this morning I went back to check the SQL server memory
usage has gone up to 850M
over all memory usage by the whole server was very close to 1G
Even though there were less users in the morning
(Yesterday peak of usage, SQL server memory usage was around 798M)
This tells me something in SQL server holding on to memory that doesn't
release
SQL server is set to Dynamically configure SQL memory, minimum memory query
is set to 1024Kb
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#rTA1fOOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> SQL Server loves memory. It will give back to the OS only when necessary.
> My workstation and laptop each have 2GB and I suggest that you give your
> server more RAM.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:u9Rd5HLOGHA.1216@.TK2MSFTNGP14.phx.gbl...
> My SQL server has 1G Ram, and I noticed that it was using about 700MB of
it
> constant. Next day I looked and usage was gone up to 798 constant.
> Is it possible that SQL server is not releasing the memory it is using,
> thus causing slow downs, and lock finally.
> My guess is that SQL server doesn't realy make use for windows page file.
> MC
>
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in[vbcol=seagreen]
> offset
>

Constant locking issue

Hello,
I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf]
in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The off
set
of the latest long IO is: 0x000000004a9c00Could you run a diagnostic on your disk subsystem?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf]
in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The off
set
of the latest long IO is: 0x000000004a9c00|||I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf
] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||Your .MDF and .LDF files should be on separate drives. This is both for
performance as well as safety. If your E: drive blew, you'd be in trouble.
Also, RAID5 is slow for update. RAID10 is preferable, if you have space
and money.
Also, check my response re RAM. More is better.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on Windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf
] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>|||When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in[vbcol=seagreen]
> offset
>|||Of course I meant that they should be on separate physical drives. That
also applies if you are using RAID. What if 2 disks fail in your RAID5?
That said, have you been able to use System Monitor to look at the Avg Disk
Queue Lengths on each physical disk set - both read and write queues? The
figure should not be 2X the number of spindles.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MC" <webmaster@.ozoptics.com> wrote in message
news:eYVKxqUOGHA.1360@.TK2MSFTNGP10.phx.gbl...
When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in[vbcol=seagreen]
> offset
>

Constant Errors on Simple Sums... Why?

There seems to be a concept I'm not grasping. I don't understand why
I can't get a simple sum...
I have a singe table report based on a single dataset and all I want
to do is summarize some financials at 4 group levels.
I get both of these errors for every Sum expression in a report:
"A value expression used for the report parameter
'=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
function. Aggregate functions cannot be used in report parameter
expressions."
"The field expression for the data set ?DatasetName' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set."
I have only 2 parameters in the report, fiscalyear(int) and
fiscalperiod(int).
Half of my Sums aggregate YTD figures that do not reference either of
the parameters.
The other half aggregate MTD figures that are dependent on the
fiscalperiod parameter.
I've tried putting the expression directly into the appropriate text
field, and I have tried making the Sum expressions their own fields
and dropping those fields into the table. Nothing works.
Can anyone explain?
Thanks,
JodyHave you added groups to the table on the form? You need groups. Go to the
footer for the group. Use the expression builder to put the appropriate
values. If you are approaching it correctly it should be very straight
forward.
HTH,
Bruce L-C
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> There seems to be a concept I'm not grasping. I don't understand why
> I can't get a simple sum...
> I have a singe table report based on a single dataset and all I want
> to do is summarize some financials at 4 group levels.
> I get both of these errors for every Sum expression in a report:
> "A value expression used for the report parameter
> '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> function. Aggregate functions cannot be used in report parameter
> expressions."
> "The field expression for the data set 'DatasetName' has a scope
> parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either the
> name of a containing group, the name of a containing data region, or
> the name of a data set."
> I have only 2 parameters in the report, fiscalyear(int) and
> fiscalperiod(int).
> Half of my Sums aggregate YTD figures that do not reference either of
> the parameters.
> The other half aggregate MTD figures that are dependent on the
> fiscalperiod parameter.
> I've tried putting the expression directly into the appropriate text
> field, and I have tried making the Sum expressions their own fields
> and dropping those fields into the table. Nothing works.
> Can anyone explain?
> Thanks,
> Jody|||All of the groups are there..
After some experimentation, I found that you don't actually have to
specify the scope in an aggregate in a table, and that helped.
Another part of the problem is that I'm getting inconsistent results
from my Preview pane and from the Debug preview window. The Debug is
generally right and the Preview if often wrong, even after I do a
rebuild.
Things are going better, but still far from what I had hoped for.
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. Tha helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. That helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody