SQL Server Performance Forum – Threads Archive

Simple recovery & 9002 errors

So, I’ve got alerts defined on a 9002 (logfile full) event. When that happens on a SIMPLE recovery database a ‘BACKUP LOG FooDB WITH NO_LOG’ is executed. This works and truncates the log (yes, I understand the recovery issues involved) as desired. However, in the SQL errorlogs there is a record written that NO_LOG & TRUNCATE_ONLY are deprecated and we are warned not to use this operation. BOL makes no indication as to what method to truncate the t-log now. The documentation merely indicates to use SIMPLE recovery model when recovery isn’t a major issue (no duh, thanks). So, I can still do NO_LOG or NO_TRUNCATE, but I’m not supposed to and have no alternative to clear up a 9002 event…. <sigh> Anybody know what we’re supposed to do for a 9002 without expanding the t-log?

Hi, please refer BOL for error *9002* it clearly definded what to do and please search forum for relavent topic : http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6168</a> http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2691</a> http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3044</a> Explanation The transaction log file for the indicated database has run out of free space. Action The user action that is appropriate to you depends on your situation. Potentially, possible actions include: Backing up the transaction log Freeing disk space Moving the log file to a disk drive with sufficient space Adding or enlarging a log file These possible actions are discussed below. Regardless of which action you adopt, you should also follow them up by considering what caused the transaction log to fill. Likely causes include a long running transaction or a published transaction. To look for such transactions, use DBCC OPENTRAN. A long-running transaction prevents truncation and reclamation of transaction log space, which normally happens either automatically (under the Simple Recovery model) or as a result of taking a log backup (under the Full Or Bulk-Logged Recovery model). You may have to use the KILL statement. Use KILL very carefully, however, especially when critical processes are running. For more information, see KILL. If replication is turned on for the database and has fallen behind, a published transaction that has not been passed into the distribution database may be preventing log truncation. For information about replication and the transaction log, see Planning for Transactional Replication. The remainder of this section discusses possible actions, any one of which should suffice. Backup the transaction log If the database is using the Full or Bulk-Logged Recovery model, you should back up the transaction log immediately to free up space. If you are not taking log backups, you should either start taking log backups or switch to the Simple Recovery model. If the database is using the Simple Recovery model, backing up the transaction log is not possible. For more information on recovery models, see Using Recovery Models. Regardless of the recovery model, consider the following actions. Free disk space You may want to free disk space on whatever disk drive contains the transaction log file for the database. Freeing disk space allows the recovery system to enlarge the log file automatically. Move the log file to a disk drive with sufficient space If you cannot free sufficient disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space. If you choose to use another drive: After ensuring that the other drive has sufficient free space for the transaction log, detach the database by executing sp_detach_db. Detaching a database makes it unavailable until it is reattached. Move the transaction log files with insufficient space to the other drive. Attach the database by executing sp_attach_db, pointing to the moved log file(s). For more information see, Insufficient Disk Space. Adding or enlarging a log file Alternatively, you can gain space by adding an additional log file for the database or enlarging the existing log file (if disk space permits). To add a log file to the specified database, use the ADD FILE clause of the ALTER DATABASE statement. Adding an additional log file allows the existing log to grow. For information about adding files, see Adding and Deleting Data and Transaction Log Files. To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. For more information on these Transact-SQL clauses, see ALTER DATABASE. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

May refer these 2 KBAs for information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;230785
http://support.microsoft.com/?kbid=873235 I do not have access to SQL 2k5 at office, will check and post from home. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If you have enough space on any disk for transaction log backup you should do it (if i got right what is the problem). That will make space of completed transaction free for reuse, so you can continue to use existing transaction log file. I guess there was not major change in that area between 2000 and 2005.

Thank you for the replies and the links. Forget about the disk space issues and the long-running transaction detective work, this is a specific technical question of "How do I do this now". mmarovic, your suggestion of disk backup is not applicable in the case of a SIMPLE recovery model (if I understood you correctly). In that recovery model you cannot backup the log to a device, the server won’t let you. So, the question still stands, "How do I truncate the transaction log of a SIMPLE recovery database without expanding the logfile. Given that NO_LOG & NO_TRUNCATE are deprecated (but obviously still work)."

http://msdn2.microsoft.com/en-us/library/ms189085.aspx
http://msdn2.microsoft.com/en-us/library/ms135735.aspx http://msdn2.microsoft.com/en-us/library/ms186865.aspx
To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together. As of now on SQL 2005 you can use both the options alongwith BACKUP LOG in order to perform intermediate log truncation. I’m sure MS will modify the affect of SIMPLE recovery model effectively without any DBA intervention. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

quote:Originally posted by satya
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together. As of now on SQL 2005 you can use both the options alongwith BACKUP LOG in order to perform intermediate log truncation. I’m sure MS will modify the affect of SIMPLE recovery model effectively without any DBA intervention. Satya SKJ

This is more applicable to mirroring/log shipping processes where you are ‘flipping’ databases from a primary to a secondary/warm server and not specific to addressing the 9002 error. I guess I’ll just wait for MS to give us the details of how and continue to use my deprecated methods…

Then I’m interested to know the MS feedback, I don’t believe not more than they will say what has been documented in BOL [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

]]>