February 3, 2015 at 9:49 am
Hi All,
I have a Customer running a database in a High Availability Group and I am not familiar with the set up...
They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.
Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....
The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way.
Is there a simple solution to this conundrum?
Thanks in advance.
Steve
Steve
We need men who can dream of things that never were.
February 3, 2015 at 4:22 pm
Let's start with the following
How many databases in the group?
How many secondarys, are they readable?
Are they synchronous or asynchronous mode?
Whart sql server version is in use on the replicas?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2015 at 10:33 am
Hi Perry,
4 Databases
2 Members, 1 Primary 1 Secondary in Synchronous Mode
Secondary not readable
Both are running SQL 2012 (v 11.0.5522.0)
Thanks in advance
Steve
Steve
We need men who can dream of things that never were.
February 5, 2015 at 1:09 pm
Anything - anybody?
Steve
We need men who can dream of things that never were.
February 6, 2015 at 4:48 am
sorry, can you supply exact details of what you're doing and show a screenshot of the message
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 9, 2015 at 1:35 pm
Surely there is enough information in this for a reasonably simple answer?
Ste_P (2/3/2015)
Hi All,I have a Customer running a database in a High Availability Group and I am not familiar with the set up...
They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.
Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....
The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way.
Is there a simple solution to this conundrum?
Thanks in advance.
Steve
We are trying to shrink the transaction logs for a database that is part of a high availability group and no matter what we try, whilst the DB is part of the group - the logs won't shrink.
Best regards
Steve
Steve
We need men who can dream of things that never were.
March 9, 2015 at 5:09 pm
they're attempting to shrink the secondary databases files, is that correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 9, 2015 at 5:20 pm
Just the primary.....
Steve
We need men who can dream of things that never were.
March 10, 2015 at 4:53 am
Hmmm, the message you're seeing is displayed when executing a shrink command against a secondary database.
On my system I run the shrink on the primary and the secondary is taken care of, so, back to my previous posts.
How exactly are you shrinking (if TSQL the exact command please)?
Can you provide screenshot?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 10, 2015 at 6:21 am
Do you have any open transactions on the primary database? what is the state of secondary database?
March 10, 2015 at 10:19 am
Perry Whittle (3/10/2015)
How exactly are you shrinking (if TSQL the exact command please)?
Hi Perry,
Just simply in Management Studio, highlight the primary database, right click - Tasks->Shrink->Files...
I also tried a script that I use occaisionally to shrink all databases and log files on an instance but that failed too
Script:
use master
DECLARE @Statement varchar (2000)
SELECT @Statement = ''
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement
EXEC sp_MSforeachdb @command1=@Statement
GO
if exists(select * from sysobjects where id = object_id('dbo.proc_shrink_databases') and xtype = 'P')
drop procedure dbo.proc_shrink_databases
go
create procedure dbo.proc_shrink_databases
as
begin
set nocount on
create table #temp_dbs_table
(
[db_name] sysname not null primary key,
[mod] tinyint not null default 1
)
insert into #temp_dbs_table ([db_name])
select
name
from
master..sysdatabases
where
dbid > 4 --- skip master, tempdb, model and msdb databases
declare @db_name sysname
set @db_name = ''
while @db_name is not null
begin
set @db_name = NULL
select top 1 @db_name = [db_name] from #temp_dbs_table where [mod] = 1
if @db_name is NULL
break
print '--------------------------------------------------'
print '> Database: ' + @db_name
print '> Changing recovery mode to simple'
declare @n_cmd nvarchar(4000)
set @n_cmd = 'alter database [' + @db_name + '] set recovery simple'
exec sp_executesql @n_cmd
print '> Shrinking database'
set @n_cmd = 'dbcc shrinkdatabase([' + @db_name + '])'
exec sp_executesql @n_cmd
update #temp_dbs_table set [mod] = 0 where [db_name] = @db_name
end
drop table #temp_dbs_table
end
go
exec dbo.proc_shrink_databases
Steve
We need men who can dream of things that never were.
March 10, 2015 at 10:31 am
Ste_P (3/10/2015)
Hi Perry,Just simply in Management Studio, highlight the primary database, right click - Tasks->Shrink->Files...
I also tried a script that I use occaisionally to shrink all databases and log files on an instance but that failed too
Well, as I said, I expect that error message when running the shrink against a secondary database. So on the instance you connect to in SSMS, scroll down and expand the AlwaysOn section and just check that the replica is acting as Primary
Ste_P (3/10/2015)
dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly);
NoTruncate and Truncateonly are ignored for transaction log files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply