Lli : Remplacement de texte : « Database » par « Databases »

2023-03-24T18:44:19Z

Remplacement de texte : « Database » par « Databases »

← Version précédente		Version du 24 mars 2023 à 20:44
Ligne 20 :		Ligne 20 :


	<code>EXECUTE master.dbo.~~DatabaseBackup~~		<code>EXECUTE master.dbo.DatabasesBackup
	@~~Databases~~ = 'USER_DATABASES',		@Databasess = 'USER_DATABASES',
	@Directory = 'D:\Backup',		@Directory = 'D:\Backup',
	@BackupType = 'LOG',		@BackupType = 'LOG',

Lli : Page créée avec «  == 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_sp... »`


2023-03-23T12:49:28Z
Page créée avec «  == see progress statuss == MSSQL Backup Status Current process and all processes  a. <code>SELECT d.name, d.log_reuse_wait_desc FROM sys.databases AS d</code>  i. log_reuse_wait_desc =  # OLDEST_PAGE----- problem with checkpoint # LOG_BACKUP ---- issue during backup not possible to <mark>shrink</mark> # NOTHING= No issue ii. Aller dans ssms et lancer une truncate des log   <code>DBCC OPENTRAN   DBCC log ('enotary-notices-prod', 4)  DBCC log (MY_DB, 4)      sp_sp... »
Nouvelle page


== see progress statuss ==

MSSQL Backup Status Current process and all processes



a. <code>SELECT d.name, d.log_reuse_wait_desc FROM sys.databases AS d</code>



i. log_reuse_wait_desc =



# OLDEST_PAGE----- problem with checkpoint

# LOG_BACKUP ---- issue during backup not possible to <mark>shrink</mark>

# NOTHING= No issue ii. Aller dans ssms et lancer une truncate des log



 <code>DBCC OPENTRAN 

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

 DBCC log (MY_DB, 4)

 

 

 sp_spaceused 

 DBCC SQLPERF(LOGSPACE);</code>



 

 <code>EXECUTE master.dbo.DatabaseBackup

 @Databases = 'USER_DATABASES',

 @Directory = 'D:\Backup',

 @BackupType = 'LOG',

 @ChangeBackupType = 'Y'</code>

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



iii. Lancer les scripts pour le <mark>shrink</mark> de toutes les dbs

     <code>DECLARE @Name AS VARCHAR(100)

 <nowiki> </nowiki>   DECLARE @IntVariable int;  

 <nowiki> </nowiki>   DECLARE @SQLString nvarchar(500);  

 <nowiki> </nowiki>   DECLARE @ParmDefinition nvarchar(500);  

 <nowiki> </nowiki>   DECLARE @ressult  varchar(1000);

 <nowiki> </nowiki>   declare @size int;

 

 <nowiki> </nowiki>   DECLARE dbCur CURSOR FOR

 <nowiki> </nowiki>   SELECT name FROM SYS.databases

 <nowiki> </nowiki>   OPEN dbCur

 <nowiki> </nowiki>   FETCH NEXT FROM dbCur

 <nowiki> </nowiki>   INTO @Name

 <nowiki> </nowiki>   WHILE @@FETCH_STATUS = 0

 <nowiki> </nowiki>   BEGIN

 <nowiki> </nowiki>             SET @ParmDefinition = N'@database varchar(1000), @size int OUTPUT, @ressult varchar(1000) OUTPUT'; 

 <nowiki> </nowiki>             SET @SQLString = 'use [' + @Name + '];

 <nowiki> </nowiki>                              CHECKPOINT;   

 <nowiki> </nowiki>                       select @ressult=name,@size=size from sys.database_files where type_desc=<nowiki>''</nowiki>log<nowiki>''</nowiki>

 <nowiki> </nowiki>                              select name,size from sys.database_files where type_desc=<nowiki>''</nowiki>log<nowiki>''</nowiki>

 <nowiki> </nowiki>                              CHECKPOINT;

 <nowiki> </nowiki>                              ' 

 

 <nowiki> </nowiki>             print @SQLString

 <nowiki> </nowiki>             begin try 

 <nowiki> </nowiki>               EXEC sp_executesql @SQLString, @ParmDefinition,@database=@Name,@size = @size OUTPUT , @ressult  =@ressult OUTPUT 

 <nowiki> </nowiki>                 print @ressult

 <nowiki> </nowiki>                 SET @SQLString = 'use [' + @Name + '];   

 <nowiki> </nowiki>                           DBCC SHRINKFILE (['+ @ressult +' ], 1)

 <nowiki> </nowiki>                                  CHECKPOINT;' 

 <nowiki> </nowiki>                    print @SQLString

 <nowiki> </nowiki>                  EXEC sp_executesql @SQLString, @ParmDefinition,@database=@Name,@size = @size OUTPUT , @ressult  =@ressult OUTPUT 

 <nowiki> </nowiki>                 print (@size/1024)

 <nowiki> </nowiki>           End try

 <nowiki> </nowiki>            begin catch

 <nowiki> </nowiki>                   print 'test'

 <nowiki> </nowiki>           end catch

 <nowiki> </nowiki>          --exec sp_executesql 'use'

 <nowiki> </nowiki>   FETCH NEXT FROM dbCur

 <nowiki> </nowiki>   INTO @Name

 <nowiki> </nowiki>   END

 <nowiki> </nowiki>   CLOSE dbCur

 <nowiki> </nowiki>   DEALLOCATE dbCur</code>



== status of <mark>shrink</mark> ==

 <code>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%'</code>

[[Catégorie:Mssql]]

Shrink All database - Historique des versions

Lli : Remplacement de texte : « Database » par « Databases »

Lli : Remplacement de texte : « Database » par « Databases »