Day 2–Trace Flag 1807–Attach Network data file

Today we are going to see how can we attach data file exists in network share with the help of trace flag 1807. By default SQL Server 7,2000 and 2005 will not allow you to attach data file that exists in network share. This is the default behavior because attach database file from network share is overhead as performance is the great factor because if network share is slow or if disruptions happen in the network share will make the database to go offline. However in some circumstances we require this to be done for testing purpose. You will be getting anyone of the error below if you try to attach the data file from network share (without using trace flag 1807).

5105 “Device Activation Error”
or
5110 “File ‘file_name’ is on a network device not supported for database files.”

For testing purpose if you want to attach network share data file you need to turn on trace flag 1807 and then give a try. I’m going to turn on 1807 trace flag and attach a network share data file as shown below. You can view the article  “Enable \ Disable Trace Flag in SQL Server” for turning trace flag in different method.

DBCC TRACEON(1807)
GO
EXEC sp_attach_db nwsharedatabase, '\\sagar-pc\db\nwsharedatabase.mdf',
'\\sagar-pc\db\nwsharedatabase_log.ldf'

trace_flag_1807_1

Once you done with your work, you can detach the db and turn off the trace flag 1807, you can use the command below

DBCC TRACEOFF(1807)

Thus trace flag 1807 is required to attach data files from network share on SQL 7, 2000 & 2005 however from SQL 2008 I’m able to attach data files from network share without turning on this trace flag.

Leave a Reply

Join the Conversation

2 Comments

  1. Pingback: DotNetShoutout
Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

SQL 2012 DTA Engine Crashes on Windows 8
Question: Some time back I wrote an article on the new feature that’s added to SQL Server 2012 DTA (database tuning advisor). I have installed S...
Day 14–Trace Flag 3505–Control SQL Server Checkpoint Behavior
What’s is checkpoint? Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from ...
Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition
Dedicated administrator is one of the new feature introduced from SQL Server 2005. This is a wonderful feature for DBA’s to troubleshoot SQL Serv...
powered by RelatedPosts