November 20, 2008 at 5:16 am
So we've recently added another 4 servers to our SQL environment to run a particular application, as part of the app it requires the use of 10 back end databases. One of these is set to the full recovery model the others are all Simple.
when trying to define the transaction log backups on two of the servers it allows me to go through the process of enabling the primary database, I complete the backup settings and schedule screens and the job runs successfully. I then check by access the database properties once again and the enable transaction check box is now unchecked and no details exist of the transaction log backups. No job is created in the job list and therefore no transaction logs are being backed up. The other two servers work fine and have been happily backing up the transaction log for the last few days.
When I export the SQL from one of the database servers that has taken these transaction changes and run an altered (in name only) version on the server with the issue I can create the job, but on a refresh the job list, it dissapears.
I'm running SQL2005 9.0.3042. All 4 installations are identical, all setups have been completed identically however I just cannot get the transaction log backups enabled on two of the four.
I am not shipping these logs to any other secondary database and I have no monitor instance setup as I just need the logs backed up to the SAN drive and kept for recoverability purposes.
Anyone seen this issue before? I cannot see anything in any of the SQL error logs to suggest this has failed, the job creation gui indicates this completed successfully however the settings and changes are not kept.
November 23, 2008 at 3:33 am
UDBNT (11/20/2008)
others are all Simple.
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2008 at 9:51 am
In simple recovery the logs auto-truncate on a regular basis and hence cannot be backed up. There's no point, it wouldn't be possible to use such backups to restore because of the broken log chain.
Tran logs can only be backed up if the DB is in full or bulk-logged recovery and there is a current database backup.
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
November 23, 2008 at 1:31 pm
are you using a maintenance plan for this ?
If yes, upgrade to at least Cumulative Update 2 for sp2 (current is CU10, sp3 is in beta.)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2008 at 1:21 am
The problem database is defined as FULL, the wording was trying (not that clearly perhaps!) to clarify that in one environment (Pre-Prod), this database is able to be defined with Trans. Log Shipping fine, but in another (Prod), despite being able to define the TLS and schedule via the 'wizard' ok, it doesn't 'take'. From what we can see, there is no noticible difference between the two setups except one works and the other doesn't.
Appreciate it if anyone has any ideas where to look, in case we have missed something?
Thanks
November 24, 2008 at 1:24 am
are you member of the db-ower db group or sysadmin at the production instance ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2008 at 2:30 am
Yes using the full sysadm account. The strange thing is I cannot even get this to work when trying to use t-sql. for some reasons the settings just do not take. Does anyone know where these sorts of issues would be logged? I cannot find any mention of this in the normal logs so assume there's some system logs out there?
November 25, 2008 at 2:45 am
can you post the full create database statement for that database on pre-prod and on prod ?
i.e. the create statement and all the following alter database statements
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2008 at 9:14 am
Try to run a backup from the query window - backup log...
If it doesn't work, what is the error?
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
November 27, 2008 at 8:00 am
Cheers for all the reply's.
I'm unable to post any sort of create database script as the databases were initially setup by a vendor and my only involvement was making sure we can recover these. I have been able to manually backup the transaction log and have managed to get the actual LSBACKUP job created by extracting the ddl from another system, with the same setup that does not have the same problem (although until I run this via a schedule it wil not backup the tran log) and applying it.
It's a strange one!
November 27, 2008 at 8:10 am
There's an option within the maint plans to log all output to a file. If you do that, what's the exact error that you get?
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
November 27, 2008 at 8:35 am
I don't have the transaction logs being backed up via any maint plan and have not seen this option to log things to a file (but will investigate). All the transaction logs are created via the database properties in the transaction log shipping section. Maybe doing this will solve my issue but would the restore history know of these tran backups or will the recovery be a very manual process do you think?
November 27, 2008 at 8:43 am
Then find the job that runs the log backups. In the properties of the job step (second tab) there's an option to log output to a file.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply