Error 5149 Status 3 Severity 16
Contents |
to transaction logs If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages,
Error: 1453, Severity: 16, State: 1.
select the forum that you want to visit from the selection below. Results 1 to error: 1454, severity: 16, state: 1. 11 of 11 Thread: SQL 2008 Mirroring running out of disk space due to transaction logs Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 12-28-10,13:47 #1 Qik View Profile View Forum Posts Registered User Join Date Dec 2010 Posts 6 Unanswered: SQL 2008 Mirroring running out of disk space due to transaction logs Last month I mirrored a SQL 2008 db to another server running sql 2008. Everything was fine until last week. The backups went from 60GB to 220GB in about 1 weeks time. I checked the mirror and noticed it is in a suspended state. I then checked the folder holding the transaction log file for the principal db and noticed it was over 150gb in size but the actual db is only 2gb. On the mirror server I am seeing a 2GB file which matches the principal db but I am am also seeing a 48GB file which has the same name as the principal db with a "_1" after it. I have a second file which is only a few meg and it is being mirrored on the same servers with no issue. I am getting desperate and planning to drop the mirror before my prodcution servers comes to a halt. Suggestions? I see the followin on the Principal server: Log Name: Application Source: MSSQLSERVER Date: 12/28/2010 10:28:16 AM Event ID: 1453 Task Category: Server Level: Error Keywords: Classic User: N/A Computer: DB.lp.local Description: 'TCP://SQLREPL.lp.local:5022', the remote mirroring partner for database 'UTOPIA', encountered error 5149, status 3, 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. and the following on the mirror server Log Name: Application Source: MSSQLSERVER Date: 12/28/2010 10:28:16 AM Event ID: 5149 Task Category: Server Level: Error Keywords: Classic User: N/A Computer: SQLREPL.lp.local Description: MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\UTOPIA_1.LDF '. Reply With Quote 12-28-10,15:22 #2 rdjabarov View Profile View Forum Posts Registered User Jo
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 the workings and policies of this site About Us Learn more about 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 http://www.dbforums.com/showthread.php?1663160-SQL-2008-Mirroring-running-out-of-disk-space-due-to-transaction-logs 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 http://dba.stackexchange.com/questions/40599/how-do-i-shrink-the-physical-transaction-log-file-when-its-the-principal-in-a-m 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 fails with the following error: The remote mirroring
0 Hi Experts, Principal Database of 300GB where log file consumed 150GB. Database participating in asynchronous mirroring with log files on different drive than principal server. When we check the log_reuse_wait_desc https://ask.sqlservercentral.com/questions/138463/mirrored-database-out-of-space-error.html we got database_mirroring and mirroring found in suspended error. Post resuming the mirror, http://www.sql-server-performance.com/forum/threads/mirroring-suspended.26912/ after some time, mirroring again changed its status to suspended and below error logged in error log. Error: 5149, Severity: 16, State: 3. MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'G Error: 1454, Severity: 16, State: severity: 16, 1. Database mirroring will be suspended. Server instance encountered error 5149, state 3, severity 25 when it was acting as a mirroring partner for database **. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages. Upon checking the disk space on mirror server, we found severity: 16, state: that 60GB space is still available but not sure why mirror database not able to grow. Please confirm below? Does SQL Server calculate the disk requirement before streaming the log records to mirror server or grow based on autogrow setting? Like it has 100GB pending transactions on principal but only 60GB left on mirror? Ideally, it should grow as it streams log records. What is the logic applied here? Thanks, Neeraj more ▼ 1 total comment 1468 characters / 231 words asked May 15 at 07:10 AM in Default NeerajTripathi 600 ● 7 ● 13 ● 18 edited May 20 at 01:41 PM Shawn_Melton 6.4k ● 21 ● 25 ● 34 what are your autogrowth settings? May 23 at 10:13 AM Kev Riley ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 0 answers: sort voted first ▼ oldest newest voted first Be the first one to answer this question toggle preview: Attachments: Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB tota
Discussion in 'SQL Server 2005 Database Mirroring' started by contactjimmyus, May 28, 2008. contactjimmyus Member I came to know that one of the server that was mirrored was suspended .I tried to resume mirroring by clicking the resume button and nothing happens on the screen .The db still in suspended mode .HOW TO FIND THE REASON FOR THE FAILURE .Is is due to transaction logs ?The logs are growing now and we have an hourly transaction log back up Please suggest so that I can resume mirroring . Thanks contactjimmyus, May 28, 2008 #2 satya Moderator Check the database mirroring monitor and SQL SErver error log on Principal & mirror servers to see why it is failing. satya, May 28, 2008 #3 contactjimmyus Member the mirroring history has status from 21st may to till date .But all shows as suspended Do i need to redo the mirroring since I dont have full transaction log from 25st.How to script out the current setting and redo it ,if needed contactjimmyus, May 28, 2008 #4 satya Moderator FIrst of all check the issue root cause from the Error logs. satya, May 28, 2008 #5 contactjimmyus Member The principal server has space issue and it was logged in the sql server log .The error desc from mirror server was (a45-u1 is the principal server and b45-x1 is the mirror server) the error was The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000002000 in file 'E:TLOGSRRHTLOGRRH_1.ldf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. Operating system error 21(The device is not ready.) on file "E:TLogsRRMTLogRRM_1.ldf" during ReadFileHdr. While acting as a mirroring partner for database 'RRI', server instance 'A45-U1' encountered error 5159, status 3, severity 25. Database mirroring will be suspended. Try to resolve the error and resume mirroring. contactji