SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 how do i know if logs were truncated
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 07/11/2014 :  15:50:09  Show Profile  Reply with Quote
Good afternoon, i need your help pls, is there any way to know if logs where truncated ?

this is one of the tasks achieved by our DBAs, but we need to know if logs were truncated before starting massive processes in Production system

thanks in advanced

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 07/11/2014 :  15:52:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you referring to the transaction log or a log table? Managing transaction logs is done through BACKUP LOG if your recovery model is not set to SIMPLE. You can check the backup jobs or the error log to see if log backups are occurring.

You don't want to truncate the transaction log as that breaks the transaction log chain, which affects your recovery points.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 07/12/2014 :  15:02:45  Show Profile  Reply with Quote
Im refering transactions log, because lately many massive insertion adn delete (DML operations) have failed with error The log file for database 'dm1_elite' is full. Back up the transaction log for the database to free up some log space.
And acording to the above problem i ve been told before restarting the instance i must ask our DBA truncate logs in order to make sure there is enough space for logs.

i know Managing transaction logs is done through BACKUP LOG, Im not a DBA but i want to make sure that really logs were truncated and i want to know if there is a specific command on system catalog or any evidence

thanks for your help in advanced
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 07/13/2014 :  19:00:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
You should not be restarting the SQL instance when you get that error as that does absolutely nothing for that error. And the DBA should not be truncating the logs when you get that error. Instead, the DBA needs to run BACKUP LOG more frequently and increase the file or disk.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 07/14/2014 :  15:45:29  Show Profile  Reply with Quote
ok ,you are right,thanks a lot for your recommendations, now i come up with a doubt afterward because scratching around ive found a job in sqlserver agent scheduled to run every 4 hours and this job executes a sentence like tyhis: BACKUP LOG "EVERYDATABASE" with TRUNCATE_ONLY. tHat means our DBA run this BACKUP LOG but aditionally trucate LOG ?

another question is our DBA says database dm1_elte recovery model is set to simple but you say BACKUP LOG if your recovery model is not set to SIMPLE, so BACKUP LOG should not work because of recovery model to simple right ? but editing the historial view it sayas was successful

Thanks for your help in advanced
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 07/14/2014 :  16:11:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
Your DBA is likely a junior DBA. You should never truncate the transaction log. Microsoft agreed so they removed the functionality starting in SQL 2012. Truncating the transaction log breaks the log chain, and that log chain does not start again until a full or diff is run. That means you are missing recovery points.

If the historical view shows log backups, then the recovery model didn't used to be SIMPLE. You can not run BACKUP LOG for a database that is using SIMPLE recovery model. It will error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 07/14/2014 :  16:12:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
Increase the BACKUP LOG job to every hour at least. We backup the log every 15 minutes. Some do it every 5 minutes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 07/15/2014 :  18:16:08  Show Profile  Reply with Quote
OK THanks a lot once again for your support and the last question to close this issue:

1. When you say: you are missing recovery points, it means data loss if a failure or disaster ocurs to the Database ? or what do you mean exactly ?

2. In the backup log job script we must exclude the clause WITH TRUNCATE ONLY ?

Thnaks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 07/15/2014 :  22:46:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
1. Yes because the tlog chain was broken by the truncate. Let's say the tlog was truncated at 5pm, and the next full or differential backup doesn't occur until 2am. If you need restore your database to a point in time, you will not be able to restore any of the data from 5pm until 2am. That's 9 hours of data loss. Not acceptable to most companies.
2. Yes exclude it. Truncating the log is so highly not recommended that it is no longer even supported in SQL Server 2012 and higher.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000