Reputation
470
Top tag
Next privilege 500 Rep.
Access review queues
Badges
1 7 20
Impact
~41k people reached

  • 0 posts edited
  • 3 helpful flags
  • 28 votes cast
Jun
30
comment Why are SELECT queries in sleeping status holding open transactions
@AaronBertrand Most are S LOCK GRANT but about 10 are Sch-S LOCK GRANT (resource_type = METADATA, resource_subtype = DATABASE_PRINICPAL, request_owner_type = 'TRANSACTION'), which still seems strange to me to see for a SELECT, but maybe that's normal. As you said, maybe I'm inventing a problem, but I want to be sure everything looks okay on the database side before I start pointing fingers at the application.
Jun
30
comment Why are SELECT queries in sleeping status holding open transactions
@AaronBertrand The vendor is insisting that there are database problems, but they can't say what, so I am doing some digging. I am not seeing any deadlocks. In a perfect scenario in which the applications are committing or rolling back all transactions, should I not see any sleeping user processes?
Jun
30
comment Why are SELECT queries in sleeping status holding open transactions
@AaronBertrand No, the processes are spid > 50, so they are user processes. They are coming from a vendor application. The handful that I spot-checked are very simple queries selecting just a few columns from small tables. Many of the queries are even selecting from a table with 1 row. Some of these have been sleeping for 12 hours now.
Jun
30
comment Why are SELECT queries in sleeping status holding open transactions
@Shanky Is it possible that the older tables have data that is not accurate? I do not see the sleeping transactions in sys.dm_exec_requests
Mar
13
comment Security for SSIS packages in SQL Server - use a domain account?
Ideally, I am thinking a non-expiring service account on the domain would work. If I just have "Windows Authentication" selected on the connection managers in the package, when it runs in a job, it runs completely under the context of the SQL Server Agent account, not my account, right? If I create a service account that can access Server B, and then set up an SSIS proxy with a credential on Server A, would that keep everything running under the service account?
Jul
3
comment When should I NOT set the Maximum Server Memory option in SQL Server?
I have also seen Jonathan's recommendations, among others. The problem is that they account for only the SQL Server engine running on the server, and not SSIS, SSAS, etc.
Jun
17
comment TempDB data files don't shrink well on SQL 2008
I had a similar problem. TempDB wouldn't shrink no matter what until I ran a few steps as seen here: calyansql.blogspot.com/2012/04/…
May
22
comment Why is this query stuck if there are no waits?
@AaronBertrand There are actually many tables, most with constraints, so it's not as simple as a delete query unfortunately.
May
22
comment Why is this query stuck if there are no waits?
@AaronBertrand That does seem to defeat the point of batching, but the utility provides no options other than "GO".
May
14
comment SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why?
I should also point out that when starting the SQL Server service from Configuration Manager, it takes about 25-30 seconds before I get the error (and it then starts anyway). And one in a while, it starts without the error. Using the Windows Service screen, the SQL Server service starts up within a second or two - every time.
May
14
comment SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why?
Thanks. I know that changing the service account/password should be done in Configuration Manager, but I still can't find what is generating the error, Nor can I find any reason a simple service start/stop should or should not be done via the Windows Services screen.
Jan
30
comment Use @@servername and db_name() on notify operator task
For the server name portion, I recommend changing the DISPLAY NAME in the mail account configuration to include the instance name. For example, all the e-mails from my servers show "[InstanceName] SQL Server Alert" in the FROM column in my mail client. That way there is no confusion regarding what server sent the alert.
Dec
6
comment How to determine if SQL Server Reporting Services database is being used?
If that logs every report execution, I might be good to go. This table in both ReportServer and ReportServerTempDB are both empty. Perhaps they were never used. You should post your coment as an answer!
Dec
6
comment Why isn't SQL Server using the Max Server Memory setting?
The development team is holding us back! :)
Dec
6
comment Why isn't SQL Server using the Max Server Memory setting?
It's an inherited server, and things are moving off it....slowly.
Oct
15
comment Downgrading SQL Server 2012 to Lower Edition
That's a shame. There should be an easy way to do this!
Sep
30
comment Multiple versions of SQL Server on the same development machine
Aaron - what version of Windows are you using for that bad boy?
Sep
30
comment Multiple versions of SQL Server on the same development machine
Great suggestions! Seeing as how you've got so many versions running successfully, perhaps I'll go ahead and add SQL Server 2014 to my new server as well. Thanks!
Sep
30
comment Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster
Nice writeup Aaron! I'm going to try this. What happens if I don't adjust the max memory while I have all 4 instances on the same node? Will the buffer caches quickly spill into the page file? Any way to prevent that other than setting the max?
Sep
24
comment Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster
Yes, I have 4 instances total, 2 per node.