Blog Page

Why is My Log File so Big??

sqlsafety 0 Comments

This is probably one of the most common posts that I see on the MSDN forums and hear from students.  The most common answer is that the database has been set to full or bulk logged recovery model and a transaction log backup has not been completed.  So let’s take a VERY simplistic look at log growth and what to do to control it.  To best demonstrate this let’s first create a database and look at the log and data file sizes as well as the log_reuse_wait_desc:
<style=”font-size: 12px;”>
USE MASTER;
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘LogGrowth’)
BEGIN
DROP DATABASE
LogGrowth
END

CREATE DATABASE LogGrowth;
GO

ALTER DATABASE LogGrowth SET RECOVERY SIMPLE;
GO

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

SELECT name,
size
FROM LogGrowth.sys.database_files;
–Log size = 98 Data Size 392
So the database is set to simple recovery model which means that the inactive portion of the log file is truncated upon checkpoint.  This can be validated by simulating transactions:
<style=”font-size: 12px;”>–Transactions
USE LogGrowth;
GO

IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = ‘Transact’)
BEGIN
CREATE TABLE
Transact(
col1   CHAR(50)
);
END
GO

DECLARE @i INT = 50000
WHILE @i > 0
BEGIN
INSERT
Transact
SELECT ‘This is row number ‘ + CONVERT(CHAR(4), @i);

SET @i  = 1
END

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

SELECT name,
size
FROM LogGrowth.sys.database_files;
–Log size 98 Data size 776

Kewl!!  So the data file has grown, as is to be expected, but the log file has remained the same.  So what happens if the database recovery model is changed to full?  Well just changing the recovery model will have no immediate effect on the transaction log growth until a database backup is made, refer to my post on pseudo-simple recovery.  Once a backup is taken, however, the game is on!!
<style=”font-size: 12px;”>ALTER DATABASE LogGrowth SET RECOVERY FULL;
GO

BACKUP DATABASE LogGrowth
TO DISK = ‘C:\Backup\LogGrowthFull.bak’;
GO

DECLARE @i INT = 50000
WHILE @i > 0
BEGIN
INSERT
Transact
SELECT ‘This is row number ‘ + CONVERT(CHAR(4), @i);

SET @i  = 1
END

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO
–NOW WE need a LOG_BACKUP!!!

SELECT name,
size
FROM LogGrowth.sys.database_files;
GO
–Log Size 25352 Data Size 1160

The log_reuse_wait_desc now shows as LOG_BACKUP and the log file has obviously grown, in fact it is substantially larger than the data file.  OK so now how can we get the log file to a more manageable size???  Good question!!  Lets go through some answers that I see posted.

 

1. Backup The Log

With the log_reuse_wait_desc showing LOG_BACKUP the obvious first action should obviously be to backup the transaction log.  Transaction log backups can prevent the log files from growing too large, but do not reclaim used space
<style=”font-size: 12px;”>
BACKUP LOG LogGrowth
TO DISK = ‘C:\Backup\LogGrowth.trn’;
GO

–So what is our log size now?
SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO
–Back to nothing

SELECT name,
size
FROM LogGrowth.sys.database_files;
GO
–Log Size 25352 Data Size 1160 NO CHANGE IN LOG?!?!?
So the first question that comes out is “Why is the log file the same size?  I backed it up?!?  Why didn’t it truncate?!?”  The answer is that logging works in a circular manner and is allocated with VLF‘s, virtual log files.  After the log backup was taken the log is not truncated, but the VLF’s that can be are marked as free and can then be used to record new transactions.  The log file growing means that new VLF’s are added upon truncation they are not deleted, but rather marked as free and then reused.

The above transaction log backup has marked the VLF’s as free, but it does not delete these, executing DBCC LOGINFO will show the VLF status.  So what was the point in backing up the log?  If you don’t the log file will grow until all disk space is consumed!!!

 

2. Change the Recovery Model to SIMPLE

Makes sense right??
<style=”font-size: 12px;”>ALTER DATABASE LogGrowth SET RECOVERY SIMPLE;
GO

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

SELECT name,
size
FROM LogGrowth.sys.database_files;
GO
–Log Size 25352 Data Size 1160
–It’s simple but still LARGE!!

Again the file size is the same.  Changing the recovery model to SIMPLE will insure that the log file sill truncate inactive VLF’s upon checkpoint but it does not reclaim disk space.   I KNOW!!!  Truncate in this sense is a bit deceiving.

THIS AFFECTS YOUR BACKUP/RESTORE CHAIN!!!

PLEASE remember that changing the recovery model to SIMPLE is an acceptable means of managing log space during events such as bulk imports and initial database population, but it will affect your backup/recovery process.  Simplistic example is a database that is set to full recovery model and a full backup is done at midnight and transaction log backups done every hour.  At noon the database recovery model is changed to Simple in order to manage logging and truncation on checkpoint for a bulk load of data.  Once complete at 12:30 the recovery model is set back to full.  All is well in the world until 1:00 when the scheduled transaction log backup runs.  The backup fails because SQL tells you there is no full backup that has been done.  True enough that a full backup ran at midnight, but by setting the recovery model to simple at noon basically started the backup/recovery over.  Since transaction log truncation occurs upon checkpoint in simple recovery the backup chain ended at noon when the recovery model changed to simple.  The first thing that should have been done at 12:30 after setting the recovery model to full was a full backup.

 

3. Detach the Database, Delete the Log, Reattach the Database Creating a new Log File

STOP and think about this!!  Can it be done?  Yep.  Should it be done? Nope.  There is the distinct possibility that you will not be able to use the database again.  Has this been used successfully?  Yes, but is the risk of total loss worth rolling the dice.  This is a LAST resort and only in extreme and a full backup should be done before attempting.

 

4. DBCC SHRINKFILE

DBCC SHRINKFILE can be used to shrink a data file, not necessarily recommended, and log file.  There are obviously considerations to this operation some of which are outlined here.

More information can be found on MSDN here.

The sample T-SQL code displayed above can be downloaded here.

 

Dealing with Duplicates in the Data Flow

sqlsafety 0 Comments

I frequently see posts in the MSDN forums requesting assistance in removing duplicate values from a source, other than SQL, to be entered into a SQL Server destination.  There are several options to accomplish this, but this post is meant to address the easiest and most straight forward.  I first want to completely outline the business need to provide the appropriate context.

1. The source is NOT SQL SERVER

2. The requirement is that no duplicate values can be inserted into the SQL Server source

As I already mentioned there are numerous way to accomplish this task.  The first method I often see referenced is to import the data first into a staging table/database and use a T-SQL method to remove duplicates, ROW_NUMBER(), DENSE_RANK(), etc.  This is a completely viable and acceptable method, and coming from the SQL world easy to support and manage.  The only concern that I have with this is the use of an intermediary step, with its associated resources, when it is not really necessary.  The quickest way to remove duplicates is to use a Sort transformation in the data flow and select the Remove rows with duplicate sort values.

Sort

This is the easiest way to remove duplicates, but it comes at a costs.  The Sort transformation is a fully blocking transformation, which means  that it can only work with the full data set in memory.  This seems pretty obvious since in order to sort the results all the results must be present, but the larger the data set that you are working with the more memory will be needed.

Another option is to use a Lookup transformation, which performs similar to a join in T-SQL.  In the lookup transformation configuration you can specify:

1. Cache mode

a. Full cache

b. Partial cache

c. No cache

2. Connection

3. Columns

So let’s consider a simplistic example to outline using a lookup to insert unique rows.  Consider that you are importing 1,000 records from a flat file source into a SQL Server table.  Each record contains an ID, first name, and last name and you must insure that none of the records already exist in the table.  To facilitate the example I am going to use a data flow task to select 1,000 random records from the Adventureworks2012.Person.Person table and insert them into a delimited file.

random

 

RandomSource

 

Within my package I will use an execute SQL task to create a database called RandomDB and a table called People that will be used to import the flat file.

ExecuteSQL

Next in a data flow I will populate 15,000 records from the Adventureworks2012.Person.Person table into the RandomDB.dbo.People table.

RandomPeople randomflow

The ground work has been set and we can now dive into the example.  We must import the records from the flat file that contains 1,000 records into the RandomDB.dbo.People table insuring that we do not insert records into the table that already exist.  If the source was SQL this would be easy enough with an INSERT statement selecting only the records that did not already exist in the table, but since the source is a flat file we are unable to do that.  The first step is to create a data flow task that will use the flat file as the source.

nodups

FlatSource

Now we will use the lookup transformation with a connection to the RandomDB.dbo.People table and specify that the BusinessEntityID column in the people table is equal to the BusinessEntityID in the flat file.

General

 

Connectipn

Columns

The lookup has now been configured to match the records from the table and flat file using the BusinessEntityID, the primary key in this case.  You will also notice that the lookup supports matching multiple columns.  So consider that the records coming in may only have the first and last name, but we still need to insure that there are no duplicate records inserted into our table.  We could configure the columns to match both first and last name.  The lookup transformation has two outputs, Lookup No Match Output and Lookup Match Output.  Since the entire premise of this exercise is to insure that there are no duplicate records entered into the RandomDB.dbo.People table the Lookup NoMatch Output will be mapped to our RandomDB.dbo.People destination and I will use a flat file destination for the matched records, this is for demonstration only as for a real production package I would leave the Matched output unmapped.

DataFlow

As you can see the Lookup acts as an equi-join between the flat file source and the table that will receive the records.  By using the unmatched output you insure that only records that do not exist in the destination are inserted while the duplicates are sent down a different path.

I create a sample package to demonstrate the usefulness of a lookup transformation and included all of the tasks necessary to run this package in a local environment and delete all associated sample files, databases, and tables in the package.

Package

This sample package is available for download here.

 

 

What are The Different Types of Database Backups?

sqlsafety 0 Comments

If you are just beginning SQL Server database administration then you might not be familiar with the different types of database backups that are available and exactly what they backup.  This post is going to outline the different types of backups and specifically what they backup, but keep in mind that your backup plan should be based on your recovery needs.  You must consider RTO, recovery time objective, and RPO, recovery point objective, when considering the type and frequency of you backups.  Another important consideration is the database recovery model, simple, bulk logged, or full.  The recovery model will dictate when and how a transaction log is truncated as well as if a transaction log backup can be taken and if a database can be restored to a point in time.

Full Database Backup

The first type database backup that most everyone is familiar with is a full backup.  When a full database backup is taken then all data files, file groups, and transaction logs are backed up.  A full database backup provides the ability to restore the database to the state that it was at the time of the backup.  Quite often I will see posts in the forums that ask why a database backup is 20GB while the database is 50GB and backup compression was not used.  A native full database backup will backup the actual data, but not empty data pages.  This means that although the database is 50GB 30GB is unused space.  When the backup is restored the backup will request the full file sizes, but it does not write empty data pages.

In simple recovery model then each full database backup is autonomous and no log file backup need to be done, and in fact cannot be done.  In simple recovery the log file is truncated upon checkpoint thereby keeping the log file a manageable size.  In full recovery or bulk logged recovery the log file WILL ONLY BE TRUNCATED WITH A TRANSACTION LOG BACKUP.  The only exception to this is for a database that is in full or bulk logged recovery model that has not been backed up, see pseudo-simple post.

 IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–Fulldatabase backup
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’
GO

File Group Backup

Every database will have a primary file group which will contain the primary data file.  Consider a file group a folder that can contain zero to many data files.  Tables and indexes are created on file groups and the file group has data files that these objects are then stored on.  Consider a large database that contains huge amounts of historic data as well as current data that is constantly changing.  The use of file groups allows you to create tables that will contain historic data on separate physical disks than tables that contain the current data.  This helps to separate your high disk reads on one disk and high disk writes on another.  For a backup strategy you may have identified that the historic data only changes at the end of the year when the past years data is migrated from the current file group to the historic file group.  A file group backup will back up ALL the files contained in the file group.  If you are using file group back ups as a means of a granular backup and restoration keep in mind that you MUST have a backup of the Primary file group.   If the primary file group becomes corrupt and you do not have a backup to restore it than the database will not be recoverable (There are ways that you can work around this, but it goes beyond the scope of this post).

File group backups can be taken from a database with a simple, bulk logged, or full recovery model.  The transaction log growth and truncation are the same as noted in the full recovery model.  File group backups can be run with or without a pre-existing full backup.

–FILEGROUP BACKUP
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–Backup the Historic filegroup
BACKUP DATABASE BackThisUp
FILEGROUP
= ‘Historic’
TO DISK = ‘C:\Backup\BackThisUpHistoric.bak’
GO

File Backup

Every SQL Server database will contain a minimum of one data file, which has a default file extension of “.mdf” and a log file, which has an extension of “.ldf“.  The primary data file contains all of the system objects of that database and can contain user defined objects.  Additional files can be added to file groups, again to distribute disk IO or to provide a granular means of backup and recovery.  The only difference between file group backup and file backs ups is that the file backup will backup only the file specified while file group backups will backup all files contained in the file group.

File backups can be taken from a database with a simple, bulk logged, or full recovery model.  The transaction log growth and truncation are the same as noted in the full recovery model.  File backups can be run with or without a pre-existing full backup.
–FILE BACKUP
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE BackThisUp
FILE = ‘BackThisUpHistoric’
TO DISK = ‘C:\Backup\BackThisUpHistoricFile.bak’
GO

Differential Backup

A differential backup backs up all changes in the database since the last FULL backup.  If a full database backup has not been taken then the differential will fail.   Keep in mind that the differential backup records all changes since the last FULL backup so if a full backup is taken at midnight and a differential is taken at 0100, 0200, 0300, and 0400 the differential backup taken at 0400 will contain everything that the 0100, 0200, and 0300 have.  The benefit of this is that during restoration the only steps necessary is to first restore the full backup, then only the most recent differential backup.

Differential backups can be taken from a database with a simple, bulk logged, or full recovery model.  The transaction log growth and truncation are the same as noted in the full recovery model as differential requires a full backup.

–DIFFERENTIAL Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–Differential Backup
–THIS WILL FAIL WITH NO PREVIOUS FULL BACKUP
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpDiff.bak’
WITH DIFFERENTIAL;
GO

–A Full database backup must first be taken before the differnetial
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’;
GO

BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpDiff.bak’
WITH DIFFERENTIAL;
GO

Transaction Log Backup

A transaction log backup backs up the active portion of the transaction log.  The database recovery model must be set to full or bulk logged and a full, file, or file group backup must first have been taken.  After a transaction log backup is taken the transaction log will be truncated.  If you are in bulk logged or full recovery model and you do not backup the transaction log then THE LOG WILL CONTINUE TO GROW!!  The higher the transactions the more often you should back up the transaction log to insure recovery and keep the log file size in check.

For the accidental dba this is a VERY common issue to have unmanageable log files due to not having scheduled transaction log backups, but I have also run into dba’s that know better and have honestly said “it’s just disk space“.  REALLY!!!  C’MON MAN!!


–LOG Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–Log Backup
–THIS WILL FAIL WITH NO PREVIOUS BACKUP
BACKUP LOG BackThisUp
TO DISK = ‘C:\Backup\BackThisUpLog.trn’;
GO

–A database backup must first be taken before the log
BACKUP DATABASE BackThisUp
FILEGROUP
= ‘Historic’
TO DISK = ‘C:\Backup\BackThisUpFileGroup.bak’;
GO

BACKUP LOG BackThisUp
TO DISK = ‘C:\Backup\BackThisUpLog.trn’;
GO
Tail Log Backup

The tail log backup is a transaction log backup, HOWEVER it is the backup of the transaction log that is taken before beginning the restoration process.  The syntax is identical to a transaction log backup, except the clause WITH NORECOVERY is added that tells SQL that the database is about to be restored.  It requires that exclusive access to the database as the database will be placed in recovery.  The tail log backup gets all of the changes since the last log backup and provides the ability to restore the database to a point in time.  Just like a transaction log backup a tail log backup requires that the database be in full or bulk logged recovery model.

One question that often arises is that if the database becomes corrupt or suspect how can the tail log be backed up.  The answer is that SQL only deeds access to the transaction log file.  If the file has not become corrupt then the backup can be taken.
–TAIL LOG Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–A database backup must first be taken before the log
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’;
GO

BACKUP LOG BackThisUp
TO DISK = ‘C:\Backup\BackThisUpLog.trn’
WITH NORECOVERY;
GO

 

Copy Only Backup

A copy only backup is a full database backup EXCEPT that IT is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored.  So why would this type of backup be used.  I have used this quite often when a developer calls for a copy of the database from production and it must be current as of this minute.  If I perform a full database backup that will affect my restoration steps.  Instead I will perform a cull database backup with COPY ONLY so that my restoration process will remain unchanged.

–COPY ONLY Database Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE
BackThisUp
END;
GO

CREATE DATABASE BackThisUp
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N’BackThisUp’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%);
GO

–Fulldatabase backup
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’
WITH COPY_ONLY
GO

Database Locks During Backups

This is a myth.  SQL does not place any locks on the database during a backup.  This misconception most often arises because you may see degradation in performance during a back up.  This is due to disk IO, the reading of the files and writing to the backup files, and not due to locking.

 

The sample script above can be downloaded here.

 

SSIS End To End Training Videos

sqlsafety 0 Comments

I recently completed 12 videos with SSWUG that covers SQL Server Integration Services, 2005 thru 2014.  These videos were created as a tool to help prepare for the Microsoft 70-463 exam, Implementing a Data Warehouse with Microsoft SQL Server 2012, as well as to introduce users to the features and concepts in SSIS.  Having worked with SSIS since it was first introduced in 2005, and its predecessor DTS, I took a great deal of time to include real life scenarios and demonstrations to insure that these videos aren’t just for “academic” purpose, but to help prepare attendees to hit the ground running.

Registration for the videos can be found here and you can use VIP Code E2E10 to get $10 off the class.

SSISEndtoEnd

It’s Simple!! Or Psuedo Simple Anyway.

sqlsafety 0 Comments
MSDN documents the log chain as:
A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time
or after the recovery model is switched from simple recovery to full or bulk-logged recovery.

This is accurate enough except for the time between changing the recovery model to full or bulk logged and when a database is backed up. After the database recovery model is changed the transaction log will continue to be
be truncated upon checkpoint. The reason is that the database engine is aware that there is no way to recover the database to a point in time without a backup. This recovery state is sometimes referred to as “Pseudo-Simple
recovery model.This state can be easily demonstrated. The below T-SQL code creates a database, pseudosSimple, and sets its recovery model to full. The sys.databases catalog view is queried to show the recovery model and the
log_reuse_wait_desc.


USE MASTER;
GOIF EXISTS(SELECT * FROM sys.databases WHERE name = ‘pseudoSimple’)
BEGIN
DROP DATABASE
pseudoSimple
ENDCREATE DATABASE

pseudoSimple;
GO

ALTER DATABASE pseudoSimple SET RECOVERY FULL;
GO

SELECT name,
recovery_model,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘pseudoSimple’;
GO

The result is:

name recovery_model log_reuse_wait_desc
pseudoSimple 1 NOTHING
Now we can simulate activity that would cause the transaction log to record transactions and then requery the catalog view to see the log_reuse_wait_desc.

<style=”font-size: 12px;”>USE pseudoSimple;
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘test’)
BEGIN
DROP TABLE
test
END

CREATE TABLE test(
col1   CHAR(150)
);
GO

DECLARE @i INT = 1000
WHILE @i > 0
BEGIN

INSERT test
SELECT ‘This is number ‘ + CONVERT(CHAR(5), @i)

SET @i = 1
END

–WAIT to insure transaction completes
WAITFOR DELAY ‘0:00:05’;
GO

SELECT name,
recovery_model,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘pseudoSimple’;
GO

Again the results are the same:

name recovery_model log_reuse_wait_desc
pseudoSimple 1 NOTHING

So now let’s backup the database, add some records, and once again view the log_reuse_wait_desc:

<style=”font-size: 12px;”>–BACKUP the database
BACKUP DATABASE pseudoSimple
TO DISK = ‘C:\Backup\pseudo.bak’;
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘test’)
BEGIN
DROP TABLE
test
END

CREATE TABLE test(
col1   CHAR(150)
);
GO

DECLARE @i INT = 1000
WHILE @i > 0
BEGIN

INSERT test
SELECT ‘This is number ‘ + CONVERT(CHAR(5), @i)

SET @i = 1
END

–WAIT to insure transaction completes
WAITFOR DELAY ‘0:00:05’;
GO

SELECT name,
recovery_model,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘pseudoSimple’;
GO

name recovery_model log_reuse_wait_desc
pseudoSimple 1 LOG_BACKUP

Once the backup is taken SQL knows that the database can be recovered to a point in time so it will no longer truncate the log upon a checkpoint and the log will ONLY be truncated upon a transaction log backup. This is not exactly as described by MSDN, but does make sense, why let the log continue to grow when there is no way that the database can be recovered to a point in time.
The sample T-SQL code can be downloaded here.

#SQLSat San Diego Count Down!!

sqlsafety 0 Comments

We are just over one week until SQL Saturday #340 San Diego! If you are on the fence as to whether to attend here are some reasons that should make this an easy decision.

1. Itzik Ben-Gan @itzikbengan Boost your T-SQL with the APPLY Operator If you bought his book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions, then you can stop waiting for the movie and stop in!!

2. Benjamin Nevarez @BenjaminNevarez Understanding Parameter Sniffing If it looks like a duck, quacks like a duck, and smells like a duck… It must be parameter sniffing. Whatever. I thought it was funny.

3. Grant Fritchey @GFritchey Getting Started Reading Execution Plans The Hooked on Phonics for execution plans

4.Randy Knight @randy_knight Understanding Transaction Isolation Levels Tired of your transactions feeling isolated? Do something about it!

5. David Sumlin @davidsumlin Turbo Charged SQL Development With SQL# Nuff said!

6. TJay Belt @tjaybelt Database Monitoring : Trying To Keep Sane Because occasionally we all need a little sanity.

7. It’s San Diego!!!  C’mon Man!!  What more reason do you need!

SQL Saturday Sandiego

How Did They Make That???

sqlsafety 0 Comments
often you may run across a stored procedure, view, or function that peeks your curiosity and you REALLY want to see how it works. This is even true of system objects. There are several ways of provinding the
text to these objects using T-SQL. The first is to query the associated catalog views. For example to view the defintion of all objects in a database you can use the sys.sql_modules and sys.objects catalog views:

SELECT name,
      
o.TYPE,
      
sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.OBJECT_ID = o.OBJECT_ID
ORDER BY o.TYPE;

To find the text of system defined objects you can replace the sys.objects catalog view with the sys.all_objects catalog view

SELECT o.name,
      
m.definition
FROM sys.system_sql_modules m
JOIN sys.all_objects o
ON m.OBJECT_ID = o.OBJECT_ID
ORDER BY o.name;
GO

Another way to find the definition of a specific objects is to use hte system function OBJECT_DEFINITION(). This works on both system defnied and user defined objects:

SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.tables’));
GO

And one last method is to use the system stored procedure sp_helptext which also works for both system and user defined objects:


sp_helptext ‘sys.tables’;
GO

So which one is best, obviously the catalog views provide the ability to return all defined objects while the OBJECT_DEFINITION and sp_help_text are for single objects. Also none of these methods work for encrypted
or CLR objects.

SSIS Advanced Techniques Workshop

sqlsafety 0 Comments

I recently completed a set of videos for SSWUG that covers some advanced techniques for SQL Server Integration Services.

1. SSIS – Creating dynamic packages
2. SSIS – Error handling and troubleshooting
3. SSIS – Deployment models
4. SSIS – Replacing dynamic SQL with variables and expressions in SSIS
5. SSIS – Leveraging the Execute SQL task in SSIS

Each session covers advanced topics in SSIS and outlines each topic for SSIS 2005 though 2014. This workshop is available online at: SSWUG.

When registering for the workshop you can use the VIP Code “DAVID10” to receive $10 off.

SSWUG Summer Learning

SSWUG Summer Learning

Automate Database Restore

sqlsafety 0 Comments




Automate Database Restore


On several occasions I have been required to restore a database from production to development or test. The requirements are that I must restore

1. The most recent full back up

2. The most recent differential backup

3. All transaction logs

4. The database must be left in STANDBY until the last transaction log is applied

This can be done through SSMS, but I wanted a way to do this using T-SQL and automate the process. The below script uses the two msdb tables backupset and backupmediafamily to get the fully qualified path of the backup file and ascertain the steps to take to insure the proper order and whether the recovery is set WITH STANDBY or WITH RECOVERY. This can easily be ported to a stored procedure that would accept an input parameter of the database name. The script can be downloaded here.

USE MASTER;
GO

DECLARE @file VARCHAR(150);
DECLARE @diffcount SMALLINT;
DECLARE @lastdate DATETIME

--Obtain the most recent full backup path
SET @file = (
SELECT TOP 1 m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND b.TYPE = 'D'    
    
AND is_copy_only = 0
ORDER BY backup_finish_date DESC
);

--Retrieve the most recent date fo the last full backup
SET @lastdate = (
SELECT TOP 1 backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND TYPE = 'D'
    
AND is_copy_only = 0
ORDER BY backup_finish_date DESC
);

--Query to see if a differential database backup exists in the current set
SET @diffcount =
(SELECT COUNT(*)
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND b.TYPE = 'I'
    
AND is_copy_only = 0
    
AND backup_finish_date > @lastdate);

--If a differential exists the full backup must be applied followed by the most recent diff
IF @diffcount > 0
BEGIN
PRINT
'Restorig most recent full backup before differential ' + @file;

RESTORE DATABASE Adventureworks2012
FROM DISK = @file
WITH NOUNLOAD,  
    
REPLACE,  
    
STANDBY = 'C:\Backup\standby.txt';

--Find the most recent differential
SET @file =
(SELECT TOP 1 m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND b.TYPE = 'I'
    
AND is_copy_only = 0
    
AND backup_finish_date > @lastdate
ORDER BY backup_finish_date DESC);
DECLARE @i INT;

PRINT 'Restoring the most recent differential backup ' + @file
RESTORE DATABASE Adventureworks2012
FROM DISK = @file
WITH NOUNLOAD,  
    
REPLACE,  
    
STANDBY = 'C:\Backup\standby.txt';

--If a differential exists then only T-Logs taken after the differential can be restored
--Reset the @lastdate variable to reflect after differential
SET @lastdate = (
SELECT TOP 1 backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND TYPE = 'I'
ORDER BY backup_finish_date DESC
);

END
ELSE
PRINT
'Restorig most recent full backup no differential exists ' + @file;

RESTORE DATABASE Adventureworks2012
FROM DISK = @file
WITH NOUNLOAD,  
    
REPLACE,  
    
STANDBY = 'C:\Backup\standby.txt';

--2014-08-25 09:20:35.000
--Obtain all of the T-Log backups in the order in which they were taken
SELECT m.physical_device_name,
      
backup_finish_date,
      
ROW_NUMBER() OVER(ORDER BY backup_finish_date DESC) AS row
INTO #tlog
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'Adventureworks2012'
      
AND TYPE = 'L'
AND backup_finish_date > @lastdate;

SELECT @i = (SELECT MAX(row) FROM #tlog)

/*Loop through all T-Log backups and restore each in the order in which they were taken
finally restore the final T-Log WITH RECOVERY*/
WHILE @i > 0
BEGIN
SELECT
@file = (SELECT physical_device_name FROM #tlog WHERE row = @i)
PRINT @file
IF @i = 1
BEGIN
PRINT
'Restoring log WITH RECOVERY ' + @file
RESTORE LOG Adventureworks2012
FROM DISK = @file;

DROP TABLE #tlog;
RETURN
END

ELSE
PRINT 'Restoring Log WITH STANDBY' + @file;

RESTORE LOG Adventureworks2012
FROM DISK = @file
WITH STANDBY = 'C:\Backup\standby.txt';
SET @i -= 1
END



Hit Counter provided by Sign Holders