TFS Transaction Log Backups Failing After SQL/SP2 Applied
May 29, 2007
The SQL server transition log backups for the ReportServer and ReportServerTempDB databases have stopped working ever since I applied SQL Server SP2 to my TFS. I know this because I know the date the SQL SP was applied and I see that my transaction logs started to fail immediately after application of the SP. This is noted because the backup logs for SQL server start to contain the failure immediately after SP2 application. The failure is because the databases are now set to the simple recovery model (or they were set to the simple model before application of SP2 and the previous SQL server allowed or properly ignored transaction log backups with the simple database recovery model). Why is this failing since the backup task indicates that databases with simple recovery are automatically excluded? Is the reportserver and reportservertempdb both supposed to be simple recovery? I thought reportserver was usually full recovery.
Here's the contents of TfsTransactionLogBackup_20070308160004.txt before the SP2 application:
Code SnippetMicrosoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "TEAMSERVER".
Maintenance Plan: TfsTransactionLogBackup
Duration: 00:00:00
Status: Succeeded.
Details:
Here's the contents of TfsTransactionLogBackup_Subplan_20070308180007.txt immediately after application of SP2:
Code Snippet
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "TEAMSERVER".
Maintenance Plan: TfsTransactionLogBackup
Duration: 00:00:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-08T18:00:02.
Task end: 2007-03-08T18:00:02.
Failed:(0) Database 'ReportServer' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-08T18:00:02.
Task end: 2007-03-08T18:00:02.
Failed:(0) Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-08T18:00:03.
Task end: 2007-03-08T18:00:03.
Failed:(-1073548784) Executing the query "BACKUP LOG [ReportServer] TO DISK = N'E:\SQL Server\Database Services\MSSQL.1\MSSQL\Backup\ReportServer_backup_200703081800.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServer_backup_20070308180002', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:BACKUP LOG [ReportServer] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupReportServer_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''ReportServer_backup_20070308180002'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [ReportServerTempDB] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupReportServerTempDB_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''ReportServerTempDB_backup_20070308180002'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsWorkItemTracking] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsWorkItemTracking_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsWorkItemTracking_backup_20070308180002'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsIntegration] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsIntegration_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsIntegration_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsVersionControl] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsVersionControl_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsVersionControl_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsWorkItemTrackingAttachments] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsWorkItemTrackingAttachments_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsWorkItemTrackingAttachments_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsActivityLogging] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsActivityLogging_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsActivityLogging_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsBuild] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsBuild_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsBuild_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [STS_Config_TFS] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupSTS_Config_TFS_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''STS_Config_TFS_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [STS_Content_TFS] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupSTS_Content_TFS_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''STS_Content_TFS_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TFSWarehouse] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTFSWarehouse_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TFSWarehouse_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-08T18:00:03.
Task end: 2007-03-08T18:00:03.
Failed:(-1073548784) Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'E:\SQL Server\Database Services\MSSQL.1\MSSQL\Backup\ReportServerTempDB_backup_200703081800.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070308180002', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:BACKUP LOG [ReportServer] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupReportServer_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''ReportServer_backup_20070308180002'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [ReportServerTempDB] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupReportServerTempDB_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''ReportServerTempDB_backup_20070308180002'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsWorkItemTracking] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsWorkItemTracking_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsWorkItemTracking_backup_20070308180002'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsIntegration] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsIntegration_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsIntegration_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsVersionControl] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsVersionControl_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsVersionControl_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsWorkItemTrackingAttachments] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsWorkItemTrackingAttachments_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsWorkItemTrackingAttachments_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsActivityLogging] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsActivityLogging_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsActivityLogging_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TfsBuild] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTfsBuild_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TfsBuild_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [STS_Config_TFS] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupSTS_Config_TFS_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''STS_Config_TFS_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [STS_Content_TFS] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupSTS_Content_TFS_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''STS_Content_TFS_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [TFSWarehouse] TO DISK = N''E:SQL ServerDatabase ServicesMSSQL.1MSSQLBackupTFSWarehouse_backup_200703081800.trn'' WITH NOFORMAT, NOINIT, NAME = N''TFSWarehouse_backup_20070308180003'', SKIP, REWIND, NOUNLOAD, STATS = 10
And here it is after the KB934458 has been applied:
Code Snippet
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3054
Report was generated on "TEAMSERVER".
Maintenance Plan: TfsTransactionLogBackup
Duration: 00:00:06
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-05-01T08:00:03.
Task end: 2007-05-01T08:00:04.
Failed:(0) Database 'ReportServer' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-05-01T08:00:03.
Task end: 2007-05-01T08:00:04.
Failed:(0) Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-05-01T08:00:04.
Task end: 2007-05-01T08:00:04.
Failed:(-1073548784) Executing the query "BACKUP LOG [ReportServer] TO DISK = N'E:\SQL Server\Database Services\MSSQL.1\MSSQL\Backup\ReportServer_backup_200705010800.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServer_backup_20070501080004', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Back Up Database (Transaction Log) (TEAMSERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-05-01T08:00:04.
Task end: 2007-05-01T08:00:04.
Failed:(-1073548784) Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'E:\SQL Server\Database Services\MSSQL.1\MSSQL\Backup\ReportServerTempDB_backup_200705010800.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070501080004', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
We have a maintenance plan in SQL 7.0 that is performs a full backup each night and is supposed to perform tran log backups every two hours throughout each business day. For the most part, the first tran log backup after the full backup works fine, but the others fail.
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
USE [master] RESTORE DATABASE [EmployeeRecovered] FROM DISK = N'D:BackupsNormalEmployeeFULL.BAK' WITH FILE = 1, MOVE N'Employee' TO N'D:MSSQLDATAEmployeeRecovered1.mdf', MOVE N'Employee_log' TO N'D:MSSQLLOGEmployeeRecovered1_log.ldf', NOUNLOAD, STATS = 5, NORECOVERY
[Code] ....
Now I realze the recovery point is somewhere in TranBackup2.trn and need to go back to the state where only TranBackup1.trn is applied. How can i do this ?
I set up a full backup maintenance plan for my databases and the database portion of the backup jobs completes successfully, but the transaction log portion successfully backs up master and model, but fails for the other databases with the message: Backup can not be performed on database 'msdb'. This sub task is ignored. What's the problem? Bob
I have created a backup that does once weekly BAK and every night it does TRN backup. This is in the same maintaince plan, the jobs appear as seprate jobs as they should. I have noticed my TRN is always failing, after looking into the event logs i see this.
please note this is lastest time to early:
4:22pm this is the warning that the event log gives:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'xxxxxxxx TRN'' (0xB87B2FA07E4FB74CA01A1D38134C9C4F) - Status: Failed - Invoked on: 2007-03-09 16:19:40 - Message: The job failed. The Job was invoked by User domainAdministrator. The last step to run was step 1 (Step 1).
the next is 4:22pm 18265 : Log backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), first LSN: 24624:14008:1, last LSN: 24624:14021:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:program filesmicrosoft sql serverMSSQLBACKUPStagingStaging_tlog_200703091622.TRN'}).
last is 4:21pm 18264 : Database backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), pages dumped: 93982, first LSN: 24624:14008:2, last LSN: 24624:14018:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:program filesmicrosoft sql serverMSSQLBACKUPStagingStaging_db_200703091618.BAK'}).
So for some very strange reason its running a full backup then a Transactional, for this reason i would say its failing but why is it even doing this backup? the job is only for TRN????
I setup a maintenance plan to create full backups every night and to backup the transaction logs every hour. In the backup log, it always fails on backing up the transaction log for Master and MSDB. I believe this is because the recovery model for these two databases is set to "SIMPLE". Is it ok to change the recovery model on these two system databases to "FULL"?
I am trying to create a backup for the transaction logs on one of the databases I support. The problem is in EM, the only options it gives me are a Full DB Backup or a Differential DB Backup. I have other DB's with TL backup's, why can't I on this particular one?
I have 5 databases on my sql server and they are taking backups on everyday twice. ANd we are not taking any transactional log backups.Now i have very larger log files which are more than 6 GB each.And the databases are less in size compared to the logfiles. Should I take transaction log backups with truncate option... PLease suggest me the best stratagy i should follow to overcome the problem. Thanks.
Yesterday I received a message from the Trans Log backup job that it could not complete because a full backup wasn't completed. I checked and a full backup was completed that morning 5:00 AM. This happened during the 4 PM run and again at 5 PM. There was enough space to do the backup, even though I did a shrink file on the log. I, also, ran another backup between 5-6 PM. Between both of these activities, the job completed during the 6 PM run. Is this a common occurance? Has anyone else seen this happen. I am curious. Any light that can be shed on this, would be greatlt appreciated. Tom.
I am new-ish to SQL servers, and I am having a real issue with my tansaction logs. I am running a SQL 7.0 server, on a NT 4 platform, with SQL SP4 and NT SP 6a.
I recieve error 4213, which according to the Microsoft website means that a non-logged operation has occured. So, first question, what is a non-logged operation?
I understand that essentially the error is saying that by performing the transaction log it will not be able to restore the DB to a point in time (i.e. the point of a TRN backup), but I am at a loss as to what could have caused this. Any Ideas?
I have scripted the job (which can be seen at the end of this thread), and I don't think that I have done anything wrong, but if you can check it would be most helpful. I have also ensured that the 'Truncate on checkpoint' is not selected.
Please can someone start pointing me in a direction that can help.
Thanks in advance.
Stewart
Script:
set nocount on declare @dbname varchar(40) declare @dumplogname char(80) declare @dumppath varchar (30)
select @dumppath = 'D:MSSQL7BACKUP'
declare dblist_cursor cursor for select name from master..sysdatabases where name not in ('model', 'Northwind', 'pubs', 'tempdb', 'msdb', 'master', 'distribution')
open dblist_cursor
fetch next from dblist_cursor into @dbname
while @@fetch_status = 0 begin select @dumplogname = @dumppath + @dbname + '_tlog_' + cast(datepart(year, getdate()) as varchar(10)) + replace(str(cast(datepart(month, getdate()) as varchar(10)), 2, 0), ' ', '0') + replace(str(cast(datepart(day, getdate()) as varchar(10)), 2, 0), ' ', '0') + replace(str(cast(datepart(hour, getdate()) as varchar(10)), 2, 0), ' ', '0') + replace(str(cast(datepart(minute, getdate()) as varchar(10)), 2, 0), ' ', '0') + '.TRN'
BACKUP LOG @dbname TO DISK = @dumplogname WITH INIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @dumplogname WITH FILE = 1, NOUNLOAD
I have a backup regime where i take a full backup of my production database once a day and then 3 min log backups, I backup my msdb and master once a week.
I need to write a script to scroll through a given directory and restore the log backups in date/time order(with no restore) until the last file and then restore.
I know this is availible in the backup history but if my SQl box crashes and i cant get a uptodate master and msdb the backup history will not be uptodate to restore the database to it's current state.
Can anybody help with a script? or give me any pointers of how to read in filenames in SQL (please remenber i am a SQL virgin).
I created a backup plan for transaction logs only to run every 30 minutes. It works great for all databases except the Master and my main database scdb_v2.
The routine backups up the other 3 databases with no issue.
Using SQL Server 6.5 and NT 4.0,..I'm trying to schedule a backup of a transaction log from one SQL Server to another SQL Server both running NT 4.0. I keep getting:
dbsvolopen:Backup device t:ackup emote.dat failed to open, operating system error =5 (Access denied)
I've checked every conceivable permissions entry on the target NT server and am now reading about the different security modes for communicating between SQL Servers but I'm concerned that this may not be where the problem really resides. Time is money.
Has anyone else configured a backup strategy where the database has a full backup to tape daily and the transaction log is backed up every couple of hours, without truncatre, to disk, on another SQL Server?
The Transaction log backups that I do for the msdb and master databases ALWAYS fail. The full database backups always succeed. Are the master and msdb databases not supposed to have their transaction logs backed up? Jobs where configured thru the Database Maintenance wizard. Will the jobs fail if nothing has changed in either database? I am also confused by the fact that database and log backups for the model database ALWAYS work when I expect their to be little if none activity in that database.
We are running SQL Server 2000 and making full database backups using a maintenance plan. The transaction logs are being backed up via a separate plan.
However, the transaction log backups aren't purging but the database ones are. They are both run under the same id and both write to the same directory. Old text reports also get purged fine. I've got 'Delete older than 1 week' ticked for everything.
Can anyone think of anything else to check ? Thanks
New to Sql Server, running SQL Server 2000.Our transaction log file backups occasionally fail as the size of thetransaction log gets really huge. We'd like to schedule additionaltransaction log backups. Does that require an exclusive on the databaseor can the db be used during a transaction log backup?Also, does switching to a bulk mode recovery model before a bulkoperation then switching back to full recovery mode after present anyissues?Thanks in advance.
Have a small shop and a maintenance plan that does a full backup on all user databases every night. I would have thought that a full backup (transaction log, too), would reduce the size of the transaction logs? Isn't that the point of a full backup? Mine seem to be growing larger. I do the shrink, rebuild indexes, backup the databse, backup the transaction log, clear history and folders older than 7 days. For example, the following db decreased only once during the period. But, note how the transaction log size continues to increases most of the time.
On our SQL Server 2005 SP 2 i recently updated our Backup Plan. I now make a Full backup every night, Differential backup every hour and Transaction log backup every 15 minutes. This all works fine all the time except sometimes when the differential and transaction log backups want to run on the same time and then 1 of the 2 fails. The error i get is code 0xC0010018. Error loading a task.
I made 1 Maintenance Plan with a subplan for each backup type (Full, differ....), so i can schedule them the way i want. Searched the internet for an answer but i couldn't find it. The tasks only fail when the run simultaneous. Starting them manually always works.
Anybody has an idea what i need to do to get this working properly? Thanks!!
I want to backing up my hourly transaction log backups direct to a mass storage unit as opposed to the local server. However when trying to set this up it only gives me option of backing up to local drives, even though I have a drive mappping to the mass storage unit.. I'm there is a simple around this.Would appreciate any advice..many Thanks..
Hello SQL Server 2005 SE sp1. Unable to take differential or transaction log backups. Get the error message a full backup need to be down. Have done a full backup. Immediately upon completion of full have tried diff and transaction log. Still no luck Other databases on server are fine. Full backup will restore.
I have a sql job which has to pull some data from a remote server. This job is running fine last few days and suddenly failing to execute with the following error. The same job is running fine in other servers.
Executed as user: NT AUTHORITYNETWORK SERVICE. The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. [SQLSTATE 42000] (Error 1206). The step failed.
Note: The steps I have already done :
1. Verified the MSDTC configurations and restarted the MS DTC
I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.
As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.
I have tested with DTCPing and it says all is ok.
The error messages are:
SQL 2000 > SQL 2005
OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" was unable to begin a distributed transaction.
And the other way round:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
If watching the DTC monitor, it shows an active transaction then goes back to 0.
All instances are on a 4 node cluster with 3 nodes turned off for the testing.
I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.
As for the 3rd server, SQL 2005, it is happy to talk
Anybody got a gem which I can try?
Edit: Both SQL 2005 servers are x64 while the 2000 is x86
Is it possible to load both the SQL 7 database and transaction logbackups to SQL 2000 ? I assume it will perform the upgrade during theload.Thanks,James
I'm the dba for a large company and have setup many maintenance plans for our sql servers. One of the options I have set is to delete old backups of databases and transaction logs after 2 days. For some reason, the old database backups are being deleted, but the old transaction logs backups are not. I have them being put in the same directory. Anybody have any ideas why this is happening and what if any fix is available?
Hey, my logshipping is working just fine but the name of the backup file is of a concern to me. It's using the name in the format of DbName_20061011200002.trn. I know 20061011 is the date. That's fine. 2000 is supposed to be the time but it's giving the wrong time. It's supposed to be 1500 because the time right now is 3pm. And I dont know why the last 2 digits before the .trn are for. Is this something new in SQL 2005 logshipping? SQL 2000 didn't have it. Thank you.
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. I could simply set up a jog to delete the older files, but that will only mask the issue.
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?
Hi All, We have a development server and about 25 Production servers. Whenever we did any DDL modification to development, it should be applied to all 25 different servers. I am trying to find a way to apply the changes to these servers from Development server without going to each server and run the script. Is there any way I can automate this action. There is no replication in all these servers. Can you please advise me on how to do this. This is a high priority task i have been assigned and I would appreciate any comments asap. Thanks in advance.
I am using merge replication between SQL Server 2005 and SQL Express. Some of the articles in my publication have filter conditions. Changes to subscription databases are being applied to the publication database without regard to these filters.
The easiest way to see this problem is to create a merge publication with a single table article with @subset_filterclause of '0 = 1'. Any inserts to the publication database will not be propagated to the subscription database, but inserts to the subscription database will be propagated to the publication database. Can this be right?
