How to dump transaction log in MSSQL 2008

The old command “DUMP TRANSACTION [db_name] WITH NO_LOG;” no longer works in SQL 2008 to dump the transaction log. Instead I found a GUI work around. I don’t like it as much but it works for now until I find something better. If I do, I’m not a DBA anyway.

1. Open SQL Management Studio and right click on the database you need to dump the trans log and click on properties.

2. Click on option from the left hand side and then click the drop down next to “recovery model” and set it to simple. Click ok.

sql08-simple

3. Right click on the database again and go to Tasks –> Shrink –> Database and click ok on the popup window. You can also read it and change some options but the defaults work fine. Do that for files as well (Tasks –> Shrink –> Files).

4. Right click on the database one last time and go to properties then options and set the recovery model back to full. Because if you leave it on simple and your SQL server crashes you would loose any transactions not flushed back into the DB.

3 Comments

 Add your comment
  1. Thank you & the script:

    ALTER DATABASE [DB_NAME] SET RECOVERY SIMPLE WITH NO_WAIT
    DBCC SHRINKFILE (N’DB_NAME’ , 0, TRUNCATEONLY)

  2. Thanks, that script should come in handy. I’ll add that to my notes. I’ve been using this:

    Use DBNAME
    GO

    Alter Database DBNAME Set Recovery Simple
    GO

    Alter Database DBNAME Set Recovery Full
    GO

    DBCC SHRINKFILE (‘DBNAME_log’, 1)
    GO

  3. Thanks, but if in a running mssql server can I run this script ?

    Frankly, my english is not good.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.