[TRANSAC-SQL] Problème dbcc shrinkfile

**Cyborg289** · 20/09/2006, 11h47

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** · 20/09/2006, 12h26

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** · 20/09/2006, 12h33

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** · 20/09/2006, 13h25

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

**Cyborg289** · 20/09/2006, 14h28

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** · 20/09/2006, 15h15

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 :

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

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 ?

**ZERS** · 20/09/2006, 15h29

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

Dans ton code initiale tu indiques

Code :

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

SET @logname = @dbname + '_log'

Ceci est ensuite utilisé dans la commande

Code :

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

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

**ZERS** · 20/09/2006, 15h39

Ca va mieux ?

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
 
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

**Cyborg289** · 20/09/2006, 15h50

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 :

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
 
/*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 :

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

1
2
 
set @sql2 = 'SELECT name from '+ @dbname + '.dbo.sysfile where fileid=2'

C'était tout

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.

**ZERS** · 20/09/2006, 16h54

Ben si on se comprend

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 :

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
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

**Cyborg289** · 21/09/2006, 08h33

Salut,

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

Vraiment sympa de ta part

Merci beaucoup et à bientôt.

**Cyborg289** · 21/09/2006, 08h53

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 :

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

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

**ZERS** · 21/09/2006, 13h00

Salut,

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

Code :

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

1
2
3
 
SET @SQL = 'USE [' + @dbName + ']'
SET @SQL = 'dbcc shrinkfile [@LOGANME]'

je ne te promets pas que ca marche

**Cyborg289** · 21/09/2006, 13h42

Effectivement ca marche

Rien à dire tu est génial.

Merci beaucoup pour ton aide

[TRANSAC-SQL] Problème dbcc shrinkfile

MS SQL Server

Discussions similaires

Partager

Partager