[TRANSAC-SQL] Problème dbcc shrinkfile

Version imprimable

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:

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:

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:

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

essaies çà

Code:

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

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:

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

20/09/2006, 13h25
Cyborg289

Merci beaucoup pour vos réponses qui m'aident beaucoup.
20/09/2006, 14h28
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.

Citation:

Envoyé par 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.

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:

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

20/09/2006, 15h22
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:

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:

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 ?
20/09/2006, 15h29
ZERS

Aie, y'a un truc que je pige plus...

Dans ton code initiale tu indiques

Code:

SET @logname = @dbname + '_log'

Ceci est ensuite utilisé dans la commande

Code:

dbcc shrinkfile (' + @logname + ', truncateonly)

LOGNAME ici contient le nom LOGIQUE du fichier de log de la base
dans mon code ci-dessus tu as le nom phyisque du fichier log (en supposant bien sur qu'il n'y a qu'un seul fichier physique).

Que veux-tu de plus ?
[EDIT] : a yest j'ai compris ce que tu veux... Je reviens dans 5mns :D

Ca va mieux ?

Code:

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
 
DECLARE @dbname varchar(50)
 
DECLARE @sql nvarchar(4000)
DECLARE @LOGFILENAME sysname
DECLARE @pLOGNAME sysname
 
DECLARE @Para NVARCHAR(500)
SET @Para = N'@LOGNAME varchar (50), @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 + ' SELECT @LOGNAME = LTRIM(RTRIM(name)) from sysfiles '
		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, @LOGNAME = pLOGNAME, @FILENAME = @LOGFILENAME OUTPUT
		select @LOGFILENAME
	END
 
--On initialise @dbname avec l'enregistrement suivant
FETCH alldbname INTO @dbname
END
 
CLOSE alldbname
 
DEALLOCATE alldbname

Merci encore pou ton aide mais en fait on se comprends mal depuis un petit bout de temps ce que je veux est me semble t'il beaucoup plus beaucoup plus simple voici mon code actuel pour repartir sur de bonnes bases :
Code:

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 /*Procedure permettant de tronquer tous les transaction logs de toutes les bases 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 @sql nvarchar(4000) DECLARE @sql2 nvarchar(4000) DECLARE alldbname CURSOR FOR SELECT name from master.dbo.sysdatabases 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 --On récupère le nom du fichier transaction log de la base de donnée set @sql2 = 'SELECT name from '+ @dbname + '.dbo.sysfile where fileid=2' set @logname = ? --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) --On initialise @dbname avec l'enregistrement suivant FETCH alldbname INTO @dbname END CLOSE alldbname DEALLOCATE alldbname
Je voudrais juste savoir comment récupérer la valeur de mon point d'intérrogation qui doit en fait contenir la valeur de ma requête précédente :
Code:

1 2 set @sql2 = 'SELECT name from '+ @dbname + '.dbo.sysfile where fileid=2'
C'était tout :oops:

Désolé de t'avoir fait perdre du temps.

Je t'explique pourquoi : tout simplement parce que je me suis aperçu que le nom du log est pas normalisé (nomdelabase_log) sur nos bases il me faut donc le nom exacte contenu dans la table sysfiles.

Ben si on se comprend :D
Il te suffit de découper en 2 l'ordre @SQL

Il te faut comprendre que de récupérer des valeurs de colonnes dans une variable n'est pas possible en utilisant EXEC(ChaineSQL), d'autant plus si tu rends le nom de tes bases varibales.
Récupérer des valeurs dans des paramètres est possible avec EXEC , à condition que tu exécutes une procédures stockée, te renvoyant une valeur. Mais ce n'était pas le sens de ta question je crois.

Donc il faut systématiquement utiliser la commande sp_executesql, en précisant dans son tableau de variable qu'il y en a une dans laquelle t'aimerais bien avoir une valeur en retour de l'execution de la chaine SQL (mot clé OUTPUT) ;)

Bref

Code:

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
 
 
DECLARE @dbname varchar(50)
 
DECLARE @sql nvarchar(4000)
DECLARE @LOGFILENAME sysname
DECLARE @pLOGNAME sysname
 
DECLARE @Para NVARCHAR(500)
 
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
	-- 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
 
                          --premiere init du tableau de parametre. On veux recuperer la valeur de @LOGNAME donc on met OUTPUT
		SET @Para = N'@LOGNAME varchar (50) OUTPUT'
		set @SQL = 'USE ' + @dbname 
		set @SQL = @SQL + ' SELECT @LOGNAME = LTRIM(RTRIM(name)) from sysfiles '
		set @SQL = @SQL + 'USE master' 
 
		exec sp_executesql @SQL, @PARA, @LOGNAME = @pLOGNAME OUTPUT
		--ICI tu as ton nom de fichier logique log
		select @pLOGNAME
 
                         --deuxieme initialisation du tableau de parametre. On passe la valeur de @LOGNAME mais on ne récupere rien en retour
		SET @Para = N'@LOGNAME varchar (50) '
 
		set @SQL = 'USE ' + @dbname 
		set @sql = @sql + ' dbcc shrinkfile (@logname , truncateonly)'
		set @SQL = @sql + ' USE master'
 
		exec sp_executesql @SQL, @PARA, @LOGNAME = @pLOGNAME
 
	END
 
--On initialise @dbname avec l'enregistrement suivant
FETCH alldbname INTO @dbname
END
 
CLOSE alldbname
 
DEALLOCATE alldbname

21/09/2006, 08h33
Cyborg289

Salut,

Merci beaucoup pour tes explications effectiement j'y vois beaucoup plus clair :D

Vraiment sympa de ta part

Merci beaucoup et à bientôt.
21/09/2006, 08h53
Cyborg289
Décidement je vais jamais m'en sortir.

Il semble que la commande fonctionne correctement. Les 5 premières bases passent et la 5 ème foire parce que son nom est tronqué.
En fait le nom de la base qui ne fonctionne pas est CSD-PKG-TEST.
Et j'obtiens l'erreur suivante à l'exécution :
Code:

1 2 Msg 911, Sev 16: Could not locate entry in sysdatabases for database 'CSD'. No entry found with that name. Make sure that the name is entered correctly. [SQLSTATE 08004]
Je ne comprends pas pourquoi le nom de ma base est tronqué. :oops:
21/09/2006, 13h00
ZERS
Salut,

essaie d'ajouter des [] avant et après chaque utilisation de @dbNAME ou @LOGNAME
Par exemple
Code:

1 2 3 SET @SQL = 'USE [' + @dbName + ']' SET @SQL = 'dbcc shrinkfile [@LOGANME]'
je ne te promets pas que ca marche
21/09/2006, 13h42
Cyborg289

Effectivement ca marche

Rien à dire tu est génial.

Merci beaucoup pour ton aide ;)