August 4, 2011 at 5:54 am
Hi
I have configured mirrioring setup fot high availablity method. Its running normally, But suddenly the db status showing 'suspended' mode. ?
Log is grwoing very larger
mdf file : 56 GB
ldf file : 144 GB
What is that reason .?
Please give advise me
Thanks
Jerry
August 8, 2011 at 5:22 am
Did you check the logs, also you can launch 'Database mirroring monitor' and see the information there.
M&M
August 8, 2011 at 9:12 am
There has to be something inside the SQL server error log.
--Rohan Joackhim
September 2, 2011 at 10:20 am
Was the database rebuilding indexes?
In the past, my sql mirrors have gone to a suspended state when rebuilding really large tables.
Matt
September 2, 2011 at 11:16 am
verify connectivity between the witness and the mirror and the principal and the mirror.
You need to get the session resumed as quick as possible as the log will keep on growing!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 5, 2011 at 10:17 am
Check the database mirroring monitor and connectivity between all parties (principal, mirror, witness). Check that the endpoints are started. Once the problem has been found and fixed the below will resume database mirroring for all user databases involved in a mirroring session.
BEGIN
--Declare the variables
DECLARE @sql VARCHAR(1000)
DECLARE @DatabaseName VARCHAR(50)
DECLARE @MinDB INT
DECLARE @MaxDB INT
--Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
DROP TABLE #Database ;
--Create temporary table
CREATE TABLE #Database
(
ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(100)
)
--Insert Databases whose roles are PRINCIPAL
INSERT #Database ( DatabaseName )
SELECT DB_NAME(database_id)
FROM sys.database_mirroring
WHERE database_id NOT IN ( 1, 2, 3, 4 ) --Exclude System Databases
AND DB_NAME(database_id) != 'distribution' --exclude distribution database
AND mirroring_role_desc IN ( 'PRINCIPAL', 'MIRROR' )
--Set Variables for the database role switch loop
SELECT @MinDB = MIN(ID),
@MaxDB = MAX(ID)
FROM #Database
--Begin loop to switch mirroring roles
WHILE @MinDB <= @MaxDB
BEGIN
--Get DatabaseName
SELECT @DatabaseName = DatabaseName
FROM #Database
WHERE ID = @MinDB
--Build COMMAND
SET @sql = 'ALTER DATABASE ' + @DatabaseName
+ ' SET PARTNER RESUME;'
--Try Catch block to switch mirroring roles and handle errors
BEGIN TRY
EXEC ( @sql
)
--PRINT @sql
PRINT 'Database mirroring role for ' + @DatabaseName
+ ' has now been resumed'
END TRY
BEGIN CATCH
SELECT @DatabaseName message_id,
severity,
[text],
FROM sys.messages
WHERE message_id = @@ERROR
AND language_id = 1033 --British English
END CATCH
--Get the next DatabaseName ID
SET @MinDB = @MinDB + 1
--End Loop
END
END
Thanks
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply