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.
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.
Support Meh
Blogroll
Categories
Calendar
| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| « Jan | ||||||
| 1 | 2 | 3 | 4 | 5 | ||
| 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 27 | 28 | 29 | ||||
April 19th, 2011 - 20:10
Thank you & the script:
ALTER DATABASE [DB_NAME] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (N’DB_NAME’ , 0, TRUNCATEONLY)
April 20th, 2011 - 12:26
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