Process Or SQL Transaction Takign Memory And Processor Time
Mar 22, 2002
Hi,
While watching through performance monitor the processor time often goes high above the memory.
Could you please tell me how to find out which process is doing that.
Thanks
John Jayaseelan
View 3 Replies
ADVERTISEMENT
Mar 13, 2008
We are seeing that the %Processor Time for the sqlservr process in Perfmon is over 100%. I am trying to understand how can the percentage of use be over 100%, and why it is over 100%. Someone told me that if the machine has multiple processors, that it will be over 100%. If that is the case, how can I determine what the maximum and normal values are? If I have 4 processors, does that mean 400% is the max? Does not make sense since it is suppose to be a percentage value...
Could someone explain to me how the CPU Utilization value is being measured, and if it is going over 100%, why that is and how I can determine what the threshold should be for monitoring?
SQL 2005 on Windows 2003 cluster.
Thanks!
View 9 Replies
View Related
Feb 13, 2001
Hi,
I have a new problem .I have a DB on server which is using only one processor and it keeps using up all memory on server.
Any thoughts appreciated!
TIA
PD
View 2 Replies
View Related
Jul 23, 2005
I was browsing Microsoft's SQL Server site, looking forsome details about SQL Server 2005. Didn't find whatI was looking for...I'm thinking about moving an existing SQL Server 2000workload to a new box, using SQL Server 2005, andmaybe the 64-bit version.My questions are:1. What is the current target date for release of SQL Server 2005?Will 64-bit ship when 32-bit ships?2. Will 64-bit SQL Server 2005 require a special versionof Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?Will it work with both Intel and AMD processors?3. How many CPUs, and how much memory, will be supported bySQL Server 2005, 32-bit and 64-bit, on each OS that can runSQL Server 2005.I'm looking for a chart here, something like the chart onpage 117 of Kalen Delaney's "Inside SQL Server 2000" book.SQL Server 2005 SQL Server 2005Feature Enterprise 32-bit Enterprise 64-bit------------------- ----------------- -----------------CPUs supportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Physical memorysupportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Has Microsoft published this info, and I just can find it?
View 1 Replies
View Related
Sep 25, 2006
Hi,
We have recently tested upgrading our web service from sql 2000 to 2005 sp1. The upgrade went smoothly enough, however we now have the problem of the sqlserver.exe process taking 90-100 % of the processors time, but using only 100 MB of memory.
We have 6GB available and we are running the enterprise editions of Windows 2003 and SQL 2005.
Machine specs,
DL380 G2, 2 X 2.8 Ghz Zeon, 6GB ram, Raid 5, database partition of 140 GB, Log partition of 35 GB.
Db is 25 GB, Log is 12 GB. Largest table has 32 million rows.
Any help would be greatly appreciated.
Rob
View 1 Replies
View Related
Aug 28, 2015
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies
View Related
Oct 9, 2007
Hello all,
We've had a problem for a few months now that has completely stumped us. We are running a heavily cursored massive data manipulation process on a 32 bit SQL Server instance running on a virtual machine, running ontop of VMWare, with the following specs
Processors: 2x2674MHz processors
Memory: 4GB
RAID 10 disk config
When we run our process on this machine, in total it runs in 30 hours.
When this process is run on another 32 bit server with the following specs
Processors: 8x3658MHx processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 25% slower
But here is the real kicker. When this process is run on a 64 bit server with the following specs
Processors: 8x3658MHz processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 75% slower.
This process consists solely of stored procedures written in TSQL. The weird thing is that on our smaller server, the CPUs' % utilization are evenly balanced (at 20-30%) when this large data manipulation process is running. However on the bigger servers, SQL Server latches onto a single processor and doesn't load balance across other processors. Such that what we're seeing is that only one processor out of the eight will be utilized and it will be throttled at 90% while the other 7 are at zero.
The default configuration settings in all three places.
Has anyone ever seen any behavior like this, where only one processor gets used by SQL Server during processing? Granted our processes are single threaded b/c they are using cursors but, it seems that the single thread shouldn't be restricted to one processor.
Any thoughts?
View 3 Replies
View Related
Nov 14, 2007
Hi,
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
cherriesh
View 4 Replies
View Related
Feb 14, 2007
Hi Folks,
I am having this table locking issue that I need to start paying attention to as its getting more frequent.
The problem is that the data in the tables is live finance data that needs to be changed and viewed almost real time so what I have picked up so far is that using 'table Hints' may not be a good idea.
I have a guy at work telling me that introducing a data access layer is the only way to solve this, I am not convinced but havnt enough knowledge to back my own feeling up. (asp system not .net).
Thanks in advance
View 1 Replies
View Related
Jan 6, 2012
We are facing deadlock issue in our web application. The below message is coming:
> Session ID: pwdagc55bdps0q45q0j4ux55
> Location: xxx.xxx.xxx.xxx
> Error in: http://xxx.xxx.xxx.xxx:xxxx/Manhatta...Bar=&Mode=Edit
> Notes:
> Parameters:
> __EVENTTARGET:
> __EVENTARGUMENT:
[code].....
View 2 Replies
View Related
May 26, 2004
I have a server with little control over most of the codeset and db design. Recently I have seen both the Processor - %Processor time and Processor - % User time go fom about 6.3 to about 24.3. The system queue length has also gone from about .2 to 1.1. In my humble opinion both of the are signs of a problem coming (luckily the cache hit ratio is still sitting at about 99%). I have been running profiler to catch the things that take more that 4500 MS, and I can probably tie the 2 together. Any opinions, or real world comparisons appreciated
View 3 Replies
View Related
Nov 10, 2015
Having a SQL Server 2012 Enterprise (x64) on a Windows 2012 R2. We need to know, a reliable way, the number of processor sql server is using at a give time. We already know how many total processor are available to sql by getting info from sys.dm_os_sys_info.
For instance, a server has 40 processors, we want to know how many of those are being used at a given time. Since the load on the server may not be that high, we would like to know how many processors we can eliminate and the load will still be unaffected.
After watching the server performance for a while, we are predicting we may only need 16. But we would like to get some statistics before we reduce it to this number.
View 12 Replies
View Related
Apr 7, 2008
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
use [master]Go
-- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO
-- Switching to our databaseuse [DatabaseName]GO
CREATE SCHEMA schemaname AUTHORIZATION usernameGO
ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO
/* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
View 10 Replies
View Related
Jun 9, 2008
Hi all
I frequently see the following message on SQL Server log
2008-06-09 07:46:18.17 spid3s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1079156, committed (KB): 17156388, memory utilization: 6%.
What does it indicates and what appropriate action has to be taken to fix it.
The database runs on
SQL 2005 Dev 64-bit SP2 9.00.3042.00
Win 2003 standard x64 SP2 16GB RAM
Thanks.
View 10 Replies
View Related
Feb 21, 2007
I am using a tool to monitor SQL Server and Windows. It is warning me that:
Process 1004:services has a virtual address space of 1,846.20 MB. This is close to the Windows two gigabyte address space limit.
When locate the process 1004, it shows 15 threads that Elapsed time for all of them is 1d, 3hrs. The Thread state is Waiting and the Thread Wait Reason is "Waiting for an Execution Delay to be resolved".
I think that 1d, 3hrs is from the time I rebooted my server.
Should I take any action? How?
View 7 Replies
View Related
Feb 3, 2006
Out techs informed me that they are getting reports of a system slow down. When they look, they find sqlserver.exe has lots of memory allocated to it. They reboot the server and then it runs okay for a few weeks. They tell me this just started happening recently.
SQLServer itself has not been touched in months. They are, however, starting to use one of the databases heavier.
I found a setting where you can set max_server_memory. Any problems if I set this to a value?
View 1 Replies
View Related
Jul 23, 2005
Hi AllSome my SQL Server are experience high memory usage.1. How can I detect which process which process cause the big memoryusage and not released?2. Which sql server components in this memory, and what are their usagedistribution?Any help will be appreciated.ThanksWillie
View 2 Replies
View Related
Feb 15, 2007
I got a Small Business Server 2003 running. It has 2 sqlserverprocesses. One of them is growing by 200mb every day. Does anyone havea clue to this. It's serving as a printserver, fileserver and exchangeserver. There is no specific use of the sqlserver. The antivirus isMCaffee
View 3 Replies
View Related
May 20, 2008
Hi All
I see the following message in SQL Server logs. What does this indicates. What should I do to avoid this.
2008-05-20 01:25:02.12 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 33920, committed (KB): 15142988, memory utilization: 0%.
The server configuration is
SQL 2005 Dev edition SP2 64bit
Win 2003 R2 SP2 Standard X64 editioin
RAM size is 16GB
Thanks.
View 4 Replies
View Related
Apr 1, 2015
In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
View 5 Replies
View Related
Feb 27, 2006
Hi,
I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet.
The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows.
When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message.
aspnet_wp.exe was recycled because memory consumption exceeded the 306 MB (60 percent of available RAM)
How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues.
-Thanks
View 1 Replies
View Related
Jul 26, 2007
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
View 9 Replies
View Related
May 11, 2015
I have worked in other ETL tools. So, i am trying to figure out how to do thefile decryption and process the data in memory using SSIS.I am using SSIS on Azure VM and my source files are on Azure storage. The files are encrypted and we are trying to use Phython script to decrypt the files and pass it to SSIS. I found out that Execute Process task can call the Phython script. However, i would like to get the decrypted data from the file and pass it to the next task (control flow) in SSIS without saving it as a file (in-memory). I found that execute process task output can be stored as a Standard Output Variable or to an object. Will this work or do I need to follow any other methods (since we need the entire file to be sent for additional processing).
View 6 Replies
View Related
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
Nov 12, 2007
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
View 8 Replies
View Related
Oct 7, 2015
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
View 0 Replies
View Related
Jul 20, 2005
Details:MS SQL 2000 dual Intel 1.2 GHz processors.1 GB RAM2.1 GB dBDynamic Memory Managment.No other apps running on this server.First question:Since I have Dynamic Memory Managment setup, Is it usuall that thesqlsrv.exe process on the server steadily climbs and is in the 800 to900 MB range. There is only about 20 MB free. In theory this is howDMM can work, but do people really see it work this way.Second question:I had users complaining about lockups in the app I have to supportthat connects to this dB. At first I thought it was the large use ofmemory, but once I was able to see in Enterprise Manager that therewas process blocking several other processes. EM then locked up and Icouldn't get to the details of what the exact process was that wasdoing the blocking. After restarting SQL services things were fine.When I checked the logs there was nothing there about a hung process.The logs seemed very sparse. Why would there not be anything in thelogs about it. The logs actually seem very thin on any information.Thanks,T.
View 1 Replies
View Related
Jul 5, 2006
When I try to install MsSQL Server 2005 Develop Edition do I get the error:
[Microsoft][SQL Native Client]Shared Memory Provider: No process is on the other end of the pipe.
I have trying to look at other posts on this forum and elsewhere, but cant find any solution that works for me - mainly cuz all solutions is after the installing.
Before trying to install MsSQL Server 2005 Dev did I install VS.Net 2005 Pro. First did the Native Client make troubles, but got it to work with reinstalling it, but now does the SQL setup stop on every try with the error above.
I have tried looking if the MSSQLServer is running when it tries to connect during install, and everything says it is running (Services, Net start, Taskman.).
I dont run any special setup on my system - it is a normal Windows XP Pro SP2 with all updates. I just need the SQL server installed so I can develop locally without access to out main SQL server.
I have been using MsSQL 2000 before and never had any problems, but the 2005 keep on bugging me.
The only solution I havent tried is to reinstall Windows itself, but I will pref. not to do so.
And to be honest, then have I no idea what a "pipe" is - I am used to develop webapplications and not so much on server maintaince/troubleshooting.
Need some more information? Then just ask.
View 1 Replies
View Related
Nov 10, 2006
Hi,
I got the following error when I try running my “comments.aspx� page with visual studio 2005Exception Details: System.Data.SqlClient.SqlException: Transaction (Process ID 83) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
fExecuteQuery(String commandText, String dataSetName) +90 fExecuteQuerySet(String commandText, String dataSetName) +36 ASP.comments_aspx.GetNarComment() +618 ASP.comments_aspx.Page_Load(Object sender, EventArgs e) +476 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.BasePage.OnLoad(EventArgs e) +1013 System.Web.UI.PopupPage.OnLoad(EventArgs e) +4 System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750
The segment code was the problem sits in file "comments.aspx"::
…
string cmdText=��;
cmdText = string.Format(@"-- Get All Narative comments fo all students in the course from @selectedTermID down to its child terms
exec aagGetStudentSectionComments @companyID={0}, @sectionID={1}, @selectedTermID={2}, @StudentID={3}
", _companyID, sectionID, selectedTermID, studentID);
ds = fExecuteQuerySet(cmdText, "getMySet");
…
// the 2 functions to deal with ADO.NET to be called in above code segment
// return a dataset.
public DataSet fExecuteQuery(string commandText, string dataSetName)
{
DataSet mds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(commandText, _cn);
da.SelectCommand.CommandTimeout = 600; // 600 seconds
da.Fill(mds, dataSetName);
return mds; // return dataset
}
// Assume para commandText contains sql query which returns a table or more.
// return a DataSet.
public DataSet fExecuteQuerySet(string commandText, string dataSetName)
{
DataSet mds = new DataSet();
mds = fExecuteQuery(commandText, dataSetName);
return mds; // return DataSet
}
Please give me the reason why that dealock happens?Thanks in advance
View 2 Replies
View Related
Feb 27, 2006
ive seen this Deadlock Error message out on the internet being discussed, but no solution being offered.
i have a windows service that's running Select Statements [one at a time] - so unless there's some command in sql server that would re-run these - it could be a problem for me.
now if im running this select proc manually - of course i see the message and re-run the process, but how can this be accomplished programatically.
see msg below:
Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
thanks for any help on this
rik
View 5 Replies
View Related
Nov 5, 2002
Hi,
My transaction log backup task for the production database which normally takes about 10 seconds has been running for almost about 2 hours now.
Is there any way to stop it without restarting sql services? The scheduled task was stopped from the jobs but spid is still in 'runnable' status. I should not kill 'backup log' or 'xp_sqlmaint' tasks.( It doesn't solve the problem anyway. the SPID goes in rollback status and stays like that until server is rebooted). Any way to find out what causes this problem and how to prevent it?
Thanks,
Shaili
View 2 Replies
View Related
Nov 29, 2004
;)
Hello Everybody,
My name is Fabio and I post from Italy.
First, I don't know if this argument was already discussed in the past, but I'm new in this group so ...
Second I'm not so expert in DB due to the fact that I'm using SQL for the first time in my life ...
I use a store procedure to pass to every single user in my intranet (more than 150), details of different clients taken from an SQL table containing around 30.000 names.
Users have an ASP page displaying the information Selected in the DB.
This means that 150 users display info of 150 different clients.
To to this I use this code in store procedure:
CREATE PROCEDURE sp_assign_name
@iduser int
AS
if exists(select top 1 * from recallornotes where tmkoperator= @iduser)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7 where id in (select top 1 id from recallornotes where tmkoperator=@iduser)
else
begin
if exists(select top 1 id from nonotes)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7, tmkoperator =@iduser where id in (select top 1 id from nonotes with (UPDLOCK) order by NewID())
end
GO
This is working quite well when the number of users are more ore less around 50/60, when the number grows, on the IIS server (Pentium IV server, with Win 2000 in English, MS SQL 2000, and 1 Giga of ram), a file called DLLHOST.exe start to use the 100% of the CPU, and the users cannot display any other ASP page on their screens.
It is not a virus (some newsgroup report this problem connect to a worm virus, but we have latest antivirus files installed and spyware detect/delete on).
The SQL log reports this error:
"transaction (process id 69) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victim. rerun the transaction".
Is there a way to avoid the conflict that occur when different users are trying to select the same record in the DB ?
In other terms, which process will you use in the same situation to select one record per user ?
Thanks in advance for your precious help,
Fabio
View 1 Replies
View Related
Apr 29, 2015
The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011).The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {001317bf:0000f736:0008}. (Source:Â
MSSQLServer, Error number: 18768).The process could not set the last distributed transaction. (Source: MSSQL_REPL, The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
View 2 Replies
View Related