none
Datenbank teilen - bzw. viele Datensätze löschen

    Frage

  • Hallo

    Ich hätte eine Frage zum Teilen einer Datenbank bzw. Löschen von vielen Datensätzen.

    Wir haben eine Datenbank  zur Zeit im Einsatz mit der Größe von ca. 500 GB. Diese setzt sich aus einigen verschiedenen Mandanten zusammen. Jetzt soll die Datenbank für einen Mandanten weiterlaufen, und die anderen nicht mehr.

    Ich dachte an ein Backup der Datenbank auf dem neuen Server für den ausgewählten Mandanten einspielen - und danach alle nicht gewünschten Mandanten-Datensätze aller betroffenen Tabellen löschen (wobei hier durch ForeignKey und Cascade-Delete) viel automatisch beim Löschen funktionieren würde.

    Nur hat doch das Löschen auch seine Grenzen - und vor allem wächst das LOG-File extrem an.

    Welche andere Lösungen gäbe es noch?

    Hat hier schon jemand Erfahrung mit solchen Anforderungen.

    Vielen Dank schon mal für eine Info oder Unterstützung dazu.

    Schönen Gruß,

    Michael



    Michael Erlinger

    Dienstag, 9. Oktober 2018 05:49

Alle Antworten

  • Hi Michael,
    ich würde genau umgekehrt an die Lösung herangehen:

    1. Neue Datenbank mit der erforderlichen Struktur anlegen, z.B. als Kopie der aktuellen Datenbank.

    2. In der neuen Datenbank sind entweder alle Tabellen leer oder die Tabellen mit Werten, die für alle Mandanten gültig sind, enthalten alle Werte. Bei einer Kopie der aktuellen Datenbank werden dafür die Inhalte aller vom Mandanten abhängigen Tabellen gelöscht.

    3. Selektiv alle Daten kopieren, die zum gewünschten Mandanten gehören.


    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Dienstag, 9. Oktober 2018 06:36
  • Hallo

    danke für die Rückmeldung - ich hätte da noch eine Frage

    ad 2) ja - aber das Löschen wird so einfach nicht gehen mit einem DELETE - oder?

    Ich habe das schon mal probiert eine Tabelle mit größeren Inhalten (Image) mit vielen Datensätze zu löschen - geht der Platz aus wegen dem LOG-File

    ad 3) Selektives Kopieren von Daten - gibt es da Tools

    Danke & schönen Gruß,

    Michael


    Michael Erlinger

    Dienstag, 9. Oktober 2018 07:57
  • Hi Michael,
    zu 2) nutze anstelle DELETE ein TRUNCATE

    zu 3) wozu Tools INSERT mit SELECTE WHERE Mandant=gewünschter Mandant.


    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Dienstag, 9. Oktober 2018 08:01
  • Hallo Michael,

    um viele Datensätze zu löschen, empfiehlt sich ein aufgeteiltes Vorgehen. Du löschst also nicht einfach alle, sondern pro Durchlauf n Datensätze. Damit bleibt auch das Transaktionsprotokoll recht klein.

    Hier mal ein Beispielskript.

    DECLARE @Counter INT = 1
    
    WHILE @Counter < 500
    BEGIN
    
        BEGIN TRANSACTION
    
        DELETE 
        FROM   <Tabelle>
        WHERE  <PrimaerschluesselSpalte> IN
               (
                SELECT TOP 10000 <PrimaerschluesselSpalte>
                FROM   <Tabelle> WITH( NOLOCK )
                WHERE  ...
                ORDER BY <PrimaerschluesselSpalte>
               )
    
        COMMIT
        SET @Counter = @Counter + 1
    END




    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport



    Dienstag, 9. Oktober 2018 09:01
    Moderator
  • zu 2) nutze anstelle DELETE ein TRUNCATE

    Da Michael erwähnte, das ForeinKeys vorhanden sind, kann man kein Truncate anwenden.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Dienstag, 9. Oktober 2018 09:38
  • Hi Olaf,
    natürlich müssen beim Truncate auch die FK's gelöscht und wiederhergestellt werden.

    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Dienstag, 9. Oktober 2018 10:47
  • natürlich müssen beim Truncate auch die FK's gelöscht und wiederhergestellt werden

    Hallo Peter,

    was aber nichts bringt bzw. recht aufwändig ist, wenn es viele FKs gibt und die verknüpften Datensätze ebenfalls gelöscht werden sollen.

    Zudem will Michael ja anscheinend nicht die Tabelle leeren, sondern selektiv (viel) löschen.

    Den Hinweis auf eine neue DB und selektives rüberkopieren der relevanten Daten finde ich da besser.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Dienstag, 9. Oktober 2018 11:16
    Moderator
  • Hallo Michael,

    um viele Datensätze zu löschen, empfiehlt sich ein aufgeteiltes Vorgehen. Du löschst also nicht einfach alle, sondern pro Durchlauf n Datensätze. Damit bleibt auch das Transaktionsprotokoll recht klein.


    Hallo Stefan,

    das geht aber nur, wenn die Datenbank im SIMPLE-Recovery Model läuft. Ansonsten wäre nach jedem Löschvorgang ein BACKUP LOG erforderlich.

    @TE:

    Ich halte die Vorgehensweise von Stefan auch für zielführend. Hierbei nur - wie oben benannt - darauf achten, dass die Datenbank im SIMPLE Recovery Modus läuft.

    Ich implementiere eine solche Löschoperation immer wie folgt:

    DECLARE @RowCount	INT = 1;
    DECLARE @NumRows	INT = 3000;
    
    WHILE @RowCount > 0
    BEGIN
    	DELETE TOP (@NumRows) FROM dbo.DeineTabelle WHERE Condition;
    	SET @RowCount = @@ROWCOUNT;
    END
    

    Die Variante TRUNCATE hat ihren Charme, da sie lediglich die Deallokation der belegten Pages protokolliert; scheitert aber bei Dir an den FK's.

    Alternativ kannst Du aber - wie von Peter beschrieben - die zu verwendenden Daten auch in eine neue Datenbank importieren. Dazu kannst Du entweder ein SSIS-Paket verwenden oder aber mit Hilfe von SELECT INTO einen minimal logged Vorgang initiieren.

    Hier musst Du anschließend aber alle Indexe, Constraints, FK, ... manuell implementieren. Das scheint mir deutlich mehr Arbeit zu sein, als die von Stefan favorisierten Löschoperationen...


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Donnerstag, 11. Oktober 2018 05:33
  • Hallo Uwe,

    das geht aber nur, wenn die Datenbank im SIMPLE-Recovery Model läuft. Ansonsten wäre nach jedem Löschvorgang ein BACKUP LOG erforderlich.

    oh, Danke. Das wusste ich auch noch nicht. Klingt aber, wenn man mal drüber nachdenkt, auch logisch :) Gut zu wissen.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport


    Donnerstag, 11. Oktober 2018 05:56
    Moderator
  • Hallo Michael,

    ich bin da relativ schmerzfrei.

    Lösche auf der neuen Datenbank wie von Dir gewünscht. Das wird ja wohl nicht alles in einer Transaktion laufen, oder?Wenn der Plattenplatz für das Log zu klein wird, schiebst Du eine Logsicherung dazwischen. Ganz am Ende verkleinerst Du das Log auf eine sinnvolle Größe, machst ein Full-Backup der Datenbank und löscht alle Logsicherungen von vorher.

    Wenn Du das ganze mit der Lösung von Stefan kombinierst, kannst Du es noch granularer steuern.


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Donnerstag, 11. Oktober 2018 06:40
  • Hallo Uwe,

    die Logsicherung brauchst Du doch erst dann, wenn der Platz eng wird. 

    Man könnte ja auch das Logfile für die Dauer dieser Migration auf eine Platte legen, wo reichlich Platz ist, oder? Nach der Löschung schiebt man das verkleinerte Log dann dahin, wo es eigentlich hin sollte.


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Donnerstag, 11. Oktober 2018 06:42
  • Hallo

    danke allen mal für die anregende Diskussion und Hilfestellungen.

    TRUNCATE kommt leider nicht in Frage, weil alle FK zu löschen und neu aufzubauen wäre ziemlich mühsam.

    INSERT mit SELECT  - Daten kopieren - sehe ich vom Aufwand ebenfalls sehr hoch an, und die Gefahr dass dann eine Tabelle übersehen wird zu kopieren.....

    DELETE ist für mich die Option - wenn es mit der Kapazität und dem Log-File funktioniert.

    Wie gesagt - die Datenbank hat aktuell 500GB (und mehr) - und ich fürchte das Löschen verursacht schon große Log-Files; zumal auch in Tabelle Bilder, Dokumente usw. vorhanden sein können.

    Danke & schönen Gruß,

    Michael

    Donnerstag, 11. Oktober 2018 12:59
  • Hallo Uwe,

    die Logsicherung brauchst Du doch erst dann, wenn der Platz eng wird. 

    Das ist richtig! Aber ich kenne keinen Kunden, der reichlich Storage zur Verfügung stellt (z. B. Banken :) ).

    Bevor Du da zusätzliches Storage bekommst, hast Du Daten eher mit dem Radiergummi gelöscht.

    Liebe Grüße...


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Freitag, 12. Oktober 2018 07:16
  • ...

    Das ist richtig! Aber ich kenne keinen Kunden, der reichlich Storage zur Verfügung stellt (z. B. Banken :) ).

    ...

    :-D

    naja, ausgerechnet Banken.. zumindest die in Deutschland (vor allem die großen) glänzen leider nicht mit aktuellen Standards oder Technologien. Das ist also kein fairer Vergleich.

    Aber zum eigentlichen Thema:

    Die Kombi aus Deletes und Backup Log macht sicherlich den meisten Sinn. (Truncate funktioniert nicht mit teilweisem Löschen. Die Tabelle ist danach ganz leer! Das wollen wir ja nicht)

    Sie eigentliche Frage hat aber offenbar keiner gestellt: Wie viele Daten von den 500 GB sollen eigentlich am Ende gelöscht sein? Diese + Reserve gehen halt übers Log. Dann kann man mit der Information wie groß das aktuelle T-Log jetzt ist auch besser sagen, wie oft man dieses wohl sichern muss. Und vielleicht erübrigt sich dann komplexe Mathematik...

    Oder man macht es eben einfach einmal je 200.00 Datensätze, oder wie viel man da letztlich entscheidet.

    Die andere Frage wäre noch, ob das Ganze während der Produktion/Arbeitszeit geschehen muss, oder ob man tatsächlich für diese Wartungsarbeit einmal auf Simple Modus stellt, und sich die Backups der Löschaktion spart (Full Backup zuvor nicht vergessen!). Das könnte effizienter sein vom Vorgehen - wenn es zulässig ist. Wenn währenddessen weiter produktiv-Daten geändert werden natürlich nicht.            

    viel Erfolg

    der Andreas


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Montag, 15. Oktober 2018 18:31