Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-10-15 : 00:16:18
|
| Hi thereThis morning, I am shock cause the disk for transaction log is full. Looking at the database and the transaction grows 125% (36GB data and 48GB trans log) than data file. Obviosualy all the jobs associated with is not working till the space is free. So I do the routine for truncating the log, shrink the log file and backup full after that. Now I am trying to find out why is causing it. Turn out that there is a Maintenance Plan for Optimisation which is Reorganise Data and Index (reindexing). I know that if this one is running will cause logging to transaction log massively. What is the best approach to handle this.Here's my current job setup for this database:All day: Transaction Log backup from 0830 AM to 0530 PM using Veritas NetBackup.All day: Integrity Check at 0900PMSunday Only: Optimisation (reindexing) at 0930PMAll day: Secondary Full Backup (SQL) at 0100AM.All day: Primary Full Backup (Veritas Netbackup) open window from 1100PM till 0500AM. Some suggestion that I should set the database to SIMPLE prior to OPTIMISATION job and set it back once it finish. With this approach I don't think you can do with optimisation job cause you can't put an entry on this current job. Alternatively I guess NOT using the Maintennace Plan and using script instead. Any thought?Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-15 : 01:04:53
|
| Run transaction log backups much more often to keep the log file size under control. I set them up to run at least every 15 minutes 24x7, and on some servers I set them up to run every 5 minutes 24x7.I recommend that you backup to disk first, and then backup the backup and transaction log files from disk to tape. You should backup to a separate disk, not the same as the ones that holds the database files.Also, please refer to this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51150CODO ERGO SUM |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-15 : 01:18:52
|
| Thanks Michael for this info ... I might consider the transaction log backup using Veritas NetBackup.Let say I change this approach and using the transaction backup to SQL instead and running as you suggest which 15 minutes for 24x7. Is this thing to much?Beside the operation for this database is only 8AM-8PM operation. But due to the optimisation is running on the weekend which is we don't backup transaction log during this time? Should I backup during this time as well (sunday night) in order to keep the transaction log under control?Thanks |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-15 : 02:10:54
|
| You have to realize that your database is not being used only 8AM-8PM. You are running maintenance operations that are making changes to the database at other times, so you need to make sure your backups take that into account.As I said, I setup transaction log backups to run at least every 15 minutes and sometimes as often as every 5 minutes, so no, I don't think it is too much. I have well over 100 databases setup exactly like this.As I said before, I do not recommend backing up the database directly to tape. On the link I posted, I think you will find that most people here will agree with that, and the reasons for that are fairly well explained.CODO ERGO SUM |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-10-15 : 04:11:30
|
| Hi MichaelLooking at your suggestion regarding 15 minutes for 24x7 and issue on backup to tape, I might consider to redo the whole backup procedure but I had a question which bothering me which is:Due to Veritas NetBackup has 2 kind of backup which is cold and hot backup. Cold backup is SQL did the backup to disc and backup to tape. While hot backup is veritas pass the command to SQL and backup directly to disc (SAN) and to tape eventualy after 24 hours. We are using HOT backup for full and transaction log. I can remove this transaction backup and relying on SQL instead. But my question is that means I have to keep 4 x 24 transaction logs? Is there differential backup in SQL to break up into a smaller routine. I am kinna nerveous if it has many transaction logs if I need to restore this.Secondaly, let say:...09.15 PM - Transaction Log with Native SQL Backup09.30 PM - Transaction Log with Native SQL Backup09.45 PM - Transaction Log with Native SQL Backup10.00 PM - Transaction Log with Native SQL Backup10.00 PM - Full backup with Native SQL Backup10.00 PM - Transaction Log with Native SQL Backup10.15 PM - Transaction Log with Native SQL Backup...12.45 PM - Transaction Log with Native SQL Backup01.00 AM - Full backup with Veritas NetBackup01.15 AM - Transaction Log with Native SQL Backup...See with the Veritas NetBackup the backup can run whenever (it used the open window) ... it could be run @ 1AM and it could be run @ 2AM.Obvisoualy this will interfering the transaction backup ... would it?What will it happen if the full backup took 1.5 hour to backup and what happen with the transaction log backup? Are they going to be skipped? Sorry about this question cause I've never in situation 24x7 kind of thing.I am appreciated your comment. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-15 : 14:16:59
|
| If you backup transaction logs to disk the disk spaced used is the same if you backup once a day or every 5 minutes (putting aside some relatively small overhead). Backing up every 5 minutes will give you lots of small files (and some of them "empty" in all probability), backing up once a day will give you one big file.We have people here asking for help with problems restoring Veritas NetBackup, and those would put me off relying on it. However, it does have the benefit that the backups have been immediately moved "off" the disk onto tape, so you are covered in the event of a total machine failure. So you may want to think about getting the more frequent TLog backups onto tape sooner than once an hour, or (as we do) just copy them to another machine on the LAN so that a total failure of your SQL box still leaves you backup files on another machine.In terms of your Log File growing very big then increasing the frequency of TLog backups will help - if you do TLog backups every 5 minutes then the Index Rebuild logging is not going to get a chance to grow the Log file hugely before it gets backed up.We use INDEXDEFRAG instead of Index Rebuild, and we only Defrag indexes marked as "fragmented", which reduces the amount of log space too.Watch out if you are using the Maintenance Wizard default setting of "Leave 10% space free" as that will change the FILLFACTOR on ALL your indexes to 90% - which is daft for an index on an IDENTITY and probably daft on some others that you may have too, and will have, sadly, already changed the indexes from what you originally set the FILL FACTOR up to be.See also:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Rebuilding%20Indexes,DBCC%20CHECDB,Reindex,INDEXDEFRAG,Tara%20Blog%20Houskeeping%20RoutinesKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-10-16 : 04:36:39
|
| first, do you really need to be in full recovery model? if no, set it to simple and be gone with the headache, if yes, read on...you have several options, if veritas is doing the backup for you, there is an option in veritas to create a job for specific servers noh? schedule it more frequentlyif you're not going to use veritas:1. you can use the frequent log backup approach2. you can use the alert-backup approach-- an alert monitors how large the used space for the log file, then calls the backup job (only on a need to basis, less number of backup files)3. or run a log file backup before a heavy jobI prefer the frequent log backup, this gives me the option of restoring transactions at a specific point in time covered by the time intervalHTH--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Transaction Log is Full