April 15, 2008 at 9:51 am
I had a error when a JOB that was created for shrink the log is executed, the message is:
" BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log. "
I was searching, and I found that this error wanna mean that I have my DB in recovery model "Full" but really I have my DB in recovery model "Simple".
Here is the code:
BACKUP LOG BDAPP WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( BDAPP_LOG ,TRUNCATEONLY )
When I executed the code in a query, this executed without problems.
I hope that somebody can help me. 😀
Kun Rdgz
April 15, 2008 at 1:33 pm
The error message means exactly what it says. BACKUP LOG WITH TRUNCATE_ONLY is deprecated and will not work in a future version of SQL Server (in SQL 2008 specifically). It will work in SQL 2005. The warning is just to let you know that it may break in a future version.
If you're in full recovery mode and you are taking transaction log backups to ensure that you can recover the DB to the point of failure, then you don't want to be running BACKUP LOG WITH TRUNCATE_ONLY. It breaks the log backup chain and prevents you from restoring to a point-in-time after that, until you take another full or differential backup.
If you don't care about bein able to recover to a point in time, then put the database into simple recovery mode and you won't have to worry about translation log backups at all.
Shrinking log files, especially when done on a regular basis is not recommended. The log will probably have to grow again, causing the system to slow down while the file grows and potentially causing file-level fragmentation.
Why are you shrinking the log on a regular basis?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2008 at 2:23 pm
Hi Gail,
Sorry, but you wanna mean that if my BD is recovery model "Simple" I don't need to Shrink the log!!??
So, what do you wanna mean too whit "shrinking the log on a regular basis? ".
Kun Rdgz
April 15, 2008 at 2:37 pm
Why do you have a job that shrinks the log file? If you're doing regular log backups, it shouldn't be necessary.
If the database is in simple recovery, the log gets truncated when a checkpoint occurs (fairly often), hence the log file doesn't grow large (usually), hence you shouldn't need to shrink it at all.
How important is this system? If there's a disaster and the DB needs to be restored, how much data loss is acceptable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2008 at 2:55 pm
Well, the BD belong to ERP Solomon Dynamics.
So...the reason of a Job Shrinking Log is 'cause previously the Log was growing until 9 GB and the size from my DB is 12 GB.
But, previously the BD was in full recovery!!!
Now I understand that If I have simple recovery I don't need a Job Shrinkig the log, right???!!!
Kun Rdgz
April 16, 2008 at 12:38 am
That's correct, but you also will only be able to restore the database using full/diff database backups.
If you take a full backup at 7pm and the next day at 5pm the server fails and the database needs to be restored, you will only be able to restore to the 7pm full backup (because of no transaction log backups)
Is that acceptable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply