We have a JDBC project that works with SQLServer, along with other
DBs. During development, we noticed that resource useage on SQLServer
seemed to grow steadily - Processes, Process Locks and/or Object Locks,
according to the "Current Activity" section of Enterprise Manager.
Fairly quickly (a few hours), SQLServer would use so many resources it
used up all available system memory and the system would crash.
Without understanding much about why that was happening, mostly out of
trial and error, we started using transactions, which seemed to fix the
problem. Now, one of our customers seems to be seeing the problem again:
511 Process Locks, 18 Object Locks, 526 Processes
after a few hours. The system runs out of memory and crashes. We have
multiple test configurations, including Linux, Win2K, XP, but we do
not see the same behaviour - we typically see no more than 20 or 30
processes, process locks or object locks, and our tests seem to run
indefinitely. Since we can not reliably reproduce the symptoms that our
customer sees, it is very difficult for us to analyze, let alone, fix
the problem.
Does anyone - especially SQLServer pros - have any insight as to what is
going on here and/or how to fix it? Like how does a process, process
lock or object lock relate to JDBC? process <-> connection,
object <-> row, table... or what, locks?
Is it possible to deploy SQL Server CE "baked into" a DLL? For example, instead of setting the build option to "Content", can I set it to "Embedded Resource"?
In Oracle & Sybase you can set login resource limits to restrict, time of day, number of i-o's, cpu use etc for any login, at the login method. All actions done from a resource limit constrained login inherit the constraints.
Is there any way to implment this with any MS SQL Server version from 200-2005/SP2?
Before anyone answers, please do not suggest set rowcount etc. thanks.
I have a deadlock chain that shows 9 parallel insert jobs into the same table. The resource that is locked (with an exclusive lock) shows a HOBT_ID...which corresponds to a clustered index.
My question is how would INSERT statements cause a whole index to lock (there is no TABLOCK hint).
I would like to know witch is the best practices to make a .rdl file multilenguaje, that is, that the same .rdl file shows the text €œAmount€? on a label when the user is English or the text €œCantidad€? when the user is Spanish.
There are not something like the resource files in the in a VS.NET web application????
I am new to SSIS, and have been struggling recently with SSIS development. I would like to find out if there are some good books out there on SSIS besides, the online resources, and BOL. Possibly a good book or tutorials. Thanks.
I would like to know witch is the best practices to make a .rdl file multilenguaje, that is, that the same .rdl file shows the text €œAmount€? on a label when the user is English or the text €œCantidad€? when the user is Spanish.
There are not something like the resource files in the in a VS.NET web application????
What are some good CASE programming to use in comjuction with SQL 7. What are some good resources for researching. Also, are there any good DB confrences coming up?
I use cursors inside stored procedures, they are generally 20 - 50 records. I have been told that cursors are very resource intensive. Am I setting myself up for scaling problems when we have hundreds of users all running these procedures? The cursors are only used within the context of the stored procedure. Any opinions/insight on this would be appreciated.
I'm attempting to update a table of around 100,000 records with a table of 20,000 records using a cursor since the update is based on a row by row criteria. When doing this I'm getting an error
--Resource is low some results are dropped--
I then cut down the table with 20,000 to a table of 5,000 and still get the same error. I've also tried to use a fast_forward cursor but still no luck Can any-one help. I know there is a way to do this using a quick update statement but I need to know this for other insert statements anyway. Help Help
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf'); GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions?????????????????????? Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Just downloaded a not too great white-paper (sponsored by HP) called SQL Server Infrastucture Optimaztion and it had a brief but eye catching section on something called 'Pooled Resource Clustering'. See below
Pooled-Resource Clustering Pooled-resource clustering is a combination, of sorts, of Windows clustering and virtualization. Essentially, it€™s a means of allowing SQL Server instances to become €œvirtual SQL Servers€? in the sense of true VMs. They€™re encapsulated and can be easily and quickly moved between physical computers. It borrows concepts from Windows clustering but does so with a crucial difference that eliminates the resource management overhead that comes along with Windows clustering. Another term for this technique is shared storage clustering. Technology Overview With pooled-resource clustering, a cluster of servers€”which can all have different hardware specifications€”are connected to a single, shared storage system (typically a SAN). By leveraging a Windows-based cluster file system, each of the nodes in the cluster can €œsee€? the
same storage area. That is, they all have (for example) an S drive,
I googled this and can't find any more about it. So my question is simple can anyone provide more info about this method. I tried tried the authors web site burt its all sales info address(
I have a 2005 server that is upgraded to SP2 and the error log indicates it is version 9.00.3042.00. However, the system resource database for the same server is reporting as version 9.00.2047.00. From another post I have found out the some replication problems I am having are due to this discrepancy.
I have reapplied SP2 hoping it would change the resource db version, but it did not. Is there another way I can upgrade the resource db without re-installing SQL from scratch?
We've been working hard with our teams here to get better/more/good information out to our users. We€™ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback: http://search.live.com/macros/sql_server_user_education/booksonline
We want to generate as much traffic as possible here so that we can see if this is useful to our users. We€™d love to hear back from everyone we can!
I was watching Bob Tabor's SQL Server 2005 Express for Beginners videos from the ASP.net 2.0 website http://www.asp.net/learn/videos/default.aspx?. In lesson 5 Manipulating the Database Data he refers to download the sample database SQLVideoResources.zip file from the above website.
I searched and did not find it. Can anyone please direct me?
I am using SSIS for sending E-Mails based on certain transactions in SQL Server. I need to dynamically populate the E-Mail content as the content some times needs to be in Japanese text. I would like to use resource files for this. Could anyone please help me on using resource files in SSIS.
after moving the resource database files with this command:
Code Snippet ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf'); GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608) (the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I got a task to get resource consumption (CPU, storage etc.) for stored procedures in certain database. I found nice script ([dbo].[usp_Worst_TSQL]) but looks like script written for SQL 2005 and we use SQL 2012.how to get resource consumption details.
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
I`m having difficulty performing a SQL dump using a network drive. I have successfully created a backup device on a network drive but when I go to perform a backup using it I get the following error message: "Can`t open dump device `RSS4DATARSA3_Dump.Dat`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)" I can successfully perform a backup using a local drive but not this network drive. I have checked the permissions on the network drive and is seems ok. I use the following command to try to perform the backup: "DUMP DATABASE RSA3 TO DISK = `Rss4DataRSA3_Dump.Dat WITH STATS = 10, INIT". Any help would be most appreciated. Thanks in advance!
I get the message when loading the bcp. I check the path variable and the sqlserver BINN directory is there, but twice. If I try opening the command window and write bcp, I get the error, if I enter the whole path c:...80 oolinncp there is no problem. I wonder what could it be?
I've manually reinstalled many times but still having trouble.
Thanx in advance
SQL SERVER 2000 or SQL SERVER 7 (I've tried with both) WINDOWS XP
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf'); GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions?????????????????????? Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Hi All,Can anyone explain me, why we have Resource and Distribution SystemDatabase in SQL Server 2005. Whether they were also available inprevious versions also.Thanks in Advance.Regards,Om
Hi, I am using OPENROWSET command to read from a dbf file in Transact-SQL of SQL Server 2005. Query runs fine for a few tries after that i get the error: OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Text Driver] System resource exceeded.]
If a restart the Server everything works fine, but then the problem starts again. I saw a related postinf with no acceptable answer, unfortunately.
Hi, I am going through the BOL for Sql Server 2005 and i came across the topic of RESOURCE DATABASE. But when i checked in the system databases in the object explorer there was no resource database can anybody tell me where can i find the resource database
I have a few questions about the amounts of resources used by the fuzzy grouping transformation. I am running a little less than 5mil records through a fuzzy grouping that exact matches one column and fuzzy matches one. The server executing the package is a dual-core xeon with 2gb ram, running a default instance of sql 2005 enterprise.
I have been attempting to execute this package for a while now but it keeps erroring out for various reasons. At first, it was from a lack of available memory. I limited the memory usage of sql server to 256mb and set the buffer temp storage path, which alleviated those errors. However, now, my tempdb transaction log is growing significantly. It failed once for not being able to grow and reallocate quickly enough, but enlarging the auto-growth factor fixed that. Then, it filled up the volume the tempdb log was on, so now I have moved it to the san and am about to try again.
I was wondering, does anyone have a general idea on approximate resource usage by fuzzy grouping? Specifically, is there an approximate relation between the number of records grouped and the amount of ram/pagefile required? Also, on the database backend, how big can I expect the tempdb data/log files to get?
