I made some progress.  New code:

ALTER procedure [dbo].[RunStoredProcedures]
as

BEGIN TRANSACTION

BEGIN TRY
truncate table dbo.testtable
select 1/0
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR('Rollback of stored procedures was successful', 10, 1)...

Hi SSC team,

I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead...

.

• This reply was modified 1 years, 7 months ago by Jonathan AC Roberts. Reason: deleted, duplicate

Using SoCal_DBD's data setup:

SELECT sa1.*, sa2.Count
  FROM #SampleAppointments sa1
 CROSS APPLY(SELECT COUNT(*) Count
             ...

SELECT T1.UNIT,
       T1.DATE,
       CASE WHEN COUNT(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN...

You need something like this:

DECLARE @var1 nvarchar(30);
SET @var1 = 'Database1';

DECLARE @sql nvarchar(max);
SET @sql = '
USE ' + QUOTENAME(@var1) + ';
-- Add the rest of your script below:
SELECT...

Does that actually make any difference?

I have query which is filling TEMPDB

I need suggestion how to modify this query to which can minimize the TEMPDB space to fill

update #TempShow1 set Name = case...

The day my bank starts using NOLOCK on its SQL Server queries is the day I start ROLLBACK TRANSACTION on my account and JOIN a new bank.

I've changed table [dbo].[CostKeyDeletes]. There is no need for and ID and CostKey should be the clustered index. I've added an extra column to show if the CostKey has been...

Hi Bruin,

Thanks for your question! Could you start a new thread and include the table definitions (DDL), details of any indexes, and explain exactly what you need the cleanup to...

Since you say the updates are not "extremely frequent", it's mostly an "Insert" index by TenantID.  It's a Type 98 Index and since there aren't a large number of...

Hi Jeff

Thank you for the detailed explanation and advice on Fill Factor settings. It is incredibly helpful.

In our case, the clustered index (TENANTID, ID) will likely see updates to rows...

post to get new page

Jeff,

Have you got any recommendations for the fill factor when I create the new clustered primary keys on (TENANTID, ID)?

Where ID is an identity column on the table.