Emergency Operation - Truncate Transaction Log
=============================================
In case the TX Log has grown in size, until it occupies 100% of the disk, it is needed to Truncate the log file.
This is an emergency operation, as the truncated data is not being backed up.
It is a (VERY) good idea to perform a full backup, once the TX Log Truncate operation has finished.
Steps:
1. Move the database Recovery Model to Simple
1. Move the database Recovery Model to Simple
SQL Server Management Studio -> Server -> Databases -> Select Database -> Properties -> Options -> Recovery Model -> Change from Full to Simple
use jiradbdev
GO
ALTER DATABASE jiradbdev SET RECOVERY FULL;
GO
2. Truncate/Shrink the TX Log
Only for SQL Server 2008 and older
BACKUP LOG jiradbdev WITH TRUNCATE_ONLY
For SQL Server 2012 and newer
Shrink the Transactions Log to 1Gb
Shrink the Transactions Log to 1Gb
DBCC SHRINKFILE(N'jiradbdev_log',1000)
GO
GO
3.
Change back, the Recovery Model to Full
ALTER DATABASE jiradbdev SET RECOVERY FULL;
GO
=============================================GO
Why the Transactions Log is growing big?
=============================================
Common causes:
- Open Transactions (i.e. Uncommited Transactions)
To check current status of open transactions run DBCC OPENTRAN
- Very Big Transactions
Log records in the transaction log files are truncated on a transaction-by-transaction basis.
If the transaction scope is large, that transaction and any transactions started after it are not removed from the transaction log unless it is completed.
This can result in large log files.
- Index Rebuild
When Running DBCC DBREINDEX and CREATE INDEX
When Running DBCC DBREINDEX and CREATE INDEX
-Client applications do not process all results
If you issue a query to SQL Server and you do not handle the results immediately, you may be holding locks and reducing concurrency on the server.
SQL Server is reading data in Pages, with shared lock on each page.
The shared lock is held until the client requests all of the data.
- In the Replication Setup
The transaction log size of the publisher database can expand if you are using replication.
Transactions that affect the objects that are replicated are marked as "For Replication."
These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them.
Keep The Transaction Log size under control
=============================================
Some Theory
The oldest log record (referred to as the MinLSN record) that is still required for a successful database wide rollback, or is still required by another activity or operation in the database marks the start of the active log.
The MinLSN log record could be the one marking the start of the oldest open transaction, or the oldest log record still required for a log backup, or the oldest log record still required by another database process, such as database backup, mirroring or transactional replication.
This explains why a long-running uncommitted transaction or an application leaving "orphaned transactions" in the database can keep large areas of the log "active" and so prevent log truncation.
For a FULL and BULK_LOGGED recovery model database, only a log backup will result in truncation of the log (i.e. enable reuse of space in the log).
When a log backup occurs, SQL Server can reuse the space in any portions of the log that are marked as "inactive".
To free up space claimed by TX Log to the host OS, need to run DBCC SHRINKFILE command.
Reference
Managing the SQL Server Transaction Log
Why is my transaction log full?
Actual commands to execute:
1. Truncate the TX Log
BACKUP LOG my_database WITH TRUNCATE_ONLY
3. Shrink the Transactions Log to 2Gb
DBCC SHRINKFILE(my_database_log,2000)
Create a database backup
=============================================
From SQL Server Management Studio:
Databases -> Select DB -> Right Click -> Tasks -> Back Up -> Backup Dialog Window
In Backup Dialog Window Select:
Backup type = Full.Backup component, = Database.
Copy Only Backup = check.
This is useful if the backup is out of sequence for regular backups
Destination = Select the path/name.bak
There might be a limit to backup to NFS, In this case backup to local disk, then move to another destination.
In T-SQL:
BACKUP DATABASE [mydb] TO DISK = N'D;\Backups\mydb_01.bak' WITH NOFORMAT, NOINIT, NAME = N'mydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
=============================================
Change backup destination
=============================================
Open up SQL Server Management Studio.
In the Object Explorer pane, right-click your database instance and click Properties.
On the left, select the Database Settings page.
Change the Backup entry to the desired backup folder, then click OK.
Restart the SQL Server service.
=============================================
SQL Serer simple Backup script
=============================================
Inside SQL Server Ajent, define a new job with several steps:
master_db_backup
BACKUP DATABASE [master] TO DISK = N'C:\SQL_SERVER_BACKUP\FULL\master_db.bak' WITH NOFORMAT, NOINIT, NAME = N'master-Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
master_db_jiradbdev
BACKUP DATABASE [jiradbdev] TO DISK = N'C:\SQL_SERVER_BACKUP\FULL\jiradbdev_db.bak' WITH NOFORMAT, NOINIT, NAME = N'msdb-Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
In addition, define a new schefuled task to move backups from default location and delete old backups.
move_to_history.bat
ECHO OFF
REM =========================================
REM Keep up to N backups per database
REM =========================================
SET WORK_DIR=C:\SQL_SERVER_BACKUP
SET FULL_BACKUP_DIR=%WORK_DIR%\FULL
SET LOG_BACKUP_DIR=%WORK_DIR%\LOG
cd %WORK_DIR%\FULL
REM ===================
REM master
REM ===================
MOVE %FULL_BACKUP_DIR%\master_db.bak_03 %FULL_BACKUP_DIR%\master_db.bak_04
MOVE %FULL_BACKUP_DIR%\master_db.bak_02 %FULL_BACKUP_DIR%\master_db.bak_03
MOVE %FULL_BACKUP_DIR%\master_db.bak_01 %FULL_BACKUP_DIR%\master_db.bak_02
MOVE %FULL_BACKUP_DIR%\master_db.bak %FULL_BACKUP_DIR%\master_db.bak_01
REM ===================
REM model
REM ===================
MOVE %FULL_BACKUP_DIR%\model_db.bak_03 %FULL_BACKUP_DIR%\model_db.bak_04
MOVE %FULL_BACKUP_DIR%\model_db.bak_02 %FULL_BACKUP_DIR%\model_db.bak_03
MOVE %FULL_BACKUP_DIR%\model_db.bak_01 %FULL_BACKUP_DIR%\model_db.bak_02
MOVE %FULL_BACKUP_DIR%\model_db.bak %FULL_BACKUP_DIR%\model_db.bak_01
REM ===================
REM msdb
REM ===================
MOVE %FULL_BACKUP_DIR%\msdb_db.bak_03 %FULL_BACKUP_DIR%\msdb_db.bak_04
MOVE %FULL_BACKUP_DIR%\msdb_db.bak_02 %FULL_BACKUP_DIR%\msdb_db.bak_03
MOVE %FULL_BACKUP_DIR%\msdb_db.bak_01 %FULL_BACKUP_DIR%\msdb_db.bak_02
MOVE %FULL_BACKUP_DIR%\msdb_db.bak %FULL_BACKUP_DIR%\msdb_db.bak_01
REM ===================
REM jiradbdev
REM ===================
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak_03 %FULL_BACKUP_DIR%\jiradbdev_db.bak_04
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak_02 %FULL_BACKUP_DIR%\jiradbdev_db.bak_03
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak_01 %FULL_BACKUP_DIR%\jiradbdev_db.bak_02
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak %FULL_BACKUP_DIR%\jiradbdev_db.bak_01
--Backup Logs
BEGIN
DECLARE @backup_file varchar(1000);
DECLARE @BACKUP_LOG_FILDER varchar(30) = 'C:\SQL_SERVER_BACKUP\LOG\';
DECLARE @DB_NAME varchar(100) = 'jiraprod_new';
DECLARE @LOG_NAME varchar(100) = 'jiraprod_new_log';
SELECT @backup_file = @BACKUP_LOG_FILDER + 'LOG_'+@DB_NAME+'_'+(FORMAT ( GETDATE(), 'yyyyMMdd_HHmm'))+'.trn';
--PRINT @backup_file;
BACKUP LOG @DB_NAME TO DISK = @backup_file WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25;
USE jiraprod_new;
DBCC SHRINKFILE (@LOG_NAME, 1000);
END;
