Comments
4 comments
-
Many thanks for your post.
The data.sdf file is the local data store for the SQL Backup, it records the backup/restore history from the SQL Backup. When you install SQL Backup, we install SQL Server Compact Edition. The data.sdf file is the data file for SQL Server Compact Edition.
SQL Server Compact Edition only allows a single connection at a time.
Can you please check to see if you have Anti-Virus software running on the SQL Server with an on-access scanner?
What I mean by an on-access scanner, some AV software will check the a file upon it being opened on a server.
Can you please ensure that the on-access scanner is disabled for this file location:
C:\Documents and settings\All users\Application Data\Red Gate\SQL Backup\Data\(LOCAL) or SQL Instance Name\Data.sdf
If you do not have AV software with an on-access scanner, can you please run an executable which is available in the Windows Server Resource Kit called oh.exe. This executable is an on-handle checker, when run against a file you can check to see what other handles are running on the file. This will help us identify as to what application has a lock on the file. -
The cause has already been definitively identified. It was the file system backup that was running not AV. The specific data.sdf file in question was backed up 1 minute before the error was logged. Prior database backups as well as subsequent backups were logged into the SQL CE tables just fine. It was just an unfortunate matter of timing.
I am looking for a solution more elegent than excluding the Data directory from the file backups. If you don't have a better solution than I would consider this a defect that needs to be addressed by Red Gate.
Let me propose two potential solutions:
1. (Simple) If it fails to open the local cache file, then it needs to retry.
2. (Better) Migrate the SQL CE tables to the SQL Server instance being backed up. This would not only resolve the issue in this thread but would also resolve ongoing corruption problems. -
We've logged this to be addressed in a future release (reference SB_4341). Ideally we'll fix the blocking issue with a more elegant solution than a retry. Thanks for reporting the issue to us.
-
This is still an issue in the 6.4 release. The problem is that sometimes backups in the source database don't get recorded in the "Server Cache" aka the SQL CE database. This is usually during times of heavy activity. Even excluding the SQL CE backups from the file system backup doesn't prevent an occasional failure to record a SQL Backup transaction log in the CE database. When it fails to record in CE, the file doesn't get shipped and log shipping breaks. I then need to manually identify the file that gets missed and copy it over to repair log shipping.
I am considering coding a work around to this problem. The work around would be along the lines of putting in a SQL Agent job step that compares the output of a sqbdata query to against the msdb..backup* tables. If a log backup is recorded in msdb but not in SQL CE, then I would perform a sqldata inserts of the missing data.
My question, is for the sqbdata insert, would it be sufficient to just insert a row into the backupfiles_copylist table? Or would I need to perform inserts into the other tables as well in order to get the the missed backup file onto the copy queue? Any guidance along those lines would be appreciated. Thanks.
Add comment
Please sign in to leave a comment.
Here is the scrubbed error from the log:
Since it wasn't able to append the log file record log copy queue the file never got copied to its intended target. I had to manually copy the file to resolve it. I am also fairly certain that this was most likely caused by a timing issue were the data.sdf file was being backed up. Is there a way to prevent this error beyond the obvious solution of excluding the data.sdf files from the OS drive backups? For example, is there an option to increase the timeout of have it retry to append into the local data store upon failure?