Home » Transaction Log

Transaction Log

sql server DBA 999

How to check Log shipping status in SQL Server 2008

How to check Log shipping status in SQL Server 2008:

SQL server recovery model has to be Full recovery mode for log shipping which allows log backups.

sql server database log backup is shiped and restore from one server or Primary to another server Secondary/destination server. Reports can be run from Secondary server for business needs. Using sql server nolock is recommended if dirty reads are allowed for reporting purposes.

This can be done by two methods:

1.By using the standard report on Instance level for Transaction log shipping status both on Primary and each secondary/destination servers individually.
On the Primary/source server, it shows when backup jobs succeeded.
On Secondary/Destination … Read the rest

sql server DBA 999

Why Copy-Only Backups taken in SQL Server?

A copy-only backup in SQL Server(any version like 2012 or 2008 or 2005) is taken in cases you do not want effect the sequence of conventional SQL Server backups.
Usually in using a normal Backup,the backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. copy-only backups serve this purpose.

Note: The transaction log is never truncated after a copy-only backup.

The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

Copy-only log backups (full recovery model and bulk-logged recovery model only)

Note:A copy-only full … Read the rest

sql server DBA 999

SQL Server 2008/2005 – Comparing Index rebuild/Reorganize and increase in Transaction Log file

Comparing ALTER INDEX options Index Rebuild and Reorganize:

1.Index Rebuild requires building the new index before dropping the old one. This means there has to be enough free space in the database to accommodate the new index; otherwise the database will grow to provide the required free space. This can be problematic for large indexes. Index Reorganize only requires 8KB of additional space in the database.
2.Index Rebuild can use multiple CPUs so the operation runs faster. Index Reorganize is always single-threaded.
3.Index Rebuild may require long-term locks on the table that can limit concurrent operations. Index REORGANIZE doesn’t hold blocking locks and it’s always been an online operation.
4.Index Rebuild can use minimal-logging to reduce transaction log growth. Index … Read the rest

sql server DBA 999


BACKUP LOG WITH TRUNCATE_ONLY is no longer a recognized BACKUP option. This command get rid of the contents of your SQL Server’s transaction log without really backing it up. This command will to invalidate the point in time recoverability of the database as this breaks the log chain.

This command is used by Database administrators when they end in situations where getting rid of the transaction log is only way by which he can shrink their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.

Microsoft always recommends that DBA’s must switch to simple recovery mode getting rid of the transaction logs instead of truncating logs by above command if no Point in Time recovery is required. … Read the rest

sql server DBA 999

SQL Server 2008 Recovery Models and Transaction log usage

SQL Server 2008 Recovery Models
In SQL Server 2008 there are 3 recovery models you can use: simple, bulk-logged, and full. These recovery models really just define how log management behaves so you can have what you need to perform your desired backups.
As the name implies the focus is really on recovery of databases in case of Disaster occurs.

Simple Mode

If a database is set for Simple Recovery mode than it means that you don’t bother to take log backups. Only Backups available will be Full Backups of Database which you will use in case of Disaster occurs. SQL Server forces the log to truncate when the database writes data to the disk (we call that a checkpoint). … Read the rest

sql server DBA 999

SQL Server Raid Configurations and Recommendations

A SQL Server DBA given choice to select his Disk configuration should know the different Raid configurations and should select them appropriately.

Common RAID Levels used are:
RAID 0 which is also called a strip set. It is Very fast but have no redundancy.
A RAID 0 array must have 2 or more disks. A RAID 0 array has no redundancy. As data is written to the array it is spread across the disks for maximum speed. This RAID level should never be used for a SQL Server. In the event of a failure of a single disk in the array all data is lost. To calculate the space created from a RAID 0 array multiple the number … Read the rest

sql server DBA 999

Transaction log file VLF management

One of the important things I would recommend is not having transaction log VLFs be too large, with 500MB being recommended maximum, so that it is not to interrupt the clearing/truncating of the log.The Reason for this is that transaction log could only clear a VLF when TLog Backup is taking place only when it’s completely inactive. So that means larger the VLF then bigger VLF to be cleared and for it to inactive.

Other important thing I would recommend is not having too many transaction log VLFs. Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur. Often, when transaction logs are … Read the rest