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 =
- OLDEST_PAGE----- problem with checkpoint
- LOG_BACKUP ---- issue during backup not possible to shrink
- 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%'