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.
Best Answer
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).