Hot answers tagged

7

From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling. So, you need to use SET XACT_ABORT ON or add some client rollback code See SQL Server Transaction Timeout for all the gory details


5

According to the documentation, SQL Server Configuration Manager requires Local Administrator permissions since various SQL Server Configuration items are stored in the local system registry. From the "SQL Server Properties (Startup Parameters Tab)" in the SQL Server Configuration Manager Help: Use of this page is restricted to users who can change the ...


5

It sounds like you might be running out of TCP ports on the SQL Server. How many connections are you seeing to the server at a time? Timeouts like that would definitely be causing the problem.


5

I've just read your entire post and it matches exactly with the symptoms we are seeing in our smaller environment. The only difference is that we are running SQL 2008 R2 on Windows 2008 R2 Standard. We have no errors on any of the switches and no errors from the network stack suggesting that the reason for the timeout is valid. The only common theme here is ...


5

Use the most_recent_sql_handle in sys.dm_exec_connections to see the last statement that was executed. SELECT t.text, QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name, c.connect_time, s.last_request_start_time, s.last_request_end_time, s.status ...


5

I would strongly suggest metadata in the connection in order to trace back to the application. In the connection string, there is an Application Name. There is also session data which can be used in the form of CONTEXT_INFO http://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes Of course all ...


5

It's a bug. This Connect ticket has a workaround for it from Kendra Little: I was able to work around this issue by temporarily adding a scheduled to the Utility Information collection (@collection_set_id=4) with the SQL command below. After doing this I was able to complete the data collection configuration via the GUI successfully. Afterwards I ...


4

You can look at the currently running processes in SSMS in Activity Monitor. You can also get it using sys.dm_exec_sessions as well as sys.dm_exec_connections and sys.dm_exec_requests. Query: SELECT DB_NAME(database_id) as [DB] , login_name , nt_domain , nt_user_name , status , host_name , program_name , COUNT(*) AS ...


4

Typically, when a query is "interfering" with another query it is either blocking or deadlocking. Either of these would not be visible via a default SQL Profiler trace. If you are experiencing deadlocks, you will want trace flags 1204 and 1222 turned on in SQL Server so the deadlock output gets sent to the errorlogs. You could also re-run the trace and ...


4

Have you tried using Adam Machanic's sp_whoisactive? There's an option to get the outer command to see if it really is within a proc. It could be the application is holding open a transaction instead of committing it. Try looking at DBCC OPENTRAN as well.


3

Open SQL Server Configuration Manager In the SQL Server Services pane, locate the instance that has the problem Right-click that instance and select Properties On the Startup Parameters tab, type -f, click Add, then click Apply Right-click that instance and select Start (or Restart) This will start the instance in minimal configuration mode (single user, ...


2

I would also suggest what Julien suggested but I guess scheduling the query through SQL Server Agent would be better idea and then every time it runs you dump the information into a physical table. Two DMVs are joined sys.dm_exec_connections and sys.dm_exec_sessions Like first create a physical table create table Session_Information ( session_id int, ...


2

Can you check you sys.dm_os_schedulers ? Specifically, does work_queue_count deviates from 0 for any significant time? This would indicate worker starvation and would explain many of your symptoms.


2

Have you checked the error log? In Object Explorer, expand Management, expand SQL Server Logs, right-click Current... and choose Show SQL Server Log. Hopefully there will be more details there about what is causing the timeouts. If you can post anything suspect that you find there, we can help you troubleshoot further (though it may be something obvious, ...


2

In your question, there is not enough information to see where the problem is from technical point of view; however, I can see that there is a problem in communication between you as developer and the DBA. This is not abnormal though as DBAs and devs do not always 'speak the same language'. Let's go the some points in your question: You should rely on the ...


2

Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem. Try running the following query replacing DBName and SPName as appropriate. USE DBName GO SELECT SUBSTRING(text, statement_start_offset/2, CASE WHEN statement_end_offset = -1 THEN 999999 ELSE ...


1

Starting with the highest and moving down: SOS_SCHEDULER_YIELD This wait type is usually caused when you have queries performing huge scans. I'd suggest checking to make sure you have the proper indexes in place and you don't have any out-of-date statistics resulting in an inefficient query plan. OLEDB OLEDB waits indicate that your query has to reach ...


1

Search for mysql-related packages that are now installed: rpm -qa | grep -i mysql Delete any packages like mysql, mysql-server, mysql-client, mysql-shared, mysql-shared-compat, mysql-libs. yum remove mysql-server You do not need to remove mysql library bindings like python-mysqldb, php-mysql, etc., unless they had a dependency with some of the previous ...


1

They just released new server & driver packages (7/25/2014). I had the same issue with the old drivers but with the 7/25 release it is gone.


1

So there's a couple of ways that this can happen. The queries in the blocking database reference objects within the blocked database. The queries are waiting for tempdb to allocate pages and one query is blocking another query. There is memory pressure and queries are waiting for memory to be allocated so they have to wait their turn in the memory ...


1

Turns out to have been a bug in the GEOS package-- there is an announcement on the postgis-users list. The link to the bug report is here.


1

I'm probably off-base here with my response, because I'm not really going to address finding the offending code, other than saying... Knowing the executed SQL from the trace, leads to knowing the associated ORM entity, which leads to Find All References in Visual Studio. Hopefully the code is DRY, but that's a separate topic. I'll focus on SQL performance ...



Only top voted, non community-wiki answers of a minimum length are eligible