SQL Is Using Up All My System Resources
May 8, 2007
Hi
SQL Server is using up all of my system resources and i can't find where the problem is, i have used
exec sp_who
exec sp_who2
but i don't know what to look for, i'v even checked the memory comparison according to SQLSkills but that is all fine, the server is extremely slow all the time. I battle to open Query analyser or even just to connect to it takes forever, any advice would be greatly appreciated.
Kind Regards
Carel Greaves
View 7 Replies
ADVERTISEMENT
Jan 29, 2007
Hello!
Hopefully someone can help me.
I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.
Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK
BackupMedium::ReportIoError: read failure on backup device '\XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).
View 1 Replies
View Related
Aug 21, 2006
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
View 28 Replies
View Related
Nov 10, 2003
I used SqlConnection/SqlDataReader to read data from SQL Server 2000 in my ASP.NET project, but I found that the CPU usage is too high, and I used SQL Query Analyzer to run the same SQL statement, The CPU usage is the half of the previous, is there anyone can tell me why? Thank you!
View 1 Replies
View Related
May 8, 2006
Hi everyone!Being new the SQL, I wanted to ask everyone if there are good resources out there that they use? I could use all the help I can get! Books, links, websites, etc.Recently my friend sent me this, I found it informative and best of all, free! https://store.pentontech.com/index.cfm?s=9&promocode=qe2165f7
Thanks in advance for your help!- Lizzy
View 2 Replies
View Related
Jul 5, 2001
Sometimes logging in to SQL 7 I get 'Memory resources not available...' and
log in process terminates. Do I need to up number of locks !
If so, how ! Pls. suggest any other ideas !
I have 2g ram, 4 CPUs NT 4 SP5
Thanks,
Rick
View 2 Replies
View Related
May 5, 2006
This proc will show you what is using SQL system resources right now. If a process is not currently using any resources, it will not show up on the list. Also, a dump of the input buffer is printed so you can see just what is going on.
I use this code whenever somebody whines that "The server is slow!".
The magic is ::fn_get_sql which came with service pack 3 for sql 2000.
use master
IF (object_id('sp_Now')) is not null
BEGIN
PRINT 'Dropping: sp_Now'
DROP PROCEDURE sp_Now
END
PRINT 'Creating: sp_Now'
GO
CREATE PROCEDURE sp_Now
as
-- FULL DETAILS OF WHAT IS RUNNING
set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500)
DECLARE TEST CURSOR FOR
select sql_handle, spid
from sysprocesses
where sql_handle <> 0x0000000000000000000000000000000000000000
-- and spid <> @@SPID --> Uncomment to stop seeing your own process
order by cpu desc
OPEN TEST
FETCH NEXT FROM TEST
INTO @handle,
@spid
set @rowcnt = @@CURSOR_ROWS
print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print ' '
set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)
print @output
WHILE @@FETCH_STATUS = 0
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '
select convert(char(15), loginame) as 'loginame',
convert(char(10),hostname) as 'hostname',
convert(char(20),db_name(dbid)) 'database',
str(spid,4,0) 'spid',
str(blocked,5,0) 'block',
-- str(waittime,9,0) 'wait_time',
str(physical_io,8,0) 'phys_io',
-- str((cpu),10,0) 'cpu(ms)',
-- str((cpu/60000.0),9,3) 'cpu(mins)',
str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END as 'cpu(mm:ss)',
str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2) 'mem(MB)',
convert(char(30),program_name) as 'program_name',
cmd,
convert(char(15),lastwaittype) 'lastwaittype',
convert(char(20),login_time,120) 'login_time',
convert(char(19),last_batch,120) 'last_batch',
convert(char(10),status) as 'status',
convert(char(15),nt_username) as 'nt_username'
from master..sysprocesses
where spid = @spid
print ' '
print ' '
dbcc inputbuffer(@spid)
print ' '
print ' '
select * from ::fn_get_sql(@handle)
FETCH NEXT FROM TEST
INTO @handle,
@spid
END
close TEST
deallocate TEST
GO
IF (object_id('sp_Now')) is not null
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO
View 2 Replies
View Related
Jul 31, 2006
Hi,
Im just looking for someone that has a demonstration Visual Basic database script.
Something that makes a database, ad / removes rows and information, does all basic database stuff, just as an example of how a SQL database is supposed to be handled.
Anyone have any links?
View 4 Replies
View Related
Jul 23, 2005
I know that a query inside a called UDF does not show up when displaying theestimated query plan, but are the resources (CPU, DiskIO) used by a UDF thatis call from within an SProc or embedded in a SQL statement included insp_who2 and Profiler BatchCompleted?Also how performance draining is it to do something like the following. Iassume for every row returned the udf will be called causing 8000 tablescans on DateTable?Create Functionu udf_GetCountDates (@StartDate datetime, @EndDate datetime)Returns IntbeginReturn(select count(*) from DateTablewhere ColDate > @TestDate and Col1Date <)end--DateTable has 5,000 rowsCreate proc Test@TestDate datetimeAsselect Col1, Col2, udf_GetCountDates(@TestDate, Col2)From OTable--OTable has 8,000 rowsGoThanks,Ray
View 1 Replies
View Related
Jul 20, 2005
I have a question...say I have an asp app that works as a front end toan sql server 2000 database. Would it be better, performance wise, toseperate the webserver from the sqlserver or to have them on the samemachine?-Jim
View 2 Replies
View Related
May 13, 2004
I have a sql job(that runs several stored procs) that is run to fix a problem, it is set to run every few minutes (no, this is not my idea). What would be the best way to identify how much this specific job/and it's sp's are using in resources, such as cpu and memory?
View 1 Replies
View Related
Jun 19, 2002
I was thinking of doing certification from Microsoft. I was first intending to write exam 70-229 which is :
Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition.
The Microsoft site http://www.microsoft.com/traincert/exams/70-229.asp#TOOLS has a list of topics that I need to be familiar with in order to pass the exam, but it does not have the detailed information.
Can someone guide me to a place where I can go to get the detailed information about these topics so that I can prepare better for the certification exam?
thanks
Aslam
View 1 Replies
View Related
Mar 9, 2006
Hi folks, thought I'd wade into the mssql waters, I've used mysql for some time, thought I'd familiarize myself with the other.
I downloaded mssql server 2005 express edition. Don't know if I missed anything on the site, maybe I did. I've got it running from the command line.
Are there any GUIs out there that are decent to use? Where can I download it?
Is there a specific manual available like mysql has? I think I stumbled across something but wasn't sure it was searchable, sorry the link is on my other 'puter at the moment so I can't see what I was looking at.
perhaps a sticky in this forum with good resources would be helpful to all.
Thanks,
Dave
View 1 Replies
View Related
May 21, 2008
I normally develop for MySQL and PHP, but we have a new application that runs on MS SQL and I'm having difficulty using the same structure as MySQL to run queries. Can someone here point me to a good resource to learn the basics?
Thanks!
View 1 Replies
View Related
Oct 20, 2005
Hello, I need to know how to see the cpu and memory usage per session, and the cause (What query is causing It)
View 2 Replies
View Related
Jun 13, 2007
Hi
I have a problem on microsoft sql server 2000.
I need to limit the amount of resources(eg cpu, mem..) a query uses
Is there a way to do this in SQL server 2000
Thks in advance
:eek:
View 1 Replies
View Related
Mar 31, 2008
What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?
Thanks!
View 2 Replies
View Related
Apr 2, 2008
Hello,
There are many Errorcodes which SSIS uses to represent errors, warnings etc.
List of errorcodes are at: http://msdn2.microsoft.com/en-us/library/ms345164.aspx
Now, in my SSIS package, I have defined error handler(OnError) and warning handler(OnWarning) to handle the events. Which logges them to a flat file with some formatting and condition checks including addional information about faulty row.
I dont want to log the exact error messages sent by SSIS as they are too much technical and not understanadble by normal user. I want them to be replaced with some custom message which I 'l pick from some resource file by matching the ErrorCode. One can consider this resource file as collection of key(ErrorCode), value(Custom Error Message) pairs.
In .NET we normally use resource files(.resx) to achive the same task. I found no way to use such resource files in SSIS.
Is there any way by which I can implement this functionality?
I dont want to implement custom logging mechanism as it does not fit into my application needs.
Please guide.
View 2 Replies
View Related
May 8, 2006
could we download the videos of SQL Express learning resources as we can do it with the VWD, VB, C#????
there is another link to do it???
will they be available in a short future?
View 1 Replies
View Related
Apr 10, 2000
The errormessage after a deadlock gives very little information: "Your transaction (process ID #12) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction."
But what resources, ie tables, indexes etc, are really deadlocked?
Is it possible to set a switch or something to log all relevant information about a deadlock for later investigation?
View 2 Replies
View Related
Jun 13, 2000
Anybody have a suggestion on text books or other materials that deal with performance tuning in SQL 7.0. From what I have seen SQL 7.0 is far diffrent than 6.5. I'm specificaly interestd in information about query plans, profiler and what a table spool does.
View 1 Replies
View Related
Jun 9, 2000
Hi,
a) Thanks for all contributions on this site, they have been very useful but,
i am trying to implement a security procedure including auditing on sql server 6.5,7, sybase and oracle can anyone help?
b) does any one know any site like this that covers issues on oracle ( besides Oracle Technet)?
View 2 Replies
View Related
Sep 20, 2002
Hi:
I am planning to give the microsoft 70-019 data warehousing exam. I am not able to find any good resources to prepare for the exam. Please help!
Thanks in advance
View 1 Replies
View Related
May 30, 2007
We've got a 3rd party application that periodically runs SQL commands throughout the day. We've been getting issues with this application showing a sql error:
Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In checking the processes on SQL Server, there were a lot, and process ID 71 was actually hitting a completely different database.
Is there a way to streamline how SQL Server handles processes, and what's the limit at any given time?
View 4 Replies
View Related
Jun 9, 2007
Will I am running SQL 2005 Express on windows 2003 sp1 with a P4 3.4Gh (with hyperthreading), 2GB ECC RAM.
I am running software that connects to SQL and reads/saves data.
One of the programs which is CPU hungry manages data and the other manages the SQL connection and reads/saves the data.
My problem is that I am recieving an error that usually means connection to SQL is lost, this tends to be because the CPU hungry application is denying SQL from functioning.
However the annoying thing is the CPU use rarely passes 45% and in a another test environment never passed 5%. RAM is the same, consumption is less than 35%.
Please advise
Thanks a lot!
View 3 Replies
View Related
Feb 15, 2006
Is there some kind of way to limit the resources (CPU, number of sessions)per user in MS SQL 2000 database.Some kind of resorce manager thing...
View 1 Replies
View Related
Jul 20, 2005
Hi,We have a heavily used production server and a table which logs everyhit on a web site. This table has grown large over time and we want toclear it down as efficiently as possible. We would like to issue atruncate table statement, but with millions of rows we are a bit waryof how this will affect server performance. The alternative is todelete is stages using rowcount but of course this will generate alarge amount of logging.Can anyone confirm whether issuing a truncate table will have anyaffect on performance - i.e. does it just clear a header block ordooes it have to do more?CheersMartin
View 3 Replies
View Related
Jan 26, 2007
Seems to me like microsoft mentions learning, but it's never for beginners. I haven't found a single tutorial that says how to make a simple database step by step.
Does anybody know about a tutorial focused on teaching the very basics (a kind of "hello world" application) from setting up the new database with a few sample fields to "deploying" it (making it ready to be used)?
Please let me know.
Once I'm through with these first steps there seems to be a lot of helpful learning resources meant for experienced and expert users, but I'm not there yet...
Thanks,
Bernardo
View 2 Replies
View Related
May 5, 2007
Hi,
I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.
The queue decleration is:
CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);
Thanks in advance,
Shai Brumer
View 9 Replies
View Related
Apr 4, 2008
I'm new to this forum and have just started working with Reporting Services. I was wondering what were some good resources to help.
Thanks
View 4 Replies
View Related
Jul 15, 2006
Can Resource Files (containing, for example, string resources holding the text of exception messages) be part of a .NET project holding Sql Server CLR Stored Procedures?
Thanks for you help,
Andy
View 3 Replies
View Related
Jan 16, 2006
I am new to SQL Server 2005 Express, where can I find some tutorials on developing web applications with this product?
Thanks!
Mike
View 2 Replies
View Related
Jun 9, 2000
Are there any packages or routines that can assist in estimating the resources needed for an
SQL based system?
The system will be developed using Visual Basic. We have the number of records that will be
converted and the volume information for daily activity.
I am looking to find a tool that will help me size the equipment and resources needed.
Thanks
View 1 Replies
View Related
