Discussion :

[Cyborg289]

Bonjour,

Je suis débutant en TRANSAC-SQL.
J'ai essayer de faire un script qui tronque le transaction log de toutes les bases utilisateurs de mon serveur dont voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
/*Procedure permettant de tronquer tous 
les logs de toutes base de données du server
sauf les bases systeme (master,model,msdb,Northwind,pubs,tempdb)*/
 
CREATE PROCEDURE TRUNCATE_TRANSACTION_LOG_ALL_DB
AS
 
DECLARE @dbname varchar(50)
DECLARE @logname varchar (50)
 
DECLARE alldbname CURSOR FOR
 
--On selectionne tous les noms des base dans la table sysdatabases
SELECT name from master.dbo.sysdatabases;
 
OPEN alldbname
 
--On initialise la variable @dbname avec la première valeur renvoyée par la requête
FETCH alldbname INTO @dbname
 
--Tant que l'on a des résultats
WHILE @@fetch_status = 0
BEGIN
	SET @logname = @dbname + '_log'
	-- Si la base n'est pas une base systeme
	if @dbname <> 'master' and @dbname <> 'model' and @dbname <> 'msdb' and @dbname <> 'Northwind' and @dbname <> 'pubs' and @dbname <> 'tempdb'
	BEGIN	
		--On tronque le transaction log
		backup log @dbname with truncate_only
		dbcc shrinkfile (@logname, truncateonly)
	END
 
--On initialise @dbname avec l'enregistrement suivant
FETCH alldbname INTO @dbname
END
 
CLOSE alldbname
 
DEALLOCATE alldname

le problème c'est que j'obtient l'erreur suivante quand je l'insère dans un job :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Msg 8985, Sev 16: Could not locate file 'xxxxx_log' in sysfiles. [SQLSTATE 42000]
Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]

Cette erreur apparait en fait car je ne suis pas positionné sur la base avant de faire le shrinkfile.
Mais quand j'essaye de faire un "USE @dbname" j'obtient l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Server: Msg 154, Level 15, State 1, Procedure TRUNCATE_TRANSACTION_LOG_ALL_DB, Line 25
a USE database statement is not allowed in a procedure or trigger.

Comment faire autrement.

Merci d'avance

[serge0934]

essaies çà

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
use Master
go
 
Create Table #BckDatabases (
  databasename sysname
)
Set nocount on 
declare @BckPath varchar(255)
declare @Prefix varchar(50)
declare @Extension varchar(10)
/****************************/
/*       Paramétrage        */
/****************************/
Set @BckPath = 'rep_save'
Set @Prefix  = 'nom_serveur'
Set @Extension = '.bak'
 
  insert into #BckDatabases select name from sysdatabases where name <> 'master' 
and name <> 'model' and name <> 'msdb' and name <> 'Northwind' 
and name <> 'pubs' and name <> 'tempdb'
 
/****************************/
PRINT ''
PRINT 'Liste des bases à Backuper :'
Select * from #BckDatabases
 
declare @CurrentDB sysname
declare @sql varchar(2000)
declare @fileName varchar(255)
 
Set nocount off
 
Declare curDB Cursor for
  Select databasename from #BckDatabases
 
open curDB
Fetch next from CurDB into @CurrentDB
while @@fetch_status = 0
begin
  Set @FileName = @bckPath +@Prefix +@CurrentDB + @Extension
  PRINT '===================================================================='
  PRINT '** '+@CurrentDB + ' Backup Started on '+Cast(GetDate() as Varchar)
  Set @sql = 'BACKUP DATABASE '+@CurrentDB + ' TO DISK=N'''+@filename+''''
  print @sql
  exec (@sql)
  PRINT '** '+@CurrentDB + ' Backup Ended on '+Cast(GetDate() as Varchar)
  PRINT '===================================================================='
  PRINT ''
  Fetch next from CurDB into @CurrentDB
end
deallocate CurDB
drop table #BckDatabases

[ZERS]

Bonjour,

sufit d'inclure le USE dans un exec.

Je t'ai rajouté une petite astuce dans le select qui te permet de ne pas avoir à tester les noms des bases

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
DECLARE @dbname varchar(50)
DECLARE @logname varchar (50)
DECLARE @sql nvarchar(4000)
 
 
DECLARE alldbname CURSOR FOR
SELECT name from master.dbo.sysdatabases
--tu peux rajouter cela
--where sid <>1
 
 
OPEN alldbname
 
--On initialise la variable @dbname avec la première valeur renvoyée par la requête
FETCH next from alldbname INTO @dbname
 
--Tant que l'on a des résultats
WHILE @@fetch_status = 0
BEGIN
	SET @logname = @dbname + '_log'
	-- Si la base n'est pas une base systeme
	-- TEST A ENLEVER SI TU AS MIS LA CLAUSE WHERE sid<>1 dans ton select
 
	if @dbname <> 'master' and @dbname <> 'model' and @dbname <> 'msdb' and @dbname <> 'Northwind' and @dbname <> 'pubs' and @dbname <> 'tempdb'
	BEGIN	
		--On tronque le transaction log
		backup log @dbname with truncate_only
 
		set @SQL = 'USE ' + @dbname 
		set @sql = @sql + ' dbcc shrinkfile (' + @logname + ', truncateonly)'
		set @SQL = @sql + ' USE master'
 
		exec(@SQL)
	END
 
--On initialise @dbname avec l'enregistrement suivant
FETCH alldbname INTO @dbname
END
 
CLOSE alldbname
 
DEALLOCATE alldbname

[Cyborg289]

Merci beaucoup pour vos réponses qui m'aident beaucoup.

[Cyborg289]

Bonjour,

Une dernière question. Je voudrais également récupérer le nom du fichier transaction log.
Il faut donc que je fasse un SELECT sur le champ "name" de la table "sysfiles" de ma base.
Comment puis-je intégrer cela dans le code ci-dessus ?

Merci d'avance.

[ZERS]

Bonjour,

Une dernière question. Je voudrais également récupérer le nom du fichier transaction log.
Il faut donc que je fasse un SELECT sur le champ "name" de la table "sysfiles" de ma base.
Comment puis-je intégrer cela dans le code ci-dessus ?

Merci d'avance.

y'avait qu'un petit effort à faire

Y'a d'autres moyens mais le suivant marche (le nom du fichier est dans la variable @LOGFILENAME)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
DECLARE @dbname varchar(50)
DECLARE @logname varchar (50)
DECLARE @sql nvarchar(4000)
DECLARE @LOGFILENAME sysname
 
DECLARE @Para NVARCHAR(500)
SET @Para = N'@FILENAME nvarchar(4000) OUTPUT'
 
DECLARE alldbname CURSOR FOR
SELECT name from master.dbo.sysdatabases
--tu peux rajouter cela
--where sid <>1
 
 
OPEN alldbname
 
--On initialise la variable @dbname avec la première valeur renvoyée par la requête
FETCH next from alldbname INTO @dbname
 
--Tant que l'on a des résultats
WHILE @@fetch_status = 0
BEGIN
	SET @logname = @dbname + '_log'
	-- Si la base n'est pas une base systeme
	-- TEST A ENLEVER SI TU AS MIS LA CLAUSE WHERE sid<>1 dans ton select
 
	if @dbname <> 'master' and @dbname <> 'model' and @dbname <> 'msdb' and @dbname <> 'Northwind' and @dbname <> 'pubs' and @dbname <> 'tempdb'
	BEGIN	
		--On tronque le transaction log
		backup log @dbname with truncate_only
 
		set @SQL = 'USE ' + @dbname 
		set @sql = @sql + ' dbcc shrinkfile (' + @logname + ', truncateonly)'
		set @sql = @sql + ' select @FILENAME = filename from sysfiles where name = ''' + @logname + ''''
		set @SQL = @sql + ' USE master'
 
		exec sp_executesql @SQL, @PARA, @FILENAME = @LOGFILENAME OUTPUT
		select @LOGFILENAME
	END
 
--On initialise @dbname avec l'enregistrement suivant
FETCH alldbname INTO @dbname
END
 
CLOSE alldbname
 
DEALLOCATE alldbname

[Cyborg289]

Merci pour ta réponse mais en fait ce n'est pas cela que je voulais.
Je voudrais que la variable logname soit récupérer dans la table sysfiles de chacune des bases pour lesquelles je fais le shrinkfile.
J'ai donc essaye de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
set @logname = select name from @dbname.dbo.sysfiles where fileid=2

mais cela ne marche pas a cause de la variable @dbname.

j'ai donc essayé un truc comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
set @sql2 = 'SELECT name from ' + @dbname + '.dbo.sysfiles where fileid=2'
set @logname = exec(@sql2)

Ca ne marche pas non plus. Comment faire pour récupérer cette valeur ?