In this article we look at the process of shrinking transaction log files in Ms SQL Server
SQL Server allows you to shrink log files to effectively free up space in quick time. It might happen that your log files do not make use of the entire space available in the transaction log files, to make full use of the available space, you can opt for shrinking the log files. Usually you can shrink your log file only when your database is online, or a single, virtual log file is not in use. Sometimes you may not able to shrink a log file till, after the truncation of the next log. The process of shrinking log files involves occupying unoccupied spaces in the front of file by moving pages located in the end of file, thus recovering space.
Things to Keep in Mind while Shrinking Transaction Log Files
Shrinking the transaction log file is one of the best and most preferred way of making full use of the unused spaces, however, the process is not as simple as it sounds and a few key points need to be considered before implementing it.
- One of the greatest limitation of the process is that, it cannot be used to make a primary data file concise than the given size of the primary file present in the model database.
- When one shrinks a file, the data can fit into any available space, thus becoming scattered and causing index fragmentation. This will not only slow down your overall operations but also make things more confusing. To avoid fragmentation, it is recommended to rebuild indexes after shrinking the log file.
- To be able to use the feature, you will require membership in the relevant sysadmin fixed server role or alternately in the db_owner fixed database role. These are the necessary permissions.
Shrinking the transaction log in SQL Server can be of great use if implemented properly, while implementing any kind of feature that make changes in the database, the DBA should be extra careful and keep all the important points in mind.
Steps for Shrinking the Transaction Log File
SQL Server allows you to shrink log files in two different ways, either by making use of the Transact SQL or the SQL Server Management Studio. In this article we will describe the steps for shrinking transaction log files using T- SQL.
- For shrinking log files or data by using the T-SQL, you will first have to establish a connection with the Database Engine.
- In the appearing Standard Bar, select New Query
- Copy-paste the given example in query window, and press Execute.
USE TestDB; GO DBCC SHRINKFILE (DataFile1, 8); GO
Essentially it reduces the size of the data file to 8 MB. You can change database name and filename as required and also specify the size of the output file.
By following this very simple procedure you will be able to shrink your data or log files, thus freeing up space, making processes faster and also making optimum use of the application without any SQL damage.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair accdb damage and sql recovery software products. For more information visit www.datanumen.com