Shrink des logs d'un BDD SQL Server

Voici un script pour réduite la taille du journal des transactions

DECLARE @size INT;
SET @size = 10;   --- Taille du log à la fin en MegaBytes

DECLARE @database VARCHAR(200);
SET @database = DB_NAME();  --- Obtient le nom de la base en cours

--- le shrink des logs
DECLARE @sqlShrink VARCHAR(MAX);
SET @sqlShrink = '
DECLARE @logfilename NVARCHAR(200);

USE [' + @database + '];
ALTER DATABASE ['+ @database +'] SET RECOVERY SIMPLE;

DECLARE crsor CURSOR FOR
SELECT [name] FROM [sys].[database_files] 
WHERE [type] = 1;
OPEN crsor 
  FETCH crsor  INTO @logfilename
  WHILE @@FETCH_STATUS = 0
  BEGIN
    DBCC SHRINKFILE (@logfilename, '+CONVERT(VARCHAR(20), @size)+');
    FETCH crsor INTO @logfilename
  END
CLOSE crsor
DEALLOCATE crsor

ALTER DATABASE [' + @database + '] SET RECOVERY FULL;


USE master;
';
EXEC (@sqlShrink);


Dans le principe :
1) On passe la base en cours dans le mode Recovery Simple
2) Pour chaque fichier de log (LDF) on fait un DBCC SHRINKFILE
3) On remet la base dans le mode Recovery Full

Il doit surement pouvoir être amélioré mais bon ça fait le job !

Merci aux auteurs initaux du script ;-)

Web services avec ServiceStack

Ci dessous la procédure de mise en oeuvre (minimale) de web services avec la librairie ServiceStack.

1) Télécharger les librairies qui vont bien : un NuGet en cherchant : "ServiceStack.ServiceInterface".

2) Dans un namespace de votre lib ou du site Web : Fichier AppHost.cs
  Classe qui dérive de AppHostBase et qui permet l'initialisation des web Services
  Le constructeur à surcharger fait tout le boulot en une ligne !
      public AppHost()
      : base("Ma librairie HttpListener", typeof(AppHost).Assembly)
    {
    }

3) Dans AppHost Toujours : override de la méthode Configure pour définir le comportement des Web services : Ajout de chaînes de connexions aux bases, entête http, compression, ...

4) Les web services : Je fais toujours un dossier par Web Service avec 3 classes : La classe Request, la classe Response, la classe web service

 5) La request : Classe qui gère les paramètres d'appel à un ou plusieurs Web services
  doit contenir les attributs suivants :

•    [Api("Nom de l'api")]  pour indiquer dans quel groupe d'api se trouve le web service
•    [Route("Url du Web service avec paramètres obligatoires", vers = "GET, POST, PUT, ...")] pour indiquer l'url du web service et les verbes auquel il doivent répondre :

Les 2 attributs sont dans le name space : ServiceStack.ServiceHost
L'attribut Route peut être mis autant de fois que nécessaire (Permet d'avoir différentes URL en fonction des besoins, ou rendre des paramètres obligatoires en fonction du verbe utilisé)
Les paramètres obligatoires de l'URL doivent être entre accolades exemple :
[Route("/commandeGet/{ApiKey}/", Verbs = "GET")] ici ApiKey doit correspondre à une propriété de la classe request.
Il faudra programmer une méthode pour chaque verbe Http déclaré ici dans la classe service.

De plus il est pas mal (mais optionnel) d'indiquer ce que retourne le Web Service en ajoutant l'interface IReturn<>
  public class MaClasseRequest : IReturn<MaClasseResponse>
 
6) La response : Classe qui contient les données de la réponse. Rien à signaler de plus sur cette classe !

7) Le service : Classe qui contient les méthodes de traitement des Web Services :
Exemple : public object Get(MaClasseRequest request)
Le nom de la méthode est le nom du verbe HTTP utilisé (Attention à la casse : dans le Request c'est en majuscule, ici c'est en Camel Upper Name)
Le paramètre de la méthode est le nom de la classe Request définie
Le type de retour est object car on retourne soit une Response soit une HttpErrror !

La classe service doit dériver de la classe Service  de ServiceStack.ServiceInterface

c# Service Stack et transactions

Je me suis fait avoir récemment donc pour ne pas oublier : Voici comment utiliser les transactions avec la librairie ServiceStack.


Une classe Tache à une méthode InsertToDb qui à pour but d’insérer l'objet et ses fils des Actions dans une base de données.

  private void InsertToDb(IDbConnection cnx)
    {
      this.Id = Convert.ToInt32(cnx.Insert<Tache>(this, selectIdentity: true));

      // insertion des actions
      foreach (Action act in this.Actions)
      {
        act.TacheId = this.Id;
        act.ToDB(cnx);
      }
    }

Pour rendre le code transactionnel il faut lancer une transaction : le réflexe est la méthode BeginTransaction() de la classe IDbConnection.
Et bien avec ServiceStack ça marche pas avec le  select identity, il semble que la transaction soit mal initialisée. Pour y arriver facilement il faut utiliser la méthodes d'extension : OpenTransaction() Qui fait la même chose sans générer d'erreur !

     using (FConnexion cnn = new FConnexion())
      {
        var tran = cnn.Db.OpenTransaction();
        try
        {
          // mettre en base la tâche
          t.ToDB(cnn.Db);
          tran.Commit();
        }
        catch (Exception ex)
        {
          tran.Rollback();
          throw ex;
        }
      }

Ne pas oublier le using qui va bien :
using ServiceStack.OrmLite;

La classe FConnection gère une propriété IDbConnection Db et se charge d'ouvrir la connexion à la base.
using System;
using System.Data;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.SqlServer;

  /// <summary>
  /// Class base pour gestion connexion.
  /// </summary>
  public class FConnexion : IDisposable
  {
    /// <summary>
    /// Initialise une nouvelle instance de la classe <see cref="FConnexion" />.
    /// </summary>
    public FConnexion()
    {
      var dbfactory = new OrmLiteConnectionFactory(System.Configuration.ConfigurationManager.ConnectionStrings["MaBase"].ToString(), SqlServerOrmLiteDialectProvider.Instance);
      this.Db = dbfactory.Open();
      this.Db.Open();
    }

    /// <summary>
    /// Obtient ou définit La connexion
    /// </summary>
    public IDbConnection Db { get; private set; }

    /// <summary>
    /// Libère les ressources de l'objet de connexion.
    /// </summary>
    public void Dispose()
    {
      this.Db.Dispose();
    }
  }

Enjoy !

SQL : Premier jour du mois et dernier jour du mois

Voici ce que j'utilise pour trouver le premier jour du mois d'une date donnée :

DECLARE @dt DATE = '2017-02-17'

SELECT  @dt, DATEADD(DAY, 1 - DATEPART(DAY, @dt), @dt)
Ou
SELECT  @dt, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0)


Ce qui donne dans les deux cas :
17/02/2017     01/02/2017

Dans le premier exemple : on retire le nombre de jour moins 1 à la date en cours.
Dans le second : on ajoute à la date "0" la différence de mois entre "0" et la date fournie



Voici ce que j'utilise pour trouver le dernier jour du mois d'une date donnée :

DECLARE @dt DATE = '2017-02-17'
SELECT  @dt, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt) + 1, 0))


Ce qui donne  :
17/02/2017     28/02/2017

Explication : on ajoute un mois au premier jour du mois et on retire un jour.


MAJ 05/2021 : Sinon pour de le dernier jour du mois, il existe a maintenant (depuis SQL Server 2014!) la fonction EOMONTH().

On lui donne un jour dans le mois, elle renvoie le dernier jour du mois, simple, Merci SQL Server ! 

Enjoy !

SQL : Savoir si on peut supprimer une ligne

Avant de savoir si l'on peut supprimer une ligne dans une base de données, il est bon de se poser la question : Est-elle référencée dans d'autres tables cette ligne ?

Pour se faire il suffit de suivre à l'envers les contraintes de foreign key de la base de données

voici une procédure utile :

CREATE PROCEDURE dbo.can_delete
  @table VARCHAR(100), @Id INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Command VARCHAR(MAX) 

  SELECT @Command = ISNULL(@Command + ' UNION ALL ', '') + ' SELECT ''' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name  +
    ''' AS [table] WHERE EXISTS(SELECT * FROM ' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name
    + ' WHERE ' + col.name + ' = ' + CAST(@Id AS VARCHAR) + ')' 
  FROM sys.foreign_key_columns fkc
  INNER JOIN sys.tables tbl ON fkc.parent_object_id = tbl.object_id
  INNER JOIN sys.columns col ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
  WHERE OBJECT_NAME(fkc.referenced_object_id) = @table

  EXECUTE (@Command);
END


A Noter dans cette procédure :
On créer une variable @commande qui va contenir une requête SQL construite dynamiquement et qui renvéra le résultat. Noter que le paramètre @command de la commande EXECUTE est entre parenthèses.

La requête fait appel à la table système de SQL SERVER ; sys.foreign_key_column qui maintient les Foreign Keys de la base de données

ATTENTION : Cette procédure ne fonctionne que si la clé primaire de la table concernée, n'est formé que d'une seule colonne (en général un entier auto incrémenté)

ServiceStack DataAnotation

ServiceStack est une librairie fort utile que je ne presente pas ici...
Petit mémo de la partie DataAnotation qui permet de faire un mapping Relationnel / Objet très simplement.


L'attribut :  [Alias("")] : Permet de mapper une table (s'il est mis sur une classe) ou une colonne s'il est mis sur une propriété.
La propriété doit toujours être de la forme : public [type] [propriété] { get; set;}
Attention aux types mappés : n'utiliser que les types valeur de base (int, double, decimal (pour les money  Sql Server), string, bool).
Vous pouvez utilisez les types nullables (int?, double?, decimal?, string, bool?)


L'attribut : [Ignore] : Permet d'ajouter des propriétés à l'objet sans les mapper à une colonne.
Exemple pour les enum je câble souvent une propriété :

public enum ValeursPossible
{
   val1 = 1,
   val2 = 2,
  ....
}

[Alias("maColonne")]
public int MonEnumInt {get; set;}

[Ignore]
public ValeursPossible MonEnum
{
   get
   {
      return (ValeurPossible)this.MonEnumInt;
   }
   set
  {
     this.MonEnumInt = (int)value;
  }
}

L'attribut : [Autoincr] est très important pour la propriété qui mappe une colonne auto-incrémentée de Sql Server : A ne pas oublier !


Enfin : Très utile !!
L'attribut : [Schema("xxx")] permet de mapper des tables qui ne sont pas dans le schéma par défaut (DBO pour Sql Server).

SQL Server : Indexer le colonnes de foreign key systématiquement

Une pratique qui permet d'obtenir un bon premier résultat lors de l’indexation d'une base de données SQL Server est de créer les indexes correspondants aux colonnes de clés étrangères.
En effet lorsque l'on fait des requêtes avec des jointures, d'un côté, la colonne de clé primaire auquel on accède est indexée par l'index de clé primaire, mais de l'autre, la colonne de clé étrangère elle, si elle n'est pas indexée, SQL Server doit parcourir l'ensemble des données.

Attention cette méthode est une première approche. Elle ne doit pas empêcher l'analyse des requêtes et structures des tables pour trouver les meilleurs index en fonction des contextes connus.

Voici ma requête qui me permet de trouver et créer les index absents lorsque la structure de la base a changé :

SELECT t.*
, 'IF (NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + t.parent_schema_name + '].[' + t.parent_table_name + ']'') AND name = N'''+ t.index_name + '''))
BEGIN
   CREATE NONCLUSTERED INDEX [' + t.index_name  + '] ON [' + t.parent_schema_name + '].[' + t.parent_table_name + '] 
(
[' + t.parent_column_name + '] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
' AS requete
FROM (
        SELECT fk.Name AS foreign_key_name, cfk.constraint_column_id,  pos.name AS parent_schema_name, po.name AS parent_table_name, cpo.name AS parent_column_name
         , oc.name AS referenced_table_name, cpc.name AS referenced_column_name
         , 'idx_' + LOWER(pos.name) + '_' + LOWER(po.name) + '_colonne_fk_' + LOWER(cpo.name) AS index_name
        FROM sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns cfk ON fk.object_id = cfk.constraint_object_id
        INNER JOIN sys.objects po ON cfk.parent_object_id = po.object_id
        INNER JOIN sys.columns cpo ON cpo.object_id = po.object_id AND cpo.column_id = cfk.parent_column_id
        INNER JOIN sys.schemas pos ON po.schema_id = pos.schema_id
        INNER JOIN sys.objects oc ON cfk.referenced_object_id = oc.object_id
        INNER JOIN sys.columns cpc ON cpc.object_id = oc.object_id AND cpc.column_id = cfk.referenced_column_id
     ) t
LEFT JOIN (SELECT ix.name AS index_name, tx.name AS table_name, sx.name AS schema_name, ix.object_id
FROM sys.indexes ix
INNER JOIN sys.tables tx ON ix.object_id = tx.object_id
           INNER JOIN sys.schemas sx ON tx.schema_id = sx.schema_id
) i ON i.schema_name = t.parent_schema_name
             AND i.table_name = t.parent_table_name
             AND i.index_name = t.index_name
WHERE i.object_id IS NULL

Le colonne "requete" une fois sélectionnées et exécutée crée les index manquants.
Ajuster le texte en fonction des besoins (surtout par rapport aux options de l'index créé)