Context: Database log file is huge and you are not able to shrink it. You do not have more free space. Disaster is coming, or it is already there.
What you have done? I hope you never use this script, but if you need to use this script, you must to think how you never use again. This atomic database bomb.
Shrink database is bad practice (Increases fragmentation and reduces performance). "Shrink database" is for puny dba, but this, well, you are god-like DBA.
I am sure, you have no choice at this moment.
This script creates a script to delete the log file when we use Always On.
Steps :
- Remove database from availability group
- Set recovery to simple
- Shrink database
- Set recovery to full
- Backup database
- Add database to availability group
- Backup database
- Restore database on secondary
- Backup transactions
- Restore transactions on secondary
- Wait for the replica to start communicating
- Alter database set HADR on secondary
Required:
- Network Shared folder.
- Use SQLCMD mode.
- You must be sysadmin.
- Change parameters : dbname, AlwaysOnName, FullPathBackupFile
If you use "Results to Text" on your query, probably, the created script will cut. Use "Results to grid" on you query execution.
/****************************************
*
* Tools for AlwaysOn - Exec on Primary Server
*
* When you use this:
* - You have Always On Server
* - Log file is full OR you need to add new database into 'Always On'
*
****************************************/
GO
--------------------------------------------------------------
-- Configuration --
--------------------------------------------------------------
DECLARE @dbname nvarchar(100) = 'Northwind';
DECLARE @AlwaysOnName nvarchar(100) = 'MyAlwaysOn';
DECLARE @FullPathBackupFile nvarchar(1024) = '\\SQL01\backup$\';
--------------------------------------------------------------
DECLARE @sql nvarchar(MAX);
DECLARE @PrimaryServer NVARCHAR(100);
DECLARE @SecondaryServer NVARCHAR(100);
SELECT @PrimaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=1;
SELECT @SecondaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=2;
IF (NOT (ISNULL(@PrimaryServer,'') = @@servername))
BEGIN
PRINT '--';
PRINT '-- You MUST execute this script on primary server.';
PRINT '--';
raiserror('You MUST execute this script on primary server.', 20, -1) with log
END
IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = @AlwaysOnName))
BEGIN
PRINT '';
PRINT '';
PRINT '-- We can not continue, Availability group does NOT exist: ';
PRINT ' ' + @AlwaysOnName;
PRINT '';
PRINT '';
raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
END
IF (NOT EXISTS(select * FROM sys.databases WHERE [name] = @dbname))
BEGIN
PRINT '';
PRINT '';
PRINT '-- We can not continue, the database does NOT exist: ';
PRINT ' ' + @dbname;
PRINT '';
PRINT '';
raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
END
SET NOCOUNT ON;
DECLARE @SHRINKFILE AS nvarchar(MAX) = '';
SELECT @SHRINKFILE=@SHRINKFILE+'USE [#(dbname)];DBCC SHRINKFILE (N'''+mf.name+''' , 0, TRUNCATEONLY);'+CHAR(10)+CHAR(13) FROM sys.master_files AS mf , sys.databases AS db WHERE mf.[type] = 1 AND mf.database_id = db.database_id AND db.name = @dbname;
IF (EXISTS(
SELECT replica_server_name
FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states
ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id
WHERE role=1 and replica_server_name = @@servername) -- role = 1 : primary
)
BEGIN
SET @sql = '
/****************************************
*
* DBA - Disaster Recovery
*
* You MUST exec on Primary Server
*
* Database name : #(dbname)
* Primary Server : #(PrimaryServer)
* Secondary Server : #(SecondaryServer)
*
* When you use this:
* - You have "Always On" SQL Server
* - Log file is full
* - You want to add new database into your "Always On"
*
* Script steps:
* - Remove database from Availability Group, if it is there.
* - Set recovery simple
* - Shrink log file
* - Backup database
* - Set recovery full
* - Backup database
* - Restore database
* - Backup transactions
* - Restore transactions
* - Add database to Always On
*
* by Sozezzo
* #(getdate)
*
****************************************/
-- Check SQLCMD mode
:SETVAR CHECK SQLCMD
GO
IF (NOT ''$(CHECK)'' = ''SQLCMD'')
BEGIN
PRINT '''';
PRINT '''';
PRINT '' ** YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. **'';
PRINT '''';
PRINT '''';
raiserror(''YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE'', 20, -1) with log
END
DECLARE @PrimaryServer NVARCHAR(100);
DECLARE @SecondaryServer NVARCHAR(100);
SELECT @PrimaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=1;
SELECT @SecondaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=2;
IF ( NOT ( ''#(PrimaryServer)'' = @PrimaryServer AND ''#(SecondaryServer)'' = @SecondaryServer))
BEGIN
PRINT '''';
PRINT '' ** The context of servers have been changed. **'';
PRINT '''';
PRINT '' This script will be create when '';
PRINT '' * Primary server is : #(PrimaryServer)'';
PRINT '' * Secondary server is : #(SecondaryServer)'';
PRINT '''';
raiserror(''The context of servers have been changed.'', 20, -1) with log
END
PRINT ''---------------------------------------------'';
PRINT '''';
PRINT ''-- Disaster recovery database : #(dbname)''
PRINT '''';
PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
PRINT '''';
PRINT ''---------------------------------------------'';
GO
-- ALTER AVAILABILITY GROUP
:CONNECT #(PrimaryServer)
GO
USE [master];
IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = ''#(AlwaysOnName)''))
BEGIN
PRINT '''';
PRINT '''';
PRINT ''-- We can not continue, Availability group does NOT exist: '';
PRINT '' #(AlwaysOnName) '';
PRINT '''';
PRINT '''';
raiserror(''** We can not continue, Availability group does NOT exist **'', 20, -1) with log;
END
IF (exists(select * FROM sys.availability_groups AS ag INNER JOIN sys.availability_databases_cluster AS adc ON ag.group_id = adc.group_id WHERE adc.database_name = ''#(dbname)'' and ag.name = ''#(AlwaysOnName)''))
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''-- REMOVE DATABASE [#(dbname)] FROM AVAILABILITY GROUP [#(AlwaysOnName)]'';
ALTER AVAILABILITY GROUP [#(AlwaysOnName)] REMOVE DATABASE [#(dbname)];
END
GO
-- Shrink database on primary server
:CONNECT #(PrimaryServer)
GO
USE [master];
GO
PRINT ''---------------------------------------------'';
PRINT ''Set recovery to simple with no wait'';
ALTER DATABASE [#(dbname)] SET RECOVERY SIMPLE WITH NO_WAIT
GO
PRINT ''---------------------------------------------'';
PRINT ''Shrink database on primary server'';
#(SHRINKFILE)
GO
USE [master];
GO
PRINT ''---------------------------------------------'';
PRINT ''Set recovery to full'';
ALTER DATABASE [#(dbname)] SET RECOVERY FULL WITH NO_WAIT;
GO
PRINT ''---------------------------------------------'';
PRINT ''Backup database : 1'';
-- FIX: Database might contain bulk logged changes that have not been backed up.
BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
PRINT ''---------------------------------------------'';
PRINT ''Add database to availability group'';
ALTER AVAILABILITY GROUP [#(AlwaysOnName)] ADD DATABASE [#(dbname)];
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
PRINT ''---------------------------------------------'';
PRINT ''Backup database : 2'';
-- FIX: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
-- Restore database on secondary server
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
-- FIX : Exclusive access could not be obtained because the database is in use
WAITFOR DELAY ''00:00:10'';
PRINT ''Restore database'';
RESTORE DATABASE [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END
-------------------------------------
GO
-- Backup transaction database on primary server
:CONNECT #(PrimaryServer)
GO
IF (@@servername = ''#(PrimaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Backup database log'';
BACKUP LOG [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END
GO
-- Restore transaction database
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Restore database log'';
RESTORE LOG [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END
---------------------------------------------
GO
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Wait for the replica to start communicating'';
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty(''IsHadrEnabled'') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty(''ComputerNamePhysicalNetBIOS'') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N''#(AlwaysOnName)''
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay ''00:00:10''
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch
PRINT ''---------------------------------------------'';
PRINT ''Alter database set HADR'';
ALTER DATABASE [#(dbname)] SET HADR AVAILABILITY GROUP = [#(AlwaysOnName)];
END
GO
PRINT ''---------------------------------------------'';
PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
PRINT ''-- done! '';
GO'
;
SET @sql = replace(@sql,'#(SHRINKFILE)' , @SHRINKFILE);
SET @sql = replace(@sql,'#(AlwaysOnName)' , @AlwaysOnName);
SET @sql = replace(@sql,'#(getdate)' , CAST(GETDATE() AS NVARCHAR(100)));
SET @sql = replace(@sql,'#(dbname)' , @dbname);
SET @sql = replace(@sql,'#(PrimaryServer)' , @PrimaryServer);
SET @sql = replace(@sql,'#(SecondaryServer)' , @SecondaryServer);
SET @sql = replace(@sql,'#(FullPathBackupFile)', @FullPathBackupFile);
declare @pText nvarchar(max) = @sql;
declare @pTextNewLine nvarchar(2) = CHAR(13) + CHAR(10); -- ** it is a good practice to use CR and LF together. CHAR(13) + CHAR(10)
declare @pTextMax int = 256; -- ** default maximum number caracters displayed - SSMS -- but you can change it
declare @pTextPrint nvarchar(max);
declare @pTextCR Int
select @pText = @pText + @pTextNewLine;
while (LEN(@pText) > 0)
begin
SELECT @pTextCR = CHARINDEX(@pTextNewLine, @pText);
IF ((@pTextCR =-1) OR (@pTextCR > @pTextMax)) SELECT @pTextCR = @pTextMax;
select @pTextPrint = SUBSTRING(@pText,0,@pTextCR),
@pText = SUBSTRING(@pText, @pTextCR+len(@pTextNewLine), len(@sql));
print @pTextPrint
end
END
This create a new script, copy and paste on new query, and execute with SQLCMD mode.
If you have any error, you can run over and over again and expecting a good results without errors. At this point, it's safe script.
Sources:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database
https://blog.sqlauthority.com/2015/02/08/interview-question-of-the-week-006-is-shrinking-database-good-or-bad/
https://blog.sqlauthority.com/2015/08/08/sql-server-adding-file-to-database-in-alwayson-availability-group/
https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/