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 |
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
 Posted - 2006-05-20 : 19:27:06
|
| Hey guys. I have a question concerning the process a backup does.Let's say i have MY_DB.mdf set to start at 1mb and unlimited growth by 1mb, and recovery mode is set to full.Let's also say the log file MY_DB.ldf is set to start at 1mb and unlimited growth by 1mb.First Question:This means the more data is added past it's current size, the file for which data was entered will grow by 1mb, forever if left untouched, right?Next Question:When I do perform a full backup, let's say a manual backup for now and that I'm using SQL 2005, does a full backup clean up the log file? What I mean by this is, if there are 10000 log entries in the log file, does a backup clean up some of those entries?---Reason for this forum:I ask this because I have a test database I am working with in conjunction with ApexSQL Log ([url]http://www.apexsql.com/sql_tools_log.asp[/url]) to test out the software. What I just ran across is that being just a test database I have not backed it up, rather have another copy of it from before I started adding data. It's properties are as described above. The ApexSQL software reads transactions logs allowing one to audit database activites and even reverse any transactions. Before I started using it, I decided to delete about 50 records. Then, a week later, I performed a backup (remember I have never done a backup before) and then I ran the software. The transactions shown in the software only dated back about 2 days earlier, not a week ago. I also looked at the size of my log file and it was only 1mb, and I expected it to be larger. i didnot, however, check the size before any of these steps. That is why I ask if in SQL 2005, does a full backup clean up and/or shrink the log file?- RoLY roLLs |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-20 : 19:38:26
|
| FirstYes - what's the other option - forever means until the disk is full.NextYou can restore log files across full backups so a full backup can't affect the log.Until the first full backup the database the log gets truncated on checkpoint. Without a full backup the logs can't be restored so they are discarded.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2006-05-21 : 16:10:37
|
| First one...gotcha, yeah till disk gets full. No problem there.Second one...so once a database goes into production, and say 500 transactions occur. Let's say log file started at 1mb, it will stay at 1mb and keep truncating the data until the first full backup? (ie: say that every 50 transactions would fill 1mb in the log file. By the end of the 500 transactions, the first 450 will be gone and only the last 50 will remain?)- RoLY roLLs |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-21 : 17:07:17
|
| Nope.If it's using the simple recovery model (or has never had a full backup) then the log will be truncated at every checkpoint. This just sets the pointers to allow space to be reused. So if a checkpoint happens after every transaction then the log entries will keep getting overwritten (I suspect - it might cycle through though).Have a look athttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 04:20:52
|
| "If it's using the simple recovery model"... and conversely if its using FULL Recovery model the log file will keep growing and growing until a Transaction Log Backup is made (or until the TLog is truncated, but that's not a sensible way to manage the Log file!)So if you want recovery to point-in-time then go with Full Recovery Model and set up TLog backups for every 15 minutes, or so, and make Full Backups once a day, or so. Otherwise set the Recovery model to Simple and just set up Full Backups as often as you would like to be able to recover to.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-22 : 04:57:33
|
| >> ... and conversely if its using FULL Recovery model the log file will keep growing and growing until a Transaction Log Backup is madeUnless a full backups hasn't been made. That's a common scenario with test systems where you don't make a full backup.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 05:31:12
|
| Good point Nigel, thanks.Kristen |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2006-05-22 : 09:25:36
|
| Thanks Guys!- RoLY roLLs |
|
|
|
|
|
|
|
|
Backup Process