Truncate Log – Rebuild Indexes – Statistics

Change FULL Recovery Model to SIMPLE and Shrink Log

SELECT  sys.databases.name ,
        SUM(size) * 8 / 1024 AS [Total_disk_pace_MB]
FROM    sys.databases
        JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY 2 DESC ,
        sys.databases.name;


USE master;
DECLARE @isql VARCHAR(2000) ,
    @dbname VARCHAR(64) ,
    @logfile VARCHAR(128);

DECLARE c1 CURSOR
FOR
    SELECT  d.name ,
            mf.name AS logfile--, physical_name AS current_file_location, size
    FROM    sys.master_files mf
            INNER JOIN sys.databases d ON mf.database_id = d.database_id
    WHERE   recovery_model_desc <> 'SIMPLE'
            AND d.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
            AND mf.type_desc = 'LOG'
            AND d.state_desc = 'ONLINE';
OPEN c1;
FETCH NEXT FROM c1 INTO @dbname, @logfile;
WHILE @@fetch_status <> -1
    BEGIN
        SELECT  @isql = 'ALTER DATABASE ' + @dbname + CHAR(13)
                + ' SET RECOVERY SIMPLE';
        PRINT @isql;
         EXEC(@isql);

        SELECT  @isql = 'USE ' + @dbname + CHAR(13) + ' checkpoint';
        PRINT @isql;
        EXEC(@isql);

        SELECT  @isql = 'USE ' + @dbname + CHAR(13) + ' DBCC SHRINKFILE ('
                + @logfile + ', 1)';
        PRINT @isql;
        EXEC(@isql);

        FETCH NEXT FROM c1 INTO @dbname, @logfile;
    END;
CLOSE c1;
DEALLOCATE c1;


SELECT  sys.databases.name ,
        SUM(size) * 8 / 1024 AS [Total_disk_pace_MB]
FROM    sys.databases
        JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY 2 DESC ,
        sys.databases.name;

Truncate Log – Rebuild Indexes – Statistics

/******************************************************************************************************
** Project: Database
** Issue: Backup and Shrink
** Desc: Backup and Shrink log and Rebuild Indexes
** Desc: Change the Database Name or Run it on Current Database where you want to Truncate Log and Rebuild
** Auth: Raju
** Date: 2015-02-26 Created

********************************************************************************************************/
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
SET XACT_ABORT  ON

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128)
SET @DB_NAME = ( SELECT DB_NAME(dbid)
                 FROM   master..sysprocesses
                 WHERE  spid = @@SPID
               )
PRINT '-----------------------------------------------------------------------'
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------'
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME
PRINT '-----SCRIPT START TIME: '
    + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121)
PRINT '-----MachineName : ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR)
PRINT '-----SQL Instance : ' + CAST(@@SERVERNAME AS VARCHAR)
PRINT '-----DB User : ' + CURRENT_USER
PRINT '-----System User : ' + SYSTEM_USER
PRINT '-----Host : ' + HOST_NAME()
PRINT '-----Application : ' + APP_NAME()
PRINT '-----TranCount : ' + CAST (@@trancount AS VARCHAR)
PRINT '-----------------------------------------------------------------------'
----------------END SQL SCRIPT HEADER---------------------

-- Standard Variables Declarations

DECLARE @ErrorMessage NVARCHAR(4000) ,
    @ErrorNumber INT ,
    @ErrorSeverity INT ,
    @ErrorState INT ,
    @ErrorLine INT ,
    @ErrorProcedure NVARCHAR(200) ,
    @ErrParameter NVARCHAR(255) ,
    @ErrParameterValue NVARCHAR(255)


-- Script Specific Variables Declarations

DECLARE @StartTime DATETIME2 ,
    @EndTime DATETIME2
SELECT  @StartTime = GETDATE()

-- Script Block Start

DECLARE @pDatabaseName sysname ,
    @pLogicalFileName sysname ,
    @pSQLStmt NVARCHAR(MAX),
	@pRecoveryModelDesc sysname

SELECT  @pDatabaseName = DB_NAME();
SELECT  @pLogicalFileName = name
FROM    sys.master_files
WHERE   database_id = DB_ID()
        AND type = 1


PRINT 'Before Database Size'

SELECT  database_id ,
        DB_NAME(database_id) AS DatabaseName ,
        type_desc ,
        name ,
        ( size * 8 ) / 1000 AS SizeinKB ,
        physical_name
FROM    sys.master_files
WHERE   database_id = DB_ID()

SELECT @pRecoveryModelDesc = recovery_model_desc FROM sys.databases
WHERE database_id = DB_ID()


PRINT 'Backup Log'
SELECT  @pSQLStmt = ' BACKUP LOG ' + @pDatabaseName + '
					 TO DISK = ''nul:'' '

PRINT 'BackupLogSQLStmt  ' + @pSQLStmt
-- Backup Log only for Full Recovery Model
IF @pRecoveryModelDesc = 'FULL'
EXEC master.dbo.sp_executesql @pSQLStmt

PRINT 'Shrink Log'

SELECT  @pSQLStmt = ' DBCC SHRINKFILE(''' + @pLogicalFileName
        + ''', 0, TRUNCATEONLY)'

PRINT 'Shrink Log SQLStmt  ' + @pSQLStmt

EXEC sp_executesql @pSQLStmt

PRINT 'Rebuilding All Indexes'
EXEC sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?', ' ', 0)"

PRINT 'Updating the Statistics'
EXEC sp_updatestats

PRINT 'After Database Size'
SELECT  database_id ,
        DB_NAME(database_id) AS DatabaseName ,
        type_desc ,
        name ,
        ( size * 8 ) / 1000 AS SizeinKB ,
        physical_name
FROM    sys.master_files
WHERE   database_id = DB_ID()



-- Script Block End

SELECT  @EndTime = GETDATE()




IF @@TRANCOUNT > 0
    BEGIN

        SELECT  @ErrorNumber = 50001 ,
                @ErrorSeverity = 16 ,
                @ErrorState = 1 ,
                @ErrorLine = ERROR_LINE() ,
                @ErrParameter = '@@TranCont' ,
                @ErrParameterValue = CONVERT(VARCHAR(255), 'You left the Transction Open!!!. Open Transaction Count :: '
                + CONVERT(VARCHAR(10), @@TRANCOUNT)) ,
                @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        GOTO ErrorHandler
    END

GOTO ExitScript


ErrorHandler:


    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
RAISERROR
        (
        @ErrorNumber,
		@ErrorSeverity,
        @ErrorState,     -- parameter: original error state.
		@ErrParameter,
		@ErrParameterValue
        );


ExitScript:


-----------BEGIN SQL SCRIPT FOOTER------------------
PRINT '-------------------------------------------'
PRINT '---FINISHED SQL SCRIPT--'
PRINT '---COMPLETED TIME:' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121)
PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR)
PRINT '-------------------------------------------'
PRINT 'Execution Time : ' + CONVERT(VARCHAR(255), ( DATEDIFF(MILLISECOND,
                                                             @StartTime,
                                                             @EndTime) ))
    + ' MilliSeconds'


-----------END SQL SCRIPT FOOTER--------------------