Convert a Unicode Database into a non-unicode database

Hi there.

We have an application that can run on a non-unicode or a unicode sql
server
database.

Currently the application is running in a unicode database, as a
non-unicode database is less than half the size, I would prefer to
have a non-unicode database for demo purposes to be on my laptop, etc
etc

Is it possible to change a unicode sql server 2000 database into a
non-unicode database?

And if so, how would I go about doing this?

Any help would be greatly appreciated.

Thanks

RodgerUnicode data uses NCHAR / NVARCHAR / NTEXT datatypes rather than CHAR /
VARCHAR / TEXT. This is defined at the column level rather than the database
level.

To change your database you could script all the tables, replace the unicode
datatypes with their non-unicode equivalents and then run the creation
script to create the new database. This might affect the behaviour of stored
procedures and other code but if the app is designed to cope with this then
presumably it shouldn't be a problem. Correctly converting your unicode data
to a non-unicode collation might be more of a challenge though.

--
David Portas
SQL Server MVP
--|||wsbackup@.hotmail.com (Rodger Dodger) wrote in message news:<c1467e75.0408102040.6037ee2f@.posting.google.com>...
> Hi there.
> We have an application that can run on a non-unicode or a unicode sql
> server
> database.
> Currently the application is running in a unicode database, as a
> non-unicode database is less than half the size, I would prefer to
> have a non-unicode database for demo purposes to be on my laptop, etc
> etc
> Is it possible to change a unicode sql server 2000 database into a
> non-unicode database?
> And if so, how would I go about doing this?
> Any help would be greatly appreciated.
> Thanks
> Rodger

I'm not sure I understand your question - MSSQL always supports
Unicode in all databases. You can change a database's collation, but
that doesn't affect Unicode support, so I guess you may want to know
how to change all your nvarchar columns to varchar? If so, then one
way is to execute the results of a query like this:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER
COLUMN ' + COLUMN_NAME + ' VARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'

This is a rather heavy-handed approach, however, so make sure you have
a backup before running it. If this doesn't help, or isn't what you're
trying to do, then please clarify what you need to achieve.

Simon

Conversion of Oracle Version 0-1 from DEC Machine

Hi... Have a customer who's running version 1 of oracle on a DEC
machine.. is there a driver out there for that stuff? How might one
get the data from the DEC machine to Sql Server 2000?? All I have
now are fdl/sfl files.

thanks in advance.

SteveSteve Walker wrote:
> Hi... Have a customer who's running version 1 of oracle on a DEC
> machine.. is there a driver out there for that stuff? How might one
> get the data from the DEC machine to Sql Server 2000?? All I have
> now are fdl/sfl files.
> thanks in advance.
> Steve

It is extremely unlikely that your customer has version 1 of Oracle
considering Oracle customer list at that point. Find out what they
really have and how they determined it.

--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Hi

If there is an ODBC driver you may be able to connect.

John
"Steve Walker" <swalker@.ainet.com> wrote in message
news:bde87b71.0410221056.b2368dc@.posting.google.co m...
> Hi... Have a customer who's running version 1 of oracle on a DEC
> machine.. is there a driver out there for that stuff? How might one
> get the data from the DEC machine to Sql Server 2000?? All I have
> now are fdl/sfl files.
> thanks in advance.
> Stevesqlsql

Conversion error

I am getting the error message Error converting data type varchar to float when running the following query:

Code Snippet

select top 175816
AV.intItemID,
AV.intAttrID,
-- AV.vchValue,
CAST(AV.vchValue AS float) AS Test,
0
from tblAttrVals AV
join tblAttributes AA
on AA.intAttributeID = AV.intAttrID
and AA.intDataTypeID in (2, 3)
and (1 = isnumeric (AV.vchValue))
order by AV.intItemID, AV.intAttrID

Here is what is strange. If I bump the top count down by one it succeeds. And even stranger, if I leave the top count the same and uncomment out the line in the select statement that shows the value being converted it succeeds.

Any ideas? This seems like a bug.

Chris:

Can you show us the specific data that is giving you trouble?

|||

I found the issue. It actually had nothing to do with the data that is being returned. It had to do with the data not being returned.

Here is the info from a post that helped me:

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)
2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate
FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.

sqlsql

Contradiction in SQL Server 2000 Licensing FAQ

Hello,
I have a 4 CPU server running SQL Server 2000 Standard Edition. I have
4 SQL Server 2000 Standard CPU Licenses for this server.
My server has been configured as case-sensitive. We have bought a
piece of third party software that requires the SQL server to be
case-insensitive. However, we do not want to risk changing our
existing installation as it would probably require a great deal of
regression testing.
One options appears to be to add a second instance of SQL server to
the same server, but install this instance as case-insensitive.
I am trying to find out whether our current licensing supports this.
From http://www.microsoft.com/sql/howtobuy/faq.asp
"A Processor license gives you the right to install any number of
copies of SQL Server 2000 on a single computer"
but further down:
"With SQL Server 2000 Enterprise Edition, you can install multiple
instances of SQL Server on the same computer without having additional
licenses. Although SQL Server 2000 Standard Edition will technically
support multiple instances, each instance requires a separate
license."
However http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
states:
"Under the Per Processor licensing model, you may install and run any
number of copies (or "instances") of the SQL Server software on a
server, provided that the required number of Processor licenses has
been acquired."
Does anyone know which is currect?
Thanks,
RichardStandard edition will support upto 4 cpus and multiple
instances, but you need separate license for another
instance of sql server
You don't need to buy separte instance licenses if you
have bought Enterprise Editon per processor license.
>--Original Message--
>Hello,
>I have a 4 CPU server running SQL Server 2000 Standard
Edition. I have
>4 SQL Server 2000 Standard CPU Licenses for this server.
>My server has been configured as case-sensitive. We have
bought a
>piece of third party software that requires the SQL
server to be
>case-insensitive. However, we do not want to risk
changing our
>existing installation as it would probably require a
great deal of
>regression testing.
>One options appears to be to add a second instance of SQL
server to
>the same server, but install this instance as case-
insensitive.
>I am trying to find out whether our current licensing
supports this.
>From http://www.microsoft.com/sql/howtobuy/faq.asp
>"A Processor license gives you the right to install any
number of
>copies of SQL Server 2000 on a single computer"
>but further down:
>"With SQL Server 2000 Enterprise Edition, you can install
multiple
>instances of SQL Server on the same computer without
having additional
>licenses. Although SQL Server 2000 Standard Edition will
technically
>support multiple instances, each instance requires a
separate
>license."
>However
http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
>states:
>"Under the Per Processor licensing model, you may install
and run any
>number of copies (or "instances") of the SQL Server
software on a
>server, provided that the required number of Processor
licenses has
>been acquired."
>Does anyone know which is currect?
>Thanks,
>Richard
>.
>|||> Standard edition will support upto 4 cpus and multiple
> instances, but you need separate license for another
> instance of sql server
This was changed about half a year ago, as far as I understand.
Richard, I'm fairly certain that you can have several on the same license. But you should really
check with MS.
Speak to MS about licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:02e601c39cc2$49861ba0$a301280a@.phx.gbl...
> Standard edition will support upto 4 cpus and multiple
> instances, but you need separate license for another
> instance of sql server
> You don't need to buy separte instance licenses if you
> have bought Enterprise Editon per processor license.
>
> >--Original Message--
> >Hello,
> >
> >I have a 4 CPU server running SQL Server 2000 Standard
> Edition. I have
> >4 SQL Server 2000 Standard CPU Licenses for this server.
> >
> >My server has been configured as case-sensitive. We have
> bought a
> >piece of third party software that requires the SQL
> server to be
> >case-insensitive. However, we do not want to risk
> changing our
> >existing installation as it would probably require a
> great deal of
> >regression testing.
> >
> >One options appears to be to add a second instance of SQL
> server to
> >the same server, but install this instance as case-
> insensitive.
> >
> >I am trying to find out whether our current licensing
> supports this.
> >
> >From http://www.microsoft.com/sql/howtobuy/faq.asp
> >
> >"A Processor license gives you the right to install any
> number of
> >copies of SQL Server 2000 on a single computer"
> >
> >but further down:
> >
> >"With SQL Server 2000 Enterprise Edition, you can install
> multiple
> >instances of SQL Server on the same computer without
> having additional
> >licenses. Although SQL Server 2000 Standard Edition will
> technically
> >support multiple instances, each instance requires a
> separate
> >license."
> >
> >However
> http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
> >states:
> >
> >"Under the Per Processor licensing model, you may install
> and run any
> >number of copies (or "instances") of the SQL Server
> software on a
> >server, provided that the required number of Processor
> licenses has
> >been acquired."
> >
> >Does anyone know which is currect?
> >
> >Thanks,
> >
> >Richard
> >.
> >

Constraints Error

Hopefully somebody here can help me, I am fairly new to SQL Server. I have
SQL Server 2000 running on a Windows 2000 Server. I have a table called
StudentInfo with a field called Date. I want the Date field to only accept
the current date. This field gets updated by teachers every day when they do
their attendance. In Access I used a validation rule of date(). In SQL I
created a contraint on the StudentInfo table and in the constraint
expression I put Date = date(). I have unchecked "validate existing data"
but left the other 2 boxes checked. However, I get the following error -
Error validating check constraint. I think I probably have the wrong syntax
in my contraint expression, can anybody help?
Thanks.
Kevin> Hopefully somebody here can help me, I am fairly new to SQL Server. I have
> SQL Server 2000 running on a Windows 2000 Server. I have a table called
> StudentInfo with a field called Date. I want the Date field to only accept
> the current date. This field gets updated by teachers every day when they
do
> their attendance. In Access I used a validation rule of date(). In SQL I
> created a contraint on the StudentInfo table and in the constraint
> expression I put Date = date(). I have unchecked "validate existing data"
> but left the other 2 boxes checked. However, I get the following error -
> Error validating check constraint. I think I probably have the wrong
syntax
> in my contraint expression, can anybody help?
Set the following constraint:
Date = GETDATE()
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank You.
Kevin
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:%23tJ18zy7DHA.3860@.tk2msftngp13.phx.gbl...
> > Hopefully somebody here can help me, I am fairly new to SQL Server. I
have
> > SQL Server 2000 running on a Windows 2000 Server. I have a table called
> > StudentInfo with a field called Date. I want the Date field to only
accept
> > the current date. This field gets updated by teachers every day when
they
> do
> > their attendance. In Access I used a validation rule of date(). In SQL I
> > created a contraint on the StudentInfo table and in the constraint
> > expression I put Date = date(). I have unchecked "validate existing
data"
> > but left the other 2 boxes checked. However, I get the following error -
> > Error validating check constraint. I think I probably have the wrong
> syntax
> > in my contraint expression, can anybody help?
> Set the following constraint:
> Date = GETDATE()
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Constraints Error

Hopefully somebody here can help me, I am fairly new to SQL Server. I have
SQL Server 2000 running on a Windows 2000 Server. I have a table called
StudentInfo with a field called Date. I want the Date field to only accept
the current date. This field gets updated by teachers every day when they do
their attendance. In Access I used a validation rule of date(). In SQL I
created a contraint on the StudentInfo table and in the constraint
expression I put Date = date(). I have unchecked "validate existing data"
but left the other 2 boxes checked. However, I get the following error -
Error validating check constraint. I think I probably have the wrong syntax
in my contraint expression, can anybody help?
Thanks.
Kevin> Hopefully somebody here can help me, I am fairly new to SQL Server. I have
> SQL Server 2000 running on a Windows 2000 Server. I have a table called
> StudentInfo with a field called Date. I want the Date field to only accept
> the current date. This field gets updated by teachers every day when they
do
> their attendance. In Access I used a validation rule of date(). In SQL I
> created a contraint on the StudentInfo table and in the constraint
> expression I put Date = date(). I have unchecked "validate existing data"
> but left the other 2 boxes checked. However, I get the following error -
> Error validating check constraint. I think I probably have the wrong
syntax
> in my contraint expression, can anybody help?
Set the following constraint:
Date = GETDATE()
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank You.
Kevin
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:%23tJ18zy7DHA.3860@.tk2msftngp13.phx.gbl...
have
accept
they
> do
data"
> syntax
> Set the following constraint:
> Date = GETDATE()
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

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

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

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 Disk Activity

While running MSDE it looks like the service hits the disk every 30
45 seconds. Any hints as to if / how to configure the service to not
hit the disk so often?
Do you have any reason to believe that is kind of activity is unusual? Are
there any clients connected to the MSDE instance that are doing extracting
data or doing updates or deletes? If data pages are being changed the the
Lazy Writer will be flushing them out to disk on a periodic basis.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?
|||Our application only connects with and interacts with the database
infrequently. We are seeing this disk activity constantly, whether
there is any application currently accessing the database or not.
|||You might try using Filemon from www.sysinternals.com. It's a free utility
that can show any file acivity on your system by any process, including SQL
Server. You can use it to see exactly what process is hitting the file
system and what file it is accessing.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405191328.657e38c3@.posting.google.c om...
> Our application only connects with and interacts with the database
> infrequently. We are seeing this disk activity constantly, whether
> there is any application currently accessing the database or not.
|||ryan_columbus@.agilent.com (Ryan Columbus) wrote:
>While running MSDE it looks like the service hits the disk every 30
>45 seconds. Any hints as to if / how to configure the service to not
>hit the disk so often?
Why are you worrying about this? What problem are you trying to solve?
- Tim Roberts, timr@.probo.com
Providenza & Boekelheide, Inc
|||The hard drives that our product will be running on have a finite
lifetime (i.e. only a certain number of continuous hours of
operation). With MSDE constantly accessing the disk, the disk is
never able to spin down. Thus, the life of our product will be
significantly shortened if we cannot stop MSDE from making these
constant disk accesses.
|||We have used a similar tool to determine that MSDE is constantly
hitting the disk. It doesn't really matter which file it is
accessing. What we need is to find a way to stop MSDE from causing
this constant disk activity.
|||I'd check if the autoclose database option is turned on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?

Consolidation of server

We have two production servers and SQL is running on both servers. The
databses in two production srevers has same name. I am in process of
consolidating server. I have to move all the datbases to another sql server
in the other box. But now i have a problem. Since dtabase name is same i am
stuck can any body help me to sort this out.
Thanks ....
You can install a separate named instance on the server, therefore you can
keep the same dbname.
http://msdn.microsoft.com/library/de...stall_259u.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> We have two production servers and SQL is running on both servers. The
> databses in two production srevers has same name. I am in process of
> consolidating server. I have to move all the datbases to another sql
> server
> in the other box. But now i have a problem. Since dtabase name is same i
> am
> stuck can any body help me to sort this out.
> Thanks ....
|||Thanks so much jens. I installed it. But i do i switch over between 2
instances. Does the same enterprise manger works.
"Jens Sü?meyer" wrote:

> You can install a separate named instance on the server, therefore you can
> keep the same dbname.
>
> http://msdn.microsoft.com/library/de...stall_259u.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
>
>
|||YOu habe to tregister the server within the Enterrise Manager, the
servername of a named instance is
Servername\InstanceName
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:509CBA13-244E-4C6C-BC15-1E9789FB2AAE@.microsoft.com...[vbcol=seagreen]
> Thanks so much jens. I installed it. But i do i switch over between 2
> instances. Does the same enterprise manger works.
> "Jens Smeyer" wrote:

Consolidation of server

We have two production servers and SQL is running on both servers. The
databses in two production srevers has same name. I am in process of
consolidating server. I have to move all the datbases to another sql server
in the other box. But now i have a problem. Since dtabase name is same i am
stuck can any body help me to sort this out.
Thanks ....You can install a separate named instance on the server, therefore you can
keep the same dbname.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_259u.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> We have two production servers and SQL is running on both servers. The
> databses in two production srevers has same name. I am in process of
> consolidating server. I have to move all the datbases to another sql
> server
> in the other box. But now i have a problem. Since dtabase name is same i
> am
> stuck can any body help me to sort this out.
> Thanks ....|||Thanks so much jens. I installed it. But i do i switch over between 2
instances. Does the same enterprise manger works.
"Jens Sü�meyer" wrote:
> You can install a separate named instance on the server, therefore you can
> keep the same dbname.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_259u.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> > We have two production servers and SQL is running on both servers. The
> > databses in two production srevers has same name. I am in process of
> > consolidating server. I have to move all the datbases to another sql
> > server
> > in the other box. But now i have a problem. Since dtabase name is same i
> > am
> > stuck can any body help me to sort this out.
> >
> > Thanks ....
>
>|||YOu habe to tregister the server within the Enterrise Manager, the
servername of a named instance is
Servername\InstanceName
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:509CBA13-244E-4C6C-BC15-1E9789FB2AAE@.microsoft.com...
> Thanks so much jens. I installed it. But i do i switch over between 2
> instances. Does the same enterprise manger works.
> "Jens Süßmeyer" wrote:
>> You can install a separate named instance on the server, therefore you
>> can
>> keep the same dbname.
>>
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_259u.asp
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>>
>> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
>> > We have two production servers and SQL is running on both servers. The
>> > databses in two production srevers has same name. I am in process of
>> > consolidating server. I have to move all the datbases to another sql
>> > server
>> > in the other box. But now i have a problem. Since dtabase name is same
>> > i
>> > am
>> > stuck can any body help me to sort this out.
>> >
>> > Thanks ....
>>

Consolidation of server

We have two production servers and SQL is running on both servers. The
databses in two production srevers has same name. I am in process of
consolidating server. I have to move all the datbases to another sql server
in the other box. But now i have a problem. Since dtabase name is same i am
stuck can any body help me to sort this out.
Thanks ....You can install a separate named instance on the server, therefore you can
keep the same dbname.
ll_259u.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
ll_259u.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> We have two production servers and SQL is running on both servers. The
> databses in two production srevers has same name. I am in process of
> consolidating server. I have to move all the datbases to another sql
> server
> in the other box. But now i have a problem. Since dtabase name is same i
> am
> stuck can any body help me to sort this out.
> Thanks ....|||Thanks so much jens. I installed it. But i do i switch over between 2
instances. Does the same enterprise manger works.
"Jens Sü?meyer" wrote:

> You can install a separate named instance on the server, therefore you can
> keep the same dbname.
>
> tall_259u.asp" target="_blank">http://msdn.microsoft.com/library/d...
tall_259u.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
>
>|||YOu habe to tregister the server within the Enterrise Manager, the
servername of a named instance is
Servername\InstanceName
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:509CBA13-244E-4C6C-BC15-1E9789FB2AAE@.microsoft.com...[vbcol=seagreen]
> Thanks so much jens. I installed it. But i do i switch over between 2
> instances. Does the same enterprise manger works.
> "Jens Smeyer" wrote:
>