Shrink All database

see progress statuss

MSSQL Backup Status Current process and all processes

a. SELECT d.name, d.log_reuse_wait_desc FROM sys.databases AS d

i. log_reuse_wait_desc =

1. OLDEST_PAGE----- problem with checkpoint
2. LOG_BACKUP ---- issue during backup not possible to shrink
3. NOTHING= No issue ii. Aller dans ssms et lancer une truncate des log

DBCC OPENTRAN 
DBCC log ('enotary-notices-prod', 4)
DBCC log (MY_DB, 4)


sp_spaceused 
DBCC SQLPERF(LOGSPACE);

EXECUTE master.dbo.DatabasesBackup
@Databasess = 'USER_DATABASES',
@Directory = 'D:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y'

Attention cette action rend la restauration compliqué en cas de problème car conflit avec AVAMAR.

iii. Lancer les scripts pour le shrink de toutes les dbs

    DECLARE @Name AS VARCHAR(100)
    DECLARE @IntVariable int;  
    DECLARE @SQLString nvarchar(500);  
    DECLARE @ParmDefinition nvarchar(500);  
    DECLARE @ressult  varchar(1000);
    declare @size int;

    DECLARE dbCur CURSOR FOR
    SELECT name FROM SYS.databases
    OPEN dbCur
    FETCH NEXT FROM dbCur
    INTO @Name
    WHILE @@FETCH_STATUS = 0
    BEGIN
              SET @ParmDefinition = N'@database varchar(1000), @size int OUTPUT, @ressult varchar(1000) OUTPUT'; 
              SET @SQLString = 'use [' + @Name + '];
                               CHECKPOINT;   
                        select @ressult=name,@size=size from sys.database_files where type_desc=''log''
                               select name,size from sys.database_files where type_desc=''log''
                               CHECKPOINT;
                               ' 

              print @SQLString
              begin try 
                EXEC sp_executesql @SQLString, @ParmDefinition,@database=@Name,@size = @size OUTPUT , @ressult  =@ressult OUTPUT 
                  print @ressult
                  SET @SQLString = 'use [' + @Name + '];   
                            DBCC SHRINKFILE (['+ @ressult +' ], 1)
                                   CHECKPOINT;' 
                     print @SQLString
                   EXEC sp_executesql @SQLString, @ParmDefinition,@database=@Name,@size = @size OUTPUT , @ressult  =@ressult OUTPUT 
                  print (@size/1024)
            End try
             begin catch
                    print 'test'
            end catch
           --exec sp_executesql 'use'
    FETCH NEXT FROM dbCur
    INTO @Name
    END
    CLOSE dbCur
    DEALLOCATE dbCur

status of shrink

select
a.session_id
, command
, b.text
, percent_complete
, done_in_minutes = a.estimated_completion_time / 1000 / 60
, min_in_progress = DATEDIFF(MI, a.start_time, DATEADD(ms, a.estimated_completion_time, GETDATE() ))
, a.start_time
, estimated_completion_time = DATEADD(ms, a.estimated_completion_time, GETDATE() )
from sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
where command like '%dbcc%'