November 30, 2007 at 3:01 am
Hi all,
we have many problems after changing our system from SQL Server 2000 to SQL Server 2005 Express. Sometimes databases are destroyed and we don't know what happened.
Here comes an example from our log file, the problem database is named CAM134_TpcProAT. You will see that there is no action between the database backup (2007-11-18 14:30:01.33) and the try to open the database one day later (2007-11-19 14:30:22.24).
2007-11-18 14:30:01.33 Backup Database backed up. Database: CAM134_TpcProAT, creation date(time): 2007/08/07(12:09:25), pages dumped: 1093, first LSN: 8218:395:36, last LSN: 8218:411:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\dataExchange\backup.tmp\CAM134_TpcProAT.bak'}). This is an informational message only. No user action is required. 2007-11-18 14:30:01.36 spid56 Starting up database 'CAM134_TpcProAT_Template'.
2007-11-18 14:30:01.82 Backup Database backed up. Database: CAM134_TpcProAT_Template, creation date(time): 2007/11/15(17:42:28), pages dumped: 443, first LSN: 462:292:37, last LSN: 462:308:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\dataExchange\backup.tmp\CAM134_TpcProAT_Template.bak'}). This is an informational message only. No user action is required.
2007-11-18 14:30:01.98 spid25s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-11-18 14:30:01.98 spid25s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-11-18 14:30:01.98 spid25s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-11-18 14:30:02.19 Backup Database backed up. Database: master, creation date(time): 2007/11/14(06:28:09), pages dumped: 388, first LSN: 414:472:50, last LSN: 415:32:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\dataExchange\backup.tmp\master.bak'}). This is an informational message only. No user action is required.
2007-11-18 14:30:02.43 Backup Database backed up. Database: model, creation date(time): 2003/04/08(09:13:36), pages dumped: 163, first LSN: 38:400:37, last LSN: 38:424:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\dataExchange\backup.tmp\model.bak'}). This is an informational message only. No user action is required.
2007-11-19 10:16:57.87 spid52 Starting up database '070522_1_{3A1A3315-EB26-4525-9243-705325765585}'.
2007-11-19 10:17:21.22 spid23s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-11-19 10:17:21.22 spid23s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-11-19 10:17:21.22 spid23s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-11-19 14:30:22.24 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\SQLData\Data\CAM134_TpcProAT.MDF] in database [CAM134_TpcProAT] (6). The OS file handle is 0x00000570. The offset of the latest long I/O is: 0x00000000010000
2007-11-19 14:30:56.63 Backup Error: 18210, Severity: 16, State: 1.
2007-11-19 14:30:56.63 Backup BackupIoRequest::WaitForIoCompletion: read failure on backup device 'E:\SQLData\Data\CAM134_TpcProAT.MDF'. Operating system error 2(error not found).
2007-11-19 14:30:57.32 Backup Error: 3041, Severity: 16, State: 1.
2007-11-19 14:30:57.32 Backup BACKUP failed to complete the command BACKUP DATABASE CAM134_TpcProAT. Check the backup application log for detailed messages.
My guess: It would be helpful to set the AUTO_CLOSE option for our databases to false. Could this be the solution? May be it is also a good idea to set the recovery interval to a higher value, e.g. 5 minutes? At the moment this value is set to 0 (default).
Best Regards,
Stefan
November 30, 2007 at 3:21 am
Yes,
definetely change the AUTO_Close to false. My guess is that opening the datbase takes too long, so the backup process can't read the file and reports an error file not found.
[font="Verdana"]Markus Bohse[/font]
November 30, 2007 at 3:33 am
Thats my opinion, too. It is not so easy to disable the AUTO_CLOSE option because we attach and detach our databases sometimes. And since we are using the Express version after attaching a database this option is reset to default value (true). But I think it would be the best way and we can set this option after attaching a database with the following SQL statement:
ALTER DATABASE Northwind SET AUTO_CLOSE OFF
Thanks for your response.
Best Regards,
Stefan
November 30, 2007 at 10:24 am
i use this cursor below on our dev machine at work;
it runs as a job twice a day, which is often enough to switch any new or newly restored databases to AUTOCLOSe=off;
note i'm changing the backup strategy to simple as well, for the same reason.
if you cant schedule jobs in express, you could use osql and use windows scheduled tasks to do the same thing i beleive:
USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname checkpoint'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1
Lowell
December 3, 2007 at 2:30 am
For our situation the solution to set the AUTO_CLOSE option after attaching a database is sufficient.
But one more question:
I found so many discussions about the AUTO_CLOSE problem, but I do not understand how it can be possible that one or more databases can be really destroyed. In SQL Managament Studio the destroyed database existed but without any objects (just the top node was visible). So I tried to save the current MDF and LDF files but it was not possible to copy the MDF file to another directory. So I had to rename the file and after this I was able to use an older backup. Is this a known problem?
Regards,
Stefan
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply