Series of Errors
Error :- The log cannot be rebuilt because the database was not cleanly shut down
Image
Textual
File activation failure. The physical file name "LDF" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. Msg 1813, Level 16, State 2, Line 3 Could not open new database 'HRDB'. CREATE DATABASE is aborted.
Scenario
Create Database from existing data files
Script
SQL
USE [master] GO CREATE DATABASE [HRDB] ON ( FILENAME = N'E:\Microsoft\SQLServer\DatabaseFiles\HRDB.mdf' ) FOR ATTACH GO
Output
File activation failure. The physical file name "HRDB_Log.LDF" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. Msg 1813, Level 16, State 2, Line 3 Could not open new database 'HRDB'. CREATE DATABASE is aborted.
Remediation
Outline
- Take Current database offline
- Copy Current Database files to new location ( New Location of Current Database ) (1)
- Copy current data files to new location
- Create new database ( shell )
- Target fresh database data and log files
- Change File Location of new database ( shell ) to point at new location ( 1)
- Change file location of new database to match new locations (1)
- Take new database ( shell ) offline
- Bring new database ( shell ) back online
- An error should appear
- Repair DB
- Place new database ( databaseShell ) in emergency mode
- Place database database in single user mode
- Issue “DBCC Checkdb” with special option to allow data loss ( REPAIR_ALLOW_DATA_LOSS )
- Place database in multi-user mode
Process
Take Current database offline
Syntax
if db_id(<database>) is not null begin ALTER DATABASE [<database>] SET OFFLINE WITH ROLLBACK IMMEDIATE end go
Sample
if db_id('HRDB') is not null begin ALTER DATABASE [HRDB] SET OFFLINE WITH ROLLBACK IMMEDIATE end go
Copy Current Database files to new location
Please place data and log files in new location.
This will ensure that we do not lose data in the event that things go sideways.
Create new Database ( shell )
Sample
exec master.dbo.xp_create_subdir N'C:\Microsoft\SQLServer\Datafiles' go exec master.dbo.xp_create_subdir N'C:\Microsoft\SQLServer\Logfiles' go set noexec off go if db_id('shell') is not null begin alter database [shell] set SINGLE_USER with rollback immediate; end go if db_id('shell') is not null begin drop database [shell] end go if db_id('shell') is not null begin set noexec on end go CREATE DATABASE [shell] ON PRIMARY ( NAME = N'shell_data' , FILENAME = N'C:\Microsoft\SQLServer\Datafiles\shell_data.mdf' ) LOG ON ( NAME = N'shell_log' , FILENAME = N'C:\Microsoft\SQLServer\Logfiles\shell_log.ldf' ) GO set noexec off go
Align file locations for shell database to match location that we copied original data files to
SQL
Sample
ALTER DATABASE [shell] MODIFY FILE ( NAME='Shell_Data' , FILENAME= 'E:\Microsoft\SQLServer\DatabaseFiles\hrdb\hrdbCopy_Data.mdf' ) GO ALTER DATABASE [shell] MODIFY FILE ( NAME = N'Shell_Log' , FILENAME= 'E:\Microsoft\SQLServer\LogFiles\hrdb\hrdbData_Log_Missing.ldf' ) GO
Output
Image
Textual
The file "Shell_Data" has been modified in the system catalog. The new path will be used the next time the database is started. The file "Shell_Log" has been modified in the system catalog. The new path will be used the next time the database is started.
Set Database shell offline
SQL
Sample
if db_id('shell') is not null begin ALTER DATABASE [shell] SET OFFLINE with ROLLBACK IMMEDIATE end go
Output
Image
Output
File activation failure. The physical file name "E:\Microsoft\SQLServer\LogFiles\hrdb\hrdbData_Log_Missing.ldf" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down.
Set Database shell online
SQL
Sample
if db_id('shell') is not null begin ALTER DATABASE [shell] SET ONLINE; end GO
Output
Image
Text
File activation failure. The physical file name "E:\Microsoft\SQLServer\LogFiles\hrdb\hrdbData_Log_Missing.ldf" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. Msg 945, Level 14, State 2, Line 7 Database 'shell' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 7 ALTER DATABASE statement failed.
Database brought online with Status “Recovery Pending”
Status
Textual
Database brought online, but with status “Recovery Pending”
Image
Repair Database
SQL
Sample
USE master GO DBCC TRACEON(3604) GO ALTER DATABASE [shell] SET EMERGENCY GO ALTER DATABASE [shell] SET SINGLE_USER GO DBCC CHECKDB ( [shell] , REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS GO ALTER DATABASE [shell] SET MULTI_USER GO
Output
Image
Textual
DBCC execution completed. If DBCC printed error messages, contact your system administrator. File activation failure. The physical file name "E:\Microsoft\SQLServer\LogFiles\hrdb\hrdbData_Log_Missing.ldf" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. The Service Broker in database "shell" will be disabled because the Service Broker GUID in the database (EA8A5A39-0D92-40EE-997D-DC3A5E211E2B) does not match the one in sys.databases (C4E3C710-791C-4DC0-B740-E7EAD5F6F663). Warning: The log for database 'shell' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. DBCC results for 'shell'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. DBCC results for 'sys.sysrowsetcolumns'. There are 1088 rows in 9 pages for object "sys.sysrowsetcolumns". DBCC results for 'sys.sysrowsets'. CHECKDB found 0 allocation errors and 0 consistency errors in database 'shell'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database Status Online
Status
Image
No more emergency mode!