January 13, 2009 at 2:55 pm
I am having a problem with a backup log statement. For one database in our system I receive an error:
BACKUP LOG xbobo WITH TRUNCATE ONLY
gives me an error "incorrect syntax near 'xbobo'"
If I specify the log file name:
BACKUP LOG xbobo to xbobo_log WITH TRUNCATE ONLY
I get the same error.
This is a database running in 6.5 compatibility mode on a 2005 server. This is frustrating because I have literally hundreds of this command running on other databases in our system without any problem.
Any suggestions?
"Sacramento" Bruce Conklin
January 13, 2009 at 3:11 pm
try BACKUP LOG xbobo WITH TRUNCATE_ONLY
6.5 compatibility mode in 2005? wow. you are probably going to often find syntax problems running on that out of date a compatibility
out of interest why are you truncating your logs like this?
---------------------------------------------------------------------
January 13, 2009 at 3:23 pm
george sibbald (1/13/2009)
try BACKUP LOG xbobo WITH TRUNCATE_ONLY
Had tried that syntax also and returned same results.
6.5 compatibility mode in 2005? wow. you are probably going to often find syntax problems running on that out of date a compatibility
We have an older application with about 200 stored procs in it. About 20 of them will not compile in any newer version of SQL Server and the company does not want to spend the money to remediate the code. :w00t:
out of interest why are you truncating your logs like this?
I am running this command bracketed by dbcc shrinkfile commands to keep the logs trimmed to save storage space.
I have actually stumbled on the solution which I will describe in a separate post to keep down the clutter.
"Sacramento" Bruce Conklin
January 13, 2009 at 3:27 pm
Found a solution to the problem. Was sitting staring at the screen in a sushi-induced stupor and wondered if I didn't have to run that command against master because of the database running as an older version.
Yup, the lightning bolt had struck home, I have to use master for that command and the actual database for the dbcc commands bracketing it.
Somebody bring me another bowl of rice!
"Sacramento" Bruce Conklin
January 13, 2009 at 3:48 pm
Bruce,
thanks for posting solution. Would be remiss of me not to mention it just in case, but you know to do a full backup immediately after truncating the log?
any way you can avoid the shrinking if the log is just going to grow again as it is wasteful of resources and fragments the file, or is this a one off?
---------------------------------------------------------------------
January 13, 2009 at 3:54 pm
george sibbald (1/13/2009)
thanks for posting solution. Would be remiss of me not to mention it just in case, but you know to do a full backup immediately after truncating the log?
George, I tell people that I don't go the men's room without taking a full backup. :hehe: I have implemented the truncate jobs just ahead of the nightly backups.
any way you can avoid the shrinking if the log is just going to grow again as it is wasteful of resources and fragments the file, or is this a one off?
I need to keep our space utilization in line and our log files are the current frontier.
Thanks for your help.
"Sacramento" Bruce Conklin
January 13, 2009 at 4:32 pm
cheers bruce, I hate to ask such questions sometimes but you never know whos going to come along in the future and read this so its best to complete the picture and help someone avoid the pitfalls.
hey, a 1000 posts! 🙂
---------------------------------------------------------------------
January 13, 2009 at 4:38 pm
george sibbald (1/13/2009)
cheers bruce, I hate to ask such questions sometimes but you never know whos going to come along in the future and read this so its best to complete the picture and help someone avoid the pitfalls.
Oh, I understand. I teach full-time at a local university in addition to my "day job." I have my students install SQL evaulation copy for my classes and I have found that details are critically important when I give them instructions.
hey, a 1000 posts! 🙂
George, step away from the computer.....
"Sacramento" Bruce Conklin
January 13, 2009 at 5:31 pm
You really need to review the article I link to in my signature about managing the transaction logs.
In all reality, you are setting yourself up for failure. Either, you will need to perform a restore to a point in time and find out that you are not able to because you are not backing up the transaction logs on a regular basis, or - you are going to run out of space and the system is going to come to a screeching halt.
If you don't need point in time recovery, then change the database to simple recovery model - size the transaction log appropriately and leave it alone.
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply