SQL Backup preferences...
-
Could someone please help me on some backup questions?
Simple vs Full Recovery model: if I understand correctly, Full Recovery mode keeps the Transaction log growing forever. I had a database (about 4 GB in size) with an 8 GB Transaction Log. I changed it to Simple Recovery mode, did a database shrink on the Transaction Log only and reduced an 8 GB Trans. Log to 1 MB.
However, this decreases my protection somewhat, does it not? I was doing Trans. Log backups every 4 hours though a 24 X 6 workweek, which would allow me to recover to a more recent point in time should it be required. That is, until the Trans. Log got so big the backup wouldn't fit on the volume anymore.
But now, I can now at best restore my database to the point it was at when it was last fully backed up. I can't do periodic Trans. Log backups in this mode.
Is there a way to use the up-to-the-minute backups that frequent Trans Log backups provide without having them grow to an unreasonable size? Is there something I am missing?
I have looked through this site for an article on backup fundamentals, but nothing I have seen so far addresses this.
Another thing that's been bugging me is that the Enterprise Manager Maintenance Plan hasn't been deleting old backup files after nnn days. I have found some docs on this, but would like to hear the opinions of those of you who work in this world, rather than just that of Microsoft.
-
If you have additinal hardware try implementing log shipping. But to take log back the recovery model should be either bulk-logged or full. You can't take a log backup in simple recovery model.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Depending on the business scenario you should have the recovery model. to decerase the tlog size if you have changed the recovery model that will not solve the issue. May be some active transaction is there.
Tlog size grows because :
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files.The process of deleting these log records to reduce the size of the logical log is called truncating the log. Log truncation occurs when:
1. Backup the log
2. Checkpoint
Please have a proper database recovery plan and a maintenanace plan to check all this.
Minaz
"More Green More Oxygen !! Plant a tree today"
-
hi,
ur 1st question has been answered previously,as far as maintenance plan is concerned,once it is created u shudnt alter it i.e once a maint for DB backup is created,a job wud naturally be created.But u shudnt alter teh job bby adding further steps,if done many such probs cud crop up.
Also if needed u cud configure the DB backup as a job through scripts instead of maintenance plans.
step 1 : u can configure it to delete the old backups as an os command
--->del 'backuppath *.bak'
step 2 : setup the backup script to backup the DB
Cheers
Deepak
[font="Verdana"]- Deepak[/font]
-
you can do a full backup more frequently so the tran log backups would only be after the last full. can you do a daily full?
-
This is one of the many things I find confusing about all this: I was doing a full backup daily, yet the Transaction Log grew to about 8 GB - even though the database itself was less than 4 GB. So the checkpoint wasn't being done, or the TX Log wasn't being cleaned out, or something...
There are two distinct backup options possible here, and therein lies another point of confusion for me: what differences are there between a full backup being done by a third-party app (BackupExec is being used in this case) and a full backup being done using Enterprise Manager? Are they both supposed to perform a checkpoint and clear out the Trans. Log?
I am amazed at how complicated all this is: I bought a book written strictly to deal with the issue of backup and have spent hours in it, but am still not clear on how all this is supposed to come together.
SQL Server is probably the most complicated and yet poorly documented software I have ever run into. Should a person really have to be a DBA, just to back up a darn database properly?
-
are you doing a lot of updates in your db or a lot of deletes combined with inserting new data? theoretically this can cause the log to be twice as large as the db
i use veritas and it uses the MS API's to backup SQL so it's pretty much the same thing as SQLEM except it's easier to manage multiple machines. with veritas you will have to set up a full backup along with the log backups in separate policies I think
-
Yes, we are using Veritas at this site as well (also at another site with a SQL datatabse). I am having a hard time learning how to properly protect their data -- the documentation is all over the place, and answers I get to these postings are brief and don't really explain anything.
I am amazed at how helpful people try to be on these forums, but am also amazed at how complicated this is, and how poorly documented. I have read dozens of articles on SQL backup and Disaster Recovery, and all I have learned is that I don't know enough to be confident n the backups being done.
If anyone can point me to an article that explains this in english, so I don't have to be a DBA to understand it, I'd appreciate a link. Meanwhile, I'll keep doing full backups with Veritas and if disaster strikes, I'll have to deal with whatever data loss comes up.
-
A Checkpoint will only truncate the inactive part of the log when in Simple Recovery mode. In the other 2 modes you have to backup the transaction log in order to truncate the active portion. (In any case a Full database backup does NOT truncate the log)
jg
-
Please review these links for more information. I am sure it will help.
A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
How to stop the transaction log of a SQL Server database from growing unexpectedly
INF: Considerations for Autogrow and Autoshrink configuration in SQL Server
[PDF] Quick Recovery of Microsoft SQL Server 2005 databases using ...
[PDF] Optimizing SQL Server 2005 Environments for Resiliency
Minaz Amin"We make a living by what we get, we make a life by what we give" - Sir Winston Churchill"More Green More Oxygen !! Plant a tree today"
-
If the log for a 4 GB database grows to 8 GB in the 4-hour span between log backups, you've either got a huge amount of activitiy or a problem like an open transaction. It doesn't matter if the last full backup was last night, last month, or five minutes ago.
I prefer to avoid letting Backup Exec, Veritas, or other tape backup products get into my SQL Server. Their setup and schedule is under the control of the network admin, and he probably doesn't want to hear from the DBA every time there's a new database, or a change in recovery mode somewhere, or some other issue that affects what needs backed up and when. I set the servers up to run their own backups to a file share somewhere, and let the network admin worry about getting that file share on tape. This also decouples the maintenance windows and lets him write the SQL backups to tape during the day without slowing down the SQL Servers, and he has all night for the Exchange and file share backups.
-
Rob,
Full Recovery mode:
You can restore up to the time of your last transaction log backup (you can also restore to an intermediate time which can ocassionally be useful).
You MUST back up the transaction log regularly as well as the database.
When you backup the Transaction Log it is truncated at which point all completed transactions should be removed from from the Log and the physical file size should shrink.
For example you might do a Full backup every night and a Transaction Log backup every 20 minutes during working hours. Personally I prefer to backup both to disk initially (for a start it runs much faster) rather than straight to tape. You can then copy these disk backup files either to tape, another disk, or whatever.
The Transaction Log represents activity that is modifying data in the database. Thus its size will depend upon how many updates/inserts/deletes have been applied to the data since it was last backued up.
If you decrease the frequency of the Transcation Log backup then the TLog backup file will be bigger. The more frequently you back up the Transaction Log then the smaller each individual TL backup file will be (on average and only up to a point). It is not advisable to back up the TLog too frequently however as there is obviously a processing overhead associated with the backup. I would usually back up the TLog every 10-60 minutes (this is a generalization as it will depend upon the application and how it is being used).
Simple Recovery mode
You do not need to back up the Transaction Log (in fact you cannot do this and even if you could there would be no point to it).
You can only recover to the last full backup.
Simple Recovery mode is suitable for batch systems where electronic files are fed into the database which can be rerun if the database needs to be restored. Also for systems which are mainly for reading and only have a small number of changes made which can be easily re-entered if necessary.
For most other systems you should be using Full Recovery mode and taking regular Full and TLog backups.
Hope this helps
Regards,
David Saville
Aldex Software
-
A transaction log backup does cause the log file to shrink. It truncates the active log, meaning it reduces the number of pages in the transaction log that are considered to be still in use (assuming they are not still tied up due to an open transaction, failed replication, or some other problem). The physical file size is only changed when a shrink operation is performed manually, by a maintenance plan, or if autoshrink is enabled for the database (not usually a good practice).
-
Scott,
I think you meant to say that it does not shrink the physical Transaction Log file?
Of course this is true and my post is inaccurate in this respect. But although the physical size of the TL is not reduced until a shrink is executed this does not mean that the TL will continue to grow until a shrink occurs. When the TL backup occurs the non-active part of the Transaction Log will be cleared and further activity can re-use this cleared space.
In practice this should mean that the TL will tend to grow to a certain size, based partly on the frequency that it is backed up, and should then stay roughly at this size (assuming that the level of activity is reasonably constant). Ie it should not keep on growing if you are undertaking regular TL backups with comparable levels of activity.
A shrink command should normally then be used at the weekend to cater for excessive growth in the TLog due to heavy activity, maintenance updates, etc.
Of course if you don't back it (the TLog) up at all and you have full recovery mode set you may get a panicky call saying that the transaction log is full and the database has stopped responding - and you then find that their TLog is 200Gb in size and has filled all of the available disk space - it happened to me 2 days ago! (not a server that we are looking after I am please to say!).
Regards,
David Saville
Aldex Software
-
With the very little I know about databases in general, and SQL specifically, I just have to take it on faith that there's a reason that everything related to SQL is more complicated than anything else in the Microsoft world.
I suppose what I'm doing here looks like I'm trying to find a "shortcut" - a way of protecting SQL data without knowing how to actually build one. Well, that's right. I figure a guy should be able to protect the data in a database without knowing how to build the database in the first place. I don't have to know Word Processing or spreadsheets or accounting to backup the data created using other software...
The database in question was being protected using an E.M. Maintenance Plan. Full database backup daily, then subsequent Transation Log backups every 4 hours. I figure that should have provided me with all the protection I needed to restore the client without losing too much data. The problem is, the Transaction Log grew to a point where it was larger than the remaining space on the disk - what good reason could there possibly be for the TX Log to grow to 8 GB - twice the size of the actual database?
Anyway, it's now in Full Database mode, getting backed up once daily. Not satisfactory, in my mind. However, changing it to Full, then doing a database shrink on the TX Log, brought it down to 1 MB in size, freeing up a whack of space on the storage volume.
Now, I'm trying to find out what to do to provide better protection than the current "once a day" Full backup. I have done a bunch of reading, including the helpful posts here, but it seems like all I'm getting are disjointed nuggets of useful information. Once I get and absorb enough of them, I may be able to put together a plan that is both thorough and simple, but I'm not there yet.
David Saville's summary, along with the others posts here, has been helpful in putting this all together for me. Another posting earlier in this thread suggests that using a third-party product makes administration more complicated - requiring constant communication between the backup admin and the DBA. Well I'm not convinced of that, although leaving BackupExec out of SQL live backups may be a good strategy. There is no DBA here (the products were developed by an outside party and sold to my client) so the backup administrator is all there is.
I'm still reading and working on it, but am amazed at how tedious it is.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply