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
