Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) wa
y
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
Showing posts with label trial. Show all posts
Showing posts with label trial. Show all posts
Tuesday, March 27, 2012
Create a table using a Stored Procedure
Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks
"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END
|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks
"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END
|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
Create a table using a Stored Procedure
Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> >
> > I am someone new to SQL Server in general. I am using the Trial Version of
> > SQL Server 2005. I want to create a stored procedure to create a table.
> > The
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--sql
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> >
> > I am someone new to SQL Server in general. I am using the Trial Version of
> > SQL Server 2005. I want to create a stored procedure to create a table.
> > The
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--sql
Thursday, March 8, 2012
CPU usage
I have been using SQL Server 2000 for some time and am attempting to upgrade
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?
Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>
|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>
>
|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...[vbcol=seagreen]
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?
Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>
|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>
>
|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...[vbcol=seagreen]
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
Wednesday, March 7, 2012
CPU usage
I have been using SQL Server 2000 for some time and am attempting to upgrade
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >I have been using SQL Server 2000 for some time and am attempting to
> >upgrade
> > to SQL Server 2005 (testing the trial version).
> >
> > I backed up my SQL Server 2000 database(s) and then restored them on SQL
> > Server 2005 which worked well.
> >
> > Everything seemed to be working fine until I updated SQL Server 2005 with
> > service pack 2.
> >
> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> > is now using 25% CPU continuously (or 100% of one of the processor
> > instances). The databases I converted are working as expected, however,
> > the
> > SQL Server process is continuously burning CPU when nothing is happening
> > (this is over several days now).
> >
> > Has anybody else see this problem?
> >
> >
>
>|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
>> Ross,
>> I have seen this happen in the past, for many different reasons.
>> I assume from your comment that you know that it is sqlservr.exe that is
>> using the 25%. Can you run sp_who2 to identify some process that
>> continues
>> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
>> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
>> find
>> the oldest transaction in each database. (However, I assume that there
>> is
>> not a blocking transaction or you would be having other problems.)
>> If you identify a problem spid, try to get the hostname for that spid.
>> Sometimes a client computer loses connectivity, but the SQL Server does
>> not
>> know that, so it keeps trying (forever) to send the result set to the
>> client. If that is the case, logging the hostname computer off of the
>> domain, then logging back into the domain, often alerts SQL Server to the
>> problem so that it will abandon that result set.
>> If appropriate, you can try to KILL the spid. (But the spid may not
>> always
>> be killable.)
>> Other software can also get into a confused state and eat up CPU. For
>> example, a DTS package, backup software, etc. can also get into an
>> confused
>> state and never stop running. Use Windows Task Manager to check whether
>> another task is actually the guilty party. If it is one of those, then
>> try
>> killing that Windows process.
>> If you cannot do anything else, you should schedule a restart of your SQL
>> Sever. That should (naturally) clear the problem out. But you will need
>> to
>> remain alert to its returning.
>> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> RLF
>>
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >I have been using SQL Server 2000 for some time and am attempting to
>> >upgrade
>> > to SQL Server 2005 (testing the trial version).
>> >
>> > I backed up my SQL Server 2000 database(s) and then restored them on
>> > SQL
>> > Server 2005 which worked well.
>> >
>> > Everything seemed to be working fine until I updated SQL Server 2005
>> > with
>> > service pack 2.
>> >
>> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> > Server
>> > is now using 25% CPU continuously (or 100% of one of the processor
>> > instances). The databases I converted are working as expected,
>> > however,
>> > the
>> > SQL Server process is continuously burning CPU when nothing is
>> > happening
>> > (this is over several days now).
>> >
>> > Has anybody else see this problem?
>> >
>> >
>>|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
>> Ross,
>> Check out: http://support.microsoft.com/kb/931821
>> RLF
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>> > Thanks for your answer.
>> >
>> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I
>> > have
>> > actually restarted SQL Server (and the machine itself) and subsequently
>> > the
>> > SQL Server process goes right back up to 25%.
>> >
>> > As you noted regarding the blocking transaction, I'm not experiencing
>> > any
>> > problems with the database, just the CPU usage.
>> >
>> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of
>> > 71195468
>> > (as
>> > it happens, the machine was restarted at 11:19PM EST last night).
>> >
>> > Full row information from sp_who2:
>> >
>> > SPID 3
>> > Status BACKGROUND
>> > Login sa
>> > HostName .
>> > BlkBy .
>> > DBName NULL
>> > Command LAZY WRITER
>> > CPU TIME 71195468
>> > DiskIO 0
>> > LastBatch 03/12 23:19:34
>> > ProgramName (blank)
>> > SPID 3
>> > REQUESTID 0
>> >
>> > Any ideas?
>> >
>> > Thanks again for your help...
>> >
>> > Ross
>> >
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Ross,
>> >>
>> >> I have seen this happen in the past, for many different reasons.
>> >>
>> >> I assume from your comment that you know that it is sqlservr.exe that
>> >> is
>> >> using the 25%. Can you run sp_who2 to identify some process that
>> >> continues
>> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look
>> >> at
>> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN
>> >> to
>> >> find
>> >> the oldest transaction in each database. (However, I assume that
>> >> there
>> >> is
>> >> not a blocking transaction or you would be having other problems.)
>> >>
>> >> If you identify a problem spid, try to get the hostname for that spid.
>> >> Sometimes a client computer loses connectivity, but the SQL Server
>> >> does
>> >> not
>> >> know that, so it keeps trying (forever) to send the result set to the
>> >> client. If that is the case, logging the hostname computer off of
>> >> the
>> >> domain, then logging back into the domain, often alerts SQL Server to
>> >> the
>> >> problem so that it will abandon that result set.
>> >>
>> >> If appropriate, you can try to KILL the spid. (But the spid may not
>> >> always
>> >> be killable.)
>> >>
>> >> Other software can also get into a confused state and eat up CPU. For
>> >> example, a DTS package, backup software, etc. can also get into an
>> >> confused
>> >> state and never stop running. Use Windows Task Manager to check
>> >> whether
>> >> another task is actually the guilty party. If it is one of those, then
>> >> try
>> >> killing that Windows process.
>> >>
>> >> If you cannot do anything else, you should schedule a restart of your
>> >> SQL
>> >> Sever. That should (naturally) clear the problem out. But you will
>> >> need
>> >> to
>> >> remain alert to its returning.
>> >>
>> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> >>
>> >> RLF
>> >>
>> >>
>> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >> >I have been using SQL Server 2000 for some time and am attempting to
>> >> >upgrade
>> >> > to SQL Server 2005 (testing the trial version).
>> >> >
>> >> > I backed up my SQL Server 2000 database(s) and then restored them on
>> >> > SQL
>> >> > Server 2005 which worked well.
>> >> >
>> >> > Everything seemed to be working fine until I updated SQL Server 2005
>> >> > with
>> >> > service pack 2.
>> >> >
>> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> >> > Server
>> >> > is now using 25% CPU continuously (or 100% of one of the processor
>> >> > instances). The databases I converted are working as expected,
>> >> > however,
>> >> > the
>> >> > SQL Server process is continuously burning CPU when nothing is
>> >> > happening
>> >> > (this is over several days now).
>> >> >
>> >> > Has anybody else see this problem?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >I have been using SQL Server 2000 for some time and am attempting to
> >upgrade
> > to SQL Server 2005 (testing the trial version).
> >
> > I backed up my SQL Server 2000 database(s) and then restored them on SQL
> > Server 2005 which worked well.
> >
> > Everything seemed to be working fine until I updated SQL Server 2005 with
> > service pack 2.
> >
> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> > is now using 25% CPU continuously (or 100% of one of the processor
> > instances). The databases I converted are working as expected, however,
> > the
> > SQL Server process is continuously burning CPU when nothing is happening
> > (this is over several days now).
> >
> > Has anybody else see this problem?
> >
> >
>
>|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
>> Ross,
>> I have seen this happen in the past, for many different reasons.
>> I assume from your comment that you know that it is sqlservr.exe that is
>> using the 25%. Can you run sp_who2 to identify some process that
>> continues
>> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
>> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
>> find
>> the oldest transaction in each database. (However, I assume that there
>> is
>> not a blocking transaction or you would be having other problems.)
>> If you identify a problem spid, try to get the hostname for that spid.
>> Sometimes a client computer loses connectivity, but the SQL Server does
>> not
>> know that, so it keeps trying (forever) to send the result set to the
>> client. If that is the case, logging the hostname computer off of the
>> domain, then logging back into the domain, often alerts SQL Server to the
>> problem so that it will abandon that result set.
>> If appropriate, you can try to KILL the spid. (But the spid may not
>> always
>> be killable.)
>> Other software can also get into a confused state and eat up CPU. For
>> example, a DTS package, backup software, etc. can also get into an
>> confused
>> state and never stop running. Use Windows Task Manager to check whether
>> another task is actually the guilty party. If it is one of those, then
>> try
>> killing that Windows process.
>> If you cannot do anything else, you should schedule a restart of your SQL
>> Sever. That should (naturally) clear the problem out. But you will need
>> to
>> remain alert to its returning.
>> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> RLF
>>
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >I have been using SQL Server 2000 for some time and am attempting to
>> >upgrade
>> > to SQL Server 2005 (testing the trial version).
>> >
>> > I backed up my SQL Server 2000 database(s) and then restored them on
>> > SQL
>> > Server 2005 which worked well.
>> >
>> > Everything seemed to be working fine until I updated SQL Server 2005
>> > with
>> > service pack 2.
>> >
>> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> > Server
>> > is now using 25% CPU continuously (or 100% of one of the processor
>> > instances). The databases I converted are working as expected,
>> > however,
>> > the
>> > SQL Server process is continuously burning CPU when nothing is
>> > happening
>> > (this is over several days now).
>> >
>> > Has anybody else see this problem?
>> >
>> >
>>|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
>> Ross,
>> Check out: http://support.microsoft.com/kb/931821
>> RLF
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>> > Thanks for your answer.
>> >
>> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I
>> > have
>> > actually restarted SQL Server (and the machine itself) and subsequently
>> > the
>> > SQL Server process goes right back up to 25%.
>> >
>> > As you noted regarding the blocking transaction, I'm not experiencing
>> > any
>> > problems with the database, just the CPU usage.
>> >
>> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of
>> > 71195468
>> > (as
>> > it happens, the machine was restarted at 11:19PM EST last night).
>> >
>> > Full row information from sp_who2:
>> >
>> > SPID 3
>> > Status BACKGROUND
>> > Login sa
>> > HostName .
>> > BlkBy .
>> > DBName NULL
>> > Command LAZY WRITER
>> > CPU TIME 71195468
>> > DiskIO 0
>> > LastBatch 03/12 23:19:34
>> > ProgramName (blank)
>> > SPID 3
>> > REQUESTID 0
>> >
>> > Any ideas?
>> >
>> > Thanks again for your help...
>> >
>> > Ross
>> >
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Ross,
>> >>
>> >> I have seen this happen in the past, for many different reasons.
>> >>
>> >> I assume from your comment that you know that it is sqlservr.exe that
>> >> is
>> >> using the 25%. Can you run sp_who2 to identify some process that
>> >> continues
>> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look
>> >> at
>> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN
>> >> to
>> >> find
>> >> the oldest transaction in each database. (However, I assume that
>> >> there
>> >> is
>> >> not a blocking transaction or you would be having other problems.)
>> >>
>> >> If you identify a problem spid, try to get the hostname for that spid.
>> >> Sometimes a client computer loses connectivity, but the SQL Server
>> >> does
>> >> not
>> >> know that, so it keeps trying (forever) to send the result set to the
>> >> client. If that is the case, logging the hostname computer off of
>> >> the
>> >> domain, then logging back into the domain, often alerts SQL Server to
>> >> the
>> >> problem so that it will abandon that result set.
>> >>
>> >> If appropriate, you can try to KILL the spid. (But the spid may not
>> >> always
>> >> be killable.)
>> >>
>> >> Other software can also get into a confused state and eat up CPU. For
>> >> example, a DTS package, backup software, etc. can also get into an
>> >> confused
>> >> state and never stop running. Use Windows Task Manager to check
>> >> whether
>> >> another task is actually the guilty party. If it is one of those, then
>> >> try
>> >> killing that Windows process.
>> >>
>> >> If you cannot do anything else, you should schedule a restart of your
>> >> SQL
>> >> Sever. That should (naturally) clear the problem out. But you will
>> >> need
>> >> to
>> >> remain alert to its returning.
>> >>
>> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> >>
>> >> RLF
>> >>
>> >>
>> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >> >I have been using SQL Server 2000 for some time and am attempting to
>> >> >upgrade
>> >> > to SQL Server 2005 (testing the trial version).
>> >> >
>> >> > I backed up my SQL Server 2000 database(s) and then restored them on
>> >> > SQL
>> >> > Server 2005 which worked well.
>> >> >
>> >> > Everything seemed to be working fine until I updated SQL Server 2005
>> >> > with
>> >> > service pack 2.
>> >> >
>> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> >> > Server
>> >> > is now using 25% CPU continuously (or 100% of one of the processor
>> >> > instances). The databases I converted are working as expected,
>> >> > however,
>> >> > the
>> >> > SQL Server process is continuously burning CPU when nothing is
>> >> > happening
>> >> > (this is over several days now).
>> >> >
>> >> > Has anybody else see this problem?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)