5

Use @@procid to get the currently executing routine and object_name() to convert the id to a name Hth


5

Another idea using Extended Events. Create an extended events session to catch errors CREATE EVENT SESSION [Track_Errors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text)) ...


4

When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error? In your CATCH block, you can use ERROR_PROCEDURE() to get the name of the procedure or trigger where the error occurred. If you simply use THROW in your CATCH block in the inner procedures, then your outer procedure can access these ...


4

how about creating a database snapshot and then running the BCP against that ? https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql?view=sql-server-ver15


2

After your updated comment, I believe my understanding is correct, that the Toolbox was only ever implemented to be used with the Maintenance Plan designer at this point. It's briefly mentioned in the Microsoft Books Online Use the Toolbox. My guess is they may have designed the Toolbox to be flexible should they implement it for other scenarios, but at this ...


2

Without the deadlock trace, we can only guess based on symptoms. Long-term blocking and deadlocks suggest the singleton row update query is performing a full scan rather than locating the row by the PK index. This will happen if the primary key parameter data type is different than the referenced column and results in a non-sargable expression that can't use ...


2

What you are you seeing is by design for tempdb Transaction Log file. I wish Microsoft had better documentation on this. How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching almost max 90-95%. From the First reference article: Tempdb is not recovered in the event ...


2

As far as I can tell, a real table can still be called [@sometable] the same as a table variable, but this is still a useful filter. The definitive XQuery filter seems to be that there is no @Database attribute on a node named Object. with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select qp.query_plan from sys....


1

I believe your CTE already gives you the list of all distances between any two Cities, so you just need to join the CTE to itself on reversed City fields to get the reverse results like so: WITH CityDistances AS ( SELECT CITYA_id, CITYA, CITYB_id, CityB, DIS_IN_KM FROM DB.dbo.DISTANCE d INNER JOIN cities ...


1

Try using the TempDBInfo procedure, it shows what currently is inside TempDB's files and who is using it most https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/TempDB%20-%20TempDBInfo.sql Procedure is not fully finished, I am going to rework the way it shows summary information, and also I am planning to add logging capabilities But ...


1

Once the partition is empty and no other partitioned table on a partition scheme using the same partition function has data in the corresponding partition, just use ALTER PARTITION FUNCTION to MERGE away boundary point in the empty partition.


1

Correlation between MaxWorkers created and CPU utilization I strongly recommend you to read the Thread and Task Architecture Guide doc. I'll quote some relevant parts: About the higher number of workers and the relation between maxworkers and CPU: On a very busy SQL Server Database Engine, it's possible to see a number of active tasks that's over the limit ...


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