Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

SQL Server 2005 - Backup, Integrity Check and Index Optimization Expand / Collapse
Author
Message
Posted Wednesday, August 18, 2010 1:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:16 AM
Points: 547, Visits: 1,125
Dated files are fine when you want everything in seperate files, each log backup etc. If you want the log backups appended into one file or device with no init then it all goes wrong at midnight when the date changes!
You also have to ensure that there are cleanup tasks as dated files won't automatically overwrite each day. I don't have the disk space for much more than a day's backups on each server - we copy and tape backup to keep older copies so there is no justification for having the large quantities of disk space required for keeping more than 24 hours worth on line.
Post #970955
Posted Friday, November 05, 2010 7:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 62, Visits: 832
Great set of scripts! But...I am having one issue and I want to see if anyone else is having the same problem.

The problem I am having is with the IndexOptmize Procedure. For my FragmenationMedium_LOB and Medium_NonLob (between 10%-30%) I run the index reorganize. But for some reason my ldf file is growing out of control. My understanding of the reorganize feature is it doesn't have that kind of an impact on your log file. Before anyone hammers me, it is not the transaction log that is growing. Yes, after this runs I do have a large transaction log file, but the one right after that one is ok. It is just the ldf that is growing.

Database mdf file is around 25 gig
Database ldf file will start at 1.5 gig and grow to almost 40 gig in 4 or 5 days.
I am growing the file in 500MB inc.
RecoveryModel: Full

Also I am logging the output of this script and it has never reached a rebuild point over 30% its always a reorganize.

Here are my options set in the IndexOptimize Script.

@Databases nvarchar(max),
@FragmentationHigh_LOB nvarchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB nvarchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB nvarchar(max) = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationMedium_NonLOB nvarchar(max) = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationLow_LOB nvarchar(max) = 'NOTHING',
@FragmentationLow_NonLOB nvarchar(max) = 'NOTHING',
@FragmentationLevel1 int = 10,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 1000,
@SortInTempdb nvarchar(max) = 'N',
@MaxDOP int = NULL,
@FillFactor int = NULL,
@LOBCompaction nvarchar(max) = 'N',
@StatisticsSample int = NULL,
@PartitionLevel nvarchar(max) = 'N',
@TimeLimit int = NULL,
@Execute nvarchar(max) = 'Y'
Post #1016532
Posted Thursday, January 27, 2011 3:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 10:45 PM
Points: 876, Visits: 3,951
just tested this wonderful scripts and definately one of the must in job schedules.
One thing I would like to know that as soon the script completed it created the job. But in job the step comes as :-
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d ...................

and this will fail with incorrect servername and the only option I found is replace $(ESCAPE_SQUOTE(SRVR)) with real sql server name.

Am I taking this script incorrectly or do I need to modify it somewhere to reflect the correct servername while running the main script to create these jobs.

edit :- Sorry just checked that this blog is quiet old. But one thing cant understand that this script is on Ola blog from thu 20jan 2011, and the discussion seems to be started here on this website from 2/25/2008 12:21:14 AM.


----------
Ashish
Post #1054460
Posted Tuesday, March 15, 2011 2:09 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 416, Visits: 1,055
Can someone please let me know if this is a bug or if I am just dreaming...

I started looking at this process and running through it...and tried it out. Given the following scenario let me know if this is valid or not.

you have a DB in full recovery mode and are using this process to do your backups...

The parameters I had set were as follows...
SET @Databases = 'DB_UTILS'
SET @Directory = 'E:\Backup'
SET @BackupType = 'LOG'
SET @Verify = 'Y'
SET @CheckSum = 'Y'
SET @NumberOfFiles = 1
SET @CopyOnly = 'N'
SET @ChangeBackupType = 'N'
SET @CheckSum = 'N'
SET @NumberOfFiles = 1
SET @Execute = 'Y'


I set it to a situation where you have a full backup plan running on a DB...the DB is in full recovery mode and has a full backup already and you are just motoring along with log backups.

SOMEONE or SOMETHING switches the db to simple mode for what ever reason.

Now your log backup job comes along and fires off with basic inputs...listed above...and I didn't have the 'ChangeBackupType' enabled since on a VLDB it would be bad to fire a full during the day due to impact to the system...I instead would want to be notified to check something out and then make a decision.

Again, unless I am missing something there is a little fault in the logic...

    IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE'
AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 0
AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL))
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabase = 'master')

This segment will eval that it is now a log backup but the recovery is simple and totally pass over the backup steps and then jumps to this block...without so much as a peep...

    -- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID

looking at my directory no file is written and I am none the wiser that it didn't run.

I again might be taking things out the intended context but this kind of occurrence while rare does happen and I just wanted to comment and see if someone can verify this for me.


Lee
Post #1078639
Posted Tuesday, March 15, 2011 2:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 62, Visits: 832
I don't know if this answers your question or not but I run 3 different jobs.

The databases I want to be part of my transaction log shipping is set to Full. Everything else simple.

Basic Logic
Sunday(Midnight) - run full backups during a weekly maintenance.
--Every hour TLog.
Monday - Saturday (Midnight)- Diff Backups
--Every hour TLog.

1st job - Full Backups (Sunday Only)
2nd Job - Diff Backups (Mon-Fri)
3rd Job - TLog/shipping (At the top of the hour - after maintenance windows)
Post #1078649
Posted Tuesday, March 15, 2011 3:34 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 416, Visits: 1,055
mcliffordDBA (3/15/2011)
I don't know if this answers your question or not but I run 3 different jobs.

The databases I want to be part of my transaction log shipping is set to Full. Everything else simple.

Basic Logic
Sunday(Midnight) - run full backups during a weekly maintenance.
--Every hour TLog.
Monday - Saturday (Midnight)- Diff Backups
--Every hour TLog.

1st job - Full Backups (Sunday Only)
2nd Job - Diff Backups (Mon-Fri)
3rd Job - TLog/shipping (At the top of the hour - after maintenance windows)


Hate the session expired deal :) had a nice reply and it was lost.

Anyways here is a quick summary of what I wrote.

I had a test db in full recovery...ran a full backup with the stored procedure...then ran several log backups...

I switched the database to simple recovery mode to simulate some 'event' that would break the log chain...

then attempted to run the log backup again...it ran with no error and listed the following...

DateTime: 2011-03-15 16:09:53
Database: [DB_UTILS]
Status: ONLINE
Mirroring role: None
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
Differential base LSN: 137000000672800036
Last log backup LSN: NULL
DateTime: 2011-03-15 16:09:53


This segment of code gets skipped because it the DB is in SIMPLE mode

    IF @ChangeBackupType = 'Y'
BEGIN
IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') <> 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabase <> 'master'
BEGIN
SET @CurrentBackupType = 'DIFF'
END
IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL AND @CurrentDatabase <> 'master'
BEGIN
SET @CurrentBackupType = 'FULL'
END
END


and also here because it is it is a LOG backup but the database is in SIMPLE mode

    IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE'
AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 0
AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL))
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabase = 'master')

the whole backup process is skipped and you loop through.

No backup occurs...

again I am wondering if I am the one doing something wrong here.


I am running the debugger on a SQL 2008 R2 server for testing.
Post #1078692
Posted Tuesday, March 15, 2011 3:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 62, Visits: 832
Yes, setting your database to simple will break the chain. You can only take log backups when you database is in Full or Bulk Logged.

You will need to set the database(s) back to Full or Bull Logged, take your full backup and start the transaction Log backup process.

I would have to say the script is functioning as expected

Post #1078697
Posted Wednesday, March 16, 2011 7:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 62, Visits: 832
Any luck with your testing?
Post #1078983
Posted Wednesday, March 16, 2011 8:05 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 416, Visits: 1,055
mcliffordDBA (3/16/2011)
Any luck with your testing?


mcliffordDBA (3/15/2011)
Yes, setting your database to simple will break the chain. You can only take log backups when you database is in Full or Bulk Logged.

You will need to set the database(s) back to Full or Bull Logged, take your full backup and start the transaction Log backup process.

I would have to say the script is functioning as expected



Hi there...I wasn't able to reply last night (no internet connection). I do understand the concepts of backups and log chain and that is what I was trying to point out in my question.

I know the normal process...full backup...diff / log. I wanted to continue testing to make sure what I think is happening is actually correct before I attempt to carry this one. Ola contacted me and I am attempting at the moment to create the scenario for him to have a discussion.

The point I was trying to make was if you had your db set up in full recovery mode...had a full backup...and then subsequent differentials and / or log backups. What happens when the log chain is broken by putting the database into SIMPLE recovery mode.

My event was supposed to highlight the change happens without your knowledge (i.e. you aren't the one doing it).

So you would think the database backups are continuing on the hour and at least in my intitial test of walking through the debugger it appeared for some reason it wasn't catching and it would totally skip over the database...which is something I wouldn't personally want.

I had taken some of the code out that related to the multiple backup software solutions that were coded in the script since I am not using any of them.

I re-deployed the original script from Ola and am about to work up a test case to see if I screwed something up.

I plan on sending it to Ola when I am done so he can see if it is correct or not.

Thanks for checking up :)
Post #1078989
Posted Wednesday, March 16, 2011 8:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 62, Visits: 832
Interesting, Never thought about testing that. Thanks for the info.
Post #1079018
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse