Troubleshooting the File Activation Error in SQL Server

In this article, we will be discussing about the File Activation error message in SQL Server and ways to resolve it.

 

The File Activation Error Message in SQL Server

In some cases, under certain circumstances, when creating a new database in SQL Server using a T-SQL script that among other, specifies the physical location of the database files, you might get an error message similar to the below:

A file activation error occurred. The physical file name “[path to physical file]” may be incorrect. Diagnose and correct additional errors, and retry the operation.

When getting this error message, it means that the T-SQL statement fails and the new database is not created.

Even though there is a short workaround that can help you proceed and create the database, let’s talk first about a possible solution to this problem.

 

How to Resolve – Solution

The first thing to check when getting the File Activation error in SQL Server, is to make sure that this is not a permission-related effort.

Therefore, you need to make sure that there are the proper permissions on the folder(s), into which you are trying to place the physical files for the database to be created via your T-SQL statement.

To this end, you need for example to ensure that the SQL Server Database Engine service account, has full permissions on the specific folder(s).

To find out which service account is used by the SQL Server Database Engine you can run the below T-SQL script:

SELECT servicename, service_account 
FROM sys.dm_server_services;
GO

 

How to Resolve – Workaround

If the above does not solve the issue, you can try a workaround.

The workaround is run the new database creation T-SQL statement, without explicitly specifying the physical locations for the database files.

However, prior to doing this, you need to make sure that the “Database default locations” are correctly set.

To this end, in order to do that, within SSMS, you navigate to server properties by right-clicking on the instance name, selecting “Properties” and navigating to the “Database Settings” tab.

Below, you can see a screenshot example of the Default database location on a demo environment:

Troubleshooting the File Activation Error in SQL Server - Article on SQLNetHub

 

 


Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Enroll Now with Discount!


 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub