Discussion :

[Invité]

Hello.
J'ai un gros job journalier qui va chercher diverses informations sur la plupart de mes serveurs pour en connaitre les évolutions.
Pour faire bref, ça fonctionne à coup de linked Servers et d'openquery et les informations sont historisées dans diverses tables. Au final, je produis un courriel en html qui me fait gagner bien du temps.
Cependant, je n'arrive pas à obtenir la taille « disponible » des fichiers de données.
En essayant avec ceci, j'obtiens juste l'information sur la bd master

Code :

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

1
2
select * from openquery ("machine56465"
,'Select DB_NAME() DBName, Name FileName, fileproperty(Name,''SpaceUsed'') SpaceUsed from master.sys.sysfiles' )

Et bien sûr, le foreachdb ne fait pas bon ménage avec le openquery

Code :

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

1
2
3
4
SELECT * FROM OPENQUERY( "test89987",'sp_msforeachdb ''select  quotename(?)''' )
 
Msg 208, Level 16, State 1, Procedure sp_MSforeach_worker, Line 102
Invalid object name '#qtemp'.

Si vous avez des pistes, je suis preneur.
Merci bien.

[SQLpro]

Le mieux serait de remonter cela avec coquillage puissant.

En 2014 une vue permet d'avoir des infos détaillées des disques et des fichiers du système.

A +

[dbaffaleuf]

Je partirais sur un job SSIS pour faire de la collecte multi instances.

[mikedavem]

... ou en PowerShell également pour la collecte multi instances

++

[SQLpro]

... ou en PowerShell également pour la collecte multi instances

C'est bien ce que je disais.... "Le mieux serait de remonter cela avec coquillage puissant.

A +

[mikedavem]

C'est bien ce que je disais.... "Le mieux serait de remonter cela avec coquillage puissant.

Yep et pour ne pas te paraphraser voici un petit bout de code (à retravailler bien sûr ... j'avais le temps de m'amuser un peu. Le script se base sur un fichier texte avec une liste de serveur mais on peut tout à fait remplacer cette source par autre chose.

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
Param 
(
    [ValidateScript({ Test-Path $_ })]
    [string] $path = $(throw "Server file path")
)
 
 
function GetDatabaseUsedSpaceByServer
{
    Param (
        [string] $Server = $(throw "SQL Server Name")
    )
 
    try
    {
        $serverConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server);
        $serverConn.Connect();
    }
    catch 
    {  
        Write-Host "The server $serverdoes not exist or is not reachable." -ForegroundColor Red;
        Exit;
    }
 
    $objServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $Server;
 
    $t = @();
 
    foreach ($database in $objServer.databases | Where-Object { $_.IsAccessible -eq $True })
    {
        foreach ($group in $database.Filegroups)
        {
 
            foreach ($file in $group.Files) 
            {
                $properties = @{"Server Name"=$Server; 
                                "Database Name"=$database.Name;
                                "Physical Name"=$file.FileName
                                "Space Used (KB)"=$file.UsedSpace
                               };
 
                $NewObject = New-Object PSObject -Property $Properties;
                $t += $NewObject;
            }
        }
 
    }
 
    $t | ft -AutoSize;
}
 
# Clear host
Clear-Host;
 
# Load assemblies
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null;
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null;
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
 
# Retrieve servers list into the text file
$content = Get-Content $path;
 
# Show used space information for each server and databases
foreach ($row_content in $content)
{
    $server_name = ($row_content.split(";"))[0];
    GetDatabaseUsedSpaceByServer -Server $server_name;
}

++

[SQLpro]

Oui, mais alors là ça va pas.... Tu pourrais forcer un peu le trait et nous faire une gentille fonction SQL CLR en mode UNSAFE pour récupérer directement ces informations en SQL !

A +

[mikedavem]

Je laisse çà à Elsuket vu qu'il adore le sqlclr en mode unsafe .. voir le mode dangerous

++

[elsuket]

Bonjour,

Le mode UNSAFE n'est pas nécessaire pour mesurer la taille des fichiers : le niveau EXTERNAL_ACCESS avec une signature ou un nommage fort sont des manières propres de faire. Par ailleurs je ne suis pas certain que l'on puisse auditer l'espace libre à l'intérieur du fichier.

Le mode UNSAFE n'est à utiliser que pour exécuter du code managé. Donc, par exemple, exécuter du code C managé à partir de la CLR, c'est un peu plus que téméraire : c'est être délibérément effronté

Pour ma part, je fais ça très simplement : je stocke ça dans une table, et je garde les données sur 30 jours roulants. Tous les jours, je viens chercher les données d'il y a 29 jours (juste au cas où le job planterait (on le sait pas une alerte SQL Agent)) avec OPENQUERY justement, et je mets ça dans une table sur une petite instance SQL Server dédiée à la DBA.
Cela présente l'avantage, mais ce n'est que mon avis, d'avoir un peu d'historique sur le serveur (c'est bien quand on en gère un paquet) sans avoir à se connecter au serveur de DBA, qui sert plus à faire de la prospective.

Le package SSIS est là aussi une bonne option. Le PowerShell est pas mal, mais malheureusement un peu lent. En revanche, cela permet, quand on est consultant comme vous, de n'avoir presque pas à toucher aux bases de données du client.

@++

[mikedavem]

Comme on en a discuté dans ce cas précis je pense que PowerShell sera beaucoup plus rapide que les autres méthodes ... ca serait intéressant de tester :-)

++