May 28, 2008 at 5:01 pm
Per MS this will be depricated. I use this to shrink my TX logs on Sunday mornings. Does anyone know what the replacement will be? I checked in SSIS, there is a "Shrink Database Task" however, it does not shrink the transaction log file.
May 28, 2008 at 5:18 pm
Warren Peace (5/28/2008)
Per MS this will be depricated. I use this to shrink my TX logs on Sunday mornings. Does anyone know what the replacement will be? I checked in SSIS, there is a "Shrink Database Task" however, it does not shrink the transaction log file.
First, you should not be shrinking the log files on a regular basis. You should only shrink the log or the database after an unusual operation has occurred.
Second, where did you see that MS has deprecated DBCC SHRINKFILE?
Jeff
See: http://www.karaszi.com/SQLServer/info_dont_shrink.asp for further information on why you shouldn't shrink.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2008 at 6:00 pm
My applogies, it was the "BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG". It appears in my log files, see below...
I run this just before I do the DBCC SHRINKFILE.
Anyone know wht the replacement is for this?
Thanks
Gene Laoyan
2008-05-18 06:00:01.43 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:01.48 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:01.66 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:02.13 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:02.37 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:03.54 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:06.69 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:06.94 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:07.15 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:07.60 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:07.85 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
2008-05-18 06:00:08.21 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
May 28, 2008 at 6:07 pm
SQL2008 ? No direct replacement I'm afraid. Change recovery model to Simple and then back to Full.
FYI: List of discontinued database engine functionality in SQL 2008 is here: http://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx
Regards,
Slawek
May 28, 2008 at 10:41 pm
This is a warning message logged when you have taken BACKUP with NO_LOG and TRUNCATE_ONLY options . Take full backup and you will not get this message.
Regards,
Rajeev
--ALWAYS BE POSITIVE !!
May 28, 2008 at 11:09 pm
Yes - that will no longer be supported and the supported way is to change the recovery model from full to simple, then change it back to full again.
Either way, by performing either the backup log with truncate_only, or changing the recovery model you have broken the log chain and will not be able to recover you system past that point. That is why the system will not allow you to even create a log backup until you have performed another full backup.
I would recommend not performing this on a regular basis at all. I would figure out why the log files grows and see if I could modify that process so that the log file does not get too large. If that is not possible - then let the log file grow to the max size, increase it to be a bit larger than that and leave it alone.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2008 at 12:33 am
You certainly should not be truncating the trasdaction log on a regular basis. If you don't care about been able to restore to a point in time, set the recovery model to simple.
Whe does your full backups run, when do you run this truncate/shrink operation?
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
May 29, 2008 at 11:00 am
Lets everybody stand back a little and take a good look at this.
Assume this is a production SQL 2005 DB and the recovery model is Full.
A dB get's data into it via user input or bulk insert or SSIS. 50 million records get added from an SSIS package. It's logged in the TX file. A daily FULL backup occurs in the early morning 2AM.
Now tell me again what is the purpose of the transaction log data after the the daily FULL backup has occurred? If a transaction has not been rolled back by whoever needs to do it by the end of the day then consider the data "GOOD".
Considering my process to "Prune" the TX log files occurs every Sunday, It would be safe to assume the data in the DB is correct/fine/accepatable, basically no roll back will be necessary.
We all know what happens to a DB when it is set to a "Simple" recovery and the backup job is set, right?
The DB that is set to "Simple" is completely skipped and no backup is done and transaction will not be recorded. That's fine for development environments though.
The TX log files grow because of many many many many transactions occur ranging from bulk updates to user updates to table synchronizations from development up to production.
May 29, 2008 at 11:33 am
Warren Peace (5/29/2008)
Lets everybody stand back a little and take a good look at this.Assume this is a production SQL 2005 DB and the recovery model is Full.
A dB get's data into it via user input or bulk insert or SSIS. 50 million records get added from an SSIS package. It's logged in the TX file. A daily FULL backup occurs in the early morning 2AM.
Now tell me again what is the purpose of the transaction log data after the the daily FULL backup has occurred? If a transaction has not been rolled back by whoever needs to do it by the end of the day then consider the data "GOOD".
Considering my process to "Prune" the TX log files occurs every Sunday, It would be safe to assume the data in the DB is correct/fine/accepatable, basically no roll back will be necessary.
We all know what happens to a DB when it is set to a "Simple" recovery and the backup job is set, right?
The DB that is set to "Simple" is completely skipped and no backup is done and transaction will not be recorded. That's fine for development environments though.
The TX log files grow because of many many many many transactions occur ranging from bulk updates to user updates to table synchronizations from development up to production.
There are a few problems with this line of thought:
- There's no way to tell it to truncate the log "up to the last GOOD full DB backup", so truncating a t-log means you're breaking the chain regardless, since you will invariably be removing some transactions that aren't covered by the FULL backup.
- Like it or not - there's no really good 100% accurate way to make sure that the DB backup is "good" without testing it, so having the T-logs allow you (if nothing else) to replay activity should your last (or last several) DB backups happen to be no good.
Again - if you're not interested in point in time restores, and are confident your DB backups are 100% good - go with Simple Recovery.
Besides - you REALLY don't need to truncate everything to do a shrink (even under the assumption that shrinking a log file is a good idea, which IMO is not something to be done routinely). If you just left it the size it "wants to be" there wouldn't be this continuous growing and shrinking going on (which kills your perf, and fragments the OS drives).
If you just "have to" shrink the log - just run a T-Log backup, and then shrink the T-Log if need be. Otherwise - just leave it alone. It will find its size and then stay there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 11:43 am
Matt, you hit on a perfectly good point and you just reminded me that yes, the TX log files are being backed up on an hourly bassis.
So, I guess one can say if the TX files ARE being backed up truncating the TX file on a weekly basis should be OK? I say this because during that FULL DB backup hours the backup of the TX files are given that window to not backup then start again after the FUL DB backups are completed.
I am having to do this because we are running short on disk space. This is a stand alone server not attached to a SAN and no room for expansion on the HD.
May 29, 2008 at 11:58 am
If you need the T-Logs backed up, then I'd say - skip truncating. Ever. If you need to shrink then by all means, do so, just do it right after a log backup. You'll still reclaim some disk space, and you won't end up destroying the transaction chain.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 12:05 pm
Matt, Big thanks to you!
My next attempt to reclaim disk is to use Winzip commandline to script the compression of each .bak file on a nightly bassis after backups occur. This has prooven great in the development env. Typical compression ratio is "ALMOST" 10:1 per file.
A SQL server never has too much disk space. :o)
May 29, 2008 at 1:08 pm
Warren Peace (5/29/2008)
Matt, Big thanks to you!My next attempt to reclaim disk is to use Winzip commandline to script the compression of each .bak file on a nightly bassis after backups occur. This has prooven great in the development env. Typical compression ratio is "ALMOST" 10:1 per file.
A SQL server never has too much disk space. :o)
What good is shrinking the log file doing? Since the log file will need to grow back to the same (or larger) size to handle the number of transactions you are doing - you still need that storage available. Think about what will happen to your system if you shrink the log file, then use up the storage that you need to complete your transactions? Your system will come to a sudden halt when you run out of storage and the transaction log cannot grow.
Compressing the backup files is a good idea. Also, consider moving the backup files after they are compressed to another system where you can keep several days worth instead of keeping those files on local storage.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2008 at 12:15 am
Warren Peace (5/29/2008)
So, I guess one can say if the TX files ARE being backed up truncating the TX file on a weekly basis should be OK? I say this because during that FULL DB backup hours the backup of the TX files are given that window to not backup then start again after the FUL DB backups are completed.
The problem thre is that a truncation of the transaction log breaks the log backup chain, no matter how many other transaction log backups you have.
Let's say you do your full backup sunday morning at 2am.
Transaction log backups at 3am, 4am, 5am, 6am
Shortly after 6am (for eg, since I don't know what time this runs), the transaction log is truncated.
7am, the transaction log is backued up again. However, there are missing log records (discarded by the truncate)
8am, 9am, 10am, 11am.... 6pm log backups occur.
Say that shortly after 6pm (for whatever reason) the database gets damaged and needs to be restored.
The latest log backup that is restorable is the 6am one, due to the broken log chain. Once a fill/diff backup is taken after the truncate, you can again restore to point-in-time.
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
March 9, 2009 at 9:22 am
Hi,
What I do is I do a nightly full backup of the USER databases to a network drive (it is relatively small so no issues with network congestion)
and I re-initialise the log backup file also at this point.
=============================================
STEP 1: BACKUP DATABASE sample_db TO sample_db WITH INIT
STEP 2 (Conditional on STEP 1 being successful): BACKUP LOG sample_db TO sample_DbLog WITH INIT
go
==============================================
In addition, I do an an hourly differential backup
==============================================
BACKUP DATABASE sample_db TO sample_db WITH DIFFERENTIAL
go
==============================================
and a 15 min log backup
==============================================
BACKUP LOG sample_db TO sample_DbLog WITH NOINIT
==============================================
In addition to the above, I do a nightly FULL Backup of the four SYSTEM dbs-master, model, msdb and distribution also to the same network folder.
The network folder into which all database backups (FULL,DIFF and TLOGs) are stored gets backed up to Tape every night. My recovery model is FULL.
Has anyone any comments on my backup strategy? I need to be able to recover to a point-in-time. There are so many different flavours of backup/recovery strategy out that it can be difficult to select the one that you're going to go with.
Constructive criticism would be welcomed.
Seán
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply