sqlschool.gr logo

articles

Articles of SQLschool.gr Team

The undocumented ATTACH_FORCE_REBUILD_LOG

Antonios Chatzipavlis
Monday 23 November 2020

Στην δουλειά του DBA υπάρχουν στιγμές που πραγματικά οι παλμοί ανεβαίνουν επικίνδυνα. Συνήθως αυτό συμβαίνει ότι υπάρχει το ενδεχόμενο απώλειας δεδομένων.

Θα πει κάποιος πως μπορεί να γίνει αυτό καθώς ο σωστός DBA έχει backups. Αυτό είναι σωστό αρκεί να μιλάμε για οργανισμό που έχει DBA πραγματικό αλλιώς όλα μπορούν να συμβούν.

Πρόσφατα επικοινώνησε μια εταιρεία μαζί που για κάποιους όχι και τόσο καλούς λόγους είχε χάσει τα πάντα από τα πάντα. Κάπου υπήρχε ένα system backup που μέσα σε αυτό ήταν τα data αρχεία μιας database (to log ήταν damaged).

Στην προσπάθεια τους να κάνουν attach αυτή την database με το CREATE DATABASE FOR ATTACH το λάθος που επιστρέφονταν ήταν το παρακάτω:

File activation failure. The physical file name "…" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'XXXXXX'. CREATE DATABASE is aborted.

Ακόμα και όταν δοκίμαζαν το CREATE DATABASE FOR ATTACH_REBUILD_LOG το ίδιο λάθος επιστρέφονταν.

Αν παρατηρήσετε το μήνυμα λάθους λέει ακριβώς το πρόβλημα

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Η λύση που υπάρχει σε αυτή την περίπτωση είναι να χρησιμοποιηθεί η δυστυχώς undocumented CREATE DATABASE FOR ATTACH_FORCE_REBUILD_LOG.

SQL Script

CREATE DATABASE demo
ON ( FILENAME  = ‘…..’ )
FOR ATTACH_FORCE_REBUILD_LOG;

//Antonios Chatzipavlis

Comments

24 Nov 2020 @ 9:48 AM

user-gravatar

Βασίλης

3 Γραμμές που μπορεί να σώσουν εταιρίες, δουλειές, ανθρώπους, καριέρες.Τρομακτικά όμορφος κώδικάς.

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.