Change FULL Recovery Model to SIMPLE and Shrink Log
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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) PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR) PRINT '-------------------------------------------' PRINT 'Execution Time : ' + CONVERT(VARCHAR(255), ( DATEDIFF(MILLISECOND, @StartTime, @EndTime) )) + ' MilliSeconds' -----------END SQL SCRIPT FOOTER-------------------- |