Problem Description:
SQL backup fails with the following error: SQL8 or INTERNAL 65535
Traceback
[2024-12-04 09:38:28,766] [ERROR] <_MainThread(MainThread)> Error while executing and fetching data with instance [InstanceName]
[2024-12-04 09:38:28,766] [ERROR] <_MainThread(MainThread)> Error in executing SQL Query List: [u'use [DBName]', 'select type from sys.database_files']
[2024-12-04 09:38:28,766] [ERROR] <_MainThread(MainThread)> Unable to process database [Sustainalytics] for VDI diff backup: ('42000', u"[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Database 'DBName' is already open and can only have one user at a time. (924))")
Cause
This issue occurs when the database is:
In single-user mode, which restricts access to only one user connection at a time.
In maintenance mode, which may temporarily set the database to single-user mode or lock access.
Resolution:
Step 1: Verify the Database Mode
Connect to the SQL Server using SQL Server Management Studio (SSMS) or any SQL client.
Run the following query to check the access mode of the database:
SELECT name, user_access_desc FROM sys.databases WHERE name = 'DBName';
Step 2: Change the Database to Multi-User Mode
If the database is in single-user mode, change it to multi-user mode using one of the following methods:
Using SQL Query:
ALTER DATABASE DBName SET MULTI_USER WITH ROLLBACK IMMEDIATE;The WITH ROLLBACK IMMEDIATE clause disconnects any existing connections to the database to enable the mode change.
Using SSMS:
Right-click on the database and select Properties.
Go to the Options tab.
Under the State section, change Restrict Access to MULTI_USER.
Step 3: Reattempt the Backup
Once the database is in multi-user mode, reattempt the log backup. Ensure no other active sessions are locking the database.
Always ensure backups are scheduled outside of maintenance windows or disable the scheduled backup during maintenance to prevent this issue.