none
Frage zu Backup eines SQL 2005 Servers RRS feed

  • Frage

  • Hallo an alle!

    Ich habe ein Problem mit einem Backup eines SQL 2005 Servers bei einem Kunden. Ich muss dazu sagen, dass ich kein Datenbank Spezialist bin und nur solide Grundkenntnisse habe.

    Auf dem SQL Server werden verschiedene Datenbanken gehostet. Zu Backup-Zwecken habe ich einen Wartungsplan erstellt, welcher alle DBs täglich um 22:30 Uhr sichert (Vollbackup). Das funktioniert auch soweit ganz gut. Seit einiger Zeit gibt es aber mit einer speziellen Datenbank Probleme. Die MDF-Datei ist knapp 5GB groß, das Backup aber ca. 12GB.

    Wie kann das sein und wie kann ich das abstellen?

    Noch ein paar Infos dazu: Das Backup des Vortages wird per Script auf eine NAS verschoben, so dass immer nur das letzte Backup im Backupverzeichnis liegt. Die Transaction Logs werden stündlich gesichert und sofort auf die NAS verschoben. Ein Anhängen des Backups an die bestehende Backupdatei sollte damit eigendlich ausgeschlossen sein.


    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Freitag, 8. Januar 2016 10:09

Antworten

  • Entschuldigung, Du hattest ja geschrieben es ging im SQL Server 2005, da gibt es die Spalte nicht, richtig müsste es so sein

      SELECT TOP 100
           [database_name]
          ,[backup_size]            
      FROM [msdb].[dbo].[backupset]
      WHERE [type] = 'D'
      ORDER BY [backup_set_id] DESC

    Die kleinen Sicherungen sind die Log Sicherungen. Der Liste nach sind die Backups in der Tat 13 GB groß; zufällig genau die Große MDF + LDF. Zwar werden offene Transaktionen mit gesichert, aber schwer vorzustellen, das zum Zeitpunkt des Backups eine so große Transaktion offen ist.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Freitag, 8. Januar 2016 12:17
  • Wieviele VLFs haben denn Status 2?

    Use [M_Data];
    go
    DBCC LOGINFO;

    Hinter der Abkürzung VLF verbergen sich Virtual Log Files.
    Als Ergebnis erhält man für jedes VLF eine Zeile zurück. Der Status 0 zeigt an, dass es nicht verwendet wird, der Status 2 zeigt an, dass dieses VLF derzeit belegt ist. die FSeqNo zeigt an, in welcher Reihenfolge diese aktiven VLFs geschrieben wurden. Das VLF mit der höchsten Nummer und dem Status 2 ist also der Punkt im Transaktionslog, wohin neue Transaktionen geschrieben werden. Wenn dieses VLF voll ist, wird das nächste freie VLF verwendet. Gibt es kein freies VLF mehr, wird das Transaktionslog (um 4, 8 oder 16 VLFs) erweitert. Kann das Log nicht erweitert werden, erhält man eine Fehlermeldung.

    Die FileSize zeigt die Größe des VLF in Byte an. Hier gibt es schon mal Rundungsdifferenzen, so dass nicht alle VLFs gleich groß sind und auch zusammen ergeben die Werte, die in einem Schritt allokiert wurden nicht immer genau den gewünschten Wert.

    Gibt es evtl. einen erheblichen Unterschied in der Bandbreite der FSeqNo? Dann könnten dort noch alte Transaktionen schlummern.
    Verwendest Du Replikation?

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

    Freitag, 8. Januar 2016 12:29
    Beantworter
  • Weil das Vollbackup alle Transaktionen aus Transaktionslog sichert welche entweder: noch Aktive sind oder welche noch nicht repliziert worden sind. Da es keine Gegenstelle gab konnten keine Transaktionen repliziert werden und somit auch das Log nicht geleert werden.

    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,


    Montag, 11. Januar 2016 08:57

Alle Antworten

  • Hallo Thorsten,

    Das ein Backup in eine neue leere Bachupdatei sollte eigentlich nicht größer sein als die Datenbank selber. Bist du sicher dass die Sicherungszieldatei leer ist wenn das Backup gestartet wird? Wenn man in eine vorhandene Sicherungsdatei schreibt und nicht überschreiben gewählt hat, wird die Sicherung an die bestehenden Daten angefügt.

    Hat die Datenbank neben der mdf Datei noch zusätzlich ndf Dateien und wenn ja wie groß sind diese?

    Gruß Benjamin


    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,

    Freitag, 8. Januar 2016 11:19
  • Hallo Benjamin,

    es gibt keine zusätzlichen ndf Dateien, und es wird jedesmal eine neue Backupdatei nach dem Schema dbname_backup_yyyymmddhhmm.bak erzeugt.

    Viele Grüße

    Thorsten


    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Freitag, 8. Januar 2016 11:23
  • Hallo Thorsten,

    Welche Größenangaben für die Datenbank liefert die Abfrage der Backup-Historie

    SELECT TOP 100
           [database_name]
          ,[backup_size]      
          ,[compressed_backup_size]
      FROM [msdb].[dbo].[backupset]
      ORDER BY [backup_set_id] DESC

    und der Datenbankgröße?

    SELECT name, size * 8192.0 / 1048576 
    FROM sys.database_files


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Freitag, 8. Januar 2016 11:28
  • Die erste Abfrage liefert einen Fehler:

    Meldung 207, Ebene 16, Status 1, Zeile 4
    Ungültiger Spaltenname 'compressed_backup_size'.

    Ohne diese Spalte bekomme ich 100 Zeilen mit allen DB_Namen und verschiedenen Größen

    Hier nur mal meine Problem-DB:

    ID   database_name backup_size

    7    M_Data               29007360

    16 M_Data                20486656

    19 M_Data                13021085184

    26 M_Data                22453760

    35 M_Data                2005504

    38 M_Data                12981434880

    45 M_Data                367104

    54 M_Data                300544

    63 M_Data                300544

    72 M_Data                300544

    81 M_Data                300544

    90 M_Data                300544

    99 M_Data                300544

    Die zweite liefert:

    M_Data_Data -> 4893.500000000

    M_Data_Log -> 8960.500000000


    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Freitag, 8. Januar 2016 11:43
  • Entschuldigung, Du hattest ja geschrieben es ging im SQL Server 2005, da gibt es die Spalte nicht, richtig müsste es so sein

      SELECT TOP 100
           [database_name]
          ,[backup_size]            
      FROM [msdb].[dbo].[backupset]
      WHERE [type] = 'D'
      ORDER BY [backup_set_id] DESC

    Die kleinen Sicherungen sind die Log Sicherungen. Der Liste nach sind die Backups in der Tat 13 GB groß; zufällig genau die Große MDF + LDF. Zwar werden offene Transaktionen mit gesichert, aber schwer vorzustellen, das zum Zeitpunkt des Backups eine so große Transaktion offen ist.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Freitag, 8. Januar 2016 12:17
  • Wieviele VLFs haben denn Status 2?

    Use [M_Data];
    go
    DBCC LOGINFO;

    Hinter der Abkürzung VLF verbergen sich Virtual Log Files.
    Als Ergebnis erhält man für jedes VLF eine Zeile zurück. Der Status 0 zeigt an, dass es nicht verwendet wird, der Status 2 zeigt an, dass dieses VLF derzeit belegt ist. die FSeqNo zeigt an, in welcher Reihenfolge diese aktiven VLFs geschrieben wurden. Das VLF mit der höchsten Nummer und dem Status 2 ist also der Punkt im Transaktionslog, wohin neue Transaktionen geschrieben werden. Wenn dieses VLF voll ist, wird das nächste freie VLF verwendet. Gibt es kein freies VLF mehr, wird das Transaktionslog (um 4, 8 oder 16 VLFs) erweitert. Kann das Log nicht erweitert werden, erhält man eine Fehlermeldung.

    Die FileSize zeigt die Größe des VLF in Byte an. Hier gibt es schon mal Rundungsdifferenzen, so dass nicht alle VLFs gleich groß sind und auch zusammen ergeben die Werte, die in einem Schritt allokiert wurden nicht immer genau den gewünschten Wert.

    Gibt es evtl. einen erheblichen Unterschied in der Bandbreite der FSeqNo? Dann könnten dort noch alte Transaktionen schlummern.
    Verwendest Du Replikation?

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

    Freitag, 8. Januar 2016 12:29
    Beantworter
  • Hi,

    bringt es etwas, wenn du dir den "Inhalt" der BAK-Datei anschaust:

    RESTORE HEADERONLY 
    FROM DISK = N'C:\AdventureWorks-FullBackup.bak' 
    WITH NOUNLOAD;
    GO

    Nicht dass ein anderer Job oder was auch immer in die gleiche Datei schreibt.

    Grüße

    Andreas

    Freitag, 8. Januar 2016 12:36
  • Ich habe hier 282 VLF und bis auf dreien haben alle die Größe 33554432

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Freitag, 8. Januar 2016 12:41
  • Was soll ich den daraus sehen? Ich bekomme eine Zeile zurück und da steht als Backupsize 12981428736. Für mich sieht es nicht so aus, als ob dort ein weiterer Job da rein schreibt

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Freitag, 8. Januar 2016 12:45
  • Was soll ich den daraus sehen?

    Wenn in einer BAK-Datei mehrere Backups enthalten gewesen wären, dann hättest du pro Backup eine Zeile gesehen. Wenn das nicht so ist, dann hast du, wie oben vermutet, nur das eine Backup in der Datei.

    Damit sollte ausgeschlossen werden, dass die BAK-Datei durch was auch immer mehrere Sets von Backups enthält.

    Grüße

    Andreas

    Freitag, 8. Januar 2016 13:38
  • Thorsten,
    kannst Du auch noch meine anderen Fragen beantworten?

    Wieviele VLFs haben denn Status 2?
    Gibt es evtl. einen erheblichen Unterschied in der Bandbreite der FSeqNo? Dann könnten dort noch alte Transaktionen schlummern.
    Verwendest Du Replikation?

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

    Freitag, 8. Januar 2016 14:06
    Beantworter
  • Thorsten,
    kannst Du auch noch meine anderen Fragen beantworten?

    Wieviele VLFs haben denn Status 2?
    Gibt es evtl. einen erheblichen Unterschied in der Bandbreite der FSeqNo? Dann könnten dort noch alte Transaktionen schlummern.
    Verwendest Du Replikation?

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

    Guten Morgen Christoph,

    heute morgen sind es 290 VLFs mit Status 2. Was verstehst Du unter der Bandbreite der FseqNo? Die ersten FSeqNo lauten:

    263413

    263414

    263415

    263398

    263399

    263400

    263401

    263412

    ...

    263678

    263679

    263680

    263681

    und die letzten 6 betragen 0

    Liebe Grüße

    Thorsten


    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Montag, 11. Januar 2016 07:21
  • Achso und Replikation benutze ich nicht. Ich hatte es mal versucht einzurichten, hatte aber so spontan nicht funktioniert.

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Montag, 11. Januar 2016 07:22
  • Anscheinend ist die Replikation noch aktiv (oder zumindest nicht vollständig abgeschaltet), dies würde zumindest die Backup Größe und den Inhalt des Logs erklären.

    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,

    Montag, 11. Januar 2016 08:31
  • Anscheinend ist die Replikation noch aktiv (oder zumindest nicht vollständig abgeschaltet), dies würde zumindest die Backup Größe und den Inhalt des Logs erklären.

    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,


    Das hört sich interessant an. Kannst Du mir einen Tipp geben, wie ich die Replikation ausschalten kann? die war ja eigendlich nie richtig aktiv

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Montag, 11. Januar 2016 08:35
  • Wird im Replikationsmenü noch eine Veröffentlichung aufgeführt?


    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,

    Montag, 11. Januar 2016 08:48
  • Anscheinend ist die Replikation noch aktiv (oder zumindest nicht vollständig abgeschaltet), dies würde zumindest die Backup Größe und den Inhalt des Logs erklären.


    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,


    Das hört sich interessant an. Kannst Du mir einen Tipp geben, wie ich die Replikation ausschalten kann? die war ja eigendlich nie richtig aktiv

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    So, ich habe die Replikation abschalten können. Jetzt scheint alles wieder OK zu sein. Ich frage mich allerdings, warum die Replikation das Back so dermaßen aufblähen kann?

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Montag, 11. Januar 2016 08:53
  • Weil das Vollbackup alle Transaktionen aus Transaktionslog sichert welche entweder: noch Aktive sind oder welche noch nicht repliziert worden sind. Da es keine Gegenstelle gab konnten keine Transaktionen repliziert werden und somit auch das Log nicht geleert werden.

    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,


    Montag, 11. Januar 2016 08:57
  • Weil das Vollbackup alle Transaktionen aus Transaktionslog sichert welche entweder: noch Aktive sind oder welche noch nicht repliziert worden sind.

    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,


    Achso. Ich hätte mit der Replikation wohl besser auf einem Testsystem experimentieren sollen ;-)

    Thorsten Schröer Dipl-Inform (FH) - EDV, Beratung & Service - double-D-IT -Office- / Information system support Bismarckstraße 18a Germany-12169 Berlin Steglitz phone: +49-(0)30-303286770 fax: +49-(0)30-303286779

    Montag, 11. Januar 2016 08:58
  • Hallo Thorsten,

    Wenn dein Problem nun behoben ist markiere bitte alle hilfreichen Beiträge als Antwort damit anderen mit dem gleichen Problem die Lösung schneller finden können.

    Gruß Benjamin


    Benjamin Hoch
    MCSE: Data Platform,
    MCSA: Windows Server 2012,

    Montag, 11. Januar 2016 10:06