Category: SQL Server

How to Apply the Latest Cumulative Update for SQL Server

This post demonstrates how to apply the latest CU( Cumulative Update ) on SQL server 2019.

1)Download the latest CU4( KB4548597 ) for SQL Server 2019 for Windows here.

2) Record the current SQL 2019 version.

3) Run “SQLServer2019-KB4548597-x64” as Administrator.

a) On “license Terms” page, choose “I accept the license terms and Privacy Statement”.

b) On “select features” page, select all the SQL server 2019 instances, shared features and all installed components.

c) On “Consent to install Microsoft R Open” page, click “Accept”.

d) On “Consent to install Python” page, click “Accept”.

e) On “Check Files in Use” page, stop all the services on the list, and click “refresh check” until no more processes listed.

f) On “Ready to update page” page, review the features list. click “Update” if everything is satisfied.

g) On “Update Process” page, monitor the patching process until it is complete.

Reboot the Windows Server if requested.

4) Verify the upgraded SQL 2019 version.

How to Enable Remote Connections to MSSQL Server

This post shows step by step on how to enable remote connections to a sql server by configuring ports and firewalls , etc.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Install SQL Server 2019 on Windows Server 2019 VM of VirtualBox

After Windows server 2019 Oracle VirtualBox VM has been created as per How to Install Windows Server 2019 on Oracle VirtualBox,  now it is time to install SQL server 2019.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Msg 3702, Level 16, State 3, Line 2 Cannot drop database “DataBaseName” because it is currently in use.

Try to drop a SQL database, but get following errors:

USE Master;
GO
DROP DATABASE dbTEST;
GO
Msg 3702, Level 16, State 4, Line 1
Cannot drop database "dbTEST" because it is currently in use.

SOLUTION

Kill the blocking sessions, or set database in SINGER_USER mode with “Rollback Immediate” option to kill  and rollback the transactions.

USE [master]
GO
ALTER DATABASE dbTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE [master]
GO
DROP DATABASE dbTEST;
GO

After this, check both data files and log files are cleaned up as well.

How to Set SQL or Oracle Database to Single-User Mode and How to Check Single User or Multi User Mode

In Oracle database, place the instance in restricted mode.  The users already connected to the database will not get disconnected. You need to manually kill these sessions.

SQL> alter system enable restricted session;

OR

Startup in restricted mode.

SQL> startup restrict;

Check the instance mode

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

In SQL database,  Below are the two most popular ways to set up database to single-user mode in SQL Server:

Using SQL Server Management Studio

Right-click the database to change, and then click Properties -> click the Options page->From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Using Transact-SQL

USE master;
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Check SQL Database Single User or Multi User Mode

a)  Select ServerProperty(‘IsSingleUser’)

It should return 1, 0, or Null.

1 = Single user.
0 = Not single user
NULL = Input is not valid, or an error.

use MyDatabase
go
Select ServerProperty('IsSingleUser')

b) Select from sys.database,  it should retuen:

  • SINGLE_USER
  • MULTI_USER
SELECT user_access_desc 
FROM sys.databases 
WHERE name = 'MyDatabase'

3) From SSMS, Right-click the database , and then click Properties -> click the Options page->From the Restrict Access option.