Full Backup Does Not Truncate Transaction Log

This discussion assumes a database Recovery Model of “Full,” which is the normal recommendation for production databases.  This setting allows the backup of the transaction log so that you can recover up to the point of failure by restoring log backups in order.  A Recovery Model of “Simple” will automatically truncate the transaction log periodically, but you are not allowed to backup the log in this mode, so you can only recover using the last database backup, which implies potential loss of transactions.  This setting is usually reserved for test or read-only databases.

Backing up the transaction log will not only backup the latest committed transactions within the log but will also truncate the log file. Truncation means that the transactions that were backed up are removed from the log file, freeing up space within the log file for new transactions.  The truth is, if you don’t backup the transaction log, it will continue to grow forever, until you run out of disk space.

New DBAs to SQL Server assume that the Full Database Backup truncates the transaction log – it doesn’t.  It does take a snapshot of the transaction log at the very end, so that transactions committed during a long-running full backup are actually backed up, too (which is quite clever), but it does not truncate the log.  Well, the long-term solution is to backup the transaction log frequently. This will keep the transaction log “lean and mean.”  But how frequent is frequent?  Well, it depends.  Generally, we try to keep the transaction log under 50% of the size of the data files.  If it grows beyond this, then we back it up more frequently. This is why, for some very active databases, we may backup the log every 15 minutes.   As I said, it depends.

Identifying SQL Server Port Number

This article provides several ways to identify the TCP/IP port number used by SQL Server.

Find the Running Port Number through Query

The following query provides a direct answer about TCP/IP port used by the SQL Server, as well as the SQL Server’s IP address.

SELECT DISTINCT local_net_address, local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_net_address IS NOT NULL;

Find the Running Port Number through xp_readerrorlog

The command xp_readerrorlog read the SQL Server logs, which consists of SQL Server boot log, to find the SQL Server listening port through keywords in the boot log.

USE master
xp_readerrorlog 0, 1, N'Server is listening on'

Get the Running Port from the TCP/IP Properties

  1. Open the SQL Server Configuration Manager
  2. In the SQL Server Configuration Manager, under the SQL Server Network Configuration, select your SQL Server instance.
  3. After that, right click on TCP/IP and select Properties. You will find the SQL Server running port on the following screen.