Error 5149 Status 3
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss error: 1453, severity: 16, state: 1. the workings and policies of this site About Us Learn more about error: 1454, severity: 16, state: 1. Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How do I shrink the physical Transaction Log file when it's the principal in a mirror? up vote 6 down vote favorite We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking up most of the drive space. I did a manual backup of the transaction log to disk to get the database running again, however running DBCC SHRINKFILE does not appear to decrease the physical size of the transaction log file. DBCC SHRINKFILE (N'MyDatabaseName_Log', 1000) If I check out the log usage using DBCC SQLPERF(LOGSPACE) I can see that only 22% of the current log is being used Database Name Log Size(MB) Log Space Used (%) Status MyDatabaseName 55440.87 22.38189 0 If I check out log_reuse_wait_desc in sys.databses, the only record I see is DATABASE_MIRRORING, so I am guessing the mirror is playing a role in why the physical size of the log file won't shrink? SELECT log_reuse_wait_desc FROM sys.databases WHERE name = N'MyDatabaseName'; I've also noticed my principal database mirroring state is Suspended, and trying to Resume it immediately f
takes a licking but it keeps onticking! Posted on March 30, 2010 by Glenn Berry With apologies to Timex, I just wanted to relate a minor incident that happened with one of my mirrored databases last night. This database needed to grow its primary data file, and the autogrow amount was set to 5GB ( I have Windows Instant File Initialization enabled). Unfortunately, there was less than 5GB of available disk space on the drive array on the mirror where this data file lived. I know, bad DBA… The data file grew on the principal, but when database mirroring sent the file growth command to the mirror side, http://dba.stackexchange.com/questions/40599/how-do-i-shrink-the-physical-transaction-log-file-when-its-the-principal-in-a-m it was not able to complete the operation, and mirroring was suspended for that database. I first noticed this when I saw that my log reuse wait description for that database was DATABASE_MIRRORING, and my transaction log was 71% full on the principal. I could also see that I had about 47GB of unsent log on the principal. The first thing I tried was resuming the mirror by issuing this https://sqlserverperformance.wordpress.com/2010/03/30/sql-server-2008-database-mirroring-it-takes-a-licking-but-it-keeps-on-ticking/ command: ALTER DATABASE ngfulltext1 SET PARTNER RESUME; This had no effect, so then I looked in the SQL Error Log on the Principal, and saw this error: TCP://SQLMIRROR01.xxx.xxxxxx.com:5022′, the remote mirroring partner for database ‘ngfulltext1', encountered error 5149, status 3, severity 16. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance. Next, I looked in the SQL Error Log on the instance where the mirror was located, and saw these two errors: MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file ‘P:\SQLData\ngfulltext1.mdf'. P:\SQLData\ngfulltext1.mdf: Operating system error 112(There is not enough space on the disk.) encountered. So duh, I was out of disk space on the P: drive. Again, dumb DBA, but luckily this was easy to fix by deleting some old backup files. After I did this, I resumed the mirroring session, which worked this time. SQL Server started rapidly sending the unsent log from the principal to the mirror (at about 58MB/sec). The way database mirroring behaves in this situation is that it sends a pretty decent chunk of the unsent log to mirror and then stops the send. It c
SQL Server 2014 Express resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers https://social.msdn.microsoft.com/Forums/en-US/080d9a9c-a634-4742-8ac5-9e32befc1b11/mirroring-has-been-stopped-since-long-time-i-am-resuming-but-still-it-is-showing-as-suspended?forum=sqldatabasemirroring Retired content Samples We’re sorry. The content you requested has been removed. You’ll http://sqlblogcasts.com/blogs/blakmk/archive/2008/06/26/mirroring-issues.aspx be auto redirected in 1 second. Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Mirroring has been stopped since long time, I am resuming but still it is showing as suspended SQL Server > Database Mirroring Question 0 Sign in severity: 16, to vote I have 2 SQL servers. One principal and the other is used as Mirror. Currently both servers are up and also both databases. But the mirroring is shown as Suspended. I am trying it to resume by clicking on the resume button on the properties screen of the principal database, but again it is showing as Paused and status is still Suspended. severity: 16, state: It is in the production environment and highly critical. Please advise what to do? Monday, August 18, 2008 5:26 PM Reply | Quote Answers 0 Sign in to vote I would suggest please check , Right click > Task>Launch Database mirroring monitor>Select registered database (If not register then register it)>Check the Status tab > then next column called "Principal Log"&'Mirrored Log'. there check Unsent log /Unrestored Log and current send rate which will help to understand the Bottleneck. If the problem seems to be Network then take a diffrential backup of princial and restored on mirrored after removing mirroirng. If the network is bottleneck then it might get fail at reconfiguring. it will be syncronised when Network will be in normal state , i encountered in my scenerio. So i tried after some time it was success. Also follow as Vidhya Sagar Suggestion for using Witness if not using. BRPraveen Barath Tuesday, August 19, 2008 9:30 AM Reply | Quote Answerer 0 Sign in to vote Ok, The unsent log is 18605298 KB and restoring rate is 0, means there is problem where it is not able
feel free to Contact me This Blog Home Contact About Syndication RSS Atom Search Go Navigation HomeBlogsPhotosDownloads News Subscribe in a reader
Tags 2008 always on AMD BI dashboard capacity planning Carbon Footprint Consolidation CPU Affinity CTP GDR1 SP2 Database Mirroring datasets DBA DBMIRROR_SEND Deadlock graph Developer DNS DR Extended events Fragmentation GUID Hardware Hyperthreading Intel java jdbc MDX mirroring Nehalem Network network encryption NUMA odbc PowerPivot Self Service reporting sla Sql Sql Server sql Server 2012 SSAS SSMS SSRS twitter UK WAN Windows 2008 Server Recent Posts Calculating estimated data loss with Always on SELECT * FROM Sql tweeters WHERE location = ‘UK’ Connecting to named Instances Using Port Number Transferring aliases from Sql Server Configuration manager between machines T-SQL Tuesday #12 – Why are DBA skills necessary? Archives December 2012 (1)December 2010 (2)November 2010 (2)October 2010 (2)August 2010 (2)July 2010 (3)June 2010 (2)April 2010 (3)January 2010 (2)December 2009 (1)June 2008 (3)May 2008 (2) Mirroring issues I recently came across this error message: the remote mirroring partner for database *********, encountered error 5149, status 1, severity 25. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance. I hate undocumented error messages so I thought I’d add my resolution. It appears that the log file on the mirror site was unable to extend the log file. The network connection dropped for a time and the build up of unsent transactions caused the primary log file to extend, when the network connection was re-established the mirrored logfile was unable to exend by the same amount. The moral of the story is to always allow enough space on both the primary and mirrored server for times when the network drops