Recover a database with a DAMAGED and/or LOST log file

In this procedure we’ll manage one of the worst situation a DBA has to manage: corrupted files and data loss. When this heppen usually the common way is restoring but we’ll use sql server features to reduce stop time (avoiding a complete restore) and data loss.

Possible starting problems:
Corrupted logfile
Corrupted logfile during a long transaction
Logfile volume corrupted or lost during transactions

At this point there are different solutions following current database settings:

SCENARIO 1: No transactions running during crash.
Solution:
If no transactions were running at crash point the solution is easy.This because SQL server rebuild automatically lost log file during database startup. So:
1) Detach corrupted database
2) Rename the old corrupted logfile in *.OLD
3) Attach database using:

CREATE DATABASE [MYDATABASE] ON
 ( FILENAME = N'D:Microsoft SQL ServerYourDataPathDataDatabase.mdf' )
 FOR ATTACH_REBUILD_LOG
 GO
 Notes:
 - SQL Server will try to rebuild log file in the ORIGINAL path.

SCENARIO 2: Transactions running during crash
Solution:
ATTACH_REBUILD_LOG in this situation *IS NOT* allowed because SQL Server find open transactions in the database and pending rollback/rollforward operations. So you’ll find the following error trying:

“File activation failure. The physical file name “D:Microsoft SQL ServerYourDataPathDataLogfile.ldf” 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 1
Could not open new database ‘MYDATABASE’.
CREATE DATABASE is aborted. “

So, follow this procedure:
1) DETACH DATABASE MyDatabase
2) Rename datafile and logfile in MDF.OLD and LDF.OLD
3) Create a new database with THE SAME name and identical original datafile and logfile position. I
4) ALTER DATABASE MyDatabase SET OFFLINE
5) Now you can put the original datafile in the original position
6) ALTER DATABASE MyDatabase SET ONLINE. This will fail but now we’ll can rebuild the log file
7) ALTER DATABASE [MyDatabase ] REBUILD LOG ON (NAME=’MyDatabaseLog’,FILENAME=’D:Microsoft SQL ServerYourDataPathDataLogfile.ldf’)
At this point the database will be usable but SQL Server at the end will show this warning:
Warning: The log for database ‘MyDatabase’ 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.
8) Final Step: open the database to users:
ALTER DATABASE [nomedb] SET MULTI_USER

Notes:
– In recovery model FULL make a new FULL BACKUP as soon as possible because the RESTORE chain is broken and you need a new baseline for log backup.
*Ask to double-check application consistency* because data recovered could be NOT consistent at application level. (we have done an uncomplete recover). If applicaton checks fails and nothing is fixable rapidly at application levele you have to consider, at the end, only a complete restore.

Pubblicità

How to script logins with the original password HASH and original SID

A quick tip useful to avoid problems during migrations.
This query allow to script instance users with orginal SID and password HASH.

This can be very useful to transfer logins to a new or different instance. All informations come from master database catalog. You have to be member of sysadmin role to run correctly this script. Users with noadmin grant we’ll have no or uncomplete output result.

select   
convert(varchar(50),'CREATE LOGIN [' + name + '] with password='),CAST(password AS VARBINARY(26)),
'HASHED,SID=',
CAST(sid AS VARBINARY(26))
from sys.syslogins  where password is not null

Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure

This is a complete configurable metascript, prepared to create detach statements, file move statement and re-attach statements for every database in your instance.
You have only to configure the final destination for datafiles and the database list you want to migrate.

The final generated script for every database selected will be something like this (remembar to use ALT-T to use text output mode in Management Studio)

============================================================
  DATABASE: myTestDB
============================================================
 
--  1) DETACH DATABASE
 
USE [master]
GO
ALTER DATABASE [myTestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'myTestDB', @keepfulltextindexfile=N'false'
GO
 
--  2) DATAFILE OS MOVING
 
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_log.LDF" L:LogPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB.mdf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_2.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_3.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_4.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_5.ndf" D:DataPath'
 
--  3) ATTACH DATABASE
 
CREATE DATABASE [myTestDB] ON 
( FILENAME = N'L:LogPathmyTestDB_log.LDF' ),
( FILENAME = N'D:DataPathmyTestDB.mdf' ),
( FILENAME = N'D:DataPathmyTestDB_2.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_3.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_4.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_5.ndf' ) 
FOR ATTACH
GO

This is the complete metascript:

set nocount on
----------------------------------------------------
--CONFIGURE HERE
----------------------------------------------------
--     DESTINATION PATH
----------------------------------------------------
--data files destination 
    declare @pathdata nvarchar(max) 
    set @pathdata = 'D:DataPath'
--log files destination   
    declare @pathlog nvarchar(max) 
    set @pathlog = 'L:LogPath'
----------------------------------------------------
--     DATABASE LIST
----------------------------------------------------
    create table #dbs (dbname nvarchar(255))
    --insert into #dbs values('db1')
    --insert into #dbs values('db2')
    --insert into #dbs values('db3')
--Note: if you want to move ALL database simply insert all db names in #dbs table using:
Insert into #dbs select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
---------------------------------------------------

declare @aDB nvarchar(255)
declare @aFile nvarchar(255)
declare @aType nvarchar(255)
declare @file_n integer
declare @count integer
declare @sep nvarchar(1)

declare db_cur CURSOR FOR select * from #dbs
OPEN db_cur
FETCH NEXT FROM db_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
	print '============================================================'
    print '  DATABASE: ' + @aDB
    print '============================================================'
    print ''
	
	print '--  1) DETACH DATABASE'
	print ''
	print 'USE [master]'
	print 'GO'
	print 'ALTER DATABASE [' + @aDB + '] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE'
	print 'GO'
	print 'EXEC master.dbo.sp_detach_db @dbname = N''' + @aDB +''', @keepfulltextindexfile=N''false'''
	print 'GO'
	print ''
	
	print '--  2) DATAFILE OS MOVING'	
	print ''
		
	declare file_cur CURSOR FOR select type_desc,physical_name from sys.master_files where db_name(database_id) = @aDB order by type_desc,physical_name
	OPEN file_cur
		
    FETCH NEXT FROM file_cur INTO @aType,@aFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
		if (@aType='ROWS')
		BEGIN			
			print 'exec xp_cmdshell ''move "' + @aFile + '" ' + @pathdata + ''''
		END
		ELSE
		BEGIN			
			print 'exec xp_cmdshell ''move "' + @aFile + '" ' + @pathlog + ''''
		END
		set @count=@count+1
	FETCH NEXT FROM file_cur INTO @aType,@aFile
	END
	CLOSE file_cur
	DEALLOCATE file_cur	
	print ''
	print '--  3) ATTACH DATABASE'	
	print ''
	--Create ATTACH Statement
	set @count=1
	set @sep=','	
	PRINT 'CREATE DATABASE [' + @aDB + '] ON '
	declare file_cur CURSOR FOR select type_desc,physical_name from sys.master_files where db_name(database_id) = @aDB order by type_desc,physical_name
	select @file_n=count(*) from sys.master_files where db_name(database_id) = @aDB
	OPEN file_cur
    FETCH NEXT FROM file_cur INTO @aType,@aFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
		if (@count=@file_n)
		BEGIN
			set @sep=' '
		END
		
		if (@aType='ROWS')
		BEGIN
			print '( FILENAME = N''' + @pathdata + RIGHT(@aFile, CHARINDEX('', REVERSE(@aFile))-1) +''' )' + @sep
		END
		ELSE
		BEGIN
			print '( FILENAME = N''' + @pathlog + RIGHT(@aFile, CHARINDEX('', REVERSE(@aFile))-1) +''' )' + @sep
		END
		set @count=@count+1
	FETCH NEXT FROM file_cur INTO @aType,@aFile
	END
	CLOSE file_cur
	DEALLOCATE file_cur
	
	print 'FOR ATTACH'
	print 'GO'
	print ''
FETCH NEXT FROM db_cur INTO @aDB
END
CLOSE db_cur
DEALLOCATE db_cur
drop table #dbs

How to make your databases smaller and faster: find unused indexes

It’s a boring job but sometimes a good DBA has to do it.

Applications change and you have to understand what become unuseful in your databases: we are talking about unused indexes. In any SQL server database indexes take up a lot of space and have to be updated every time an application runs an update on an indexed table. You have to rebuild and reorganize them… and you have to backup them, every day.

SQL Server gives you a good instrument to understand which indexes are really used. SYS.DM_DB_INDEX_USAGE_STATS is a dynamic management view used by SQL engine to collect information about this.
The first time a new index is used SQL server adds a new line to this table with many different counters. Those counters are used to collect data about the index every time it’s used. On every instance restart SQL reset index counters.
Querying this view is easy to understad which indexes are really used:
– Indexes not listed here are never used
– Indexes with blank counters are never used since the last instance restart

Those are a queries ready to make this kind of analisys. Remembar that not every application use alway ALL their indexes. Some indexes are used only when specific application functions are turned on. For this reason talk ALWAYS with application support guys before dropping anything.

--Indexes Never Used
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
	SELECT S.INDEX_ID
	FROM SYS.DM_DB_INDEX_USAGE_STATS S
	WHERE S.OBJECT_ID = I.OBJECT_ID
	AND I.INDEX_ID = S.INDEX_ID
	AND DATABASE_ID = @dbid)
	ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
--Indexes never used since the last restart
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT u.*
FROM [sys].[indexes] i
JOIN [sys].[objects] o
ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u
ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = @dbid 
WHERE o.[type] <> 'S'
AND i.[type_desc] <> 'HEAP'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
ORDER BY i.indexname asc

Analyze SQL Server database historical growth: MONTLY size changes

The most simple way to analyze database historical growth is quering database backup catalog.
SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning

This time we take the max size reached during every month to calculate the monthly size change.
This is the report query:

declare @dbname nvarchar(1024)  

--Configure HERE database name
set @dbname ='YourDatabaseName';

--Month Report
WITH TempTable(Row,database_name,backup_start_date,Mb) 
as 
(
select 
ROW_NUMBER() OVER(order by left(convert(nvarchar(20),backup_start_date,112),6)) as Row,
database_name,
left(convert(nvarchar(20),backup_start_date,112),6) month,
MAX(cast(backup_size/1024/1024 as decimal(10,2))) Mb 
from msdb..backupset
group by database_name,left(convert(nvarchar(20),backup_start_date,112),6),type
having 
type='D' and 
database_name=@dbname
)
select 
A.database_name,
A.backup_start_date,
A.Mb as max_backup_size_mb,
A.Mb - B.Mb as delta_mb
from TempTable A left join TempTable B on A.Row=B.Row+1
order by database_name,backup_start_date

Analyze SQL Server database historical growth: DAILY size changes

The most simple way to analyze database historical growth is quering database backup catalog.
SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning. Remembar only this:
msdb..backupset stores historical informations about backup size NOT database file size. This is good for you if you need to understand how your real stored data are growing day by day… but obviously datafiles are tipically larger: there is empty space inside for future data.
– evey full database backup contains a little part of logs used for recover. For this reason the size reported is not alway exactly your data dimension but usually this is not relevant

In this report you can see the daily changes in your database effective size.

tableresults

 

declare @dbname nvarchar(1024)  
declare @days int            
         
--Configure HERE database name
set @dbname ='YourDBName'
--...and number of days to analyze
set @days   =365;

--Daily Report
WITH TempTable(Row,database_name,backup_start_date,Mb) 
as 
(
select 
ROW_NUMBER() OVER(order by backup_start_date) as Row,
database_name,
backup_start_date,
cast(backup_size/1024/1024 as decimal(10,2)) Mb 
from msdb..backupset
where 
type='D' and 
database_name=@dbname and
backup_start_date>getdate()-@days
)
select 
A.database_name,
A.backup_start_date,
A.Mb as daily_backup,
A.Mb - B.Mb as increment_mb
from TempTable A left join TempTable B on A.Row=B.Row+1
order by database_name,backup_start_date