Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 how do i know if logs were truncated

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 system

thanks 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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 successful

Thanks for your help in advanced
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -