SQL Server Performance

Logs Not Visible after Restoration

Discussion in 'SQL Server 2008 General DBA Questions' started by AnandV, Jul 15, 2009.

  1. AnandV New Member

    Hello All,
    I have a DB backup of production server which I restored on my local m/c.
    My problem is that , when I try to view transaction logs for the database using dbcc and fn:, it only shows the logs that are generated after restoration of the database.
    Surprisngly, I find that .ldf file size is 150 MB. This means transactions are present there but I can only view those logs that come after restoration of the database.
    Could anyone please explain this behaviour ?? Also if I have to see all the logs, what process should I follow
    Waiting for answers in anticipation
  2. MohammedU New Member

    Welcome to the forum....
    I believe if you restore WITH RECOVERY option it will truncate the log and will roll back and rollforward the transactions to make the data to consistenct state...
  3. AnandV New Member

    Hello Sir,
    First of all few basic information. Databases at our production server are Have FULL recovery model. While taking backup, always a full backup is taken.
    So if I restore the DB at my m/c I will of course use WITH RECOVERY option because there are no more resotre to be done on the database. LDF file size is still 150 MB
    Also,When we take Transaction Log Backup then all the logs that are backed up are removed from transaction log. But I Perform a trnsaction Log backup. Its creats *.trn file but still I find that my LDF size is some 150 MB.
    Tell me one thing, Am I confusing between LDF file and Transaction Log ??
    Why I am asking is as I have 2 requirements.
    1. Some faulty scripts ran on some production DBs and I have to trace it. So I restored the faulty DB on my m/c to go through the log. But all I could see was log that came after restoration.
    2. I think our backup strategy is very very BAD. Because I have seen for some DBs the MDF file is 3 GB and corresponding LDF file size is 80 GB. All databases on our production server are with FULL recovery model and their full backup is taken every day. Though I think a full backup must be taken every week and transaction log backups must be taken after that, so that LDF file size is reduced. But when I tried to take transaction Log backup of database, LDF file size remained same. I am confused.
    HELP
  4. moh_hassan20 New Member

    As database is Full recovery model , it should be to do regular scheduled transaction log backups ,for example every 4 hours.
    If you don't care with regular backup transaction log (i don't recommend that ) , set recovery model simple.
    When you make log Backup , the active space is marked inactive to be latter used. You can shrink log file to free that unused space.
    Check if there is open trans by: DBCC OPENTRAN().

  5. AnandV New Member

    Okay then my backup strategy must be something like
    1. Sunday Full Backup 2. Monday Midday Transaction Log backup 3. Monday Night Transaction Log Backup.
    Instead of say 4 hours, I want transaction log backup to be taken every 12 hours. Since very few people access the web site in site so I have planned it take backup during day time. Also I didn't want to put extra stress on Server by taking backup while traffic is most expected. Is this sounds good ??
    I tried to shrink the database and Yes the sizes reduced drastically. MDF file size from 160 MB came down to 30 MB. Although LDF file size from 150 MB came down only to 130 MB. I was expecting the reverse as I had taken backup of TransactionLog after a full backup. So actually transaction logs were backed up. So after the backup, I would shrink the DB then LDF file should reduced to few KBS or MBs
    I checked with DBCC OPENTRAN(). There were no open transaction. I was still not able to view transaction logs of the database that happened before I restored the DB.
  6. AnandV New Member

    Yes I got it...I was actually shrinking database and not explicity the files. I did it and got the desired results.
    Also about the back up strategy I was thinking about something like this
    1. Sunday - Full Backup 2. Monday - Evry 6 hours log backup 3. Monday Nite - Differential backup...
    But still one more doubt that remains. If I have to view the Transaction Logs of the database whose backup I have taken, then what process step should I follow. Because if I am restoring it, then I am loosing all the transaction logs which I do not want, as I have to analyse the LOG
  7. moh_hassan20 New Member

    backup log trnsaction doesn' effect performance it may be minutes., and keep the log file small and let you being able to do a point in time recovery in case of disaster recovery.
    I suggest:
    • full backup once per week (sunday)
    • differential backup once per day at 12 pm
    • traction log every 6 hour (i.e 4 times a day)
    can you post the result of DBCC SQLPERF(logspace)
    what script you use for backup log transaction
    Are you using log shipping or transaction replication?
    Avoid shrinking database as you can , for it create internal defragmentation in the index and reduce performance.
    as a quick way to reduce the log file:
    • create full backup
    • run DBCC SQLPERF(logspace) , register values
    • set the database recovery model to 'simple'.
    • Run a checkpoint command to write the records of the transaction log to the database.
    • truncate the transaction log: BACKUP LOG yourdatabase WITH NO_LOG
    • DBCC SHRINKFILE .....
    • set the database recovery model to 'FULL'
    • run DBCC SQLPERF(logspace) , what is the new size?

  8. AnandV New Member

    Yes I perfectly agree with the backup strategy that you have given. Thankswhat script you use for backup log transaction - We do not do transaction log backup. thats why LDF files are growing out of size in our database. Nobody has given a thought on this now but I know with in few monthsthis would be a huge trouble. I want to avoid it on the first handAre you using log shipping or transaction replication? - No we have clustered server acting as Linked Server. So we do not do Log Shipping or Transaction ReplicationHere are the results of DBCC SQLPERF(logspace) before shrinkingDatabaseName Log Size Log Space used(%) StatusTestDB 160.8047 7.20953 0Its clear from this stat that the back up does not contain Transaction Log because only 7% is used space out of 160 mbAfter DBCC SHRINKFILE....DatabaseName Log Size Log Space used(%) StatusTestDB 40.24219 15.53946 0So it did work perpectly fine. I also shrinked the MDF file.So it came out that the backup that I was using didn't contain any transactions on the first hand. But suppose I have to analyse the transaction log of Production Server what strategy should I adopt while taking backup.Should it be like I take a transactionLog backup only...But to restore a transactionLog backup I need a full backup..But If I take full backup then transaction logs are being truncated......My sincere apologies for my ignort questions, but I am still learning...
  9. moh_hassan20 New Member

    [quote user="AnandV"]Should it be like I take a transactionLog backup only[/quote]
    No , it is a complete Scheduled backup plan, executed by agent automatically .
    when you want to restore the database ,for example, in case of loss data at 10 am Tues.), you do these tasks in order:
    • backup log transaction (the last before failure)
    • restore the full backup( of sunday)
    • restore the last defferential backup (of monday)
    • restore the last transaction log after last differential backup
    • restore trans before failure


  10. AnandV New Member

    Hello Sir,
    I understand this plan. This plan I will suggest for future use.
    My problem is, currently on our production server the recovery model is full and every night Full backup is taken. On one fine day in the morning we came to know that some delete queries ran on our databases. We took the backup of corrupted database(full backup, for analysis purpose). Then we restored back the last night back up to bring database to consistent state. I restored this corrupted database backup on my m/c for analysis purpose. My plan was to fetch last 100 queries that were executed on the corrupted database. Unfortunately, there is no straight way to do it through some T-Sql Command. There is this table sys.dm_exec_sql_text but it didn't turn out to be complete help.
    So I shifted my attention towards transaction log as because everything that has executed must have been logged in the transaction log. I used dbcc Log() and fn_dblog(null, null) but the information that I got was inscrutable and abtruse for me. So googled the internet for Log reader and found out one which came with evaluation version. I downloaded it for further use. But here is my problem. I look at the logs using dbcc log(), I am getting only 300 rows , which I suppose must be are the logs that have been generated after I have restored the corrupted database on my m/c. LDF file size is 160 mb but DBCC sqlpref(Logspace) gives only 7% space is used
    Is it something like, in case when we take full backup, the transaction logs are trucated. Also I didn't use command for either restoration and backup. we did with UI. And then if I want to retain the transaction log while taking backup, in such a way that they are not truncated, what should be my backup strategy ?
  11. MohammedU New Member

    Based on the info...looks like you are using SQL 2005 in that case SQL server runs backround trace all the time and it keeps the 10 files I believe...You should have check that before doing anything...unfortunately these files are delete as the number grows....
    By the way if you want to fetch the last 100 queries or some thing like that you should run on the main server where the db was hosted not on restored server because this information stored in system dbs not in user dbs...
    As I mentioned you can see the log file usage but when you are restoring with recovery I believe SQL is truncating the log and making it inactive...
    If you want to try...then try to restore the db with NORECOVERY or STANDBY then try to read the tlog using dbcc log() you may get some info...but you don't know when the delete was happened and if there was any log backups after the delete because sql truncates log...
    HTH
  12. satya Moderator

    What is the database recovery model you have used?

Share This Page