SQL Server Database Transaction Log Full

Problem

Your SQL Server database returns an error that your transaction log is full when you try to execute some SQL statements. You check the hard drive free space, and it's full; or perhaps the transaction log has reached its maximum configured size. You check your database's recovery model, and it's set to "Full", even though restoring from a previous night's backup is acceptable recovery in the event of failure.

Solution

Unless you change the default log file settings or database recovery model, the transaction log is a ticking time bomb that grows until disk space is full, rendering your database unusable. This quick solution makes space immediately available, and the long-term solution automates the clean-up of the transaction log file.

The quick solution

The quick solution, after ensuring you have backed up your database as needed, is to execute the following statements in SQL Studio to (1) remove all inactive logs from your database's transaction log, then (2) shrink the log file down to a reasonable size.

1. Purge all inactive logs from a database:
   BACKUP LOG <YourDBName> WITH TRUNCATE_ONLY
   
   For example:
   
   BACKUP LOG XyzWebsite WITH TRUNCATE_ONLY


2. Shrink the database's log to a certain number of MB:
   DBCC SHRINKFILE(<YourDBLogFileName>, <NumMegabytes>)
   
   For example, shrink the log to 100 MB:
   
   DBCC SHRINKFILE(XyzWebsite_log, 100)

Do not shrink frequently, as physical fragmentation impacts database processing time; so after doing this, consider the long-term solution to prevent this from happening again.

The long-term solution

The long-term solution to automatically purge inactive logs is to change the database recovery mode from "Full" to "Simple". As long as restoring from a previous night's full database backup is acceptable recovery for you: in SQL Studio, right-click the database, select Properties ► Options, then change Recovery model to Simple.

For further reading, see the following MSDN references:

• Backup Under the Simple Recovery Model
  http://msdn.microsoft.com/en-us/library/ms191164.aspx
• BACKUP (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ms186865.aspx
• DBCC SHRINKFILE (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ms189493.aspx

Inter-database Communication is Easy in T-SQL

Problem

You need to obtain data from one data source to process and store in another. You don't want your server-side-script to use two connection objects and be the slow middle-man between them, as in the diagram on the left.

Solution

If both data sources are databases on the same MS SQL Server, it's a piece of cake to do optimally with just one connection object and pure T-SQL.

Just do the following:

1. Set up Security - Add the user of the destination database as a user of the source database with at least db_datareader access.
   
   
2. Prefix foreign tables with Schema - In your SQL that writes to the destination-database, prefix all tables housed in the source(foreign)-database with their "fully qualified" position in the schema to indicate which database they reside in, as illustrated in the example below.
   
   

   Example

   Table tblSource is from of the ForeignDB database, but tblDestination is from the active database.
   insert into tblDestination (colA, colB)
   select col1, col2
   from [ForeignDB].dbo.tblSource