I have a handful of queries I use. The general concepts are that the table with the information is catalog.operation_messages and you are interested in events with a 120 (error) type.
Depending on how robust of a query you want to build out, the following two derived tables might also be of interest.
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 files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my ...
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.
SELECT DB_NAME(database_id) as [DB]
, COUNT(*) AS [...
You can use:
FROM catalog.operation_messages AS MSG
INNER JOIN catalog.operations AS OPR
ON OPR.operation_id = MSG.operation_id
WHERE MSG.message_type = 120
This will give you only the error messages from SSIS package ...
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 sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'
I guess this is the equivalent of switching it off and then back on again...
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 contention on certain tracking structures.
Since having multiple tempdb files allows the proportional fill and round robin algorithms to effectively take place ...
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 not open source, which means you can't contribute code to it, and you're not allowed to distribute it to others.
We needed something we could redistribute to ...
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, no ...
Here is one query I created to find the job error message from the SSISDB:
DECLARE @DATE DATE = GETDATE() - 7 -- This is to restrict the data for last 7 days, used in ON condition
SELECT O.Operation_Id -- Not much of use
,E.Folder_Name AS Project_Name
,E.Project_name AS SSIS_Project_Name
,CONVERT(DATETIME, O.start_time) AS Start_Time
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).
EXEC sp_removedbreplication @subscriptionDB
Are the saviours when cleaning up messed up replications.
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 hard to say exactly what was going on at the time.
For some reason the max number of worker was exceeded, this could explain why no one could connect.
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 ...
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 ...
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 required, without guessing what's going on.
"Encrypted" stored procedures in SQL Server are actually not encrypted AT ALL. They are simply obfuscated using a ...
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,
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 occurred in database "PosBe", table "dbo.Item", column 'Item_ID'.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VendorPartNo_Item". The ...
I'm not immediately sure how to interpret the 'nativecode=08004' part of the message shown. In decimal, error -8004 is about 'Illegal FLOAT constant' and is for one of the Informix SQL compilers. In hex, it corresponds to 32772 decimal, but that isn't a known error number. If it is an SQLSTATE value, then … hmmm, I'm not sure how to find out the ...
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 '...
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.
SELECT SUBSTRING(text, statement_start_offset/2,
CASE WHEN statement_end_offset = -1 THEN 999999 ELSE
Error 32 is defined in winerror.h as:
The process cannot access the file because it is being used
by another process.
Use SysInternals' Handle.exe to determine who (or what) has a file open.
If the error log location is D:\MSSQL\ErrorLogs, you could run this to see who has the ERRORLOG file open:
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 will "upgrade" the components.
If that doesn't resolve the issue, then we need to make sure the client components are on the same service pack/build version as ...
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 available. Short after load the host reclaims excess memory from the VM. At the same moment SQL Server process starts to create that unexplainable CPU load.
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 SQL thinks so).
You know you have your publisher (the source database), and at least one subscriber (the destination database), and that these are two different ...
Enable SQL Server login auditing for successful and failed logins: https://docs.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio
Changing the auditing scope would require a SQL service restart. Once service is restarted, all successful logins(user) and its IP Address will start getting logged in SQL Server logs. This data ...
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 is opened, it is flagged (in the .MYI file) that it is open.
In a graceful shutdown, the index blocks from the key_buffer are flushed (to the .MYI) and the the ...
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, after knowing which job was causing the error, I spent a lot of time to determine why the error was happening, given that the job itself was not failing.
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 - right click on the ssms icon
2 - on the SSMS option above, press shift + right click and choose the Run as a different user
3 - add the credentials of the ...
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.
The query could be unnecessarily complex: doing more work than needed to get the same result as a more optimal version. Maybe what it is doing in ~0.27s could actually be done in ~0.07s a ...
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):
-- checking what we have and where we point to