| Author |
Topic |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2014-07-11 : 15:50:09
|
| 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 systemthanks in advanced |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-11 : 15:52:21
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2014-07-12 : 15:02:45
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-13 : 19:00:19
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2014-07-14 : 15:45:29
|
| 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 successfulThanks for your help in advanced |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 16:11:39
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 16:12:23
|
| Increase the BACKUP LOG job to every hour at least. We backup the log every 15 minutes. Some do it every 5 minutes.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2014-07-15 : 18:16:08
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-15 : 22:46:51
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
how do i know if logs were truncated