Force to Shrink Log File Over AlwaysOn

  1
  2/****************************************
  3*
  4* Tools for AlwaysOn - Exec on Primary Server
  5*
  6* When you use this:
  7*  - You have Always On Server
  8*  - Log file is full OR you need to add new database into 'Always On'
  9*
 10****************************************/
 11GO
 12--------------------------------------------------------------
 13-- Configuration --
 14--------------------------------------------------------------
 15
 16DECLARE @dbname nvarchar<span class="sh-symbol">(</span><span class="sh-number">100</span><span class="sh-symbol">)</span> <span class="sh-symbol">=</span> <span class="sh-string">'Northwind'</span><span class="sh-symbol">;</span>
 17DECLARE @AlwaysOnName nvarchar<span class="sh-symbol">(</span><span class="sh-number">100</span><span class="sh-symbol">)</span> <span class="sh-symbol">=</span> <span class="sh-string">'MyAlwaysOn'</span><span class="sh-symbol">;</span>
 18DECLARE @FullPathBackupFile nvarchar<span class="sh-symbol">(</span><span class="sh-number">1024</span><span class="sh-symbol">)</span> <span class="sh-symbol">=</span> <span class="sh-string">'</span><span class="sh-specialchar">\\</span><span class="sh-string">SQL01</span><span class="sh-specialchar">\b</span><span class="sh-string">ackup$</span><span class="sh-specialchar">\'</span><span class="sh-string">;</span>
 19
 20--------------------------------------------------------------
 21
 22DECLARE @sql nvarchar(MAX);
 23DECLARE @PrimaryServer NVARCHAR(100);
 24DECLARE @SecondaryServer NVARCHAR(100);
 25SELECT @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;
 26SELECT @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;
 27
 28IF (NOT (ISNULL(@PrimaryServer,'') = @@servername))
 29BEGIN
 30PRINT '--';
 31PRINT '-- You MUST execute this script on primary server.';
 32PRINT '--';
 33raiserror('You MUST execute this script on primary server.', 20, -1) with log
 34END
 35
 36IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = @AlwaysOnName))
 37BEGIN
 38PRINT '';
 39PRINT '';
 40PRINT '-- We can not continue, Availability group does NOT exist: ';
 41PRINT ' ' + @AlwaysOnName;
 42PRINT '';
 43PRINT '';
 44raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
 45END
 46
 47IF (NOT EXISTS(select * FROM sys.databases WHERE [name] = @dbname))
 48BEGIN
 49PRINT '';
 50PRINT '';
 51PRINT '-- We can not continue, the database does NOT exist: ';
 52PRINT ' ' + @dbname;
 53PRINT '';
 54PRINT '';
 55raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
 56END
 57
 58SET NOCOUNT ON;
 59DECLARE @SHRINKFILE AS nvarchar(MAX) = '';
 60SELECT @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;
 61
 62IF (EXISTS(
 63SELECT replica_server_name
 64FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states
 65ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id
 66WHERE role=1 and replica_server_name = @@servername) -- role = 1 : primary
 67)
 68BEGIN
 69
 70SET @sql = '
 71/****************************************
 72*
 73* DBA - Disaster Recovery
 74*
 75* You MUST exec on Primary Server
 76*
 77* Database name : #(dbname)
 78* Primary Server : #(PrimaryServer)
 79* Secondary Server : #(SecondaryServer)
 80*
 81* When you use this:
 82*    -  You have "Always On" SQL Server
 83*    -  Log file is full
 84*    -  You want to add new database into your "Always On"
 85*
 86* Script steps:
 87*    - Remove database from Availability Group, if it is there.
 88*    - Set recovery simple
 89*    - Shrink log file
 90*    - Backup database
 91*    - Set recovery full
 92*    - Backup database
 93*    - Restore database
 94*    - Backup transactions
 95*    - Restore transactions
 96*    - Add database to Always On
 97*
 98* by Sozezzo
 99* #(getdate)
100*
101****************************************/
102
103-- Check SQLCMD mode
104:SETVAR CHECK SQLCMD
105GO
106IF (NOT ''$(CHECK)'' = ''SQLCMD'')
107BEGIN
108PRINT '''';
109PRINT '''';
110PRINT '' ** YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. **'';
111PRINT '''';
112PRINT '''';
113raiserror(''YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE'', 20, -1) with log
114END
115
116DECLARE @PrimaryServer NVARCHAR(100);
117DECLARE @SecondaryServer NVARCHAR(100);
118SELECT @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;
119SELECT @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;
120
121IF ( NOT ( ''#(PrimaryServer)'' = @PrimaryServer AND ''#(SecondaryServer)'' = @SecondaryServer))
122BEGIN
123PRINT '''';
124PRINT '' ** The context of servers have been changed. **'';
125PRINT '''';
126PRINT '' This script will be create when '';
127PRINT '' * Primary server is : #(PrimaryServer)'';
128PRINT '' * Secondary server is : #(SecondaryServer)'';
129PRINT '''';
130raiserror(''The context of servers have been changed.'', 20, -1) with log
131END
132
133PRINT ''---------------------------------------------'';
134PRINT '''';
135PRINT ''-- Disaster recovery database : #(dbname)''
136PRINT '''';
137PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
138PRINT '''';
139PRINT ''---------------------------------------------'';
140GO
141-- ALTER AVAILABILITY GROUP
142:CONNECT #(PrimaryServer)
143GO
144USE [master];
145
146IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = ''#(AlwaysOnName)''))
147BEGIN
148PRINT '''';
149PRINT '''';
150PRINT ''-- We can not continue, Availability group does NOT exist: '';
151PRINT '' #(AlwaysOnName) '';
152PRINT '''';
153PRINT '''';
154raiserror(''** We can not continue, Availability group does NOT exist **'', 20, -1) with log;
155END
156
157IF (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)''))
158BEGIN
159PRINT ''---------------------------------------------'';
160PRINT ''-- REMOVE DATABASE [#(dbname)] FROM AVAILABILITY GROUP [#(AlwaysOnName)]'';
161ALTER AVAILABILITY GROUP [#(AlwaysOnName)] REMOVE DATABASE [#(dbname)];
162END
163GO
164-- Shrink database on primary server
165:CONNECT #(PrimaryServer)
166GO
167USE [master];
168GO
169PRINT ''---------------------------------------------'';
170PRINT ''Set recovery to simple with no wait'';
171ALTER DATABASE [#(dbname)] SET RECOVERY SIMPLE WITH NO_WAIT
172GO
173PRINT ''---------------------------------------------'';
174PRINT ''Shrink database on primary server'';
175#(SHRINKFILE)
176GO
177USE [master];
178GO
179PRINT ''---------------------------------------------'';
180PRINT ''Set recovery to full'';
181ALTER DATABASE [#(dbname)] SET RECOVERY FULL WITH NO_WAIT;
182GO
183PRINT ''---------------------------------------------'';
184PRINT ''Backup database : 1'';
185-- FIX: Database might contain bulk logged changes that have not been backed up.
186BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
187IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
188GO
189PRINT ''---------------------------------------------'';
190PRINT ''Add database to availability group'';
191ALTER AVAILABILITY GROUP [#(AlwaysOnName)] ADD DATABASE [#(dbname)];
192IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
193GO
194PRINT ''---------------------------------------------'';
195PRINT ''Backup database : 2'';
196-- FIX: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
197BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
198IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
199GO
200-- Restore database on secondary server
201:CONNECT #(SecondaryServer)
202GO
203IF (@@servername = ''#(SecondaryServer)'')
204BEGIN
205PRINT ''---------------------------------------------'';
206-- FIX : Exclusive access could not be obtained because the database is in use
207WAITFOR DELAY ''00:00:10'';
208PRINT ''Restore database'';
209RESTORE DATABASE [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
210IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
211END
212-------------------------------------
213GO
214-- Backup transaction database on primary server
215:CONNECT #(PrimaryServer)
216GO
217IF (@@servername = ''#(PrimaryServer)'')
218BEGIN
219PRINT ''---------------------------------------------'';
220PRINT ''Backup database log'';
221BACKUP LOG [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD, STATS = 10;
222IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
223END
224
225GO
226-- Restore transaction database
227:CONNECT #(SecondaryServer)
228GO
229IF (@@servername = ''#(SecondaryServer)'')
230BEGIN
231PRINT ''---------------------------------------------'';
232PRINT ''Restore database log'';
233RESTORE LOG [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
234IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
235END
236
237---------------------------------------------
238GO
239:CONNECT #(SecondaryServer)
240GO
241IF (@@servername = ''#(SecondaryServer)'')
242BEGIN
243PRINT ''---------------------------------------------'';
244PRINT ''Wait for the replica to start communicating'';
245-- Wait for the replica to start communicating
246begin try
247declare @conn bit
248declare @count int
249declare @replica_id uniqueidentifier
250declare @group_id uniqueidentifier
251set @conn = 0
252set @count = 30 -- wait for 5 minutes
253
254if (serverproperty(''IsHadrEnabled'') = 1)
255and (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)
256and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
257begin
258select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N''#(AlwaysOnName)''
259select @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
260while @conn <> 1 and @count > 0
261begin
262set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
263if @conn = 1
264begin
265-- exit loop when the replica is connected, or if the query cannot find the replica status
266break
267end
268waitfor delay ''00:00:10''
269set @count = @count - 1
270end
271end
272end try
273begin catch
274-- If the wait loop fails, do not stop execution of the alter database statement
275end catch
276
277PRINT ''---------------------------------------------'';
278PRINT ''Alter database set HADR'';
279ALTER DATABASE [#(dbname)] SET HADR AVAILABILITY GROUP = [#(AlwaysOnName)];
280END
281GO
282PRINT ''---------------------------------------------'';
283PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
284PRINT ''-- done! '';
285
286GO'
287;
288
289SET @sql = replace(@sql,'#(SHRINKFILE)' , @SHRINKFILE);
290SET @sql = replace(@sql,'#(AlwaysOnName)' , @AlwaysOnName);
291SET @sql = replace(@sql,'#(getdate)' , CAST(GETDATE() AS NVARCHAR(100)));
292SET @sql = replace(@sql,'#(dbname)' , @dbname);
293SET @sql = replace(@sql,'#(PrimaryServer)' , @PrimaryServer);
294SET @sql = replace(@sql,'#(SecondaryServer)' , @SecondaryServer);
295SET @sql = replace(@sql,'#(FullPathBackupFile)', @FullPathBackupFile);
296
297declare @pText nvarchar(max) = @sql;
298declare @pTextNewLine nvarchar(2) = CHAR(13) + CHAR(10); -- ** it is a good practice to use CR and LF together. CHAR(13) + CHAR(10)
299declare @pTextMax int = 256;  -- ** default maximum number caracters displayed - SSMS -- but you can change it
300declare @pTextPrint nvarchar(max);
301declare @pTextCR Int
302select @pText = @pText + @pTextNewLine;
303while (LEN(@pText) > 0)
304begin
305
306 SELECT @pTextCR = CHARINDEX(@pTextNewLine, @pText);
307 IF ((@pTextCR =-1) OR (@pTextCR > @pTextMax)) SELECT @pTextCR = @pTextMax;
308
309 select @pTextPrint = SUBSTRING(@pText,0,@pTextCR),
310 @pText = SUBSTRING(@pText, @pTextCR+len(@pTextNewLine), len(@sql));
311
312 print @pTextPrint
313
314end
315END