Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Read transaction log file Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 6:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
vinu512 (4/24/2013)
GilaMonster (4/24/2013)
Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.


Thanks, so in simpler words it means I cant get the data back...right???......All I can do now is to set the recovery model to FULL so that something like this doesn't happen in future...right??....


yes, in addition to it sechdule transaction log backup (as per your need) so that log size will be in control and recovery to the nearest is possible.



Regards
Durai Nagarajan
Post #1445906
Posted Wednesday, April 24, 2013 5:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 9, Visits: 47
guys am a new dba , just leanring, l will like to pos , what section can l do my post
Post #1446261
Posted Wednesday, April 24, 2013 10:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
durai nagarajan (4/24/2013)
vinu512 (4/24/2013)
GilaMonster (4/24/2013)
Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.


Thanks, so in simpler words it means I cant get the data back...right???......All I can do now is to set the recovery model to FULL so that something like this doesn't happen in future...right??....


yes, in addition to it sechdule transaction log backup (as per your need) so that log size will be in control and recovery to the nearest is possible.



Thanks for the tips Durai.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1446285
Posted Wednesday, April 24, 2013 10:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
fadewumi (4/24/2013)
guys am a new dba , just leanring, l will like to pos , what section can l do my post


Hi, Welcome To SSC.
The Forum menu is very self descriptive.
For SQL Server 2005 issues you can post in the SQL Server 2005 section....there are different links for administration and TSQL, depending upon ur requirement you can post in any of them.
Similar, is the case for SQL Server 2008.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1446286
Posted Thursday, April 25, 2013 1:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
GilaMonster (4/24/2013)
Hang on, if the database that the delete was run on was in simple recovery model, why did you tell me it was in full recovery?

When restoring a database, all that you get in the log is operations after the restore. There's no reason why any log records from before would be needed and full backups only include the portion of the log over the time of the full backup anyway. That's for any recovery model.


Hi Gail,

One small doubt that is still bugging me is that when i see the size of the log, of the newly restored db, it still shows 17 GB.....if it does not have any transactions before the time it was restored then what does the 17 GB log have??


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1446316
Posted Thursday, April 25, 2013 1:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
Basically log is some works in circular motion, once the log file is full it will try to over write the backed up log or before full backup db point logs.

It grows only when it is needed and above overwrite not possible and It never returns the space to OS automatically.

go through gails article given below

http://www.sqlservercentral.com/articles/Administration/64582/


Regards
Durai Nagarajan
Post #1446320
Posted Thursday, April 25, 2013 2:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
vinu512 (4/25/2013)
One small doubt that is still bugging me is that when i see the size of the log, of the newly restored db, it still shows 17 GB.....if it does not have any transactions before the time it was restored then what does the 17 GB log have??


Probably 17GB of empty space. A restore recreates the DB as it was at time of backup, down to the exact file sizes. Hence if you back up a database with a 17 GB log file and restore it elsewhere, the log file will be 17GB in size.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1446344
Posted Thursday, April 25, 2013 10:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Thaank you very much Durai and Gail. The last week was a very important lesson. Learnt it the hard way, but learnt it well.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1446790
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse