This seems so complicated I have been reduced to detaching the db every day (mostly) and making a zip copy of it for backup and then reattaching it. So far so good except the log file has grown to 377Mb.

I have recovery set to SIMPLE.I managed to cook up a maintenance plan that runs every night at 3am that does two things...1. Does a FULL backup of the database2. Backs up the transaction log.

This seems to work in that it gives me two files every night.(a .bak file and a .trn file).But the log keeps on growing and despite studying this endlessly including on this site I'm lost in the terminiology between truncate and shrink and simple and full. I have even read the treatises written by various gurus.Sorry, but I'm not a DBA nor do I plan to be one.I need to know how to get a daily backup of the database, how to run a differential backup maybe every hour during the day, and how to get the log to stop growing... because once I've done my backup the log file size should reset itself or something.I like to use the SSMS for the setup because the DBCC commands are kinda Greek to me (no disrespect meant to people from Greece).I need to know what to do.Should I set the recovery model to FULL?Should I continue to do a full backup every night?How do I do a differential backup hourly?Is there a way to back up the log and do I need to do that, and how to I get it to reset itself so that it is smaller again?Thanks

a (.LDF) log file, will always grow, when you backup the log, you are removing all the transactions that have been flushed to disk. but it will not affect the log file physical size. it just increases the free space within the log file. the truncate option for the backup log chops off the free space at the end of the transaction log.

To physically resize the log file, if it is too big, is to do a backup log, then use dbcc shrinkfile, and shrink the log file, this can cause fragmentation which you can sort later on, by rebuilding the indexes.

How it works: - Job dbnameFULL starts with disabling dbnameLog_Incremental renaming it to "BU_PROC_Disabled_dbnameLog_Incremental". (The rename is done just to keep track the backup sequence is on going)

Right before its last step, it sets the next run-time for the dbnameLog_Init jobas last step , it copies all bak.files to its own safe zone.

- Job dbnameLog_Init has a "run only once" schedule that is being modified by dbnameFull job. Job dbnameLog_Init makes LOG backups (using the with INI parameter) Before it copies the bak files to its own safe zone, it enables (and renames back to original) the BU_PROC_Disabled_dbnameLog_Incremental job.

-edited-

We only shrink files if we run out of space or after we planned a huge data cleanup (and don't expect teh data to grow within a reasonable periode of time !Because of the frequent log backups, the log file(s) shouldn't grow that much, unless someone performes an exceptional huge transaction)

Johan

Don't drive faster than your guardian angel can fly ...but keeping both feet on the ground won't get you anywhere

Thanks to Silverfox and ALZDBA for the quick replies!OK, if I do this am I on the right track?:1) Manually: Set recovery model to Full Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size Do a Shrinfile of the log using the DBCC command to get the file size back down2) Set up scheduled jobs steps toBackup the database fully in the middle of the night i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)at some point schedule a log backup with INIT (I don't get that part)

ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?Thanks

PhilM99 (9/24/2009)Thanks to Silverfox and ALZDBA for the quick replies!OK, if I do this am I on the right track?:1) Manually: Set recovery model to Full Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size Do a Shrinfile of the log using the DBCC command to get the file size back down2) Set up scheduled jobs steps toBackup the database fully in the middle of the night i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)at some point schedule a log backup with INIT (I don't get that part)

ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?Thanks

You will understand that once you get to your statement

(what does that mean, I'll look itup)

The INIT parameter means it will overwrite the given backup file's content.

The NOINIT parameter means it will NOT overwrite the file's content.

I've built in this little piece of rocket science with the construct of these 3 jobs because I don't want to have two backup jobs running at the same time and because I aim for incremental log-bak files having a single file spanning between two full backup sequences.

The separate Log _Init job is in place because the last step of the Full bak job has this xcopy step towards the safe zone.

By default, I alter the schedule of the log_init job the current time + 30 minutes.Since the log_init will overwrite the log-bak file, size of that file will be small.After that, the incremental log backup job is activated, and the circle is complete.

Johan

Don't drive faster than your guardian angel can fly ...but keeping both feet on the ground won't get you anywhere

PhilM99 (9/24/2009)Thanks to Silverfox and ALZDBA for the quick replies!OK, if I do this am I on the right track?:1) Manually: Set recovery model to Full Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size Do a Shrinfile of the log using the DBCC command to get the file size back down2) Set up scheduled jobs steps toBackup the database fully in the middle of the night i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)at some point schedule a log backup with INIT (I don't get that part)

ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?Thanks

do a log backup using the backup log command, and apart from that you should be set to go.

--------------------------------------------------------------------------------------Recommended Articles on How to help us help you andsolve commonly asked questions