| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2010-09-13 : 08:55:13
|
| how do i look at the transaction log for a database? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-13 : 09:07:44
|
| The log is not designed or intended to be human readable. What are you trying to find out?--Gail ShawSQL Server MVP |
 |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-13 : 09:12:02
|
| i need to know what is in the log...most of the stuff probably needs to be deleted but my boss just wants me to make sure |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-13 : 09:12:03
|
| i need to know what is in the log...most of the stuff probably needs to be deleted but my boss just wants me to make sure |
|
|
|
DuncanP
Starting Member
12 Posts |
Posted - 2010-09-13 : 09:33:30
|
| There's the DBCC LOG command, and there are some third party tools for reading logs from the likes of Apex and Redgate.However...In the normal course of events, you never need to actually look at a transaction log, which is why it would be interesting to know what you want to achieve. If it's just that the log file is getting large, then your best bet would be to start taking log backups and store them somewhere.Duncan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-13 : 10:14:31
|
quote:
Originally posted by dtrivedi
how do i look at the transaction log for a database?
Back the Log up. The pages backed up will be marked for reuse and overwritten. (This will stop the log growing forever).If you ever need to you can restore to point-in-time by restoring Full backup, then optionally a later Differential Backup, then all Tlog backups since.Recommendation is to backup your Tlog every 15 minutes.Examples:Your database is corrupted.Remedy: Chuck all users off. Take a final Tlog backup. Restore from last Full backup, and restore all Tlog backups since, perform a Consistency Check to make sure the DB is OK.Result: You have lost zero data.(Note that Tlog is maintained differently to data itself, thus there is a good chance if the database is corrupted that the Tlog won't be - which is helped if you have the Tlog on separate disk and, if poss, separate controller.Alternative without Tlog: Restore from last FULL backup. Repeat all work since.Second scenario: Your FULL backup is corrupted too.Remedy: Restore from an earlier Full Backup (that you have checked is not corrupted), restore all Tlog backups since, perform consistency check. If OK then zero data loss.Alternative without Tlog: Restore from last FULL backup that is known NOT to be corrupted. Repeat all work since. (May be days / weeks / months of work)In the event that Tlog backup is also corrupted restore forwards from Full Backup to a point that you determine the Tlog was not corrupted. Result = some data loss, but less than restore from preceding full backupOther use 1: Someone deleted half the customers by mistake at 10:12 this morning.Remedy: Restore last night's full backup to a NEW TEMPORARY Database. Restore all Tlogs too using STOP AT 10:11Copy the deleted customers from TEMP database back to LIVE database.DROP TEMP database.Alternative without Tlog: Restore from last nights full backup and do the same. No way of telling which customers have been added / changed today, so likelihood is that you will introduce some goofy data which users will complain about (possibly forever  )Other use 2: Someone is suspected of Fraud. You need to check the database to see what changes actually occurred to the data.Remedy: restore full backup and Tlog backup to NEW TEMP datbase. Check the data. Restore the Tlog backup(s) until a suitable later time. Check the data again. Repeat and Rinse!Alternative without Tlog: Do the same with Full backups, but you can only see how the data was each day, so anyone covering their tracks during the day will not be detectable. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-13 : 10:30:30
|
quote:
Originally posted by dtrivedi
i need to know what is in the log...most of the stuff probably needs to be deleted but my boss just wants me to make sure
Deleted?You should never consider deleting the transaction log. It's not for you to decide if the log entries are necessary, it's for SQL to decide based on recovery model and transaction status.Take a look through this article - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-13 : 10:41:15
|
| there is no log back up set up @ all for this database...where is the best how to for me to set it up? (PS i'm new @ SQL ) |
|
|
|
DuncanP
Starting Member
12 Posts |
Posted - 2010-09-13 : 11:03:19
|
| Start here: [url]http://msdn.microsoft.com/en-us/library/ms191284.aspx[/url]You may also want to look at setting up a maintenance plan: [url]http://msdn.microsoft.com/en-us/library/ms189953.aspx[/url]Duncan |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-13 : 11:26:03
|
| i gound the log file...its a .ldf file isthere way to look @ it? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-13 : 14:20:30
|
| Not really, no. There are tools - but they are used in extreme situations only (data rescue) by people who have intimate knowledge with how SQL logs work ... not really a job for the likes of you and ISet up a maintenance plan to back it up and then forget about it.You are proposing your solution ("I want to look at the log file") for a problem that I can only guess at ... we need it the other way round please! You need to explain what problem you are trying to solve, and then someone can make suggestions. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-13 : 15:29:31
|
quote:
Originally posted by Kristen
Set up a maintenance plan to back it up and then forget about it.
Or, if you don't need the ability to restore to a point-in-time, switch to Simple recovery and forget about the log.Do Not Consider Deleting It!--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-14 : 03:22:27
|
quote:
Originally posted by GilaMonster
Or, if you don't need the ability to restore to a point-in-time, switch to Simple recovery and forget about the log.
Yes, that's a good point Gail, although I always feel like I have "lost" when someone decides to use Simple Recovery Model on an OLTP database and throws away some disaster recovery opportunities - but then I'm a detail&belt-and-braces-person  |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-14 : 04:19:33
|
| If restoring to the last full backup is acceptable, so be it. Dev and test DBs should (imho) always be in Simple recovery and if it's a DB that can easily be recreated completely, or is only updated once a day, tran log backups may be a waste of time.There's nothing to indicate that the OP has a production OLTP database here.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-14 : 05:31:58
|
| Well ... we run our DEV DBs in Full Recovery model. Not uncommon to want to restore to a point-in-time prior to some experiment that went wrong (i.e. we work on the basis that we can do that, rather than being careful to have a back-out plan). But we are a small development team, that wouldn't work with lots of people wanting a 10-minute-rollback several times a day!I think folk often consider that he cost of a day's data re-entry, in case of a disaster, is acceptable (and may therefore plump for Simple Recovery Model), but overlook the opportunities of reversing "Accidentally deleted half the customers", or a full backup being corrupted, or a fraud investigation etc.But Tlog backups need a fair amount of Disk, and then Tape, space and adds some complexity to the system and, for sure, would be a complete waste of time for a copy database. Where did I read about the Astronomical database that is adding hundreds(??) of GB of data each day and has no backup, but could recreate from source data if needed. That's some recreate job!You're right, I don't know its an OLTP databases, but the O/Ps interest in the contents of the TLog suggests that it has some value |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-15 : 08:51:43
|
| well thank you all. it was my misunderstading. my director who doesn't have much knowlege of SQL databases told me we could open it and look @ it and couput it after the discussion here i explined that we cud and I backed it upThanks for ur help! |
|
|
|
|
transaction log