During a Wise Installation upgrade of our software, we are renaming a
directory that contains our ExtendedSP DLL. We issue a
DBCC TSWSQLXP(Free) call before doing the rename, yet SQL Server,
still "holds on" to that DLL. We install a new version, and the ListDLLs
utility (from sysinternals) lists the new DLL in the correct directory.
However, when trying to remove the renamed directory, it won't let us
remove the old DLL because it says it is in use. We can delete the NEW
DLL in the NEW directory with no problem.
I have run the DBCC call numerous times and SQL Server STILL won't
release the DLL for deletion. The only way to delete the OLD DLL is to
stop the SQL Server, delete the DLL, and then restart SQL Server. We
do NOT want to do this because there may be other processes running in
SQL Server.
I'm running into a problem where the class I'm running seems to eat up a lot of memory with sql server. When it's done running, the memory usage never goes down in taskmanager. I can't figure out where the memory leak might be. Here's the code that is being called. Does anyone see a reason why it would continue to eat memory as it runs and then not release it? Thanks. using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient;
I have a co-worker whose sql server 2005 is exhibiting strange behavior. We have already re-installed sql server 2005 and service packed it to SP2 to try and see if the behavior stops but it has not.
Every so often during the day sql server 2005 will start to slow down to the point that my co-worker's queries begin to time out. He turned on profiler to look at what was going on behind the scenes.
We see where sa is releasing and acquring locks to the tune of 180,000 rows in a fifteen minute span when this behavior starts so does his time outs. He has reporting options and analysis services installed but not configured. His only connection is to his local database. Occasionally, you see a number like - (03000d8f0ecc) appear in the Text Data column in profiler for sa. I read something about reporting options using sa for clean up but I don't think that is what is happening here.
Does someone have a clue as to what is happening and a way we can prevent the behavior? It is affecting his ability to work on his application.
When I close a web form that has a connection to my SQL Server, I am not seeing the memory process close in task manager (of the SQL Server). I am using the "open late close early" theory of database connections. I am using the "close" method for my database connections. Is there any automated utility that will shut down these processes? I thought when the user was disconnected from the database, the memory process would automatically shut down.
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
My application is a VB.net client server app with SQL Express on the backend, for some reason both my SQL server and Application continue to increase their memory usage over time, every procedure utilizes the close method of the sqlconnection and then sets the sqlconnection to nothing. Is there anything else I should be doing to close the connection and prevent this memory increase?
Has anyone ever seen a situation where SQLSRVR.EXE starts gobbling RAM when under load but does not seem to release it (as seen by mem usage under Task Manager or the related PerfMon counters?) I am running a test of 4 client applications that are hammering against the server but when I check the stats memory is consumed up to the maximum - when I halt the client applications and reduce the processing load to zero the usage stats still show the SQL engine as holding the memory.
I'm running a copy of SQL 7.0 EE on Win2K Advanced Server, using a Compaq 8500 w/ 750MB RAM.
vighnahar writes "I am using SQL server 2000 / 2005 If I run any query on SQL server it is using some memory for it’s execution but not releasing it’s memory after completion on SQL query. This is giving a problem in my application. Where for each user it is consuming 400 MB RAM on SQL server. after login of fifteen users server is getting slow. Is there any way by which I can release memory of SQL server as I don’t want SQL server to keep it’s result etc in memory, So that I can use this memory for other processing.
I am writing sample code of VB6 to check for memory utilization. After clicking on button you can observer memory in task manager.
Private Sub Command1_Click() Dim con As ADODB.Connection Dim sSql As String Dim Rs As New ADODB.Recordset Set con = New ADODB.Connection con.Open "Upcrest", "sa", "" Rs.Open " select * from sientity ", con MsgBox Rs.Source Rs.Close Set Rs = Nothing con.Close Set con = Nothing End Sub"
When i deleted some 8000 rows from aspnet_profile, some space should have been released. On the contrary, the db size increased. Where did the space go and why did the db size increase after deleting the records? There are no triggers either.
I thought it might be log files..but my hosting provider tells me that db is set to Simple Recovery which does not utilize a Log File. So we cannot shrink it.
Any idea how can i release some space. Does truncating a table release db space and not fill the log?
Please guide step by step. I am not very thorough with sql
i am using visual web developer 2005 and SQL Express 2005 with VB as the code behindi am using the following code to update the database table Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = 2 ,progresspercentage = 15 , fromdesignlink = '" + designlink + "' WHERE order_id =" + ordersid.ToString() update.Update() update.Dispose() update = Nothing i am using update.Dispose() and update = nothing to release the memoryis it really necessary to use both the commandsif not , in my case which one is enough and what is the reasonplease help me
My Transactional log size increased to 39GB, it is in full recovery mode,
To regain the space, i have done the following BACKUP LOG DB_NAME WITH TRUNCATE_ONLY DBCC SHRINK_FILE (LOG_FILE_NAME,500) But not able to regain the space in the hard disk.
No Transactional backups to truncate the log file were setup.
Can you please tell me why the space was released and what should i do further to clean up the sapce
I am trying to run a stored procedure in SQL 2005 which calls a registry value using the RegRead stored procedure which in conjunction executes a line of code. This stored procedure has previously worked in both Sql 7/2000.
However, I am getting the following error when trying to run it in SQL 2005:
Msg 22001, Level 16, State 1, Line 0 xp_regread() returned error 5, 'Access is denied.'
Does anyone know if Extended Stored Procedures are supported in SQL 2005?
I have a .NET 1.1 component that I want to use as an extended stored procedure in SQL Server 2000. Is this possible? FYI, I read in many sources (e.g. MSDN blogs) that creating extended stored procedures can only be done using a low level language such as C/C++.
I am wondering if it's possible to expose this component as a COM component using the .NET interop library, then register it with SQL Server 2000, and finally be able to consume it from TSQL?
Could anyone inform me how much the conventional memory SQL7 Enterprise on Advanced Server 2000 can have? The SQL is on an active/active cluster. Currently each runs at 2GB of memory, with available memory for failover. I am planning to add another 2GB to each SQL node. Is this possible? Are there any configurations I need to do? Do I need to set AWE enabled? and How much?
I use this code in a utility procedure (for performance testing) but it is really slow.
For example, a session with three events is taking 5 seconds to complete this query:
DECLARE @xml xml= ( SELECT CAST(xet.target_data AS xml) FROM sys.dm_xe_session_targets AS xet JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address) WHERE xe.name = @name );
I am wanting to get/filter on all queries and procs that take longer than 2 seconds to run (I'll balance real values later) but I'm not sure which Action out of the XE that I need.
I am using SQL Server 2014 and thought I had used sqlserver.sql_statement_completed.duration > 2000 in a previous version.
What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
I have two extended events sessions running on a server. I do have sql jobs that automatically stop the XE sessions and import the results to working tables. I see two "ALTER EVENT SESSION XXXX ON SERVER STATE = STOP" statements that are being executed for more than 2 days, the wait types are XE_SERVICES_MUTEX and PREEMPTIVE_XE_SESSIONCOMMIT. This is not the first time I see this behavior.I do not want to kill the sessions (I guess the sessions won't die anyway) neither restart the sql service
I have created an extended stored procedure (that executes a DLL file) in SQL Server 2005 Express. When I execute this procedure the execution enters an infinite loop and hangs there. When I execute the same procedure on SQL Server 2000 it executes correctly. I wonder if the problem relates to SQL Server 2005, since no problem occurs with SQL Sever 2000. And if so, how could I repair it?
I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!
I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.
WITH xEvents AS (SELECT object_name AS xEventName, CAST (event_data AS xml) AS xEventData FROM sys.fn_xe_file_target_read_file ('C:LoginTraceShared_0*.xel', NULL, NULL, NULL)) SELECT distinct top 1000 xEventName, xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name, xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name FROM xEvents
I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
I need to find any stored procedures that have not been used over a certain time period.
I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
I've installed Windows 2003 SP2 in a stand-alone cluster (will be adding a second node later). I'm now installing SQL Server 2005 Developer Edition 64-bit extended and would like to have the "Create a SQL Server Failover Cluster" box to not be grayed out. Even when I check the "SQL Server Database Services" box, it still has the cluster box grayed out.
i'm calling a create table SQL from my ASP. There are a lot of fields created by this sql. Wondering if an extended stored procedure would be a better choice? (can't use a stored procedure as i need to pass arrays) Will it enhance performance and also, can i use VB to make that DLL or i need to use VC?
I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT
