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!