New book from Red-Gate

Red-gate has released another free e-book recently: Brad's Sure Guide to SQL Server Maintenance Plans. This book is all about maintenance plans and covers both SQL Server 2005 and 2008. The book is designed not to just talk on theories but the practical aspects of maintenance plans. It is written in a way that new and accidental DBAs can understand things clearly. I recommend this book as a must have to all DBAs.

As you all know red-gate is committed to bring better products and bring better DBAs to the society. They have produced some great tools, out of which I love SQL Compare and SQL Data Compare. They also support many SQL Server related sites including www.sqlservercentral.com and www.simple-talk.com. Through their continuous support SQL Server Sri Lanka User group (SSSLUG) is able manage the monthly meetings. In addition to that they also produce great e-books.

I have not finished reading this book yet, but I need to admit that this book is well written.This book talks about many things you can do with maintenance plans.  If you are a DBA or want to be a DBA you need to study this book clearly.
Enough advertising :) You can get the book from this link

Backup Process

Recently I had a question: What will happen to the transactions executed while a backup job is in progress? This question prompted me to learn more about backup process. Additionally I was asked to give training to a set of administrators so I wanted to be prepared with the answer before they ask the question.

There are a lot of articles in many sites about backup and restore and this article summarizes what I learned from those articles.

Active portion of Transaction Log

A transaction is considered active under various circumstances.

1. In any recovery model, while a transaction is non completed (neither committed nor rolled back) it is considered as active
2. In full or partial recovery model, until a log backup is taken even the committed transactions are considered active. In simple recovery model, it automatically becomes inactive when the transaction is committed.

Active transactions in the log keep the space. There is no way of removing the space without data loss. It is always the inactive part of the log which get cleared when BACKUP LOG with truncate only or SHRINK DATABASE command is executed. (By the way, from SQL Server 2008 onwards, BACKUP LOG WITH TRUNCATE_ONLY has no meaning. Microsoft recommends changing the recover model to simple.)

I have seen log files growing even in simple recovery model. There weren’t many large transactions happen either. I couldn’t figure the reason and I didn’t bother much too as I didn’t become that big issue to investigate.

Recently I read an article, which explained the reason: During backup process, whatever is written to the transaction log is kept active until the end of the backup process. So if the backup job takes more time, or if there were large volume of transactions (need not to be large transactions; small many transactions would do the job!) the entire volume of transaction log will be kept active until the backup job completes.


Wanna read more? Try this: Understanding SQL Backups
This article not only gives the reason for the growth of Transaction log, but also an in-depth understanding of how backup is taken in SQL Server. A good article to all those need in-depth knowledge of SQL backup!

Handling Multiple Jobs - Revisited

Few days back I wrote about organizing multiple backup jobs so that they will not overlap (to avoid disk and memory contention) and won’t be idle for a long time. Even though my post is about backup jobs, after talking to many people, I decided to genralize the issue
A recap of the scenario here:


We have multiple long time consuming jobs in our servers. Even though they are scheduled to run at different times, often a job can start when another job is still executing, Also there could be an idle time where no job is running. As these jobs take considerable resources, we need to complete all the tasks within the time.

After writing the post,  there were two solutions suggested:

1. Remove the schedules from all your jobs. Manually plan the order of the jobs. Add a step on the first job to call the second job and second job to call the third job and so on. Schedule the first job only
2. Remove the schedules from all your jobs. Manually plan the order of the jobs. Create another Job to call each job and check for the completion of the job and start the next job until all jobs are executed. Schedule this new job.

Each method has its pros and cons.

Method 1:
This is a great method, if you have many jobs of different types. By simply removing the schedule and adding an additional step, (you also need to update the previous job outcome) this could be implemented. Either the code should be cleaver enough in finding the next job using a clear methodology, or else when a job is deleted / disabled, the jobs may start to fail and no other job will execute unless someone change the next job again. Additionally we may need a mechanism of adding the jobs too.

Method 2:

This really works well for backup jobs. If you have many databases and you want to create backups, it is great to have one maintenance job that will create the backups one after the other. In fact, SQL Server’s database maintenance plan does something similar. However, for jobs of multiple categories this is not the best option as different jobs may have different jobs and different notification methods.

Users need to decide on the best solution before making a decision.

Scheduling Database Backups: Need a Solution

Recently we had this issue.

We have a huge database server with 2.5 TB of database space. The server has 8 GB of RAM and two dual core processors. It also has a not so good storage device. Due to the limitations in RAM and hard disk, (the disks are really slow) we were facing a problem in taking full backups.

Initially, the database server had a set of jobs, each one was starting on 5th of every month at 2:00 am. It created a whole heap of issues, including too many (is 59 a big number?) backup jobs starting at the same time causing each one blocking the other due to memory crunch and hard disk bottleneck.

So we decided to schedule the task one after the other. We also scheduled it to happen on a weekend of the moth. (example: First Saturday) We did a calculation on hard disk speed and database size and schedule the jobs to start at different times. For the first month everything worked nicely. But when the databases started growing, it forced us to reschedule the backup jobs. Can we reschedule the jobs every month? Currently we have 29 servers to maintain and some have more databases. If we don’t re schedule the jobs we may face one of the two scenarios (actually both scenarios)

1. Before a database backup is completed, the second one starts and creates memory and disk contention.
2. One backup completes earlier than expected but the next backup waits until its time comes.

We also had another issue: most of the databases are inter-connected so the backup jobs need to be completed one after the other. As some of the databases are really huge, the backup jobs pass the 24 hour time frame. It created another issue for scheduling backups.

 How to solve the issue?

Please come up with your solutions.

Share this post :

Log Shipping Issue

When we setup Log shipping manually it seems to be an easy task. But within a couple of days we were proved to be wrong. The log shipped database didn’t get the updates on time. Additionally we started receiving messages stating the log backup has failed. It took some time to identify the cause. All backups were set to start at the same time... So we manually changed the starting time so they started working fine.

Soon we faced the next situation: Since we have more than 60 databases, and setting them independently while giving enough room for log shipping to complete became a problem. Wither the time si not enough or we step into another log shipping time. Doing such a minute calculation was little costly for the operation we did, so we decided to live with this problem until the migration completes.

Do we have a solution for it. I think we have. We are planning to try that next week and I will update when we do that. Please keep your breath until then.

Full Recovery Model

If you haven't change the recovery model of "model" database, you the new databases you create will be in full recovery model by default. However, it does not mean, all the changes done to the database are logged and kept.
Until the first backup is taken, all databases behave like they are in simple recovery model. So to take the full benefit of the recovery model, take the backup of the database immediately after creation.
Want to check it out?
Here is the test:
In any recovery model, transactions are written to log and forced to write to disk when transaction is completed. In simple recovery model, transactions will be marked inactive when these conditions met:
1. Transaction is either committed or rolled back.
2. The data page is written to disk.
So, These are the steps:
1. Create a database and a table there.
2. Add data one by one and check the log file size.
3. Add the same data in one statement and check the size.
4. Drop the database
5. Repeat step 1
6. Take a backup
7. Repeat steps 2 & 3
Please note, to avoid log file growth due to data pages waiting in memory (to be written to disk) we need to flush them to disk before the next insert.
Now try the code:

-- ********************* FIRST TEST ************************
SET NOCOUNT ON
-- Create database
USE master;
CREATE DATABASE RecoveryModel
GO
--Make sure it is in full recovery model
SELECT 'Before Backup' = DATABASEPROPERTYEX('RecoveryModel', 'Recovery')
GO
-- Create table
USE RecoveryModel;
GO
CREATE TABLE Test
(
TestID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
SampleData nvarchar(max) DEFAULT(REPLICATE(N'a', 8000))
)
;
-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'
--Insert 1000 rows one by one
DECLARE @i int
SET @i=1
WHILE @i <>
BEGIN
INSERT INTO Test DEFAULT VALUES;
SET @i =@i+1
END

-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'


--Insert 1000 rows at once
INSERT INTO Test SELECT SampleData FROM Test WHERE TestID<=1000;
--Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
GO
USE master;
DROP DATABASE RecoveryModel;
GO

--******************** SECOND TEST *****************************
SET NOCOUNT ON
-- Create database
USE master;
CREATE DATABASE RecoveryModel
GO
--Make sure it is in full recovery model
SELECT 'Before Backup' = DATABASEPROPERTYEX('RecoveryModel', 'Recovery')
GO
-- Create table
USE RecoveryModel;
GO
CREATE TABLE Test
(
TestID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
SampleData nvarchar(max) DEFAULT(REPLICATE(N'a', 8000))
)
;
BACKUP DATABASE RecoveryModel TO DISK = 'C:\RecoveryModel.bak';
GO
-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'
--Insert 1000 rows one by one
DECLARE @i int
SET @i=1
WHILE @i<1000
BEGIN
INSERT INTO Test DEFAULT VALUES;
SET @i =@i+1
END

-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'

--Insert 1000 rows at once
INSERT INTO Test SELECT SampleData FROM Test WHERE TestID<=1000;
--Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
GO
USE master;
GO
DROP DATABASE RecoveryModel;

Recovering database using proper Backup/Restore Strategy

Again I was asked to speak at the Sri Lankan SQL Server User group Meeting. This time it is on Recovering database using proper Backup/Restore Strategy.
It happened on January 16 2008. I was supposed to be the first speaker. Due to some technical difficulties I took the second slot.

The slides and examples will be published at http://sqlserveruniverse.com/ shortly.