January 28, 2008 at 12:05 pm
I have an Agent job that drops and then re-creates a snapshot of a mirrored DB every night. It's been running fine since I started it up a few weeks ago. Saturday night it encountered an error:
Error 5123 Severity 16 State 1
CREATE FILE encountered operating system error 5 (access is denied) while attempting to open or create the physical file 'K:\Snapshots\dbfile2.snp'
This is one file of many that are (re)created.
I don't see anything else in Windows or SQL Server logs. The job ran again last night without any issues.
Only thing I can think of is a timing / access issue between the drop and the recreate. Anyone else have any ideas?
January 28, 2008 at 7:58 pm
That's usually because the file already exists or you don't have permission in the directory. What's the SQL that you're executing?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 29, 2008 at 1:16 am
The SQL is dynamically created due to changing filegroups/files. Essentially, it's:
DROP DATABASE olap_snap
--auto-gen the actual files here via sys.database_files--
CREATE DATABASE olap_snap ON
(Name = [fliename], FILENAME = 'k:\snapshots\filename.snp'),
(Name = [fliename2], FILENAME = 'k:\snapshots\filename2.snp')
as SNAPSHOT of olap
January 29, 2008 at 1:44 am
The only thing I can think of is that the SQL statement completes before the file system has really finished dropping the file - which shouldn't happen. Could be a race condition somewhere, but again, it shouldn't happen.
I tried running your code in a tight loop looking for @@ERROR=5123 with a variety of operations running against the source database (so the snapshot wasn't empty) and it didn't pop after a few hours.
I'll check with some friends in the dev team and let you know if I hear anything.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply