Discussion :

[kacedda]

Bonjour,

Je souhaiterais connaitre le taux de remplissage de mon fichier de log.
J'arrive a recuperer les parametre courant comme ca taille courante et sa taille max, mais je n'arrive pas a savoir son taux de remplissage.

Merci

[mikedavem]

Bonjour,

DBCC SQLPERF(LOGPSPACE)

++

[kacedda]

Effectivement, merci ca marche bien!

Mais question subsidiaire:
Sur un pc j'ai cree une base, j'ai un soft qui logge sans arret, j'ai volontairement limiter(restricted) le fichier de log a 10MB et je m'apercois que le fichier se nettoie tout seul.
Je n'ai pas besoin de faire de backup, avec DBCC SQLPERF(LOGPSPACE) je vois le "Log Space Used" tourné il passe de valeur basses a hautes et repasse a des valeur basse:
t0 20%
t1 40%
t2 80%
t3 10%
...

[SQLpro]

Simplement parce que le mode de journalisation (recovery mode) doit être SIMPLE.

A +

[mikedavem]

Quel est le mode recovery de votre base ?

Si le mode est SIMPLE ce comportement est tout à fait normal car le fichier de log est vidé automatiquement après chaque CHECKPOINT

PS : Dsl téléscopage avec SQLPro

++

[kacedda]

Bonsoir, merci je viens de regarder et non je suis bien en mode recovery Full.
Je comprend pas...

[elsuket]

Bonjour,

Alors c'est que des sauvegardes sont réalisées sur cette base de données de façon fréquente.
Si vous êtes sous SQL Server 2005, vous pouvez exécuter la requête suivante :

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
SELECT F.physical_drive,
		F.physical_name,
		F.logical_name,
		F.file_size,
		S.name,
		S.user_name,
		S.database_creation_date,
		S.backup_start_date,
		S.backup_finish_date,
		CASE S.type
			WHEN 'D' THEN 'Base de données - Complet'
			WHEN 'I' THEN 'Base de données - Différentiel'
			WHEN 'L' THEN 'Journal de transactions'
			WHEN 'F' THEN 'Fichier ou groupe de fichiers'
			WHEN 'G' THEN 'Fichier - Différentiel'
			WHEN 'P' THEN 'Partiel'
			WHEN 'Q' THEN 'Partiel - Différentiel'
		END,
		S.database_name,
		S.server_name,
		S.machine_name,
		S.recovery_model
FROM msdb.dbo.backupfile F
JOIN msdb.dbo.backupset S ON F.backup_set_id = S.backup_set_id
WHERE CAST(FLOOR(CAST(S.backup_start_date AS FLOAT) AS DATETIME) = CAST(FLOOR(CAST(GETDATE() AS FLOAT) AS DATETIME)
ORDER BY S.backup_start_date DESC

qui vous donnera la liste de toutes les sauvegardes effectuées aujourd'hui.

@++

[kacedda]

Merci pour la requête, mais je ne vois pas de sauvegarde a son execution.
Elle marche bien, faut encore que je la comprenne, il y avait juste un manque au niveau des parenthèses.

Je comprend toujours pas...
Je fais faire plus d'essais.

[SQLpro]

Si vous n'êtes pas passé par l'agent SQL pour faire vos sauvegardes, ceci est normal. Seules les BACKUP opérés à l'aide de SQL Agent dont tracées.

A +

[kacedda]

Bonjour,

Désolé de revenir à la charge mais...
J'ai pu reproduire mon phénomène dans une table de test, voici mes requêtes de création, je les ai extrait a partir de SQL Management.

Pour précision: je réalise mes tests sur WinXP Pro avec SQL Server 2005 sans service pack (Microsoft SQL Server Management Studio 9.00.1399.00 - Operating System 5.1.2600).

Création de la BD:

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
61
62
63
64
65
66
67
68
 
USE [master]
GO
/****** Object:  Database [DataBaseTest]    Script Date: 01/23/2009 17:22:07 ******/
CREATE DATABASE [DataBaseTest] ON  PRIMARY 
( NAME = N'DataBaseTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DataBaseTest.mdf' , SIZE = 102400KB , MAXSIZE = 102400KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DataBaseTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DataBaseTest_log.ldf' , SIZE = 10240KB , MAXSIZE = 10240KB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DataBaseTest', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DataBaseTest].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DataBaseTest] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DataBaseTest] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DataBaseTest] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DataBaseTest] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DataBaseTest] SET ARITHABORT OFF 
GO
ALTER DATABASE [DataBaseTest] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DataBaseTest] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DataBaseTest] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DataBaseTest] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DataBaseTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DataBaseTest] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DataBaseTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DataBaseTest] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DataBaseTest] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DataBaseTest] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DataBaseTest] SET  ENABLE_BROKER 
GO
ALTER DATABASE [DataBaseTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DataBaseTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DataBaseTest] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [DataBaseTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [DataBaseTest] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DataBaseTest] SET  READ_WRITE 
GO
ALTER DATABASE [DataBaseTest] SET RECOVERY FULL 
GO
ALTER DATABASE [DataBaseTest] SET  MULTI_USER 
GO
ALTER DATABASE [DataBaseTest] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DataBaseTest] SET DB_CHAINING OFF

On peut voir que le mode recovery est FULL

Code :

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

ALTER DATABASE [DataBaseTest] SET RECOVERY FULL

Et que j'ai bien limité la taille du log a 10MB.

Création de ma table:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
USE [DataBaseTest]
GO
/****** Object:  Table [dbo].[TableTest]    Script Date: 01/23/2009 17:24:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableTest](
	[RowID] [bigint] IDENTITY(1,1) NOT NULL,
	[Value] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Voici mon code d'insertion pour tester(c'est petre pas ce qui se fait de mieux):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
DECLARE @value as bigint
SET @value=123456789
 
WHILE 1=1
	BEGIN
		INSERT INTO [DataBaseTest].[dbo].[TableTest]
			([Value])
		VALUES
			(cast(@value as nchar(10)))
	END

Et voici mon test:
Après avoir créer la BD et la table, j'exécute mon code de test en continue boucle infini.
En parallèle dans un autre onglet:

Code :

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

DBCC SQLPERF(LOGSPACE)

J'exécute donc ceci régulièrement et je me retrouve avec:
24%
53%
89%
34%
67%
11%
...

Donc je re-boucle, et j'avoue que je ne comprend pas. Soit il y a quelque chose que j'interprète mal ou que je comprend pas soit je suis fou!

[mikedavem]

Effectivement.

Mais faites le test suivant. Je pense que vous allez comprendre :

- Interdiser le grossissement du fichier journal (taille fixe)

Laissez votre script tourner comme vous l'avez fait.. Donc l'occupation du journal grossit et se réduit sans incidence.

Maintenant en laissant votre script tourner, lancez une sauvegarde FULL de votre base en même temps. Regardez maintenant ce qui se passe ....

++

[kacedda]

Oui, effectivement, je me prend un "datalog is full" mais j'avoue ne pas comprendre.
Est-ce que ça veut dire que si j'atteins la taille limite d'un coup ca bloque. Car ça bloque aussi les requêtes, jusqu'à ce que je joue un BACKUP LOG.
Toujours est-il que je me demande aussi si mon test est pertinent, est-ce que SQL réagit pareil avec un log de 10MB ou de 10Giga?

En tout cas merci pour le complément du test, ça m'est bien utile!

[mikedavem]

Bonjour,

Oui, effectivement, je me prend un "datalog is full" mais j'avoue ne pas comprendre.

Cela peut paraître logique en fait.. Vous activez le mode FULL RECOVERY sur votre base et vous lancez votre script... Pour pouvoir restaurer votre backup de transactions il vous faut comme point de départ une sauvegarde FULL . Sans cette première sauvegarde vos sauvegardes du fichier log ne servira pas à grand chose et vous ne pourrez pas faire de sauvegarde de votre fichier log de toute façon ...

Donc dans votre cas c'est normal. Le serveur sql reste en mode "AUTO TRUNCATE", c'est à dire qu'il tronquera votre journal tant que cette 1ère sauvegarde ne sera pas faite.. Vous avez observé ce phénomène avec un fichier de log qui se remplit et se réduit automatiquement après chaque CHECKPOINT automatique.

Est-ce que ça veut dire que si j'atteins la taille limite d'un coup ca bloque. Car ça bloque aussi les requêtes, jusqu'à ce que je joue un BACKUP LOG.

Oui si votre fichier de transactions est plein et que vous n'activez pas la croissance automatique, vos requêtes ne pourront effectivement plus se faire, tant que vous n'aurez pas fait de place dans votre fichier de transactions à l'aide d'un BACKUP LOG justement. Bien sûr en production vous n'interdirez pas cette croissance automatique pour des raisons évidentes de sécurité ...

Toujours est-il que je me demande aussi si mon test est pertinent, est-ce que SQL réagit pareil avec un log de 10MB ou de 10Giga?

Non votre serveur ne réagit pas de la même facon, votre ficher de transactions se remplira moins vite du fait de sa taille mais au bout du compte s'il se remplit à 100% le résultat sera bien le même. Après le but est de pouvoir dimensionner votre fichier de transactions pour que celui puisse acceuillir vos transaction sans avoir à grossir.. (la fameuse option autogrowth) mais cela est une autre histoire ...

++

[kacedda]

C'est cool! merci je comprend beaucoup mieux maintenant!
Par contre est-ce qu'il y a un endroit où MS en parle dans sa doc SQL?
En tout cas les infos m'ont bien aidées!