Cacher is the code snippet organizer for pro developers

-------------------------------Rebuild Index of table-----------------------------
1.
USE <<DatabaseName>>;
GO
ALTER INDEX ALL ON DatabaseName.TableName
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO
2.
sp_updatestats

----------------------------Shrink Database-----------------------------------------------------

ALTER DATABASE Insight SET RECOVERY SIMPLE
DBCC SHRINKDATABASE (Insight);
ALTER DATABASE Insight SET RECOVERY FULL   

------------------------SP to Shrink Database Log-------------------------------------------------------------------------

USE master;
GO

CREATE PROCEDURE dbo.ShrinkLogAndResetRecovery
    @DatabaseName SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LogFileName SYSNAME;
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @BackupPath NVARCHAR(500);
    DECLARE @DateSuffix NVARCHAR(20);

    -- Build date suffix like: 20250630_1845
    SET @DateSuffix = CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '');

    -- 1. Change to SIMPLE recovery model
    SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE';
    EXEC sp_executesql @SQL;

    -- 2. Get the logical name of the log file
    SET @SQL = '
        SELECT @LogFileName_OUT = name 
        FROM [' + @DatabaseName + '].sys.database_files 
        WHERE type_desc = ''LOG''';
    EXEC sp_executesql @SQL, N'@LogFileName_OUT SYSNAME OUTPUT', @LogFileName OUTPUT;

    -- 3. Shrink the log file to 1024 MB
    SET @SQL = 'USE [' + @DatabaseName + ']; DBCC SHRINKFILE ([' + @LogFileName + '], 1024)';
    EXEC sp_executesql @SQL;

    -- 4. Change back to FULL recovery model
    SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY FULL';
    EXEC sp_executesql @SQL;

    -- 5. Take full backup
    SET @BackupPath = 'I:\SQLBackups\' + @DatabaseName + '_FullBackup_' + @DateSuffix + '.bak';
    SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = N''' + @BackupPath + ''' WITH INIT, FORMAT';
    EXEC sp_executesql @SQL;

    PRINT 'Shrink and recovery reset complete for database: ' + @DatabaseName;
END;