I'm running MS SQL 7.0.
I've been trying to figure this out for a couple of days without success.
I need to change the values in column6 of a table to NULL where the value of
column6 is not NULL, and the value of column5 is 97, and the values of
column1, column2, column3, and column4 each match the corresponding columns
in the other row.
In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
match the same columns in row 2, and the value of column6 in row 1 is 97 and
in row2 is something else (it doesn't matter what the value is), and the
value of column 6 is not NULL.
Example:
row1 col1 col2 col3 col4 col5 col6
A B C D 97 2
row2 col1 col2 col3 col4 col5 col6
A B C D 1 3
In this example, I need to change the value in row1, column6 from 2 to NULL.
I need to do that to all rows with similar matching qualities, which I
figure to be around 1000 rows. But I DO NOT want rows returned if the value
of col6 in one of the rows is not 97.
Can anyone help?
Thanks,
John Steen
On Fri, 20 Aug 2004 08:43:01 -0700, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:
>I'm running MS SQL 7.0.
>I've been trying to figure this out for a couple of days without success.
>I need to change the values in column6 of a table to NULL where the value of
>column6 is not NULL, and the value of column5 is 97, and the values of
>column1, column2, column3, and column4 each match the corresponding columns
>in the other row.
>In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
>match the same columns in row 2, and the value of column6 in row 1 is 97 and
>in row2 is something else (it doesn't matter what the value is), and the
>value of column 6 is not NULL.
>Example:
>row1 col1 col2 col3 col4 col5 col6
> A B C D 97 2
>row2 col1 col2 col3 col4 col5 col6
> A B C D 1 3
>In this example, I need to change the value in row1, column6 from 2 to NULL.
> I need to do that to all rows with similar matching qualities, which I
>figure to be around 1000 rows. But I DO NOT want rows returned if the value
>of col6 in one of the rows is not 97.
>Can anyone help?
>Thanks,
>John Steen
Hi John,
Pity you didn't post the DDL (CREATE TABLE statements) and INSERTS for the
sample data that would have allowed me to test. Anyway, here's an untested
suggestion:
UPDATE MyTable
SET col6 = NULL
WHERE col6 IS NOT NULL
AND col5 = 97
AND EXISTS
(SELECT *
FROM MyTable AS x
WHERE x.col1 = MyTable.col1
AND x.col2 = MyTable.col2
AND x.col3 = MyTable.col3
AND x.col4 = MyTable.col4
AND ( x.col5 <> 97
OR x.col5 IS NULL))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||if i understand your requirement correctly you can try the query given in
following example.
--sample data
create table t(col1 char(1),col2 char(1),col3 char(1),col4 char(1),col5
int,col6 int)
go
insert into t
select 'A','B','C','e',97, 2 union all --row will be updated
select 'A','B','C','e',9, 3 union all
select 'A','B','C','e',95, 2 union all
select 'A','B','C','e',97, null union all
select 'A','B','C','e',97, 5 union all --row will be updated
select 'A','B','C','x',9, 3 union all
select 'A','B','C','x',95, 2
go
--query
update t set col6 = null
where exists
(select col1,col2,col3,col4
from t x
where t.col1 = x.col1 and t.col2 = x.col2 and t.col3=x.col3 and t.col4 =
x.col4
group by col1,col2,col3,col4
having count(*) > 1)
and t.col5 = 97 and t.col6 is not null
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks, Hugo and Vishal! Both solutions worked.
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Monday, March 26, 2012
Help find better option:Views from table in various Db Viz local d
I am having performance issues Timeout, blocking , long running stored
procedures etc.
Application is written in C sharp and uses database sql2000 sp3.
I have about 25 databases, view is used that joins tables from all db with
left outer join and multiple conditions in where clause.
Online updates are taking place which again goes back to that particular
table in spec. db. Triggers are used at few places to update the view. Every
where stored procedures are used extensively instead of t-sql. In sp at
places table variables are used to minimise locks.
In the initial phase only with each database having few thousand records
user start getting timeout ,slow response & blocking complaints.
Should I copy the view to have local copy of data and have job that run
every min or 2 to update local table . In that too I am not clear that how
useful it will be to keep local copy bcz. it will grow huge in size with
growth in all db.
Very complex thing I see is continuous updates back and forth , which may
keep local copy block for most of the time or else user wan't have latest
data available in their app.
Any one who has worked with similar situation can be lot more useful,
I will highly appreciate any suggestions or discussions from sql experts.
Thanks,Views can be good or bad. If you follow the rules for creating partitioned
views then only the needed table(s) will be scanned. Else, every table in a
view will be scanned which would introduce more unnecessary locks.
http://msdn.microsoft.com/library/e...des_06_9mlv.asp
-oj
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
>I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
> Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
> experts.
> Thanks,
>
>
>
>
>
>
>
>|||Sameer
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
Have you started to investigate the queries? Have you defined indexes on the
tables?
http://www.sql-server-performance.com
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
experts.
> Thanks,
>
>
>
>
>
>
>
>
procedures etc.
Application is written in C sharp and uses database sql2000 sp3.
I have about 25 databases, view is used that joins tables from all db with
left outer join and multiple conditions in where clause.
Online updates are taking place which again goes back to that particular
table in spec. db. Triggers are used at few places to update the view. Every
where stored procedures are used extensively instead of t-sql. In sp at
places table variables are used to minimise locks.
In the initial phase only with each database having few thousand records
user start getting timeout ,slow response & blocking complaints.
Should I copy the view to have local copy of data and have job that run
every min or 2 to update local table . In that too I am not clear that how
useful it will be to keep local copy bcz. it will grow huge in size with
growth in all db.
Very complex thing I see is continuous updates back and forth , which may
keep local copy block for most of the time or else user wan't have latest
data available in their app.
Any one who has worked with similar situation can be lot more useful,
I will highly appreciate any suggestions or discussions from sql experts.
Thanks,Views can be good or bad. If you follow the rules for creating partitioned
views then only the needed table(s) will be scanned. Else, every table in a
view will be scanned which would introduce more unnecessary locks.
http://msdn.microsoft.com/library/e...des_06_9mlv.asp
-oj
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
>I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
> Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
> experts.
> Thanks,
>
>
>
>
>
>
>
>|||Sameer
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
Have you started to investigate the queries? Have you defined indexes on the
tables?
http://www.sql-server-performance.com
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
experts.
> Thanks,
>
>
>
>
>
>
>
>
Friday, March 23, 2012
HELP DTS problems !
I had some DTS packages that are scheduled to run as a job every week. They have been running fine for about 4 months.
One of the jobs failed in the weekend. i was normally able to run the job by logging into the server. The job failed instantenously.
I can't even run the DTS package. I am registered on my enterprise manager as an administrator as well. I tried registering as sa but it comes with the same result. i do not have any aliases. and the DTS packages are only working within the indivdual server. The errror messsage is :-
[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied
Any help would be greatly appreciated.mmmm...have you checked that all your connection inside the DTS point to a valid server? Can you connect to the server that you use inside the DTS?|||i actually created another new package and it works.
so
1. the old package does not run
2. The new package runs fine.
Do you think that my msdb database is corrupted ? or is it the individual databases ?|||Right-mouse click on the package and select "Versions". See what that shows you (busy fingures, man, busy fingures ;))|||Did that, the packages haven't been modified for the last 6 months.
I think i might restore the msdb database and go from there.|||Check the schedule relationed with your DTS package in SQL Server agent manager.See whether the owner of job had been changed or not.|||The SQL Server agent is being run as a domain user who is an administrator on the SQL Server.
Currently merging our backup tape to get the msdb database.
There was actually some patching of the servers in the weekend and one of the domain controllers was rebooted.
Does anyone think that there is some relation with the domain account, sql server agent and my dts packages are not working|||Things don't happen on their own. Some had to have been changed for the "anomaly" to happen.|||I restored the msdb database still the same error
[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied
the UDLs i believe i not working properly. Does anyone know about UDL corruption?
Would restoring the master database help ?|||Is there anything of note in your server logs? What shows up in the job history (especially if you show details)?
Have you tried to execute the package one step at a time, to see what fails? If you have multiple connections, it might not be failing where you'd expect.
Once you know which connection is failing, try to make a connection using the same credentials via Query Analyzer or OSQL.EXE to see if you get a more useful diagnostic message.
If push comes to shove, export your DTS package to a structured storage file and post it... Maybe someone here can see something in there that might help.
I'd exhaust every other avenue before even considering restoring master. That would be an absolute last ditch option for me.
-PatP|||Restoring MASTER would have no affect on success or failure of a DTS package. There NOTHING in MASTER that relates to DTS, NOTHING!..Having said that, - actually there is everything, - databases, logins, linked servers...I'd save the package as a BAS (not structured, Pat) and see what's going on there.|||When you restore the msdb database from a different server, u restore the jobs, dtspackages etc. Does any part of the system tables point to previous server. ? like sysjobs.
One of the jobs failed in the weekend. i was normally able to run the job by logging into the server. The job failed instantenously.
I can't even run the DTS package. I am registered on my enterprise manager as an administrator as well. I tried registering as sa but it comes with the same result. i do not have any aliases. and the DTS packages are only working within the indivdual server. The errror messsage is :-
[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied
Any help would be greatly appreciated.mmmm...have you checked that all your connection inside the DTS point to a valid server? Can you connect to the server that you use inside the DTS?|||i actually created another new package and it works.
so
1. the old package does not run
2. The new package runs fine.
Do you think that my msdb database is corrupted ? or is it the individual databases ?|||Right-mouse click on the package and select "Versions". See what that shows you (busy fingures, man, busy fingures ;))|||Did that, the packages haven't been modified for the last 6 months.
I think i might restore the msdb database and go from there.|||Check the schedule relationed with your DTS package in SQL Server agent manager.See whether the owner of job had been changed or not.|||The SQL Server agent is being run as a domain user who is an administrator on the SQL Server.
Currently merging our backup tape to get the msdb database.
There was actually some patching of the servers in the weekend and one of the domain controllers was rebooted.
Does anyone think that there is some relation with the domain account, sql server agent and my dts packages are not working|||Things don't happen on their own. Some had to have been changed for the "anomaly" to happen.|||I restored the msdb database still the same error
[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied
the UDLs i believe i not working properly. Does anyone know about UDL corruption?
Would restoring the master database help ?|||Is there anything of note in your server logs? What shows up in the job history (especially if you show details)?
Have you tried to execute the package one step at a time, to see what fails? If you have multiple connections, it might not be failing where you'd expect.
Once you know which connection is failing, try to make a connection using the same credentials via Query Analyzer or OSQL.EXE to see if you get a more useful diagnostic message.
If push comes to shove, export your DTS package to a structured storage file and post it... Maybe someone here can see something in there that might help.
I'd exhaust every other avenue before even considering restoring master. That would be an absolute last ditch option for me.
-PatP|||Restoring MASTER would have no affect on success or failure of a DTS package. There NOTHING in MASTER that relates to DTS, NOTHING!..Having said that, - actually there is everything, - databases, logins, linked servers...I'd save the package as a BAS (not structured, Pat) and see what's going on there.|||When you restore the msdb database from a different server, u restore the jobs, dtspackages etc. Does any part of the system tables point to previous server. ? like sysjobs.
help copying databases
I am trying to import all of my production databases onto my development
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>sql
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>sql
help copying databases
I am trying to import all of my production databases onto my development
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
Fred
Did u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
Fred
Did u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
help copying databases
I am trying to import all of my production databases onto my development
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
--
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
--
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
Wednesday, March 21, 2012
Help calling DTS package.
I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting"
Error Number:
-2147467259 {Integer}
Error Number:
-2147467259 {Integer}
Error Description:
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}
This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.
Any ideas??
ThanksAfter a few modifications I'm getting a different error now. But it still is obviously a permissions problem. Everything I've read says set Impersonation equal to true. Unfortunately it already is. Any suggestions from you SQL Server gurus?
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
Thankssql
help and advice on waittype 0x0044
Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.
help and advice on waittype 0x0044
Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
613151115052100x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.
Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 613151115052100x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>
|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange
|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
613151115052100x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.
Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write of
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 613151115052100x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>
|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange
|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.
help and advice on waittype 0x0044
Hi,
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write o
f
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.
I wonder if anyone can shed any light on the following as i just can't
explain it.
A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.
The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.
There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
In sysprocess it showing the following :-
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732
the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
gives results :-
6 13 151115052 10 0x0000000008624080
I just can't explain why it is so slow when nothing else is ruuning.
Anyone have any ideas on what i can check on?
Thanks
Ian.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write o
f
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.
>|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> Hi,
> I wonder if anyone can shed any light on the following as i just can't
> explain it.
> A user is running an update on a 500m+ row table setting a column
> value, computing its value from another column in the table. It's now
> been running for 23hours.
> The server is Itanium 64, enterprise 2005, SAN based storage and it
> usually handles anything with this volume quite quickly, probably
> about 30 mins or so.
> There is nothing else running currently although overnight batches,
> backups etc have been running within the last 23 hours.
> In sysprocess it showing the following :-
> spid kpid blocked waittype waittime
> lastwaittype waitresource
> 52 5236 0 0x0044 30
> PAGEIOLATCH_EX 6:13:1754732
> the process seems to stay in this waittype for a few secnds and then
> goes to a 0x0000 and then back into this one again. I can see from the
> IO counter that IO is increasing and also looking at the current IO i
> see the following so presume the query is still working :-
> select
> database_id,
> file_id,
> io_stall,
> io_pending_ms_ticks,
> scheduler_address
> from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
> sys.dm_io_pending_io_requests as t2
> where t1.file_handle = t2.io_handle
> gives results :-
> 6 13 151115052 10 0x0000000008624080
> I just can't explain why it is so slow when nothing else is ruuning.
> Anyone have any ideas on what i can check on?
> Thanks
> Ian.|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.
Monday, March 19, 2012
Help about running SQL CE on Win CE 4.2
Hi everybody,
I developed mobile programs for WM5.0 with C#.NET using SQL Mobile. (Visual Studio 2005, SQL Server 2005 Mobile, Mobile SDK)
But I couldn't run SQL CE on WinCE 4.2 ? (Visual Studio 2003, SQL Server 2000 CE). For example on emulator, an Insert command is running successfully. But on device (Intermec CK 31) we are getting error.
Here is my code :
using System;
using System.Data;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace UmurBS.Forms.BasimSayimForms
{
publicclass bsEkleForm : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dg;
private System.Windows.Forms.Button button1;
private System.Data.SqlServerCe.SqlCeConnection sqlceconn;
privatestring connstr = "DATA SOURCE = UmurDB.sdf";
public bsEkleForm()
{
InitializeComponent();
}
protectedoverridevoid Dispose( bool disposing )
{
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
privatevoid InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dg = new System.Windows.Forms.DataGrid();
//
// button1
//
this.button1.Location = new System.Drawing.Point(160, 264);
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dg
//
this.dg.Location = new System.Drawing.Point(8, 8);
this.dg.Size = new System.Drawing.Size(224, 200);
this.dg.Text = "dg";
//
// bsEkleForm
//
this.ClientSize = new System.Drawing.Size(237, 291);
this.Controls.Add(this.dg);
this.Controls.Add(this.button1);
this.Text = "bsEkleForm";
}
#endregion
privatevoid button1_Click(object sender, System.EventArgs e)
{
sqlceconn = new System.Data.SqlServerCe.SqlCeConnection(connstr);
this.ekle();
}
privatevoid ekle()
{
try
{
SqlCeCommand cmd = new SqlCeCommand("insert into test (testValue) values ('hi world')", sqlceconn);
sqlceconn.Open();
int rowsaffected = cmd.ExecuteNonQuery();
}
catch(Exception exc)
{
MessageBox.Show(exc.Message);
}
finally
{
if (sqlceconn != null)
if (sqlceconn.State == ConnectionState.Open)
sqlceconn.Close();
}
}
}
}
Any idea ?
Regards
Serkan
In order to run SQL Mobile on WinCE 4.2 devices, you will have to request the following hotfix from Microsoft: http://support.microsoft.com/default.aspx/kb/924811
Hope this assists.
|||Hi Erik
Actually I don't understand what must I do? Which files I must download? I have an Intermec CK 31 mobile device.
When I install CF 2.0 SP1 on it, I getting "not compatible" error. But completing install. But application does not run. :(
Thanks for any help , suggesstions.
Regards
Serkan
|||Which processsor architecture is your Intermec device based on - you must load the corresponding cabs for both .NET CF and SQL CE.|||Hi Erik,
Processor is Intel XScale PXA255, 400 MHz . I think Architecture is ARM4.
|||You need to call Microsoft support for the hotfix, for your Intermec device, you need to request the following files:
For ARMV4i-based devices
as the only cab files made available for Windows CE 4.2 are for PocketPC devices, not general Windows CE 4.2 Devices.
ie: sqlce30.ppc.wce4.armv4.CAB, sqlce30.repl.ppc.wce4.armv4.CAB, sqlce30.dev.ENU.ppc.wce4.armv4.CAB
You may need to have a look at this as well: http://support.microsoft.com/kb/914829/
Help about running SQL CE on Win CE 4.2
Hi everybody,
I developed mobile programs for WM5.0 with C#.NET using SQL Mobile. (Visual Studio 2005, SQL Server 2005 Mobile, Mobile SDK)
But I couldn't run SQL CE on WinCE 4.2 ? (Visual Studio 2003, SQL Server 2000 CE). For example on emulator, an Insert command is running successfully. But on device (Intermec CK 31) we are getting error.
Here is my code :
using System;
using System.Data;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace UmurBS.Forms.BasimSayimForms
{
public class bsEkleForm : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dg;
private System.Windows.Forms.Button button1;
private System.Data.SqlServerCe.SqlCeConnection sqlceconn;
private string connstr = "DATA SOURCE = UmurDB.sdf";
public bsEkleForm()
{
InitializeComponent();
}
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dg = new System.Windows.Forms.DataGrid();
//
// button1
//
this.button1.Location = new System.Drawing.Point(160, 264);
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dg
//
this.dg.Location = new System.Drawing.Point(8, 8);
this.dg.Size = new System.Drawing.Size(224, 200);
this.dg.Text = "dg";
//
// bsEkleForm
//
this.ClientSize = new System.Drawing.Size(237, 291);
this.Controls.Add(this.dg);
this.Controls.Add(this.button1);
this.Text = "bsEkleForm";
}
#endregion
private void button1_Click(object sender, System.EventArgs e)
{
sqlceconn = new System.Data.SqlServerCe.SqlCeConnection(connstr);
this.ekle();
}
private void ekle()
{
try
{
SqlCeCommand cmd = new SqlCeCommand("insert into test (testValue) values ('hi world')", sqlceconn);
sqlceconn.Open();
int rowsaffected = cmd.ExecuteNonQuery();
}
catch(Exception exc)
{
MessageBox.Show(exc.Message);
}
finally
{
if (sqlceconn != null)
if (sqlceconn.State == ConnectionState.Open)
sqlceconn.Close();
}
}
}
}
Any idea ?
Regards
Serkan
In order to run SQL Mobile on WinCE 4.2 devices, you will have to request the following hotfix from Microsoft: http://support.microsoft.com/default.aspx/kb/924811
Hope this assists.
|||
Hi Erik
Actually I don't understand what must I do? Which files I must download? I have an Intermec CK 31 mobile device.
When I install CF 2.0 SP1 on it, I getting "not compatible" error. But completing install. But application does not run. :(
Thanks for any help , suggesstions.
Regards
Serkan
|||Which processsor architecture is your Intermec device based on - you must load the corresponding cabs for both .NET CF and SQL CE.|||Hi Erik,
Processor is Intel XScale PXA255, 400 MHz . I think Architecture is ARM4.
|||You need to call Microsoft support for the hotfix, for your Intermec device, you need to request the following files:
For ARMV4i-based devices
as the only cab files made available for Windows CE 4.2 are for PocketPC devices, not general Windows CE 4.2 Devices.
ie: sqlce30.ppc.wce4.armv4.CAB, sqlce30.repl.ppc.wce4.armv4.CAB, sqlce30.dev.ENU.ppc.wce4.armv4.CAB
You may need to have a look at this as well: http://support.microsoft.com/kb/914829/
Monday, March 12, 2012
HELP ! Suddenly, terribly slow SQLServer Enterprise Manager
Hi,
I am using SQLServer on my development laptop, and it's running without
any problems since at least 6 or 7 months. Suddenly, since yesterday,
when I start the Entreprise Manager, it takes absolute AGES (minutes,
with a very intensive disk activity) until it shows the console root,
and each click on a table takes again ages until the rows are shown.
Even paging takes now 10 times longer as before. My DB is very snmall,
something like 50,000 rows in total max. A double click on the table
icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A
click on the "Run" icon in the tool bar takes more than 10 seconds to
complete, to show 40-50 rows !
I have reorganized the DB, but that does not help at all for the
performance.
I noticed that the log file is 76 Megabytes whereas the DB after
shrinking is only 8 Meg. I have done a backup of the log file, but it is
only a ridicule 196 KBytes, and the live log is just as big as before !
The very strange thing is that, if I log as another user on my laptop,
then I have the normal speed in Enterprise Manager. How is that possible ?
What's going on ? Why suddenly, where I can't remember having done
anything special these last days. And more importantly, how do I get
back to the previous status, where everything was real quick ? I just
can't work properly anymore if every single click is taking minutes !!!
Thanks for help.
Bernard
Sound like someone has turned on ODBC tracing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:%230mAl35kFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using SQLServer on my development laptop, and it's running without any problems since at
> least 6 or 7 months. Suddenly, since yesterday, when I start the Entreprise Manager, it takes
> absolute AGES (minutes, with a very intensive disk activity) until it shows the console root, and
> each click on a table takes again ages until the rows are shown. Even paging takes now 10 times
> longer as before. My DB is very snmall, something like 50,000 rows in total max. A double click on
> the table icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A click on the
> "Run" icon in the tool bar takes more than 10 seconds to complete, to show 40-50 rows !
> I have reorganized the DB, but that does not help at all for the performance.
> I noticed that the log file is 76 Megabytes whereas the DB after shrinking is only 8 Meg. I have
> done a backup of the log file, but it is only a ridicule 196 KBytes, and the live log is just as
> big as before !
> The very strange thing is that, if I log as another user on my laptop, then I have the normal
> speed in Enterprise Manager. How is that possible ?
> What's going on ? Why suddenly, where I can't remember having done anything special these last
> days. And more importantly, how do I get back to the previous status, where everything was real
> quick ? I just can't work properly anymore if every single click is taking minutes !!!
> Thanks for help.
> Bernard
|||THANK YOU ! I DID turn it on, and did not see any log at all coming up,
so I thought it had not worked and totally forgot about it !
Yes, now my Enterprise Manager is back to normal.
You made my day ! Sometimes, it's so simple...
Ah, what a relief it is...
And what about my <database>_Log.LDF file ? How do I get rid of/truncate
it ?
Regards, and thanks again.
Bernard
Tibor Karaszi wrote:
> Sound like someone has turned on ODBC tracing.
>
|||> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
The ldf file is a part of the database. You can't get rid of it. If you consider it being too large,
then you should read in Books Online about recovery model and decide whether or not you want to do
regular transaction log backups. If you have had the db in full recovery model and not been doing
regular transaction log backups and have a very large ldf file, you can shrink it using DBCC
SHRINKFILE. Also check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:OKNqi36kFHA.2472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> THANK YOU ! I DID turn it on, and did not see any log at all coming up, so I thought it had not
> worked and totally forgot about it !
> Yes, now my Enterprise Manager is back to normal.
> You made my day ! Sometimes, it's so simple...
> Ah, what a relief it is...
> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
> Regards, and thanks again.
> Bernard
>
> Tibor Karaszi wrote:
I am using SQLServer on my development laptop, and it's running without
any problems since at least 6 or 7 months. Suddenly, since yesterday,
when I start the Entreprise Manager, it takes absolute AGES (minutes,
with a very intensive disk activity) until it shows the console root,
and each click on a table takes again ages until the rows are shown.
Even paging takes now 10 times longer as before. My DB is very snmall,
something like 50,000 rows in total max. A double click on the table
icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A
click on the "Run" icon in the tool bar takes more than 10 seconds to
complete, to show 40-50 rows !
I have reorganized the DB, but that does not help at all for the
performance.
I noticed that the log file is 76 Megabytes whereas the DB after
shrinking is only 8 Meg. I have done a backup of the log file, but it is
only a ridicule 196 KBytes, and the live log is just as big as before !
The very strange thing is that, if I log as another user on my laptop,
then I have the normal speed in Enterprise Manager. How is that possible ?
What's going on ? Why suddenly, where I can't remember having done
anything special these last days. And more importantly, how do I get
back to the previous status, where everything was real quick ? I just
can't work properly anymore if every single click is taking minutes !!!
Thanks for help.
Bernard
Sound like someone has turned on ODBC tracing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:%230mAl35kFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using SQLServer on my development laptop, and it's running without any problems since at
> least 6 or 7 months. Suddenly, since yesterday, when I start the Entreprise Manager, it takes
> absolute AGES (minutes, with a very intensive disk activity) until it shows the console root, and
> each click on a table takes again ages until the rows are shown. Even paging takes now 10 times
> longer as before. My DB is very snmall, something like 50,000 rows in total max. A double click on
> the table icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A click on the
> "Run" icon in the tool bar takes more than 10 seconds to complete, to show 40-50 rows !
> I have reorganized the DB, but that does not help at all for the performance.
> I noticed that the log file is 76 Megabytes whereas the DB after shrinking is only 8 Meg. I have
> done a backup of the log file, but it is only a ridicule 196 KBytes, and the live log is just as
> big as before !
> The very strange thing is that, if I log as another user on my laptop, then I have the normal
> speed in Enterprise Manager. How is that possible ?
> What's going on ? Why suddenly, where I can't remember having done anything special these last
> days. And more importantly, how do I get back to the previous status, where everything was real
> quick ? I just can't work properly anymore if every single click is taking minutes !!!
> Thanks for help.
> Bernard
|||THANK YOU ! I DID turn it on, and did not see any log at all coming up,
so I thought it had not worked and totally forgot about it !
Yes, now my Enterprise Manager is back to normal.
You made my day ! Sometimes, it's so simple...
Ah, what a relief it is...
And what about my <database>_Log.LDF file ? How do I get rid of/truncate
it ?
Regards, and thanks again.
Bernard
Tibor Karaszi wrote:
> Sound like someone has turned on ODBC tracing.
>
|||> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
The ldf file is a part of the database. You can't get rid of it. If you consider it being too large,
then you should read in Books Online about recovery model and decide whether or not you want to do
regular transaction log backups. If you have had the db in full recovery model and not been doing
regular transaction log backups and have a very large ldf file, you can shrink it using DBCC
SHRINKFILE. Also check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:OKNqi36kFHA.2472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> THANK YOU ! I DID turn it on, and did not see any log at all coming up, so I thought it had not
> worked and totally forgot about it !
> Yes, now my Enterprise Manager is back to normal.
> You made my day ! Sometimes, it's so simple...
> Ah, what a relief it is...
> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
> Regards, and thanks again.
> Bernard
>
> Tibor Karaszi wrote:
HELP ! Suddenly, terribly slow SQLServer Enterprise Manager
Hi,
I am using SQLServer on my development laptop, and it's running without
any problems since at least 6 or 7 months. Suddenly, since yesterday,
when I start the Entreprise Manager, it takes absolute AGES (minutes,
with a very intensive disk activity) until it shows the console root,
and each click on a table takes again ages until the rows are shown.
Even paging takes now 10 times longer as before. My DB is very snmall,
something like 50,000 rows in total max. A double click on the table
icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A
click on the "Run" icon in the tool bar takes more than 10 seconds to
complete, to show 40-50 rows !
I have reorganized the DB, but that does not help at all for the
performance.
I noticed that the log file is 76 Megabytes whereas the DB after
shrinking is only 8 Meg. I have done a backup of the log file, but it is
only a ridicule 196 KBytes, and the live log is just as big as before !
The very strange thing is that, if I log as another user on my laptop,
then I have the normal speed in Enterprise Manager. How is that possible ?
What's going on ? Why suddenly, where I can't remember having done
anything special these last days. And more importantly, how do I get
back to the previous status, where everything was real quick ? I just
can't work properly anymore if every single click is taking minutes !!!
Thanks for help.
BernardSound like someone has turned on ODBC tracing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:%230mAl35kFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using SQLServer on my development laptop, and it's running without any problems since at
> least 6 or 7 months. Suddenly, since yesterday, when I start the Entreprise Manager, it takes
> absolute AGES (minutes, with a very intensive disk activity) until it shows the console root, and
> each click on a table takes again ages until the rows are shown. Even paging takes now 10 times
> longer as before. My DB is very snmall, something like 50,000 rows in total max. A double click on
> the table icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A click on the
> "Run" icon in the tool bar takes more than 10 seconds to complete, to show 40-50 rows !
> I have reorganized the DB, but that does not help at all for the performance.
> I noticed that the log file is 76 Megabytes whereas the DB after shrinking is only 8 Meg. I have
> done a backup of the log file, but it is only a ridicule 196 KBytes, and the live log is just as
> big as before !
> The very strange thing is that, if I log as another user on my laptop, then I have the normal
> speed in Enterprise Manager. How is that possible ?
> What's going on ? Why suddenly, where I can't remember having done anything special these last
> days. And more importantly, how do I get back to the previous status, where everything was real
> quick ? I just can't work properly anymore if every single click is taking minutes !!!
> Thanks for help.
> Bernard|||THANK YOU ! I DID turn it on, and did not see any log at all coming up,
so I thought it had not worked and totally forgot about it !
Yes, now my Enterprise Manager is back to normal.
You made my day ! Sometimes, it's so simple...
Ah, what a relief it is...
And what about my <database>_Log.LDF file ? How do I get rid of/truncate
it ?
Regards, and thanks again.
Bernard
Tibor Karaszi wrote:
> Sound like someone has turned on ODBC tracing.
>|||> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
The ldf file is a part of the database. You can't get rid of it. If you consider it being too large,
then you should read in Books Online about recovery model and decide whether or not you want to do
regular transaction log backups. If you have had the db in full recovery model and not been doing
regular transaction log backups and have a very large ldf file, you can shrink it using DBCC
SHRINKFILE. Also check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:OKNqi36kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> THANK YOU ! I DID turn it on, and did not see any log at all coming up, so I thought it had not
> worked and totally forgot about it !
> Yes, now my Enterprise Manager is back to normal.
> You made my day ! Sometimes, it's so simple...
> Ah, what a relief it is...
> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
> Regards, and thanks again.
> Bernard
>
> Tibor Karaszi wrote:
>> Sound like someone has turned on ODBC tracing.
I am using SQLServer on my development laptop, and it's running without
any problems since at least 6 or 7 months. Suddenly, since yesterday,
when I start the Entreprise Manager, it takes absolute AGES (minutes,
with a very intensive disk activity) until it shows the console root,
and each click on a table takes again ages until the rows are shown.
Even paging takes now 10 times longer as before. My DB is very snmall,
something like 50,000 rows in total max. A double click on the table
icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A
click on the "Run" icon in the tool bar takes more than 10 seconds to
complete, to show 40-50 rows !
I have reorganized the DB, but that does not help at all for the
performance.
I noticed that the log file is 76 Megabytes whereas the DB after
shrinking is only 8 Meg. I have done a backup of the log file, but it is
only a ridicule 196 KBytes, and the live log is just as big as before !
The very strange thing is that, if I log as another user on my laptop,
then I have the normal speed in Enterprise Manager. How is that possible ?
What's going on ? Why suddenly, where I can't remember having done
anything special these last days. And more importantly, how do I get
back to the previous status, where everything was real quick ? I just
can't work properly anymore if every single click is taking minutes !!!
Thanks for help.
BernardSound like someone has turned on ODBC tracing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:%230mAl35kFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using SQLServer on my development laptop, and it's running without any problems since at
> least 6 or 7 months. Suddenly, since yesterday, when I start the Entreprise Manager, it takes
> absolute AGES (minutes, with a very intensive disk activity) until it shows the console root, and
> each click on a table takes again ages until the rows are shown. Even paging takes now 10 times
> longer as before. My DB is very snmall, something like 50,000 rows in total max. A double click on
> the table icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A click on the
> "Run" icon in the tool bar takes more than 10 seconds to complete, to show 40-50 rows !
> I have reorganized the DB, but that does not help at all for the performance.
> I noticed that the log file is 76 Megabytes whereas the DB after shrinking is only 8 Meg. I have
> done a backup of the log file, but it is only a ridicule 196 KBytes, and the live log is just as
> big as before !
> The very strange thing is that, if I log as another user on my laptop, then I have the normal
> speed in Enterprise Manager. How is that possible ?
> What's going on ? Why suddenly, where I can't remember having done anything special these last
> days. And more importantly, how do I get back to the previous status, where everything was real
> quick ? I just can't work properly anymore if every single click is taking minutes !!!
> Thanks for help.
> Bernard|||THANK YOU ! I DID turn it on, and did not see any log at all coming up,
so I thought it had not worked and totally forgot about it !
Yes, now my Enterprise Manager is back to normal.
You made my day ! Sometimes, it's so simple...
Ah, what a relief it is...
And what about my <database>_Log.LDF file ? How do I get rid of/truncate
it ?
Regards, and thanks again.
Bernard
Tibor Karaszi wrote:
> Sound like someone has turned on ODBC tracing.
>|||> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
The ldf file is a part of the database. You can't get rid of it. If you consider it being too large,
then you should read in Books Online about recovery model and decide whether or not you want to do
regular transaction log backups. If you have had the db in full recovery model and not been doing
regular transaction log backups and have a very large ldf file, you can shrink it using DBCC
SHRINKFILE. Also check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:OKNqi36kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> THANK YOU ! I DID turn it on, and did not see any log at all coming up, so I thought it had not
> worked and totally forgot about it !
> Yes, now my Enterprise Manager is back to normal.
> You made my day ! Sometimes, it's so simple...
> Ah, what a relief it is...
> And what about my <database>_Log.LDF file ? How do I get rid of/truncate it ?
> Regards, and thanks again.
> Bernard
>
> Tibor Karaszi wrote:
>> Sound like someone has turned on ODBC tracing.
HELP ! Suddenly, terribly slow SQLServer Enterprise Manager
Hi,
I am using SQLServer on my development laptop, and it's running without
any problems since at least 6 or 7 months. Suddenly, since yesterday,
when I start the Entreprise Manager, it takes absolute AGES (minutes,
with a very intensive disk activity) until it shows the console root,
and each click on a table takes again ages until the rows are shown.
Even paging takes now 10 times longer as before. My DB is very snmall,
something like 50,000 rows in total max. A double click on the table
icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A
click on the "Run" icon in the tool bar takes more than 10 seconds to
complete, to show 40-50 rows !
I have reorganized the DB, but that does not help at all for the
performance.
I noticed that the log file is 76 Megabytes whereas the DB after
shrinking is only 8 Meg. I have done a backup of the log file, but it is
only a ridicule 196 KBytes, and the live log is just as big as before !
The very strange thing is that, if I log as another user on my laptop,
then I have the normal speed in Enterprise Manager. How is that possible ?
What's going on ? Why suddenly, where I can't remember having done
anything special these last days. And more importantly, how do I get
back to the previous status, where everything was real quick ? I just
can't work properly anymore if every single click is taking minutes !!!
Thanks for help.
BernardSound like someone has turned on ODBC tracing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:%230mAl35kFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using SQLServer on my development laptop, and it's running without an
y problems since at
> least 6 or 7 months. Suddenly, since yesterday, when I start the Entrepris
e Manager, it takes
> absolute AGES (minutes, with a very intensive disk activity) until it show
s the console root, and
> each click on a table takes again ages until the rows are shown. Even pagi
ng takes now 10 times
> longer as before. My DB is very snmall, something like 50,000 rows in tota
l max. A double click on
> the table icon of the largest table (20,000 rows) takes 2 MINUTES to compl
ete ! A click on the
> "Run" icon in the tool bar takes more than 10 seconds to complete, to show
40-50 rows !
> I have reorganized the DB, but that does not help at all for the performan
ce.
> I noticed that the log file is 76 Megabytes whereas the DB after shrinking
is only 8 Meg. I have
> done a backup of the log file, but it is only a ridicule 196 KBytes, and t
he live log is just as
> big as before !
> The very strange thing is that, if I log as another user on my laptop, the
n I have the normal
> speed in Enterprise Manager. How is that possible ?
> What's going on ? Why suddenly, where I can't remember having done anythin
g special these last
> days. And more importantly, how do I get back to the previous status, wher
e everything was real
> quick ? I just can't work properly anymore if every single click is taking
minutes !!!
> Thanks for help.
> Bernard|||THANK YOU ! I DID turn it on, and did not see any log at all coming up,
so I thought it had not worked and totally forgot about it !
Yes, now my Enterprise Manager is back to normal.
You made my day ! Sometimes, it's so simple...
Ah, what a relief it is...
And what about my <database>_Log.LDF file ? How do I get rid of/truncate
it ?
Regards, and thanks again.
Bernard
Tibor Karaszi wrote:
> Sound like someone has turned on ODBC tracing.
>|||> And what about my <database>_Log.LDF file ? How do I get rid of/truncate i
t ?
The ldf file is a part of the database. You can't get rid of it. If you cons
ider it being too large,
then you should read in Books Online about recovery model and decide whether
or not you want to do
regular transaction log backups. If you have had the db in full recovery mod
el and not been doing
regular transaction log backups and have a very large ldf file, you can shri
nk it using DBCC
SHRINKFILE. Also check out http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:OKNqi36kFHA.2472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> THANK YOU ! I DID turn it on, and did not see any log at all coming up, so
I thought it had not
> worked and totally forgot about it !
> Yes, now my Enterprise Manager is back to normal.
> You made my day ! Sometimes, it's so simple...
> Ah, what a relief it is...
> And what about my <database>_Log.LDF file ? How do I get rid of/truncate i
t ?
> Regards, and thanks again.
> Bernard
>
> Tibor Karaszi wrote:
I am using SQLServer on my development laptop, and it's running without
any problems since at least 6 or 7 months. Suddenly, since yesterday,
when I start the Entreprise Manager, it takes absolute AGES (minutes,
with a very intensive disk activity) until it shows the console root,
and each click on a table takes again ages until the rows are shown.
Even paging takes now 10 times longer as before. My DB is very snmall,
something like 50,000 rows in total max. A double click on the table
icon of the largest table (20,000 rows) takes 2 MINUTES to complete ! A
click on the "Run" icon in the tool bar takes more than 10 seconds to
complete, to show 40-50 rows !
I have reorganized the DB, but that does not help at all for the
performance.
I noticed that the log file is 76 Megabytes whereas the DB after
shrinking is only 8 Meg. I have done a backup of the log file, but it is
only a ridicule 196 KBytes, and the live log is just as big as before !
The very strange thing is that, if I log as another user on my laptop,
then I have the normal speed in Enterprise Manager. How is that possible ?
What's going on ? Why suddenly, where I can't remember having done
anything special these last days. And more importantly, how do I get
back to the previous status, where everything was real quick ? I just
can't work properly anymore if every single click is taking minutes !!!
Thanks for help.
BernardSound like someone has turned on ODBC tracing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:%230mAl35kFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using SQLServer on my development laptop, and it's running without an
y problems since at
> least 6 or 7 months. Suddenly, since yesterday, when I start the Entrepris
e Manager, it takes
> absolute AGES (minutes, with a very intensive disk activity) until it show
s the console root, and
> each click on a table takes again ages until the rows are shown. Even pagi
ng takes now 10 times
> longer as before. My DB is very snmall, something like 50,000 rows in tota
l max. A double click on
> the table icon of the largest table (20,000 rows) takes 2 MINUTES to compl
ete ! A click on the
> "Run" icon in the tool bar takes more than 10 seconds to complete, to show
40-50 rows !
> I have reorganized the DB, but that does not help at all for the performan
ce.
> I noticed that the log file is 76 Megabytes whereas the DB after shrinking
is only 8 Meg. I have
> done a backup of the log file, but it is only a ridicule 196 KBytes, and t
he live log is just as
> big as before !
> The very strange thing is that, if I log as another user on my laptop, the
n I have the normal
> speed in Enterprise Manager. How is that possible ?
> What's going on ? Why suddenly, where I can't remember having done anythin
g special these last
> days. And more importantly, how do I get back to the previous status, wher
e everything was real
> quick ? I just can't work properly anymore if every single click is taking
minutes !!!
> Thanks for help.
> Bernard|||THANK YOU ! I DID turn it on, and did not see any log at all coming up,
so I thought it had not worked and totally forgot about it !
Yes, now my Enterprise Manager is back to normal.
You made my day ! Sometimes, it's so simple...
Ah, what a relief it is...
And what about my <database>_Log.LDF file ? How do I get rid of/truncate
it ?
Regards, and thanks again.
Bernard
Tibor Karaszi wrote:
> Sound like someone has turned on ODBC tracing.
>|||> And what about my <database>_Log.LDF file ? How do I get rid of/truncate i
t ?
The ldf file is a part of the database. You can't get rid of it. If you cons
ider it being too large,
then you should read in Books Online about recovery model and decide whether
or not you want to do
regular transaction log backups. If you have had the db in full recovery mod
el and not been doing
regular transaction log backups and have a very large ldf file, you can shri
nk it using DBCC
SHRINKFILE. Also check out http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bthouin" <bernard_thouin@.bluewin.com> wrote in message
news:OKNqi36kFHA.2472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> THANK YOU ! I DID turn it on, and did not see any log at all coming up, so
I thought it had not
> worked and totally forgot about it !
> Yes, now my Enterprise Manager is back to normal.
> You made my day ! Sometimes, it's so simple...
> Ah, what a relief it is...
> And what about my <database>_Log.LDF file ? How do I get rid of/truncate i
t ?
> Regards, and thanks again.
> Bernard
>
> Tibor Karaszi wrote:
Help !
Im going mad trying and failing to figure this out . running SQl server 2000
(sp3). I have had code snippets and examples thrown at me, yet im missing
something fundermental .. if i use the following code in TSQL i get a
sucess on the executon of the DTS
Declare @.Packagename varchar(255) -- Gets most recent Version
Declare @.Userpwd Varchar(255) -- Login Password
Declare @.Intsecurity bit
Declare @.pkgpwd varchar(255)
Declare @.hr int
Declare @.Object int
Set @.Intsecurity = 0
Set @.Userpwd = 'MyPassword'
set @.pkgpwd = NULL
Set @.Packagename = 'TESTDTS'
-- Create the Package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.Object OUTPUT
If @.hr <> 0
Begin
Print 'Error Creating Package'
End
Else
Begin
Print 'Package Created'
End
-- Load the package
Declare @.svr varchar(15)
Declare @.login varchar(100)
Select @.login = 'MyUserName'
Select @.svr = @.@.serverName
Declare @.flag int
Select @.flag = 0
if @.intsecurity = 0
if @.userpwd = Null
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd
else
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd, @.ServerPassword = @.userpwd
else
begin
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword
=
@.pkgPwd
end
If @.hr <> 0
Begin
Print 'Error Loading Package'
End
Else
Begin
Print 'Package loaded'
End
EXEC @.hr = sp_OAMethod @.object, 'Execute'
If @.hr <> 0
Begin
Print 'Error Executing Package'
End
Else
Begin
Print 'Package Executed'
End
-- unitialize the package
EXEC @.hr = sp_OAMethod @.object, 'UnInitialize'
If @.hr <> 0
Begin
Print 'Error UnInitializing Package'
End
Else
Begin
Print 'Package UnInitialized'
End
-- release the package object
EXEC @.hr = sp_OADestroy @.object
If @.hr <> 0
Begin
Print 'Error Releasing Package'
End
Else
Begin
Print 'Package Released'
End
That use's SQL Authentication, however i need to use Wondows Authenticaton.
i can log on query analiser using windows Authentication, yet no matter what
i seem to do to the variables to try and get it to use Windows
Authentication it always fails. The SQL server is currently set for SQl and
Windows Authentication. Can anybody shed any light on what im overlooking ?> if @.userpwd = Null
What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
ML|||ML, this code was taken from a help page.. I have amended as you suggested
but the results are still the same . I Log onto the network using my logon
user name and password. I can open a session of T-SQL and log on usoing
windows authentication without a problem.. so i dont understand why the
loadfromsqlserver wont allow me to use windows Authentication
"ML" wrote:
> What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
>
> ML|||What about the @.Intsecurity variable? Have you tried setting it to 1 ?
ML|||ML
I have set @.Intsecurity = 1 , @.Userpwd = Null, @.login = SUSER_SNAME() and
@.Flag = 256 . still get an error -2147217843 when loading the package
"ML" wrote:
> What about the @.Intsecurity variable? Have you tried setting it to 1 ?
>
> ML|||Please post the entire error message.
Have you tried contacting the author of the script?
Is there a special reason behind executing the DTS package from T-SQL?
ML|||ML,
Im going to give up on this idea, Ive spent days at this problem with no
solution. I dont seem to be able to get any sort of error message back, onl
y
-2147217843 when i interrigate the value of @.hr. I can not find that error
number in the sql server books online. I have looked in the Server Logs ..
but nothing in there. Im a a total loss as to why this wont work with Window
s
Authentication and more frustrated at the fact that i carnt find out what is
causing the problem. I had origionaly tried to hav this run from a VB6
application that gets fired as an event from a FTP server, but then i was
still having issues on accesss rights across the network when trying to open
a txt file. I will fire a question of to the author of the code... see if he
can help at all .... if not its back to the drawing board
thanks for taking the time out to try and help
"ML" wrote:
> Please post the entire error message.
> Have you tried contacting the author of the script?
> Is there a special reason behind executing the DTS package from T-SQL?
>
> ML
(sp3). I have had code snippets and examples thrown at me, yet im missing
something fundermental .. if i use the following code in TSQL i get a
sucess on the executon of the DTS
Declare @.Packagename varchar(255) -- Gets most recent Version
Declare @.Userpwd Varchar(255) -- Login Password
Declare @.Intsecurity bit
Declare @.pkgpwd varchar(255)
Declare @.hr int
Declare @.Object int
Set @.Intsecurity = 0
Set @.Userpwd = 'MyPassword'
set @.pkgpwd = NULL
Set @.Packagename = 'TESTDTS'
-- Create the Package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.Object OUTPUT
If @.hr <> 0
Begin
Print 'Error Creating Package'
End
Else
Begin
Print 'Package Created'
End
-- Load the package
Declare @.svr varchar(15)
Declare @.login varchar(100)
Select @.login = 'MyUserName'
Select @.svr = @.@.serverName
Declare @.flag int
Select @.flag = 0
if @.intsecurity = 0
if @.userpwd = Null
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd
else
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd, @.ServerPassword = @.userpwd
else
begin
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword
=
@.pkgPwd
end
If @.hr <> 0
Begin
Print 'Error Loading Package'
End
Else
Begin
Print 'Package loaded'
End
EXEC @.hr = sp_OAMethod @.object, 'Execute'
If @.hr <> 0
Begin
Print 'Error Executing Package'
End
Else
Begin
Print 'Package Executed'
End
-- unitialize the package
EXEC @.hr = sp_OAMethod @.object, 'UnInitialize'
If @.hr <> 0
Begin
Print 'Error UnInitializing Package'
End
Else
Begin
Print 'Package UnInitialized'
End
-- release the package object
EXEC @.hr = sp_OADestroy @.object
If @.hr <> 0
Begin
Print 'Error Releasing Package'
End
Else
Begin
Print 'Package Released'
End
That use's SQL Authentication, however i need to use Wondows Authenticaton.
i can log on query analiser using windows Authentication, yet no matter what
i seem to do to the variables to try and get it to use Windows
Authentication it always fails. The SQL server is currently set for SQl and
Windows Authentication. Can anybody shed any light on what im overlooking ?> if @.userpwd = Null
What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
ML|||ML, this code was taken from a help page.. I have amended as you suggested
but the results are still the same . I Log onto the network using my logon
user name and password. I can open a session of T-SQL and log on usoing
windows authentication without a problem.. so i dont understand why the
loadfromsqlserver wont allow me to use windows Authentication
"ML" wrote:
> What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
>
> ML|||What about the @.Intsecurity variable? Have you tried setting it to 1 ?
ML|||ML
I have set @.Intsecurity = 1 , @.Userpwd = Null, @.login = SUSER_SNAME() and
@.Flag = 256 . still get an error -2147217843 when loading the package
"ML" wrote:
> What about the @.Intsecurity variable? Have you tried setting it to 1 ?
>
> ML|||Please post the entire error message.
Have you tried contacting the author of the script?
Is there a special reason behind executing the DTS package from T-SQL?
ML|||ML,
Im going to give up on this idea, Ive spent days at this problem with no
solution. I dont seem to be able to get any sort of error message back, onl
y
-2147217843 when i interrigate the value of @.hr. I can not find that error
number in the sql server books online. I have looked in the Server Logs ..
but nothing in there. Im a a total loss as to why this wont work with Window
s
Authentication and more frustrated at the fact that i carnt find out what is
causing the problem. I had origionaly tried to hav this run from a VB6
application that gets fired as an event from a FTP server, but then i was
still having issues on accesss rights across the network when trying to open
a txt file. I will fire a question of to the author of the code... see if he
can help at all .... if not its back to the drawing board
thanks for taking the time out to try and help
"ML" wrote:
> Please post the entire error message.
> Have you tried contacting the author of the script?
> Is there a special reason behind executing the DTS package from T-SQL?
>
> ML
Friday, March 9, 2012
HELP - SQL Server Crash ? Memory leak ?
Hi everybody !
Im maintaining a large intranet (approx 10000 concurrent users) running on
one IIS box and one DB box with sqlserver 2000.
Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on
the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
be using it all.
Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.
There is no indications in the counters before the crashes, they just happen
very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage and
mostly the sqlserver then crashes an hour or 2 later.
The only thing that peaks a lot are the locks/sec counter.
My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.
Can anybody give me a clue as to what i should do ?
Best regards, Thue"Thue Tuxen Srensen" <tuxen@.esynergy.dk> wrote in message
news:3ffd487f$0$9746$edfadb0f@.dread14.news.tele.dk ...
> Hi everybody !
> Im maintaining a large intranet (approx 10000 concurrent users) running
on
> one IIS box and one DB box with sqlserver 2000.
> Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed
on
> the db box.
> Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
> be using it all.
> Currently SQLSERVER 2000 crashes at least once a day.
> Its very weird, I run performance monitor with counters on, memory, disk
> usage, num users, locks and such.
> There is no indications in the counters before the crashes, they just
happen
> very sudden.
> Only indication is that sqlserver makes some huge jumps in memory usage
and
> mostly the sqlserver then crashes an hour or 2 later.
> The only thing that peaks a lot are the locks/sec counter.
> My analysis of disk usage, queues etc. tells me i got no kind of i/o
> bottlenecks.
> Can anybody give me a clue as to what i should do ?
First, make sure you've applied all the latest service packs.
Also, look at the most recent errorlog after a crash (errorlog.1 most
likely). It should have a dump of what was going on.
That might give you a clue.
Also check your event log for anything.
Finally, if this doesn't turn up anything, call Microsoft.
SQL Server does not normally crash. I have some boxes that ran for more
than a year before we had to reboot them due to a physical move.
> Best regards, Thue|||1. Check the server logs for any informative error messages.
"Thue Tuxen Srensen" <tuxen@.esynergy.dk> wrote in message
news:3ffd487f$0$9746$edfadb0f@.dread14.news.tele.dk ...
> Hi everybody !
> Im maintaining a large intranet (approx 10000 concurrent users) running
on
> one IIS box and one DB box with sqlserver 2000.
> Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed
on
> the db box.
> Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
> be using it all.
> Currently SQLSERVER 2000 crashes at least once a day.
> Its very weird, I run performance monitor with counters on, memory, disk
> usage, num users, locks and such.
> There is no indications in the counters before the crashes, they just
happen
> very sudden.
> Only indication is that sqlserver makes some huge jumps in memory usage
and
> mostly the sqlserver then crashes an hour or 2 later.
> The only thing that peaks a lot are the locks/sec counter.
> My analysis of disk usage, queues etc. tells me i got no kind of i/o
> bottlenecks.
> Can anybody give me a clue as to what i should do ?
> Best regards, Thue|||[posted and mailed, vnligen svara i nys]
Thue Tuxen Srensen (tuxen@.esynergy.dk) writes:
> Only indication is that sqlserver makes some huge jumps in memory usage
> and mostly the sqlserver then crashes an hour or 2 later.
The fact that the memory usage of SQL Server jumps, is perfectly normal,
and is only a sign of that someone is using the application.
By default, SQL Server grabs as much memory it can. This is because the
bigger the cache SQL Server can have, the better will the response time
be.
Possible causes for SQL Server crashes:
* Bug in SQL Server, provoked by some SQL statement.
* Access violation in a extended procedure or OLE object that is called
by SQL Server from application code.
* Hardware problems.
The error log for SQL Server should give information about the case.
If I were you, I would investigate the second point before I opened a
case with Microsoft, because this is the most likely reason.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all the answers !
Its running with SP3.
I think I explained the crashes a bit wrong before maybe ...
What I mean is that the sqlserver suddenly 'hangs' and that its impossible
to communicate with in any way.
The performance monitor also stops getting input and just freezes.
The only way to get the site up running again is to restart the sqlserver
service (not the server).
Theres no indication in the errorlogs, as to what happens just before the
'crash'.
Ive looked through all of them, to see if any of them had some info I could
use.
All errorlogs begin with info regarding the startup of sqlserver
initialising the listener and starting up the dbs and such.
After all the info regarding the startup there is nothing in the log.
The next piece of info in the log is the entry where it writes that
sqlserver is terminating due to 'stop' request from service control manager.
And the stop request is issued by me after the system has crashed / is
hanging.
The event viewer is also not helping with anything.
No messages regarding what could cause the error.
Im really frustrated about the problem, because I dont have a clue to
chase down.
But thanks agian for all the answers and your time.
Please do not hesitate to write ! :o) if any of you suddenly comes up with
more things I could check out before calling in a pro.
Best Regards Thue|||Hi there
I share your pain and frustration. I would like all possible causes of CPU
100% to be listed somewhere so I can check that I have taken all precautions
to avoid this. Is there such a page anywhere?!
I want to resolve the CPU 100% problem myself - I thought I had resolved
before but has come back to haunt me now with a new database server machine
with SQL Server 2000, SP 3, which apparently does not allow one to fully
remove the "named pipes" protocol. I resolved a CPU 100% issue months ago by
making sure only TCP/IP was used as the protocol, and removing Named pipes.
Removing from the Enterprise manager (button at bottom in general settings
tab), network protcol "named pipes", and from client connection settings
manager, so that only TCP/IP is allowed as a network protocol. My theory is
that these pipes become blocked, and this causes 100% CPU usage. Could
anyone confirm that this is a known symptom of the named pipes protocol ?!
I am currently having to reboot the machine every few days now since we put
in a new database server with the latest service packs (SQL Server 2000,
SP3). Removing the named-pipes protcol does not seem to have resolved this
nasty problem this time round. I have seen on some newsgroup postings, that
it is no longer possible to actually remove Named Pipes fully since SP3.
The following article thread indicates this:-
http://www.mcse.ms/message97673.html
which is kind of worrying to me, because I was fairly sure removing Named
Pipes as a protocol before, completely cured the CPU 100% symptoms.
My correspondence chess website www.chessworld.net makes heavy use of SQL
Server 2000. It has been running for over 2 years now, and sometimes has
about 200 members online or more within the space of 10 minutes. Overall SQL
Server 2000 has been great, but recently these reboots have been quite
frustrating, and I cannot seem to identify the cause. I continually monitor
any ASP pages that time out with SQL Server errors, and always keen to
ensure all queries run quick on my site. I do not think it is a bad sql
query problem. I continually make efforts to optimise all queries used on
the site. I have also made sure from a long time ago that (NO LOCK) is being
used on select statements to minimise lock escalation.
I found the following article today which is another possible cause of CPU
100%:-
http://support.microsoft.com/defaul...&NoWebContent=1
which possible attributes the Microsoft search service to CPU 100%. I have
now disabled this service from our new database server machine, and put it
to Manual on Startup.
Help needed to resolve CPU 100% issue !
Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net
"Thue Tuxen Srensen" <tuxen@.esynergy.dk> wrote in message
news:3ffd487f$0$9746$edfadb0f@.dread14.news.tele.dk ...
> Hi everybody !
> Im maintaining a large intranet (approx 10000 concurrent users) running
on
> one IIS box and one DB box with sqlserver 2000.
> Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed
on
> the db box.
> Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
> be using it all.
> Currently SQLSERVER 2000 crashes at least once a day.
> Its very weird, I run performance monitor with counters on, memory, disk
> usage, num users, locks and such.
> There is no indications in the counters before the crashes, they just
happen
> very sudden.
> Only indication is that sqlserver makes some huge jumps in memory usage
and
> mostly the sqlserver then crashes an hour or 2 later.
> The only thing that peaks a lot are the locks/sec counter.
> My analysis of disk usage, queues etc. tells me i got no kind of i/o
> bottlenecks.
> Can anybody give me a clue as to what i should do ?
> Best regards, Thue|||"Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
news:btmms5$3n2$1$830fa78d@.news.demon.co.uk...
> Hi there
> I share your pain and frustration. I would like all possible causes of CPU
> 100% to be listed somewhere so I can check that I have taken all
precautions
> to avoid this. Is there such a page anywhere?!
> I want to resolve the CPU 100% problem myself - I thought I had resolved
> before but has come back to haunt me now with a new database server
machine
> with SQL Server 2000, SP 3, which apparently does not allow one to fully
> remove the "named pipes" protocol. I resolved a CPU 100% issue months ago
by
> making sure only TCP/IP was used as the protocol, and removing Named
pipes.
> Removing from the Enterprise manager (button at bottom in general settings
> tab), network protcol "named pipes", and from client connection settings
> manager, so that only TCP/IP is allowed as a network protocol. My theory
is
> that these pipes become blocked, and this causes 100% CPU usage. Could
> anyone confirm that this is a known symptom of the named pipes protocol ?!
Nope, never seen that happen.
> I am currently having to reboot the machine every few days now since we
put
> in a new database server with the latest service packs (SQL Server 2000,
> SP3). Removing the named-pipes protcol does not seem to have resolved this
> nasty problem this time round. I have seen on some newsgroup postings,
that
> it is no longer possible to actually remove Named Pipes fully since SP3.
> The following article thread indicates this:-
> http://www.mcse.ms/message97673.html
> which is kind of worrying to me, because I was fairly sure removing Named
> Pipes as a protocol before, completely cured the CPU 100% symptoms.
> My correspondence chess website www.chessworld.net makes heavy use of SQL
> Server 2000. It has been running for over 2 years now, and sometimes has
> about 200 members online or more within the space of 10 minutes. Overall
SQL
> Server 2000 has been great, but recently these reboots have been quite
> frustrating, and I cannot seem to identify the cause. I continually
monitor
> any ASP pages that time out with SQL Server errors, and always keen to
> ensure all queries run quick on my site. I do not think it is a bad sql
> query problem. I continually make efforts to optimise all queries used on
> the site. I have also made sure from a long time ago that (NO LOCK) is
being
> used on select statements to minimise lock escalation.
Keep in mind that is NOT always a good solution.
> I found the following article today which is another possible cause of CPU
> 100%:-
>
http://support.microsoft.com/defaul...&NoWebContent=1
> which possible attributes the Microsoft search service to CPU 100%. I have
> now disabled this service from our new database server machine, and put it
> to Manual on Startup.
> Help needed to resolve CPU 100% issue !
Best bet is probably to try to have profiler running.
There can be many reasons. Keep in mind it's perfectly possible to be using
100% of the CPU and it not be a bug etc. It could simply be you're that
busy.
We have a DB server that from time to time hits 100% CPU. And stays that
way for a few seconds or more. It hurts performance, but returns to normal.
> Best wishes
> Tryfon Gavriel
> Webmaster
> www.chessworld.net
>
>
> "Thue Tuxen Srensen" <tuxen@.esynergy.dk> wrote in message
> news:3ffd487f$0$9746$edfadb0f@.dread14.news.tele.dk ...
> > Hi everybody !
> > Im maintaining a large intranet (approx 10000 concurrent users) running
> on
> > one IIS box and one DB box with sqlserver 2000.
> > Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed
> on
> > the db box.
> > Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem
to
> > be using it all.
> > Currently SQLSERVER 2000 crashes at least once a day.
> > Its very weird, I run performance monitor with counters on, memory, disk
> > usage, num users, locks and such.
> > There is no indications in the counters before the crashes, they just
> happen
> > very sudden.
> > Only indication is that sqlserver makes some huge jumps in memory usage
> and
> > mostly the sqlserver then crashes an hour or 2 later.
> > The only thing that peaks a lot are the locks/sec counter.
> > My analysis of disk usage, queues etc. tells me i got no kind of i/o
> > bottlenecks.
> > Can anybody give me a clue as to what i should do ?
> > Best regards, Thue|||"Thue Tuxen Srensen" <tuxen@.esynergy.dk> wrote in message
news:3ffe994e$0$9745$edfadb0f@.dread14.news.tele.dk ...
> Thanks for all the answers !
> Its running with SP3.
Good.
> I think I explained the crashes a bit wrong before maybe ...
> What I mean is that the sqlserver suddenly 'hangs' and that its impossible
> to communicate with in any way.
You contradict this down below. Which is somewhat critical.
> The performance monitor also stops getting input and just freezes.
What metrics are you measuring.
> The only way to get the site up running again is to restart the sqlserver
> service (not the server).
> Theres no indication in the errorlogs, as to what happens just before the
> 'crash'.
> Ive looked through all of them, to see if any of them had some info I
could
> use.
> All errorlogs begin with info regarding the startup of sqlserver
> initialising the listener and starting up the dbs and such.
> After all the info regarding the startup there is nothing in the log.
> The next piece of info in the log is the entry where it writes that
> sqlserver is terminating due to 'stop' request from service control
manager.
Ok. This indicates that the server IS listening.
One thing you may want to do is issue a NET STOP SQLSERVERAGENT command
followed by NET STOP MSSQLSERVER and see which one (if either takes a long
period of time).
When starting does it start up quickly or take time? Is there anything in
the error log about recovering a DB?
Also, does your app call ANY extended stored procs (XP_fooname)
> And the stop request is issued by me after the system has crashed / is
> hanging.
Yeah. I wouldn't call this a crash. Not even sure I'd call it a hang. But
that's partly semantics.
> The event viewer is also not helping with anything.
> No messages regarding what could cause the error.
> Im really frustrated about the problem, because I dont have a clue to
> chase down.
> But thanks agian for all the answers and your time.
> Please do not hesitate to write ! :o) if any of you suddenly comes up with
> more things I could check out before calling in a pro.
Just the above.
What happens if you wait? (how long do you wait before cycling it?)
> Best Regards Thue|||Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> I am currently having to reboot the machine every few days now since we
> put in a new database server with the latest service packs (SQL Server
> 2000, SP3). Removing the named-pipes protcol does not seem to have
> resolved this nasty problem this time round. I have seen on some
> newsgroup postings, that it is no longer possible to actually remove
> Named Pipes fully since SP3.
100% CPU may not be cause for alarm. When SQL Server becomes completely
unresponsive, it certainly is.
I know of two way this can happen. Or rather, I know of one, and one
"seemingly unresponsive". The one case where it becomes unresponsive,
is error 17883. If this happens, you should see this in the error log,
where you get a load of these messages. The message only appears with
SP3 or later hotfixes.
The other case I've seen was with some poor SQL. In this particular
case I was testing performance of this poor SQL for an article on my
web site. I was surprised to see that this particular query took so
much CPU, that issuing an sp_who could have a response time on over
30 seconds.
But there are probably more possibilities than these two. But then again,
it certainly not somehing which happens all over town, so if your SQL
Server becomes unresponsive, there is something fishy on your machine,
be that hardware or poor SQL statements.
One way to track down the latter is to have a profiler trace running,
and see what you get just before the machine goes into nirvana.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi again.
Im measuring CPU, MEMORY, LOCKS and Disk Usage.
Nothing speciel in the logs about revovering.
We dont use any extended procedures.
I havent tried to wait for a long time before restarting it, because theres
a lot of users waiting for it to be up again.
Usually i wait like 5 minutes or so.
/Thue
"Greg D. Moore (Strider)" <mooregr@.greenms.com> skrev i en meddelelse
news:k2HLb.131878$JW3.4033@.twister.nyroc.rr.com...
> "Thue Tuxen Srensen" <tuxen@.esynergy.dk> wrote in message
> news:3ffe994e$0$9745$edfadb0f@.dread14.news.tele.dk ...
> > Thanks for all the answers !
> > Its running with SP3.
> Good.
>
> > I think I explained the crashes a bit wrong before maybe ...
> > What I mean is that the sqlserver suddenly 'hangs' and that its
impossible
> > to communicate with in any way.
> You contradict this down below. Which is somewhat critical.
>
> > The performance monitor also stops getting input and just freezes.
> What metrics are you measuring.
>
> > The only way to get the site up running again is to restart the
sqlserver
> > service (not the server).
> > Theres no indication in the errorlogs, as to what happens just before
the
> > 'crash'.
> > Ive looked through all of them, to see if any of them had some info I
> could
> > use.
> > All errorlogs begin with info regarding the startup of sqlserver
> > initialising the listener and starting up the dbs and such.
> > After all the info regarding the startup there is nothing in the log.
> > The next piece of info in the log is the entry where it writes that
> > sqlserver is terminating due to 'stop' request from service control
> manager.
> Ok. This indicates that the server IS listening.
> One thing you may want to do is issue a NET STOP SQLSERVERAGENT command
> followed by NET STOP MSSQLSERVER and see which one (if either takes a long
> period of time).
> When starting does it start up quickly or take time? Is there anything in
> the error log about recovering a DB?
> Also, does your app call ANY extended stored procs (XP_fooname)
>
> > And the stop request is issued by me after the system has crashed / is
> > hanging.
> Yeah. I wouldn't call this a crash. Not even sure I'd call it a hang.
But
> that's partly semantics.
> > The event viewer is also not helping with anything.
> > No messages regarding what could cause the error.
> > Im really frustrated about the problem, because I dont have a clue to
> > chase down.
> > But thanks agian for all the answers and your time.
> > Please do not hesitate to write ! :o) if any of you suddenly comes up
with
> > more things I could check out before calling in a pro.
> Just the above.
> What happens if you wait? (how long do you wait before cycling it?)
>
> > Best Regards Thue|||Thank you Greg and Erland
I ran an Event trace using SQL profiler when CPU was at 100%, generating
approaching 3200 rows within a few minutes, and interestingly, ordering by
"Duration" revealed the following entries:-
(The first six are all event type 15 - which is "Disconnect" i believe. They
have massive duration times, and massive values for Reads.)
1002 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 90
616436 2004-01-12 18:04:35.687 187793 2 3326
1474 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 58
614733 2004-01-12 18:04:44.687 212743 1 20373
3118 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 108
612796 2004-01-12 18:05:42.107 215728 0 19657
2522 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 66
600640 2004-01-12 18:05:41.810 281198 5 12674
1881 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 72
256296 2004-01-12 18:10:57.093 69592 0 375
353 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 86
126046 2004-01-12 18:12:17.403 331 0 15
974 10 declare @.P1 int set @.P1=180150025 declare @.P2 int set @.P2=8 declare
@.P3 int set @.P3=1 declare @.P4 int set @.P4=0 exec sp_cursoropen @.P1 output,
N' select (select IsNull(min(boardnumber),0) from boardsplayers a WITH
(NOLOCK), games b WITH (NOLOCK) where a NULL 1320 Microsoft(R) Windows (R)
2000 Operating System sa 62 36763 2004-01-12 18:14:14.797 19868 0 0
I am not sure how to interpret these events. What does a massive duration on
Event Type 15 mean?! Also there are a massive amount of "Reads" associated
with these.
Any help greatly appreciated!
If I order by the CPU column descending, the top 10 rows are:-
1474 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 58
614733 2004-01-12 18:04:44.687 212743 1 20373
3118 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 108
612796 2004-01-12 18:05:42.107 215728 0 19657
2522 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 66
600640 2004-01-12 18:05:41.810 281198 5 12674
1002 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 90
616436 2004-01-12 18:04:35.687 187793 2 3326
56 12 SELECT
notepadvisible,sandbagger,tournamentsummaryview,ad min,ShowRatingsofplayers,j
avascriptboardcreator,htmlemails,listcurrentgamesv iew,logincount,JavaScriptS
upportLevel,RatingPredictorStyle,MessageBoxType,La nguageID,TeamCreator,banne
rs,IsNull(CountryID,1 NULL 1320 Microsoft(R) Windows (R) 2000 Operating
System sa 102 12843 2004-01-12 18:14:00.733 11599 0 2875
954 12 SELECT
notepadvisible,sandbagger,tournamentsummaryview,ad min,ShowRatingsofplayers,j
avascriptboardcreator,htmlemails,listcurrentgamesv iew,logincount,JavaScriptS
upportLevel,RatingPredictorStyle,MessageBoxType,La nguageID,TeamCreator,banne
rs,IsNull(CountryID,1 NULL 1320 Microsoft(R) Windows (R) 2000 Operating
System sa 96 25050 2004-01-12 18:14:25.340 11599 0 2797
1817 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 85
6810 2004-01-12 18:15:02.640 3484 0 1516
1533 12 select GameNumber,TournamentID from TournamentGames WITH (NOLOCK)
where gamenumber = 349913 NULL 1320 Microsoft(R) Windows (R) 2000 Operating
System sa 58 1563 2004-01-12 18:14:59.437 755 0 1015
1553 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 58
1966 2004-01-12 18:14:59.437 765 0 1015
1881 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 72
256296 2004-01-12 18:10:57.093 69592 0 375
Again any help in diagnosing the cause of this, which puts the CPU to 100%
would be greatly appreciated.
Best wishes
Tryfon
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns946C95ED49A6Yazorman@.127.0.0.1...
> Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> > I am currently having to reboot the machine every few days now since we
> > put in a new database server with the latest service packs (SQL Server
> > 2000, SP3). Removing the named-pipes protcol does not seem to have
> > resolved this nasty problem this time round. I have seen on some
> > newsgroup postings, that it is no longer possible to actually remove
> > Named Pipes fully since SP3.
> 100% CPU may not be cause for alarm. When SQL Server becomes completely
> unresponsive, it certainly is.
> I know of two way this can happen. Or rather, I know of one, and one
> "seemingly unresponsive". The one case where it becomes unresponsive,
> is error 17883. If this happens, you should see this in the error log,
> where you get a load of these messages. The message only appears with
> SP3 or later hotfixes.
> The other case I've seen was with some poor SQL. In this particular
> case I was testing performance of this poor SQL for an article on my
> web site. I was surprised to see that this particular query took so
> much CPU, that issuing an sp_who could have a response time on over
> 30 seconds.
> But there are probably more possibilities than these two. But then again,
> it certainly not somehing which happens all over town, so if your SQL
> Server becomes unresponsive, there is something fishy on your machine,
> be that hardware or poor SQL statements.
> One way to track down the latter is to have a profiler trace running,
> and see what you get just before the machine goes into nirvana.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> (The first six are all event type 15 - which is "Disconnect" i believe.
> They have massive duration times, and massive values for Reads.)
>...
> I am not sure how to interpret these events. What does a massive
> duration on Event Type 15 mean?! Also there are a massive amount of
> "Reads" associated with these.
As you said, event 15 is disconnection. Duration is just how long the
connection was open. And Reads are just the accumulated number of
reads during that session.
In itself, not that exciting. Then again, maybe it is a clue that four
long-running processes owned by sa quits just before the machines
reaches nirvana. No, please don't ask me what that clue would mean!
It is possible that the SQL statements you see when you sort on Duration
has anything to do with the CPU hog. However, I wouid not really expect
that process to show up. I would include the Starting events in the trace,
and then investigate the uncompleted events at the end of the trace
when the CPU goes 100%.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946EED4BB70EFYazorman@.127.0.0.1>...
> Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> > (The first six are all event type 15 - which is "Disconnect" i believe.
> > They have massive duration times, and massive values for Reads.)
> >...
> > I am not sure how to interpret these events. What does a massive
> > duration on Event Type 15 mean?! Also there are a massive amount of
> > "Reads" associated with these.
> As you said, event 15 is disconnection. Duration is just how long the
> connection was open. And Reads are just the accumulated number of
> reads during that session.
> In itself, not that exciting. Then again, maybe it is a clue that four
> long-running processes owned by sa quits just before the machines
> reaches nirvana. No, please don't ask me what that clue would mean!
> It is possible that the SQL statements you see when you sort on Duration
> has anything to do with the CPU hog. However, I wouid not really expect
> that process to show up. I would include the Starting events in the trace,
> and then investigate the uncompleted events at the end of the trace
> when the CPU goes 100%.
TJI:
I have seen a similar problem (loss of connectivity, performance
grinds to a halt, etc.) when one of our servers has 3 (or more)
exchange waits active for extended periods of time. We start seeing
performance degradation at 2 exchange waits, which raises the flag for
us.
What is happening in our case is that users for some reason disconnect
their side (reboot their workstation, etc) with bad timing, just in
the middle of a network handshake before responding to the server. The
server just waits for the packet from the client that never comes.
These transactions stay alive, tying up datapages and locks, which
escalate as users log back in and retry....
(We saw this by waiting, sometimes as long as 1/2 hour, for EM to
connect, then waiting again for the Process list to present itself.)
With your large number of concurrent users, I would not be surprised
if this is not your problem.|||Hi Erland and all
The server has been standing for 2 weeks without a reboot. This has been a
great relief to me. If my solution may help others, the two things I did
were:-
a) Simplify some of the SQL - taking out some luxury sub-queries off many
pages
b) Taking off auto-grow from three of the databases - tempdb, the main
Chessworld db, and master.
I was not exactly sure if it was a) or b) but I have more evidence now it
was in fact b) that was causing massive slow-downs requiring a reboot
because CPU seems to go unrecoverably to 100%.
The reason for more evidence, is that today, I finally had a "cannot
allocate space error" being logged. I increased the size of the chessworld
db, and the tempdb, and put back the auto-grow on the chessworld db. Within
about an hour or two, the symptoms of a big slow-down came back with CPU
100%.
I rebooted the database server but have again taken off auto-grow options. I
believe for my site with many concurrent users, the auto-grow is causing
issues. I will keep you posted.
Best wishes
Tryfon
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns946EED4BB70EFYazorman@.127.0.0.1...
> Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> > (The first six are all event type 15 - which is "Disconnect" i believe.
> > They have massive duration times, and massive values for Reads.)
> >...
> > I am not sure how to interpret these events. What does a massive
> > duration on Event Type 15 mean?! Also there are a massive amount of
> > "Reads" associated with these.
> As you said, event 15 is disconnection. Duration is just how long the
> connection was open. And Reads are just the accumulated number of
> reads during that session.
> In itself, not that exciting. Then again, maybe it is a clue that four
> long-running processes owned by sa quits just before the machines
> reaches nirvana. No, please don't ask me what that clue would mean!
> It is possible that the SQL statements you see when you sort on Duration
> has anything to do with the CPU hog. However, I wouid not really expect
> that process to show up. I would include the Starting events in the trace,
> and then investigate the uncompleted events at the end of the trace
> when the CPU goes 100%.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
news:bvhpk1$7qm$1$8302bc10@.news.demon.co.uk...
> Hi Erland and all
> The server has been standing for 2 weeks without a reboot. This has been a
> great relief to me. If my solution may help others, the two things I did
> were:-
> a) Simplify some of the SQL - taking out some luxury sub-queries off many
> pages
> b) Taking off auto-grow from three of the databases - tempdb, the main
> Chessworld db, and master.
That'll do it right there.
Here's a typical scenario:
DB growth is set to 10%
DB is 100MB...
An insert is performed... limit gets reached. So, now the DB wants to
expand.
It starts to allocate 10MB.
During this time, deletes and updates can generally be performed, but
basically any additional inserts will be blocked while the space is
allocated. (and any updates or deletes that need to occur on those blocked
inserts obviously get blocked.)
Now, SQL Server can generally allocate 10MB pretty quick.
But now you've got 110MB. Next expansion will be 11MB. Putting you at
121MB. Next one will be 12.1 MB. And this continues.
Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and the
kicker is, it probably only needs 10MB at that point. :-)
And of course during this allocation, the DB appears hung.
So, I generally try NOT to allow auto-growth, or set it to a fixed amount
(like 10MB or 100MB, etc. depending on the size and type of DB).
Also, this can occur a lot with transaction logs. Which generally means
that no transcation backups are being done. Which on a production DB is
almost always a "bad thing".
Hmm, given what yu say, I'm guessing that your tempb may be growing a lot.
(Since upon restart I believe it'll get resized back to it's original size.)
This could be a result of a bad design, or simply the result of a necessary
design.
What I'd do is check which DB is growing the most and resize it.
The master DB normally should not grow much at all.
So it's most likely the tempdb or the chessworld one. (as he states the
obvious.)
> I was not exactly sure if it was a) or b) but I have more evidence now it
> was in fact b) that was causing massive slow-downs requiring a reboot
> because CPU seems to go unrecoverably to 100%.
> The reason for more evidence, is that today, I finally had a "cannot
> allocate space error" being logged. I increased the size of the chessworld
> db, and the tempdb, and put back the auto-grow on the chessworld db.
Within
> about an hour or two, the symptoms of a big slow-down came back with CPU
> 100%.
> I rebooted the database server but have again taken off auto-grow options.
I
> believe for my site with many concurrent users, the auto-grow is causing
> issues. I will keep you posted.
Please do.
> Best wishes
> Tryfon
>
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns946EED4BB70EFYazorman@.127.0.0.1...
> > Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> > > (The first six are all event type 15 - which is "Disconnect" i
believe.
> > > They have massive duration times, and massive values for Reads.)
> > >...
> > > > I am not sure how to interpret these events. What does a massive
> > > duration on Event Type 15 mean?! Also there are a massive amount of
> > > "Reads" associated with these.
> > As you said, event 15 is disconnection. Duration is just how long the
> > connection was open. And Reads are just the accumulated number of
> > reads during that session.
> > In itself, not that exciting. Then again, maybe it is a clue that four
> > long-running processes owned by sa quits just before the machines
> > reaches nirvana. No, please don't ask me what that clue would mean!
> > It is possible that the SQL statements you see when you sort on Duration
> > has anything to do with the CPU hog. However, I wouid not really expect
> > that process to show up. I would include the Starting events in the
trace,
> > and then investigate the uncompleted events at the end of the trace
> > when the CPU goes 100%.
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi there
Thank you for that feedback.
I should have also mentioned the following: There was an unexpected database
server shutdown last night recorded in the w2k event viewer. I had to
request the database server to be restarted. Once it did it was working fine
for a while, but then I noticed on my ASP error log, the "cannot allocate
space" errors. It was then that I increased the size, but also put auto-grow
on. It just died within 2 hours. What I then did was take off autogrow, and
restart the database server again.
So basically:-
* I have doubled the size of the tempdb database to 2 gig for data file and
1 gig for transaction log
* I have also doubled the size of the Chessworld database (One concern here
is the time it takes the backup the database, but it still seems to be able
to back it up within a few minutes.. a relief :) )
* Auto-grow taken off both databases
If the server shuts down in 2 months time, then fine. I will request a
database server reboot and increase their sizes again. I cannot have a
background process re-allocating space, when I have tonnes of players online
playing chess moves (or trying to!), resulting in me having to reboot the
server. The "cannot allocate" space errors that occured last night have now
stopped.
The following may be useful for other ASP/SQL Server developers for general
problem diagnosis: About two weeks ago, I knocked up an ASP admin page to
monitor the sysprocesses table. This is useful to me in trying to understand
the processes with greatest CPU usage. I ordered it by CPU, but also made it
highlight in red processes which had a last batch time of more than 10
minutes ago. The idea was to highlight potential processes that could be
killed. I found the following three particularly useful web references :-
Kill documentation:
http://msdn.microsoft.com/library/d..._kf-kz_1zos.asp
Tips for handling blocking:
http://www.sql-server-performance.com/blocking.asp
Understanding and resolving blocking problems:
http://support.microsoft.com/defaul...&NoWebContent=1
Which I have put links at the top of my admin page for viewing processes :-)
It also made me paranoid about the background processes going on- hence my
intuition to turn off the auto-grow tick boxes.
Some insights include - simplification of pages, do seem to lead to
processes consuming less CPU, and generally a faster site. But also the
Background processes are highlighted. I think viewing the sysprocesses table
is very useful point of reference, and the reason I started investigating
it, is because it is mentioned in SQL Serrver 2000 programming book page
1081, where it also highlights using the following tools for analysing
problems:-
a) SHOWPLAN TEXT | ALL
b) STATISTICS IO
c) DBCC
d) Query governer
e) sp_lock
f) sysprocesses table
g) SQL Server Profiler
(should be listed because detailed): h) Perfmon
Before posting to this excellent group, I had not actually used the SQL
profiler much at all. I did have admin pages already for sp_lock and sp_who.
But I usually use sp_lock for analysing locks, and ignored the sp_who most
of the time. The view on sysprocesses is more useful to me because you can
order by cpu, etc. I now regularly look at the sp_lock page and the
"processes" page.
I also make use of the ASP error object to generate errors in a log file,
and my most frequently logged error is now SQL Server related. This means I
can immediately see any bottleneck ASP pages where there is potentially bad
SQL or other issues.
Best wishes
Tryfon
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Ug0Tb.13391$n62.4463@.twister.nyroc.rr.com...
> "Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
> news:bvhpk1$7qm$1$8302bc10@.news.demon.co.uk...
> > Hi Erland and all
> > The server has been standing for 2 weeks without a reboot. This has been
a
> > great relief to me. If my solution may help others, the two things I did
> > were:-
> > a) Simplify some of the SQL - taking out some luxury sub-queries off
many
> > pages
> > b) Taking off auto-grow from three of the databases - tempdb, the main
> > Chessworld db, and master.
> That'll do it right there.
> Here's a typical scenario:
> DB growth is set to 10%
> DB is 100MB...
> An insert is performed... limit gets reached. So, now the DB wants to
> expand.
> It starts to allocate 10MB.
> During this time, deletes and updates can generally be performed, but
> basically any additional inserts will be blocked while the space is
> allocated. (and any updates or deletes that need to occur on those blocked
> inserts obviously get blocked.)
> Now, SQL Server can generally allocate 10MB pretty quick.
> But now you've got 110MB. Next expansion will be 11MB. Putting you at
> 121MB. Next one will be 12.1 MB. And this continues.
> Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and
the
> kicker is, it probably only needs 10MB at that point. :-)
> And of course during this allocation, the DB appears hung.
> So, I generally try NOT to allow auto-growth, or set it to a fixed amount
> (like 10MB or 100MB, etc. depending on the size and type of DB).
> Also, this can occur a lot with transaction logs. Which generally means
> that no transcation backups are being done. Which on a production DB is
> almost always a "bad thing".
> Hmm, given what yu say, I'm guessing that your tempb may be growing a lot.
> (Since upon restart I believe it'll get resized back to it's original
size.)
> This could be a result of a bad design, or simply the result of a
necessary
> design.
> What I'd do is check which DB is growing the most and resize it.
> The master DB normally should not grow much at all.
> So it's most likely the tempdb or the chessworld one. (as he states the
> obvious.)
>
> > I was not exactly sure if it was a) or b) but I have more evidence now i
t
> > was in fact b) that was causing massive slow-downs requiring a reboot
> > because CPU seems to go unrecoverably to 100%.
> > The reason for more evidence, is that today, I finally had a "cannot
> > allocate space error" being logged. I increased the size of the
chessworld
> > db, and the tempdb, and put back the auto-grow on the chessworld db.
> Within
> > about an hour or two, the symptoms of a big slow-down came back with CPU
> > 100%.
> > I rebooted the database server but have again taken off auto-grow
options.
> I
> > believe for my site with many concurrent users, the auto-grow is causing
> > issues. I will keep you posted.
> Please do.
>
> > Best wishes
> > Tryfon
> > "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> > news:Xns946EED4BB70EFYazorman@.127.0.0.1...
> > > Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> > > > (The first six are all event type 15 - which is "Disconnect" i
> believe.
> > > > They have massive duration times, and massive values for Reads.)
> > > >...
> > > > > > I am not sure how to interpret these events. What does a massive
> > > > duration on Event Type 15 mean?! Also there are a massive amount of
> > > > "Reads" associated with these.
> > > > As you said, event 15 is disconnection. Duration is just how long the
> > > connection was open. And Reads are just the accumulated number of
> > > reads during that session.
> > > > In itself, not that exciting. Then again, maybe it is a clue that four
> > > long-running processes owned by sa quits just before the machines
> > > reaches nirvana. No, please don't ask me what that clue would mean!
> > > > It is possible that the SQL statements you see when you sort on
Duration
> > > has anything to do with the CPU hog. However, I wouid not really
expect
> > > that process to show up. I would include the Starting events in the
> trace,
> > > and then investigate the uncompleted events at the end of the trace
> > > when the CPU goes 100%.
> > > > > --
> > > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > > > Books Online for SQL Server SP3 at
> > > http://www.microsoft.com/sql/techin.../2000/books.asp|||Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> * I have also doubled the size of the Chessworld database (One concern
> here is the time it takes the backup the database, but it still seems to
> be able to back it up within a few minutes.. a relief :) )
My experience is that the time to do a backup is related to the actual
amount of data in the database. That is, if you allocate 60 GB for a
1 GB database, then those 59 GB are cheap. (The one occassion they
cost, is when you want to restore a backup into a clone database;
then the allocation of those 59 GB will take 10-20 minutes extra.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
news:bviv5o$hnf$1$8302bc10@.news.demon.co.uk...
> Hi there
> Thank you for that feedback.
> I should have also mentioned the following: There was an unexpected
database
> server shutdown last night recorded in the w2k event viewer. I had to
> request the database server to be restarted. Once it did it was working
fine
> for a while, but then I noticed on my ASP error log, the "cannot allocate
> space" errors. It was then that I increased the size, but also put
auto-grow
> on. It just died within 2 hours. What I then did was take off autogrow,
and
> restart the database server again.
> So basically:-
> * I have doubled the size of the tempdb database to 2 gig for data file
and
> 1 gig for transaction log
That's a fairly large tempdb. I'd question your design if you really need
that much. But I don't know how many users you're supporting.
But it does indicate that perhaps your transactions are lasting for two
long.
Consider this, let's say (and this is a wild guess here) you're supporting 1
million users. If each one needs 1KB of temp data stored, that would be
about 1 gig of data. However, in theory it should be unusual for all 1
million users to need that data stored at once.
But like I say, I don't know much about the design, but I'd be suspicious
about how long your transactions are being held open.
> * I have also doubled the size of the Chessworld database (One concern
here
> is the time it takes the backup the database, but it still seems to be
able
> to back it up within a few minutes.. a relief :) )
Note that a backup will not affect database performance. (or at least the
affect is extremely small because of an increased disk I/O. Note that a
backup does NOT stop processing.)
> * Auto-grow taken off both databases
> If the server shuts down in 2 months time, then fine. I will request a
> database server reboot and increase their sizes again.
Even if the SQL server locks up, simply stopping or worse killing it should
be enough. Rebooting the box should be an absolutel last resort.
> I cannot have a
> background process re-allocating space, when I have tonnes of players
online
> playing chess moves (or trying to!), resulting in me having to reboot the
> server. The "cannot allocate" space errors that occured last night have
now
> stopped.
Understandable.
> The following may be useful for other ASP/SQL Server developers for
general
> problem diagnosis: About two weeks ago, I knocked up an ASP admin page to
> monitor the sysprocesses table. This is useful to me in trying to
understand
> the processes with greatest CPU usage. I ordered it by CPU, but also made
it
> highlight in red processes which had a last batch time of more than 10
> minutes ago. The idea was to highlight potential processes that could be
> killed. I found the following three particularly useful web references :-
Hmm, can you post thse someplace? Might be interesting to use.
Just as a reference we have databases that had uptimes over over a year.
(only reason we needed to change that was due to finally being able to apply
SP3a.)
> Kill documentation:
http://msdn.microsoft.com/library/d..._kf-kz_1zos.asp
> Tips for handling blocking:
> http://www.sql-server-performance.com/blocking.asp
> Understanding and resolving blocking problems:
http://support.microsoft.com/defaul...&NoWebContent=1
> Which I have put links at the top of my admin page for viewing processes
:-)
> It also made me paranoid about the background processes going on- hence my
> intuition to turn off the auto-grow tick boxes.
> Some insights include - simplification of pages, do seem to lead to
> processes consuming less CPU, and generally a faster site. But also the
> Background processes are highlighted. I think viewing the sysprocesses
table
> is very useful point of reference, and the reason I started investigating
> it, is because it is mentioned in SQL Serrver 2000 programming book page
> 1081, where it also highlights using the following tools for analysing
> problems:-
> a) SHOWPLAN TEXT | ALL
> b) STATISTICS IO
> c) DBCC
> d) Query governer
> e) sp_lock
> f) sysprocesses table
> g) SQL Server Profiler
> (should be listed because detailed): h) Perfmon
> Before posting to this excellent group, I had not actually used the SQL
> profiler much at all. I did have admin pages already for sp_lock and
sp_who.
> But I usually use sp_lock for analysing locks, and ignored the sp_who most
> of the time. The view on sysprocesses is more useful to me because you can
> order by cpu, etc. I now regularly look at the sp_lock page and the
> "processes" page.
> I also make use of the ASP error object to generate errors in a log file,
> and my most frequently logged error is now SQL Server related. This means
I
> can immediately see any bottleneck ASP pages where there is potentially
bad
> SQL or other issues.
That's some good stuff.
> Best wishes
> Tryfon
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:Ug0Tb.13391$n62.4463@.twister.nyroc.rr.com...
> > "Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
> > news:bvhpk1$7qm$1$8302bc10@.news.demon.co.uk...
> > > Hi Erland and all
> > > > The server has been standing for 2 weeks without a reboot. This has
been
> a
> > > great relief to me. If my solution may help others, the two things I
did
> > > were:-
> > > > a) Simplify some of the SQL - taking out some luxury sub-queries off
> many
> > > pages
> > > b) Taking off auto-grow from three of the databases - tempdb, the main
> > > Chessworld db, and master.
> > > That'll do it right there.
> > Here's a typical scenario:
> > DB growth is set to 10%
> > DB is 100MB...
> > An insert is performed... limit gets reached. So, now the DB wants to
> > expand.
> > It starts to allocate 10MB.
> > During this time, deletes and updates can generally be performed, but
> > basically any additional inserts will be blocked while the space is
> > allocated. (and any updates or deletes that need to occur on those
blocked
> > inserts obviously get blocked.)
> > Now, SQL Server can generally allocate 10MB pretty quick.
> > But now you've got 110MB. Next expansion will be 11MB. Putting you at
> > 121MB. Next one will be 12.1 MB. And this continues.
> > Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and
> the
> > kicker is, it probably only needs 10MB at that point. :-)
> > And of course during this allocation, the DB appears hung.
> > So, I generally try NOT to allow auto-growth, or set it to a fixed
amount
> > (like 10MB or 100MB, etc. depending on the size and type of DB).
> > Also, this can occur a lot with transaction logs. Which generally
means
> > that no transcation backups are being done. Which on a production DB is
> > almost always a "bad thing".
> > Hmm, given what yu say, I'm guessing that your tempb may be growing a
lot.
> > (Since upon restart I believe it'll get resized back to it's original
> size.)
> > This could be a result of a bad design, or simply the result of a
> necessary
> > design.
> > What I'd do is check which DB is growing the most and resize it.
> > The master DB normally should not grow much at all.
> > So it's most likely the tempdb or the chessworld one. (as he states the
> > obvious.)
> > > I was not exactly sure if it was a) or b) but I have more evidence now
i
> t
> > > was in fact b) that was causing massive slow-downs requiring a reboot
> > > because CPU seems to go unrecoverably to 100%.
> > > > The reason for more evidence, is that today, I finally had a "cannot
> > > allocate space error" being logged. I increased the size of the
> chessworld
> > > db, and the tempdb, and put back the auto-grow on the chessworld db.
> > Within
> > > about an hour or two, the symptoms of a big slow-down came back with
CPU
> > > 100%.
> > > > I rebooted the database server but have again taken off auto-grow
> options.
> > I
> > > believe for my site with many concurrent users, the auto-grow is
causing
> > > issues. I will keep you posted.
> > > Please do.
> > > Best wishes
> > > Tryfon
> > > > > > "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> > > news:Xns946EED4BB70EFYazorman@.127.0.0.1...
> > > > Tryfon Gavriel (tryfon@.gtryfon.demon.co.uk) writes:
> > > > > (The first six are all event type 15 - which is "Disconnect" i
> > believe.
> > > > > They have massive duration times, and massive values for Reads.)
> > > > >...
> > > > > > > > I am not sure how to interpret these events. What does a massive
> > > > > duration on Event Type 15 mean?! Also there are a massive amount
of
> > > > > "Reads" associated with these.
> > > > > > As you said, event 15 is disconnection. Duration is just how long
the
> > > > connection was open. And Reads are just the accumulated number of
> > > > reads during that session.
> > > > > > In itself, not that exciting. Then again, maybe it is a clue that
four
> > > > long-running processes owned by sa quits just before the machines
> > > > reaches nirvana. No, please don't ask me what that clue would mean!
> > > > > > It is possible that the SQL statements you see when you sort on
> Duration
> > > > has anything to do with the CPU hog. However, I wouid not really
> expect
> > > > that process to show up. I would include the Starting events in the
> > trace,
> > > > and then investigate the uncompleted events at the end of the trace
> > > > when the CPU goes 100%.
> > > > > > > > --
> > > > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > > > > > Books Online for SQL Server SP3 at
> > > > http://www.microsoft.com/sql/techin.../2000/books.asp
> >|||It seems time to change db :-))|||> * I have doubled the size of the tempdb database to 2 gig for data file and
> 1 gig for transaction log
Hi,
You might want to check the amount of free space, for the drives that
the TempDB and TLog are on.
Subscribe to:
Posts (Atom)