Statistiques sur la taille d'un base

[Jcattelain]

Bonjour les experts, avant toutes choses je veux juste preciser que je n'y connais absolument rien en mssql.

Je dois faire dans le cadre de mon projet en entreprise un petit script pour faire certaines statistiques.

Pour le script en lui meme tout va bien, mais par contre je n'ai absolument aucune idee des lignes de commandes a tapper pour pouvoir obtenir ce que je veux des bases.

1 - J'ai besoin d'une ligne de commande pour calculer les mdf,ldf,ndf
J'ai trouve ca pour les mdf,ldf sur developpez.com

Code: Tout sélectionner

use myDB
GO
SELECT  8 * SUM(CONVERT(DEC(15),SIZE)) as Taillemdf
FROM dbo.sysfiles
WHERE (status64 = 0)
GO


en enlevant le WHERE status64=0 il me sort taillemdf 5376 (base cree par defaut lors de l'installation du sql server)

Ca veut dire que le total de mes mdf est egual a 5 megaoctets et des poussieres ?

Est ce que cette commande me listera les mdf 1 par 1 ou va me mettre la taille totale ?

2 - une seconde pour calculer le taux de remplissage de mes datafiles.

3 - avez vous un lien me permettant de comprendre la gestion des fichiers sous mssql ?

Cordialement,

Jonathan

PS:merci de m'expliquer ce que fait le code histoire que je comprenne un peut mieux les mechanismes de mssql

[Maxime]

Bonjour,

Bonjour les experts, avant toutes choses je veux juste préciser que je n'y connais absolument rien en mssql.

Il faut bien commencer à un moment

1 - J'ai besoin d'une ligne de commande pour calculer les mdf,ldf,ndf
J'ai trouve ca pour les mdf,ldf sur developpez.com

Code: Tout sélectionner
use myDB
GO
SELECT 8 * SUM(CONVERT(DEC(15),SIZE)) as Taillemdf
FROM dbo.sysfiles
WHERE (status64 = 0)
GO

en enlevant le WHERE status64=0 il me sort taillemdf 5376 (base cree par defaut lors de l'installation du sql server)
Ca veut dire que le total de mes mdf est egual a 5 megaoctets et des poussieres ?
Est ce que cette commande me listera les mdf 1 par 1 ou va me mettre la taille totale ?

Tout d'abord, le code que tu as trouvé sur developpez.com te renvoie uniquement la taille des master datafiles (.mdf). Ceci est du à la clause WHERE status&64 = 0.
La taille de 5376 que tu obtiens est celle allouée au mdf de la base "myDb" exprimée en Ko.

PS:merci de m'expliquer ce que fait le code histoire que je comprenne un peut mieux les mechanismes de mssql

En gros, chaque base de données SQL Server comporte des tables/vues systèmes qui répertorient toutes les informations les concernant.
Dans ta requête, tu interroges la table dbo.sysfiles qui contient uniquement des informations sur les fichiers de la base de données "myDB".

Si tu souhaites obtenir des informations sur les fichiers d'autres bases, tu es obligé de modifier à chaque fois le contexte à l'aide du "USE <myDB> ".





Voici un code qui te permet de récupérer les stats complètes sur les fichiers de toutes les bases de données présentes sur l'instance.

Code: Tout sélectionner

--//////////// Création d'une table temporaire pour étendre sysaltfiles \\\\\\\\\\\\--
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name like '%tmpsysaltfiles%')
   DROP TABLE #tmpsysaltfiles
   
CREATE TABLE #tmpsysaltfiles (
   dbid int, fileid smallint, groupid smallint, size int NOT NULL, maxsize int NOT NULL, growth int NOT NULL,
   status int, perf int, name sysname NOT NULL, filename nvarchar(520) NOT NULL, used_pages int, percent_used smallint
)
GO
sp_MSforeachdb 'USE [?];
INSERT INTO #tmpsysaltfiles
SELECT   DB_ID(), fileid,groupid,size,maxsize,growth,status,perf,name,filename,
      FILEPROPERTY(name,''SpaceUsed'') [used_pages],FILEPROPERTY(name,''SpaceUsed'')*100/size [percent_used]
FROM   dbo.sysfiles'


--//////////// Extraction de stats sur les fichiers \\\\\\\\\\\\--
SELECT   DB_NAME(dbid) AS [database_name],
      CASE status&0x40 WHEN 0 THEN 'Datafile' ELSE 'Transaction Log' END AS [file_type],
      name AS [logical_filename],
      CONVERT(BIGINT,size)*8/1024 AS [size_in_mb],
      CONVERT(BIGINT,used_pages)*8/1024 AS [used_size_in_mb],
      CONVERT(BIGINT,maxsize)*8/1024 AS [maxsize_in_mb],
      percent_used,
      CONVERT(VARCHAR,growth*8/1024) + ' ' + CASE growth&100000 WHEN 0 THEN '%' ELSE 'MB' END [file_growth],
      filename [physical_filename]      
FROM   #tmpsysaltfiles
ORDER BY 1

Le résultat obtenu par cette requête est en pièce jointe.

3 - avez vous un lien me permettant de comprendre la gestion des fichiers sous mssql ?

Voici un lien vers la MSDN Microsoft qui explique rapidement le fonctionnement des fichiers sous SQL Server: http://msdn.microsoft.com/fr-fr/library/ms179316.aspx

[Jcattelain]

Merci beaucoup pour ta reactivite et ton code, ca me permets d'adapter ça a mes besoins.

Code: Tout sélectionner

sp_MSforeachdb 'USE [?];
SELECT   DB_ID(), fileid,groupid,size,maxsize,growth,status,perf,name,filename,
      FILEPROPERTY(name,''SpaceUsed'') [used_pages],FILEPROPERTY(name,''SpaceUsed'')*100/size [percent_used]
FROM   dbo.sysfiles'


comment serait-il possible de mettre tous les resultats dans un seul tableau ? car avec ca il me le decoupe en plusieurs parties et pour mon script il me faut juste un select qui retourne

filename,used_space,free_space,total

ps: j'ai essaye de bricoler ta commande mais j'ai vraiment du mal surtout lorsque les commandes de base oracle du type DESC ne passent pas ^^

Mes bases oracle et MS vont renseigner la meme table ( voici a quoi ressemble la sortie oracle )

Code: Tout sélectionner

NAME                                 USED       FREE      TOTAL
------------------------------ ---------- ---------- ----------
UNDOTBS1                               31        419        450
SYSAUX                                275         35        310
USERS                                   1         25         26
SYSTEM                                536         14        550
EXAMPLE                                77         23        100

Cordialement, jonathan.

[Maxime]

Merci beaucoup pour ta reactivite et ton code, ca me permets d'adapter ça a mes besoins.

Code: Tout sélectionner

sp_MSforeachdb 'USE [?];
SELECT   DB_ID(), fileid,groupid,size,maxsize,growth,status,perf,name,filename,
      FILEPROPERTY(name,''SpaceUsed'') [used_pages],FILEPROPERTY(name,''SpaceUsed'')*100/size [percent_used]
FROM   dbo.sysfiles'


comment serait-il possible de mettre tous les resultats dans un seul tableau ? car avec ca il me le decoupe en plusieurs parties et pour mon script il me faut juste un select qui retourne

filename,used_space,free_space,total

ps: j'ai essaye de bricoler ta commande mais j'ai vraiment du mal surtout lorsque les commandes de base oracle du type DESC ne passent pas ^^

Mes bases oracle et MS vont renseigner la meme table ( voici a quoi ressemble la sortie oracle )

Code: Tout sélectionner

NAME                                 USED       FREE      TOTAL
------------------------------ ---------- ---------- ----------
UNDOTBS1                               31        419        450
SYSAUX                                275         35        310
USERS                                   1         25         26
SYSTEM                                536         14        550
EXAMPLE                                77         23        100

Cordialement, jonathan.

Pour obtenir le même résultat que celui renvoyé par Oracle, il faut que tu passes par une table temporaire.
Voici le code TSQL qui te donne exactement le même output:

Code: Tout sélectionner

    --//////////// Création d'une table temporaire pour étendre sysaltfiles \\\\\\\\\\\\--
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name like '%tmpsysaltfiles%')
       DROP TABLE #tmpsysaltfiles
       
    CREATE TABLE #tmpsysaltfiles (
       dbid int, fileid smallint, groupid smallint, size int NOT NULL, maxsize int NOT NULL, growth int NOT NULL,
       status int, perf int, name sysname NOT NULL, filename nvarchar(520) NOT NULL, used_pages int, percent_used smallint
    )
    GO
    sp_MSforeachdb 'USE [?];
    INSERT INTO #tmpsysaltfiles
    SELECT   DB_ID(), fileid,groupid,size,maxsize,growth,status,perf,name,filename,
          FILEPROPERTY(name,''SpaceUsed'') [used_pages],FILEPROPERTY(name,''SpaceUsed'')*100/size [percent_used]
    FROM   dbo.sysfiles'


    --//////////// Extraction de stats sur les fichiers \\\\\\\\\\\\--
    SELECT   name AS [NAME],
         CONVERT(BIGINT,used_pages)*8/1024 AS [USED],
         (CONVERT(BIGINT,size)-CONVERT(BIGINT,used_pages))*8/1024 [FREE],
         CONVERT(BIGINT,size)*8/1024 AS [TOTAL]
    FROM   #tmpsysaltfiles
    ORDER BY 1

Cdt,
Maxime

[Jcattelain]

C'est dommage qu'on ne puisse pas faire ca sans creer de table. Merci beaucoup pour toutes ces info utiles

[Jcattelain]

Je donne le resultat de tout ca pour ceux qui ont besoin de ces bouts de code:

============================== MSSQL =======================================
Voici le code:

Code: Tout sélectionner

--//////////// Création d'une table temporaire pour étendre sysaltfiles \\\\\\\\\\\\--
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name like '%tmpsysaltfiles%')
       DROP TABLE #tmpsysaltfiles
       
    CREATE TABLE #tmpsysaltfiles (
       dbid int, fileid smallint, groupid smallint, size int NOT NULL, maxsize int NOT NULL,

growth int NOT NULL,
       status int, perf int, name sysname NOT NULL, filename nvarchar(520) NOT NULL, used_pages

int, percent_used smallint
    )
    GO
    sp_MSforeachdb 'USE [?];
    INSERT INTO #tmpsysaltfiles
    SELECT   DB_ID(), fileid,groupid,size,maxsize,growth,status,perf,name,filename,
          FILEPROPERTY(name,''SpaceUsed'') [used_pages],FILEPROPERTY(name,''SpaceUsed'')*100/size

[percent_used]
    FROM   dbo.sysfiles'


    --//////////// Extraction de stats sur les fichiers \\\\\\\\\\\\--
    SELECT   name AS [NAME],
    CONVERT(BIGINT,size)*8/1024 AS [TOTAL],
        (CONVERT(BIGINT,size)-CONVERT(BIGINT,used_pages))*8/1024 [FREE],
    CONVERT(BIGINT,used_pages)*8/1024 AS [USED],
         
       filename AS FILE_NAME
    FROM   #tmpsysaltfiles
    ORDER BY 1


ce qui donne:


============================== ORACLE ======================================

Voici le code:

Code: Tout sélectionner

SELECT A.tablespace_Name NAME, A.Alloue TOTAL,B.Libre FREE, C.Occupe USED, D.file_name
    FROM (select tablespace_name, ROUND(sum(bytes)/1024/1024) AS ALLOUE from dba_data_files group by tablespace_name) a,
         (select tablespace_name, ROUND(Sum(bytes)/1024/1024) AS LIBRE from dba_free_space group by tablespace_name) b,
         (select tablespace_name, ROUND(Sum(bytes)/1024/1024) AS OCCUPE from dba_segments group by tablespace_name) c,
         (select tablespace_name,file_name from dba_data_files) d
   WHERE C.tablespace_Name = A.tablespace_Name
     AND B.Tablespace_Name = C.Tablespace_Name
       AND A.tablespace_name = D.tablespace_name;

Ce qui donne:

Code: Tout sélectionner

NAME                      TOTAL       FREE       USED FILE_NAME
-------------------- ---------- ---------- ---------- ------------------------------------------------------------
UNDOTBS1                    450        422         28 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSAUX                      310         35        275 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
USERS                        26         25          1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
SYSTEM                      550         14        536 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE                     100         23         77 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF