none
How to truncate transactional log RRS feed

Answers

  • Hi Avyayah,

    By the way, it is recommended to have full backup of all databases.

    Because if you want to accomplish any type of full restore of a database, you must make a full database backup occasionally to use as a starting point for other types of backups.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Avyayah Tuesday, November 26, 2019 9:41 PM
    Monday, November 18, 2019 7:04 AM

All replies

    1. BACKUP DATABASE YOurDBName TO DISK = 'C:\wheretoputfile\backupfilename.bak'

      2.  Truncate your log file
      BACKUP LOG YourDBName WITH TRUNCATE_ONLY

      3.  Shrink the log file
      DBCC SHRINKFILE('logname', 0)

    Do you think these steps will help us to resolve the issues?

    Friday, November 15, 2019 9:36 PM
  • Emergency use:

    Backup LOG YourDBName to disk = 'C:\wheretoputfile\backupfilename.trn' WITH Compression

    Note that this is a LOG backup, not a DATABASE backup.

    Ideally, you need to schedule this to happen regularly, to a filename that includes a date and time.  SQL Server provides a built in way to do this called Maintenance Plans.  there are numerous other ways (free scripts, 3rd party tools) as well.

    I did a video on this topic that may help as well:

    https://youtu.be/Fh6e2xjFsv8



    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.


    Friday, November 15, 2019 10:03 PM
  • Thank you Kevin for your quick response but the database cannot be backed up. I am also watching YouTube link.

    How should I proceed without backup?

    Friday, November 15, 2019 11:19 PM
  • The LOG needs to be backed up (says so in the screenshot....LOG_BACKUP), not the Database, in order to get you out of this situation.

    How big is the .LDF file for this database on disk?

    Also, is this a production database?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Friday, November 15, 2019 11:49 PM
  • Yes, this is production. All this happened because from 9th Nov log files are not backed up since commvault was failing to backup because of tape issue.

    I am running a job to backup all logs but this will probably take sometime. I should have probably used your

    suggested method:

    Backup LOG YourDBName to disk = 'C:\wheretoputfile\backupfilename.trn' WITH Compression

    Saturday, November 16, 2019 12:01 AM
  • Data: 442568, Log 390447 MB
    Saturday, November 16, 2019 12:03 AM
  • Can I close this job now and just execute on few databases:

    Backup LOG YourDBName to disk = 'C:\wheretoputfile\backupfilename.trn' WITH Compression

    Saturday, November 16, 2019 12:05 AM
  • I see transactional logs saved for 3 databases from today @ 8 am and 2 am
    Saturday, November 16, 2019 12:11 AM
  • If there is a job running a log backup, let it finish would be my typical recommendation

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Saturday, November 16, 2019 1:01 AM
  • Did this finish?

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Saturday, November 16, 2019 5:42 PM
  • Hi Avyayah,

    Log truncation and shrink log file are two different things.

    And 'BACKUP LOG WITH TRUNCATE_ONLY' has been depreicated, I don't suggest to do it.

    why don't you tell us your recovery mode and log_reuse?

    select log_reuse_wait_desc,recovery_model_desc,name From sys.databases

    I suggest to read this article 

    https://docs.microsoft.com/en-us/previous-versions/technet-magazine/dd392031(v=msdn.10)?redirectedfrom=MSDN


    https://sqlserver.code.blog/

    Sunday, November 17, 2019 12:57 AM
  • Hi Kevin,

    Yes it finished finally last night and as suggested by you I enabled the job and I see the disc now has space.

    Will keep the job running until commvault error is corrected. Thank you for the suggestion.

    Sunday, November 17, 2019 6:01 AM
  • Sunday, November 17, 2019 6:08 AM
  • Hi Kevin,

    Yes it finished finally last night and as suggested by you I enabled the job and I see the disc now has space.

    Will keep the job running until commvault error is corrected. Thank you for the suggestion.

    Feel free to close this out, with my suggestion as the answer :)

    Glad you got past the issue


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Sunday, November 17, 2019 4:07 PM
  • Hi Kevin, I will leave this thread open until tomorrow. Since this is a weekend I did not make too many changes to the server.

    Steps taken since Friday:

    Could not backup the database so executed the following step

    1. Backup LOG [Database_Archive] to disk = 'J:\Database_log.trn' WITH Compression

    2. Enabled the log backup job and tried to backup few databases since there is drive space issue.

    3. From your YouTube Link learnt that I can shrink the logfiles or change the recovery mode to Simple and that will truncate the logfiles. But did not perform this step since I do not have full backup of all databases. 

    Sunday, November 17, 2019 7:55 PM
  • Hi Avyayah,

    By the way, it is recommended to have full backup of all databases.

    Because if you want to accomplish any type of full restore of a database, you must make a full database backup occasionally to use as a starting point for other types of backups.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Avyayah Tuesday, November 26, 2019 9:41 PM
    Monday, November 18, 2019 7:04 AM