Truncate Log – Rebuild Indexes – Statistics

Posted on February 26, 2015 by

Raju Venkataraman Posted in SQL Server, Technology

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;

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--------------------

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

/******************************************************************************************************

** 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)

Truncate Log – Rebuild Indexes – Statistics

Recent Posts

Categories

Recent Posts

Archives