Here we are (sorry, if this is wrong way to post script):

******************************************************************

[font="Arial"]USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ============================================================

--rebuild index using dbo.index_rebuild_log ahead prepared data

-- ============================================================

CREATE PROCEDURE [dbo].[usp_IndexRebuild]

AS

BEGIN

SET NOCOUNT ON;

declare @start...

1) 2005- Sql Server 2005 Practical Troubleshooting- Ken Henderson and Co

2) 2008- Troubleshooting SQL Server - A Guide for the Accidental DBA- Kehayias, Jonathan

Enjoy reading 🙂

Just did this yesterday night, i.e. break/restore Mirror:

1. restore Full backup with NoRecovery

2. restore all sequential TrLog backups with NoRecovery

3. setup partners on Principal and Mirror to point to each...

Mirror Monitor is your first friend- it provides info on: how long it can take to sync, latest moved transaction, etc. A lot of helpful info

Just to confirm that changing backup retention period from 1 day to 23 hours solved this mystery (from time to time failed old backup cleaning) for me too

Thanks guys for all your answers-

just to summarize- for index maintenance we need to take into account both logical fragmentation and page density. Still have couple questions left: 1)...

It makes sense to consider internal fragmentation while making decision to rebuild indexes- why in this case majority scripts/advices (including mentioned BOL sample) do not care about it (only external...

To resume- restored DB is always identical backed up one (like mirror copy). Thanks a lot, Gail for your time and detailed explanation. Yuri

Make sense- thanks. If you do not mind 1 more question- regarding TrLog- same story or not? I mean original DB has 90% free log (let say after huge index...

Sorry Gail but IMO it does not sound logical- if during backup only used (with data) pages are written to backup file then restored DB should have only such pages...

Make sense, thanks

What does not make sense...

What means "used pages"? Can you please clarify. Thanks

Kevin,

How big is your DB? I had same issue trying to backup TB DB. Eventually did local backup and then copied file to network storage

As per this book recommendation this value (AvailableMbytes) "it remains above 150–300 MB". Probably that mean in my case (around 100 MB) we should decrease a bit Max memory value...

"Monitor, if you see memory allocations keep dropping very"

Can you advice- what to monitor? I know, know- a lot of info, but honestly very often it's not clear and confusing....