Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
17 votes
Accepted

Find all IP's or users who access a specific SQL Server

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: ...
Julien Vavasseur's user avatar
16 votes

What is hot spotting in the context of adding files to tempdb?

What is hot spotting? "Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding ...
Aaron Bertrand's user avatar
12 votes
Accepted

How to fix a messed up replication on MS SQL Server

TLDR: It appears that disabling and re-enabling replication probably fixed the issue: exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false' exec ...
TallGuy's user avatar
  • 371
11 votes
Accepted

SSRS error "something went wrong please try again later" in web portal

After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some ...
Bartosz X's user avatar
  • 503
10 votes

What is hot spotting in the context of adding files to tempdb?

What is hot spotting? Aaron is correct and I'm not going to rehash what he has said above, however it's not just about disk IO. The main part that most people have issues with in TempDB is due to ...
Sean Gallardy's user avatar
10 votes
Accepted

sp_WhoIsActive vs sp_BlitzFirst @ExpertMode = 1

They're just different scripts. Adam's sp_WhoIsActive is powerful, free, and copyrighted. I've used it for at least a decade, and I love it, and I still continue to use it personally. However, it's ...
Brent Ozar's user avatar
8 votes

How to fix a messed up replication on MS SQL Server

I had a mess with replication and solved it with this DECLARE @subscriptionDB AS sysname SET @subscriptionDB = N'DBName' -- Remove replication objects from a subscription database (if necessary). ...
Guillermo Zooby's user avatar
6 votes
Accepted

Max worker thread exceeded after manual AG failover

Due to the high urgency of the problem, we restarted the SQL Server Service on the primary and the problem was disappeared. Since no investigation or gathering of data was completed, it's going to be ...
Sean Gallardy's user avatar
5 votes

Troubleshooting a slow performing encrypted stored procedure

Notwithstanding that your question is about how to troubleshoot without decrypting the stored procedure, I would suggest that you do decyrpt the stored procedure so you can do troubleshooting as ...
Hannah Vernon's user avatar
  • 70.1k
5 votes

Find all IP's or users who access a specific SQL Server

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. ...
Shanky's user avatar
  • 19k
5 votes
Accepted

Activity Monitor paused

Found the solution for this after @TiborKaraszi 's tip about WMI. Run wmimgmt.msc on the DB-server Open the properties of WMI-Control (right-click) Open the security tab. Select CIMv2 and click the ...
chittybang's user avatar
3 votes
Accepted

How can I trace the root cause of a MySQL database table that crashed?

The cause is almost always a non-graceful shutdown -- abort, power failure, etc. So, I suggest you already know what is causing each occurrence. It goes something like this... When a MyISAM table ...
Rick James's user avatar
  • 78.3k
3 votes
Accepted

Visual Studio 2010 Shell Error - is it Causing this SQL Server error?

You have several SQL Server errors in the posted message: String or binary data would be truncated. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UPC_Item". The conflict ...
Andriy M's user avatar
  • 23k
3 votes

How do I find out what's causing processes to wait for table flush?

I am going to hazard a guess that the first query at the top, as Rick said, is the cause of it, especially if it is an ANALYZE TABLE query. This is a rather typical cause of the queries piling up in ...
Gordan Bobić's user avatar
3 votes
Accepted

Find which IP added a specific record to a table in SQL Server

Is there a nice way of finding which IP added a specific record? ALTER TABLE YourTable ADD IP_Address varchar(20) DEFAULT cast(connectionproperty('client_net_address') as varchar(20)) This ...
Aleksey Vitsko's user avatar
3 votes
Accepted

what sql server is holding in the used memory?

[...] is there a way to find out what sql server is holding in there memory? Yes and no, depending on what you mean by "holding in there memory" (sic). If you mean what's using memory in ...
Sean Gallardy's user avatar
2 votes
Accepted

SQL Server uses too much CPU

Issue is the following. Let a Hyper-V VM is configured with high startup memory (like 10240MB), and dynamic memory limits active and are like 512-10240 range. VM starts up with full amount of RAM ...
yaapelsinko's user avatar
2 votes

How to fix a messed up replication on MS SQL Server

Restoring the database will break replication, so that's normal. Also most of the other error messages are just follow ons because you haven't been able to remove all the subscriptions (or at least ...
Cody Konior's user avatar
  • 3,508
2 votes

Find all IP's or users who access a specific SQL Server

Enable SQL Server login auditing for successful and failed logins: https://learn.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio Changing the auditing scope would ...
Masood Hashim's user avatar
2 votes
Accepted

SQLServerAgent Error: 32

Error 32 is defined in winerror.h as: ERROR_SHARING_VIOLATION The process cannot access the file because it is being used by another process. Use SysInternals' Handle.exe to determine who (or ...
Hannah Vernon's user avatar
  • 70.1k
2 votes
Accepted

SQL SERVER FileTable/FILESTREAM share suddenly becomes unavailable

This could be related to KB4492899 - FIX: FileTable database level directory is inaccessible after database startup in SQL Server 2016 and 2017. SQL Server 2014 (and possibly earlier additions) also ...
timb's user avatar
  • 136
2 votes

"SSIS Evaluation Period Has Expired" on Dev Instance?

Run regedit and change the value of CommonFiles REG_DWORD under: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\ConfigurationState ...from 1 to 3. Run the Edition Upgrade and this ...
stacylaray's user avatar
  • 2,565
2 votes
Accepted

Replicaton - The current transaction cannot be committed and cannot be rolled back to a savepoint

I re-connected to sql-server using the credentials of my replication agent - which is a sysadmin on my publication server, and db_owner on my subscription database at my subscription server. 1 - ...
Marcello Miorelli's user avatar
2 votes
Accepted

length of the result exceeds the > length limit (2GB) of the target large type

While I can tell you that in my case, I found the job causing the error by it's frequency, (the error was happening every five minutes) and there were only 2 jobs running every 5 minutes. However, ...
HWisnik's user avatar
  • 36
2 votes

Can a query that executes quickly still be a poorly written query?

Simply put: yes. Something being fast does not mean it is necessary good as there are other factors, some objective and some very subjective, to consider. Some examples: The query could be ...
David Spillett's user avatar
2 votes
Accepted

The service broker is administratively disabled

I got it fixed after reviewing and resetting all broker related database IDs. First thing I did was to see what we have (on both source and target servers): --========================================...
Marcello Miorelli's user avatar
2 votes
Accepted

When queries start executing on standby server the replay lag starts to increase

The reason are recovery conflicts. The database can either delay replication or cancel the conflicting query. This is governed by the max_standby_streaming_delay parameter on the standby, which ...
Laurenz Albe's user avatar
  • 52.7k
2 votes

Operational stats mention the same table twice, what does this mean?

Of course it may return the same TableName twice (or more), because the query you're using is looking at system views that return information about indexes, and one table can have multiple indexes on ...
J.D.'s user avatar
  • 37.8k
1 vote

Some timeout in query store but get all SP:Starting and SP:Completed in profiler

If you capture queries when they finish with the SQL:BatchCompleted or SP:Completed trace running, aborting a query will show up as a finished query. Lets say I run this query: select * from master.....
Randi Vertongen's user avatar
1 vote

Imposssible to expand a database in SQL Server Management Studio

Open a new query window and run SELECT * FROM sys.databases. See if the database in question is listed in the results. If it is, see if you can access it by running USE MyDatabase. If that works, ...
MelvinLusk's user avatar

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