How to shrink the transaction log

[Origin]: https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/

By: 

One thing that I see a lot of administrators ask about is transaction log size and how to truncate it. Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database. Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.

Log truncation frees up space in the log file so the transaction log can reuse it. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model) or after a log backup (if the database is in FULL or BULK-LOGGED recovery model). MSSQLTips.com offers plenty of tips regarding transaction log truncation, but I’ll show you two ways to shrink the log.

Shrink the log in SQL Server Management Studio

To shrink the log in SSMS, right click the database, choose Tasks, Shrink, Files:

Shrink the log in SQL Server Management Studio

On the Shrink File window, change the File Type to Log. You can also choose to either release unused space, reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same filegroup:

On the Shrink File window, change the File Type to Log.

Shrink the log using TSQL

If the database is in the SIMPLE recovery model you can use the following statement to shrink the log file:

DBCC SHRINKFILE (AdventureWorks2012_log, 1)

Replace AdventureWorks2012_log with the logical name of the log file you need shrunk and change 1 to the number of MB you want the log file shrunk to.

If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL if you don’t care about losing the data in the log.

ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks2012_log, 1)
GO
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL

**You can find the logical name of the log file by using the following query:

SELECT name FROM sys.master_files WHERE type_desc = 'LOG'

Another option to shrink the log using the FULL recovery model is to backup the log for your database using the BACKUP LOG statement and then issue the SHRINKFILE command to shrink the transaction log:

BACKUP LOG AdventureWorks2012 TO BackupDevice
Advertisements

SQL Server Management Studio – tips for improving the TSQL coding process – Stack Overflow

[Origin]: https://stackoverflow.com/questions/101079/sql-server-management-studio-tips-for-improving-the-tsql-coding-process

community owned wiki Answer – feel free to edit or add comments:

Keyboard Shortcuts

  • F5CTRL + E or ALT + X – execute currently selected TSQL code
  • CTRL + R – show/hide Results Pane
  • CTRL + N – Open New Query Window
  • CTRL + L – Display query execution plan

Editing Shortcuts

  • CTRL + K + C and CTRL + K + U – comment/uncomment selected block of code (suggested by Unsliced)
  • CTRL + SHIFT + U and CTRL + SHIFT + L – changes selected text to UPPER/lower case
  • SHIFT + ALT + Selecting text – select/cut/copy/paste a rectangular block of text

Addons

Other Tips

  • Using comma prefix style (suggested by Cade Roux)
  • Using keyboard accelerators (suggested by kcrumley)

Useful Links

share