Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are you FULL recovery model? And if so PLEASE tell me you are taking transaction log backups? –  Kenneth Fisher Aug 5 '13 at 16:12
    
The question is a bit unclear: are you trying to fix this in your test environment or your production environment? (Or were you testing on production?) Also, are we correct to assume that the backup that failed to complete was a transaction log backup? –  Jon Seigel Aug 5 '13 at 16:22
    
@KennethFisher Yes I'm using FULL recovery and yes I'm backing up Transaction Logs every hour. –  Federico Giust Aug 6 '13 at 8:38
    
@JonSeigel I'm trying to fix this in the production server. The backup that failed to complete was the full back up, rebuilding the indexes took way to long and it seems to be cancelled. –  Federico Giust Aug 6 '13 at 8:40
    
Why did the full backup fail? Look in the job history. This isn't making sense to me now if the transaction log backups have succeeded all the way through. –  Jon Seigel Aug 6 '13 at 13:45

2 Answers 2

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

share|improve this answer
    
Thanks, I'll have a look at those links. –  Federico Giust Aug 6 '13 at 8:44
    
Why not use DBCC SHRINKDATABASE? I use this in test environments on some of our more naff servers so maybe I'm in need of some re-education! Perhaps I should post my own Q .. –  Paul Aug 7 '13 at 15:02
    
@Paul Tried the GUI to shrink both Database and Files but nothing gets released. –  Federico Giust Aug 8 '13 at 10:14

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.

share|improve this answer
    
I've tried so many times the GUI to shrink the transaction logs but nothing was recovered. For example if I click through the GUI to shrink files and select file type Log, it says available free space 6639.99MB. I click on OK for it to release it, but nothing happens it just closes the window. –  Federico Giust Aug 8 '13 at 10:13
    
@Federico: The in-use VLFs within the transaction log may be at the end, preventing a shrink, or you're using other features in SQL Server that require the transaction log records, which prevents clearing. See this: rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log –  Jon Seigel Aug 8 '13 at 13:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.