Do you have a big database ?
Your transactions is very slow?
When the transaction logs become too large. Full transaction logs can make your Microsoft SQL Server database unusable.Since the speed of transactions are limited with the speed of writing in the log file, you have to manage log file space and grownig.
Reduce the transaction log size
To recover from a situation where the transaction logs are full, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file.
Note The transaction logs are very important to maintain the transactional integrity of the database. Therefore, you must not delete the transaction log files even after you make a backup of your database and the transaction logs.
Truncate the inactive transactions in your transaction log
When the transaction logs are full, you must immediately back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.
Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.
For steps to backup your database transaction log review the following:
How to backup transaction log
Shrink the transaction log file
The backup operation or the
Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file.
Note The DBCC SHRINKFILE Transact-SQL statement cannot truncate the log and shrink the used space inside the log file on its own.
For steps to shrink your database transaction log review the following:
(can be used on log files and data files)
How to shrink the transaction log file
Examples:
1- Shrinking a data file to a specified target size
The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO
2- Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks2008R2 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
3- Truncating a data file
The following example truncates the primary data file in the AdventureWorks2008R2 database. The sys.database_files catalog view is queried to obtain the file_id of the data file.
USE AdventureWorks2008R2;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);
Now you can continue work with your Database.
