| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2011-11-07 : 22:05:21
|
| Hi.I have a job that deletes rows from a table every 15minutes.The amount is not great, it is 20-30 rows at every job execution.What i want to ask is if this will raise the transaction log a lot because even if i am not deleting many rows , i still delete every 15 minutes so at the end of the day i may have 30x4x24 =2880 rows a day.I don't want to log any row but i cannot truncate because i am keeping some rows in the table.Any advise?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-08 : 00:23:38
|
| Hi.Full recovery model.The backup is the database on a zip drive daily.I'm not in charge of this so i don't know if the tlog is backed up but i'm sure the main database is backed up every day. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-08 : 00:42:40
|
| Will do.Can you tell me what problems i may run in the long run,or point me to a link?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-08 : 00:44:57
|
| Well if you aren't backing up the transaction log and the recovery model is set to full, the file is going to continually grow until it either hits its maximum size or until you run out of space on the drive. So there'll be downtime here either way. Make sure you find out the answer and get it corrected if needed. If it isn't being backed up, your options are to start backing it up regularly, like every 15 minutes, or change your recovery model to SIMPLE. Please be aware of what SIMPLE and FULL means before making a switch though as it affects your point-in-time recovery options.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-08 : 01:20:47
|
| Thanks. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-08 : 04:29:27
|
| Maybe this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-08 : 18:37:31
|
| Thanks.I asked and the main log is backed up too.Can rollback transactions be used in bulk model sufficiently?I am using almost every update sp with rollback transaction so i wouldn't like to have any problems.An up to 3 days data loss can be accepted. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-08 : 19:17:49
|
| Hmm.Sorry i didn't get the last comment(non native english language).I can use bulk model with transactions?Is that what you wrote?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-08 : 19:20:20
|
| Oh sorry, I thought you were asking about BULK INSERT. I guess you are asking about the BULK_LOGGED recovery model. Yes you can rollback transactions using that recovery model. You can rollback transactions even with SIMPLE recovery model.The recovery model just determines what happens after the transaction completes (commit or rollback), it has nothing to do with what happens during the transaction.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-08 : 21:11:37
|
| Ok,got it.Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-09 : 03:13:59
|
| "up to 3 days data loss can be accepted."Sounds like SIMPLE recovery model will do for you then, with daily backups.We use FULL recovery model to:Be able to restore with the minimum data lossBe able to restore to point-in-time (e.g. to investigate possible fraud, or some other "how did this happen" type question)Have additional safeguard against database corruption. A corruption that went unnoticed for a while would quite likely be cured by restoring the last "clean" full backup, and all Log backups since.To be able to use Log shipping to a separate disaster-recovery server in case of total loss of the primary server.If you don't need any of those benefits then SIMPLE should be sufficient for you. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-09 : 20:23:29
|
| It's not my call Kristen.I would like all these options but it's not up to me.Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 03:36:10
|
| Well currently you have all those options as you are using FULL Recovery Model. However, for an application that can tolerate a 3-day data loss you could use SIMPLE.You are having to work on issues such as minimising Logs, because you are using FULL, which you would not need to spend time on using SIMPLEAs others have said, ask your Admin folk how often they back up the Log file. If its "never" you will get Disk Full at some point! if its once-a-day then the LDF file is probably huge, in comparison to what it needs to be, and "every 15 minutes" is, in my experience, the most commonly recommended interval for log backup.The LDF Transaction Log File will grow to hold the largest transaction set that it has ever seen. If it is backed up once a day that will be the size of all the transaction on the "busiest" day. If the log is backed up every 15 minutes then the LDF only needs to be large enough to hold the "busiest" 15 minutes.The total size of all the backup files, taken every 15 minutes, will be the same as the single once-a-day log backup file (well, there will be some "overhead" in each file), so there is rarely any good reason for not doing backing up the log "frequently" |
|
|
|
|
deleting rows affect transaction log?