Transaction log has grown huge after maintenance plan to rebuild idex

Question

A couple of days ago I was testing some stuff using the Maintenance Plans in SQL Server 2008.

I created one to rebuild indexes and take a full backup every week. (I know, bad thing I've found out today).

The thing is, since the transaction log grew a lot (around 80gb and the db is 60gb), the backup did not run. Now I've been googling around all day to see if there is a way to shrink the transaction log to the size it used to be before, which was something like 200mb.

UPDATE

This is on our production server and the maintenance plan was as follows:

Rebuild Indexes

On success BackUp Full Db

On success Delete backups older than two weeks.

This didn't finish, it broke at some point so I guess it took way too long to rebuild the indexes. So the backup didn't take place.

We are also backing up transaction logs every hour.

Is this possible? Why does this happen? I know while rebuilding it creates a copy of the index in the transaction log or something like that, but is it possible to get rid of this copy?

This is what I have on the Job History, so it looks like for some reason it failed when creating the indexes, and since the plan was on success do the back up it stopped there.

Message :

Executed as user: SHOCKLOGIC\DB1$. ...ress: 2013-08-05 03:01:03.43 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivitiesPerGroup] ON [dbo].[Acti...".: 12% complete End Progress Progress: 2013-08-05 03:01:03.43 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 12% complete End Progress Progress: 2013-08-05 03:01:03.52 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivitiesPerHotelCat] ON [dbo].[A...".: 12% complete End Progress Progress: 2013-08-05 03:01:03.52 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 12% complete End Progress Progress: 2013-08-05 03:01:03.69 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivitiesPerHotelCat_OLD] ON [dbo...".: 12% complete End Progress Progress: 2013-08-05 03:01:03.69 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 12% complete End Progress Progress: 2013-08-05 03:01:08.61 Source: Rebuild Index Task Executing query "ALTER INDEX [_dta_index_ActivitiesPerPerson_10_292...".: 13% complete End Progress Progress: 2013-08-05 03:01:08.61 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 13% complete End Progress Progress: 2013-08-05 03:01:12.85 Source: Rebuild Index Task Executing query "ALTER INDEX [_dta_index_ActivitiesPerPerson_10_292...".: 13% complete End Progress Progress: 2013-08-05 03:01:12.85 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 13% complete End Progress Progress: 2013-08-05 03:01:20.44 Source: Rebuild Index Task Executing query "ALTER INDEX [IX_ActivitiesPerPerson] ON [dbo].[Act...".: 13% complete End Progress Progress: 2013-08-05 03:01:20.44 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 13% complete End Progress Progress: 2013-08-05 03:01:57.55 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivitiesPerPerson] ON [dbo].[Act...".: 13% complete End Progress Progress: 2013-08-05 03:01:57.55 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 14% complete End Progress Progress: 2013-08-05 03:01:57.58 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivityGroupInputTypes] ON [dbo]....".: 14% complete End Progress Progress: 2013-08-05 03:01:57.58 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 14% complete End Progress Progress: 2013-08-05 03:01:57.65 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivityGroupTypes] ON [dbo].[Acti...".: 14% complete End Progress Progress: 2013-08-05 03:01:57.66 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 14% complete End Progress Progress: 2013-08-05 03:01:57.66 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivityGroupTypeForGroups] ON [db...".: 14% complete End Progress Progress: 2013-08-05 03:01:57.66 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 14% complete End Progress Progress: 2013-08-05 03:01:57.74 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ActivityInputTypes] ON [dbo].[Acti...".: 15% complete End Progress Progress: 2013-08-05 03:01:57.74 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 15% complete End Progress Progress: 2013-08-05 03:01:57.77 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Activity_B7BCF0184055F19F] ON [d...".: 15% complete End Progress Progress: 2013-08-05 03:01:57.77 Source: Rebuild Index Task Executing query "USE [Eventlogic] ".: 15% complete End Progress Progress: 2013-08-05 03:01:57.88 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_AddressConfirmationScreenFieldsLan...".: 15% complete End Progress Progress: 2013-08-05 03:01:57.88 Source: Rebuild Index ... The package execution fa... The step failed.

Answer 1

Rebuilding an index needs enough space to create the new index. A simplified rule of thumb seems to be that you need about 120% of the space used by the original index. This may be in the database or in tempdb, depending on whether SORT_IN_TEMPDB is ON or OFF.

If possible, have SORT_IN_TEMPDB = ON reduced some of the logging that is done.

If you rebuild all of the indexes between LOG backups, then all of the logs for reindexing all of the indexes will be in the log file. Therefore, major reorganization needs to have the appropriate resources of disk space, log space, and so forth. (For example, you might try reorganizing one table at a time and doing a log backup after each.)

For index space needs: http://msdn.microsoft.com/en-us/library/ms191183(v=sql.110).aspx For transaction logs: http://msdn.microsoft.com/en-us/library/ms184246.aspx

You could try changing to a minimally logged recovery model such as SIMPLE or BULK_LOGGED. However, for a production database you would have to weigh the negative side-effects and determine what is best. (A change to SIMPLE for reorganizing the databsse should probably be followed by changing to FULL and doing a FULL backup.)

A log file can be shrunk, but only after the high order pages are freed by a log backup. (This is usually a cycle of backup log, DBCC SHRINKFILE, then check the space and try again.)

For shrinking the log file do not use DBCC SHRINKDATABASE. Use DBCC SHRINKFILE (logfilename, targetsize).

Answer 2

I created one to rebuild indexes and take a full backup every week. (I know, bad thing I've found out today).

Correct.

These processes should be separated so they can be run independently. Certainly the backup process should proceed regardless of whether or not the index rebuilds succeed or fail. But don't bother trying to fix that, because:

Completely rebuilding the indexes all the time is rarely necessary, and is usually quite expensive. I recommend using a more intelligent process that first determines if an index is fragmented and by how much, and then takes appropriate action. I personally use and recommend Ola Hallengren's scripts to do this.

The thing is, since the transaction log grew a lot (around 80gb and the db is 60gb), the backup did not run. Now I've been googling around all day to see if there is a way to shrink the transaction log to the size it used to be before, which was something like 200mb.

Based on what you told me in the comments, the backup that "failed" (didn't run) was the full backup. Assuming the transaction log backups have succeeded all along, you should be able to use DBCC SHRINKFILE (or the SSMS GUI, which is easier) to shrink the log file back to a reasonable size.

I know while rebuilding it creates a copy of the index in the transaction log or something like that, but is it possible to get rid of this copy?

You cannot prevent it from being created in the first place, which is why doing rebuilds all the time is so expensive, and why I recommend using the scripts as mentioned.

When the transaction log gets backed up, the space that was used can then be reused, or released to the O/S as part of a SHRINKFILE.

Answer 3

in order to release the space you first need to change the recovery model from full to simple, then you can release the space as you have mentioned.After it is finished you can revert from simple to full anyway this is a temporary fix.. the log will eventually grow again sooner or later..