Shrinkfile and log backup

Question

Post reply

Shrinkfile and log backup

SSDBA2k5

SSC-Addicted

Points: 457
More actions
October 22, 2008 at 9:32 am

#194933

Hello,
I would like to know your opinion about the following situation:
A maintenance plan with the following;
Optimization job, which rebuild indexes and shrink database files
Transaction log backup.
The optimization job runs and its taking long, so it is colliding with the transaction log.
The problem is that the optimization job increases too much the log file, but the shrink seems not to be working
properly, it does no shrink the log.
The transaction log back ups jobs backups the log, but the space it not freed.
I think, because the log is being backuped the optimization could not shrink the file.
What do you think?
I would really appreciate your comments
Thanks

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

victor_bosc (10/22/2008)
Optimization job, which rebuild indexes and shrink database files

Why?

I generally refer to that job setup as a complete waste of time. The rebuild indexes takes a lot of CPU, IO and time to put all the index pages into the correct order, then the shrink will go and spend more time, CPU and IO shuffling the index pages into as small a space as possible, usually resulting in far higher fragmentation than before the rebuild started.

Why do you feel you need to shrink the database files? SQL requires some free space to work properly and shrinking the file means that the next time data is added, the file has to grow. That will slow all transactions down while the file grows. Repeated shrink/grow can also cause external (file-level) fragmentation, which is rather hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SSDBA2k5 SSC-Addicted Points: 457 More actions · Answer 2

Hi, thanks for your quick response.

I agree with you regarding fragmentation generated, also with that transactions will be slower while the file is growing.

I am shrinking the files, because I think that the log’s size is increasing the backup times. What do you think?

The optimization job runs once a week.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

victor_bosc (10/22/2008)
I am shrinking the files, because I think that the log’s size is increasing the backup times. What do you think?

Not likely. Backups are only concerned with the contents of the files, not the size of the files on disk. The latter is what shrink affects.

If the log is getting large (as in the contents), then backup the log more frequently.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SSDBA2k5 SSC-Addicted Points: 457 More actions · Answer 4

Hi, Thanks again for post.

I have a transaction log backup every one hour.

I have the following idea, please let me know if I'm wrong:

The transaction log backup backups the log, but it does not free the space to the operating system. And the shrinkfile release the space.

On the hand, you mentioned that the backup time is not necessarily related to the size. In which I can detect the bottlenecks of the backup process?

Thanks

PD: I really enjoy reading your post since they are really helpful

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

victor_bosc (10/23/2008)
The transaction log backup backups the log, but it does not free the space to the operating system. And the shrinkfile release the space.

Correct. However you don't want to keep shrinking the file to release unused space, then growing the file because if doesn't have enough space, then shrinking the file to release unused space, then growing the file because if doesn't have enough space, ....

Make the transaction log the size it needs to be to handle the transactions that it needs to hold between log backups, and then leave it alone.

On the hand, you mentioned that the backup time is not necessarily related to the size. In which I can detect the bottlenecks of the backup process?

Why do you think you have a bottleneck in the backup process?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

victor_bosc (10/22/2008)
The problem is that the optimization job increases too much the log file,

set the recovery mode to bulk logged before the index rebuild and then back to full after the rebuild to avoid the log growing too much. Once completed backup the database

in bulk logged mode alter and create index commands are minimally logged 😉

From BOL

If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:
CREATE INDEX operations (including indexed views).
For more information, see CREATE INDEX (Transact-SQL).
ALTER INDEX REBUILD or DBCC DBREINDEX operations.
For more information, see ALTER INDEX (Transact-SQL) and DBCC DBREINDEX (Transact-SQL).

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SSDBA2k5 SSC-Addicted Points: 457 More actions · Answer 7

Thanks Gail and Perry for your postings

Gail: I think that I have a problem with the full backups because the backup process used to take between 45 and 60 minutes.Now, it is taking between 2 and 3 hours. I would like to find the cause of this time increment. Do you know some method/technics or something to do so. The Database has been growing normally.

As your previous posts you do not recommend recommend rebuilding indexes and shrinking. I agree with that periodically shrinking is not a good idea, but should I NEVER do a shrink?

And, what do you think about only rebuilding indexes once a week?

Thank you

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

victor_bosc (10/23/2008)
As your previous posts you do not recommend recommend rebuilding indexes and shrinking. I agree with that periodically shrinking is not a good idea, but should I NEVER do a shrink?

I believe that is a bit of a misinterpretation. It's good to rebuild indexes especially if the Clustered Indexes are not in some temporal order either by date or by IDENTITY. I believe what Gail meant is that it's a waste of time to do proper index rebuilds and then to follow that with the folly of DB shrinkage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

... sorry.... and to answer the other question, I never do a shrink on the database unless some runaway code happened to occur. Database growth should be planned and never take you by surprise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

victor_bosc (10/23/2008)
I think that I have a problem with the full backups because the backup process used to take between 45 and 60 minutes.Now, it is taking between 2 and 3 hours. I would like to find the cause of this time increment. Do you know some method/technics or something to do so. The Database has been growing normally.

In general, decreasing backup performance (when not in line with the increase in size of the DB) is due to IO bottlenecks on one of more drives. What's your disk layout look like? What do the following perfmon counters look like (during the backup)

Physical disk: Avg sec/read

Physical disk: Avg sec/write

Physical disk: Avg disk queue length

Physical disk: % idle time.

Also check in the SQL error log and see if you have any messages related to the backup buffer.

As your previous posts you do not recommend recommend rebuilding indexes and shrinking.

You misunderstood. I said that a rebuild followed by a shrink is a waste of time as the shrink will leave the indexes more fragmented then they were before the rebuild. Rebuilds are necessary

I agree with that periodically shrinking is not a good idea, but should I NEVER do a shrink?

The only time I would shrink a database would be after a data archive or a large data purge where there is a lot of space free within the data file that will not be reused in a reasonable amount of time

And, what do you think about only rebuilding indexes once a week?

Depends how fast your indexes get fragmented. If you have one where the fragmentation increases by 2% per week, then rebuilding it every week is overkill. If you have one that can go from 0% to 90% in 6 hours, then once a week is not enough.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SSDBA2k5 SSC-Addicted Points: 457 More actions · Answer 11

Hi Jeff, thanks for replies.

I understood what Gail recommend about rebuilding indexes followed by a shrink. But I wanted to you know your

opinion about running them separately.

Summing: Never run shrink, only if it is necessary.

Run index rebuild periodically.

Could you please tell a me a situation in which a shrink file will help?

Thanks

SSDBA2k5 SSC-Addicted Points: 457 More actions · Answer 12

Hello Gail,

Sorry if I didn't write properly in my post. I understood your idea, and I agree with you.

Thanks for the backups information, I will check those counters to see what can i find.

Have a nice day.

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

victor_bosc (10/24/2008)
Could you please tell a me a situation in which a shrink file will help?
Thanks

A shrink could help you with overall storage needs. If you have 50 G of unused space in your DB, and you NEED that space for another database or files, then you could shrink your db (leaving some space for growth) to free up the space for the other needs. I had to do that a while back until we solved the storage space problems.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

True enough.... but not until you find out [font="Arial Black"]WHY [/font]it grew to 50GB in the first place and fix it. Otherwise, it's just gonna grow again. 1 growth = 1 fragment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)