How to switch SSISDB after a shrink operation to the real one

Alen Cappelletti 992 Reputation points
2024-07-08T21:19:08.9066667+00:00

Hi all,
I'm finishing a operation on a copy of the SSISDB database. It was several GB due to incorrect settings in the log processes (verbose) and some jobs that continued indefinitely without stopping. Now, these issues have also been fixed with limited loops and subsequent restarts.

I would like some advice on how to proceed better. I made a backup of the current SSISDB and restored it as 'SSISDB_2_BE'. On this, I am performing some TRUNCATE operations on certain tables (after deleting the FKs... which I'll then restore).

At the end of this operation, I'll proceed with the shrink on the data file in chunks of 50 MB (from 70Gb to few Mb)... 'til the desired size is reached (after about 1 hour and 15 minutes more roughly).

Now my doubt is how to proceed best? Obviously, I have a backup on the net ready in case of problems. Should I do a rename between the two databases, or is it better to stop the integration services and remove the real one for the other... naturally rename before correctly... after this a restart of SSIS service on the instance?

-- 1. NO stop SSIS service...
USE master;  
GO  
ALTER DATABASE SSISDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE SSISDB MODIFY NAME = SSISDB_OLD;
GO  
ALTER DATABASE SSISDB_2_BE MODIFY NAME = SSISDB;
GO  
ALTER DATABASE SSISDB_OLD SET MULTI_USER;

or

-- 1. Stop only SSIS service (no SQL instance)
-- 2. Detach SSISDB actual (renamed via filesystem)
-- 3. rename the new one like the old
-- 4. restart SSIS service

USE master;  
GO   

ALTER DATABASE SSISDB_2_BE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE SSISDB_2_BE MODIFY NAME = SSISDB;
GO  
ALTER DATABASE SSISDB SET MULTI_USER;
GO

Which of the two options is more advisable? Or if you have better ideas, I would appreciate them in advance.
I do not consider SSISDB as a system database since it can have any database_id. Is it necessary for it to have the same datafile names? I don't think so...

Thanks ALEN

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,788 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,577 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 36,511 Reputation points
    2024-07-09T02:19:12.0566667+00:00

    Hi @Alen Cappelletti,

    You may check below blogs about how to manage SSISDB database.

    SQL Server Integration Services Catalog Best Practices

    Managing the size of the SQL Server SSIS catalog database

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.