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:

 

 

---

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!

Learn More

---

 

Featured Online Courses:

• SQL Server 2022: What’s New – New and Enhanced Features 
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

 

Read Also:

• An object or column name is missing or empty
• SQL Server error 18456: Login failed for user <username> – How to Resolve
• Operating System Error 170 (Requested Resource is in use)
• There is no SQL Server Failover Cluster Available to Join
• Installing SQL Server 2016 on Windows Server 2012 R2: Rule KB2919355 failed
• Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.
• A connection was successfully established with the server, but then an error occurred during the login process.
• SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
• Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
• Fix: VS Shell Installation has Failed with Exit Code 1638
• Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
• initerrlog: could not open error log file. Operating system error=3 … – How to Resolve It
• Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
• There is insufficient system memory in resource pool ‘internal’ to run this query.
• How to Resolve: Bulk load data conversion error (type mismatch or invalid character for the specified codepage)…
• Resolving the Error Message: Rule “KB2919355 Installation” failed
• Error converting varchar to numeric in SQL Server
• Error converting data type varchar to float
• SQL Server Error 262: CREATE DATABASE permission denied in database – How to Resolve

 

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 votes, average: 5.00 out of 5)

Loading...

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

© SQLNetHub