The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

USE [master]
GO
CREATE LOGIN [TESTUSER] WITH PASSWORD=N'Password123_4U', DEFAULT_DATABASE=[TESTDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master];
GO
USE [TESTDB]
GO
CREATE USER [TESTUSER] FOR LOGIN [TESTUSER]
GO
USE [TESTDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TESTUSER]
GO
USE [TESTDB]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [TESTUSER]
GO
/*------------------------
USE [master]
GO
CREATE LOGIN [TESTUSER] WITH PASSWORD=N'Password123_4U', DEFAULT_DATABASE=[TESTDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master];
GO
USE [TESTDB]
GO
CREATE USER [TESTUSER] FOR LOGIN [TESTUSER]
GO
USE [TESTDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TESTUSER]
GO
USE [TESTDB]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [TESTUSER]
GO
------------------------*/
Completion time: 2024-12-02T11:22:44.0652732+05:30
--Create the user
USE [TESTDB]
GO
CREATE USER [TESTUSER] FOR LOGIN [TESTUSER] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TESTUSER]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [TESTUSER]
GO
USE [TESTDB]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [TESTUSER]
GO
USE [TESTDB]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [TESTUSER]
GO
DROP USER [TESTUSER]
GO
/*------------------------
DROP USER [TESTUSER]
GO
------------------------*/
Msg 15138, Level 16, State 1, Line 32
The database principal owns a schema in the database, and cannot be dropped.
Completion time: 2024-12-02T11:28:28.1249217+05:30


ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [dbo]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [dbo]
GO

/*------------------------
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [dbo]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [dbo]
GO
------------------------*/
Completion time: 2024-12-02T11:29:59.8570656+05:30

DROP USER [TESTUSER]
GO
/*------------------------
DROP USER [TESTUSER]
GO
------------------------*/
Completion time: 2024-12-02T11:30:23.8254282+05:30

SQLCMD output with echo sql statement

SQLCMD output with echo sql statement

 sqlcmd -S <Server IP Address> -d TESTDB -U sa -P TestPasswd123 -e -i C:\test.sql -o C:\test.log

Input file : test.sql

select db_name();
go
select getdate();
go

Output File : test.log

select db_name();

                                                                                                                               

-------------------------------------------------

TESTDB 

(1 rows affected)

select getdate();

                       

-----------------------

2023-09-05 05:19:02.637

(1 rows affected)

Solved SQL Server Error : There is insufficient memory available in the buffer pool

While Taking backup of SQL Server table getting error Insufficient memory available in buffer pool.

Note : This is one time activity therefor I didn't try to increase buffer pool memory

select * INTO USER_TABLE_2023_BKP19JULY2023 from USER_TABLE_2023

GO

Error : there is insufficient memory available in the buffer pool

(1 row affected)

Msg 802, Level 17, State 0, Line 1

There is insufficient memory available in the buffer pool.

Completion time: 2023-07-19T11:53:00.2383646+05:30

Possible Solution : Insert data to backup table in chunk.

DECLARE @rerord_count INT = 1;
WHILE @rerord_count > 0
BEGIN
  BEGIN TRANSACTION;
  INSERT USER_TABLE_2023_BKP19JULY2023(USERNAME, PROGRAM,USER_STATUS)
    SELECT TOP (5000)  USERNAME, PROGRAM,USER_STATUS 
    FROM USER_TABLE_2023 AS ST
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM USER_TABLE_2023_BKP19JULY2023 AS TT
      WHERE cast(TT.USERNAME as VARCHAR(100))+'#$----$#'

            +cast(TT.PROGRAM as VARCHAR(100))+'#$----$#'

            +cast(TT.USER_STATUS as VARCHAR(100)) = 

            cast(ST.USERNAME as VARCHAR(100))+'#$----$#'

            +cast(ST.PROGRAM as VARCHAR(100))+'#$----$#'

            +cast(ST.USER_STATUS as VARCHAR(100))

    )
    ORDER BY USERNAME,PROGRAM,USER_STATUS;
  SET @rerord_count = @@ROWCOUNT;
  COMMIT TRANSACTION;
END
GO


(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(5000 rows affected)
(3508 rows affected)
(0 rows affected)
Completion time: 2023-07-19T11:30:17.5047267+05:30

---Check Buffer pool size for a database

SELECT

  database_id AS DatabaseID,

  DB_NAME(database_id) AS DatabaseName,

  COUNT(file_id) * 8/1024.0 AS BufferSizeInMB

FROM sys.dm_os_buffer_descriptors

where database_id=24

GROUP BY DB_NAME(database_id),database_id

ORDER BY BufferSizeInMB DESC

GO 

DatabaseID DatabaseName                 BufferSizeInMB

24                 TESTDB_LOCAL                 0.414062

DatabaseID DatabaseName                BufferSizeInKB

24                TESTDB_LOCAL                 424

---Table Size

SELECT

s.Name AS SchemaName,

t.Name AS TableName,

p.rows AS RowCounts,

CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,

CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,

CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

where t.Name IN('USER_TABLE_2023')

GROUP BY t.Name, s.Name, p.Rows

ORDER BY s.Name, t.Name

GO

SchemaName TableName                RowCounts Used_MB Unused_MB Total_MB

dbo                 USER_INTERFACE 353508         31.09          0.95          32.03

SQL Server Find out the session details comming through application

--Find out the session details comming through application

select getdate(),@@servername,DB_NAME(dbid) as DBName,program_name,client_net_address ,client_tcp_port,loginame as LoginName
from sys.sysprocesses sp
join
sys.dm_exec_connections ec
on ec.session_id=sp.spid
where program_name like '%JDBC%' 

GO

A network-related or instance-specific error has occurred while establishing a connection to SQL Server [Error Number: 53]

Connecting SQL Server database remotely using SQLCMD getting below error

C:\Users>sqlcmd -S  192.168.5.100 -d TESTDB_2023 -U sa -P Password_4U
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. 
                                                         Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. 
                                                         For more information see SQL Server Books Online..

While I was trying to make connection SQL Server Management Studio getting below error

===================================

Cannot connect to 192.168.5.100.

===================================

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

------------------------------

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error

------------------------------

Error Number: 53

Severity: 20

State: 0

------------------------------

Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

The network path was not found

-------------------------------------END---------------------------------------------------

 

Possible Solution

---------------------

Possible reason is bloking IP and Port for incoming connection in the server
Let suppose Local Machine static IP : 192.138.10.51 where where you want to access sql server
and SQL Server IP : 192.168.5.100 where the Sql server is Installed

On the SQL server Machine
   1. Press the Windows + R 
   2. type control firewall.cpl  --> click on Advance Security
   3. click on Inbound Rule
   4. Click on New Rule
   5. General Tab --> Write Name of Rule Example : SQL_Server_TCP_IN
   6. Scope Tab   --> Local IP Address  --> By default selected on Any IP Address
                  --> Remote IP Address --> Add your local machine IP Example : 192.138.10.51
                    Or you can specify range of IP Address of your On-Primises Network
   Protocol and Ports 
              --> Protocol Type : TCP(By default this is selected)
              --> specify the DB port for Example SQL Server By default port is 1433
                  otherwise you will get network related error while accessing SQL Server DB.

  Verify TCP is enabled for SQL Server using SQL Server Configuration Manager
  1. Click the Windows key + R
  2. Type compmgmt.msc
  3. Expand Services and Applications
  4. Expand SQL Server Configuration Manager
  5. Sql server Network Configuration 
       Protocol of <Instance Name>
       Enable TCP/IP
  6. Click on Sql Server Sevices and right click on Sqlserver(<INSTANCE_NME>) and restart the service
  
C:\Users>sqlcmd -S  192.168.5.100 -d TESTDB_2023 -U sa -P Password_4U
1>
2> select db_name()
3> go
--------------------------------------------------------------------------------------------------------------------------------
TESTDB_2023
(1 rows affected)
1>

Now able to connect remotely.

Also  can be reviewed TCP/IP Protocol is enable or not

WIndows + R

    compmgmt.msc

Computer Management local

     SQL Server Configuration Manager

     SQL Server Network Configuration

     Protocols for <Instance_name>

    Make Sure TCP/IP and Named Pipe should enable

    If not then you would have to enable, SQL Server service restart will required (Downtime approval will required)

  

SQL Server Restore Error - Access is Denied

 USE [master]
RESTORE DATABASE [SS_TESTDB_2023] FROM  DISK = N'E:\SQLServer_DB\SS_PRODDB_29MAY2023.bck' WITH  FILE = 1,  
MOVE N'SS_PRODDB' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf',  
MOVE N'SS_PRODDB_log' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf',  NOUNLOAD,  STATS = 5
GO
SQL Server logfile location 
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 
2023-06-15 12:43:00.92 spid60      Error: 3633, Severity: 16, State: 1.
2023-06-15 12:43:00.92 spid60      The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf' at 'container.cpp'(2693).
2023-06-15 12:43:00.92 spid60      Error: 3633, Severity: 16, State: 1.
2023-06-15 12:43:00.92 spid60      The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf' at 'container.cpp'(2693).

solution
grant read,write permission on folder for the which is logged in
best practice is to login through Administrator and restore so that we could avoid the error

USE [master]
RESTORE DATABASE [SS_TESTDB_2023] FROM  DISK = N'E:\SQLServer_DB\SS_PRODDB_29MAY2023.bck' WITH  FILE = 1,  
MOVE N'SS_PRODDB' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf',  
MOVE N'SS_PRODDB_log' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf',  NOUNLOAD,  STATS = 5
GO
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 3869 pages for database 'SS_TESTDB_2023', file 'SS_PRODDB' on file 1.
Processed 2 pages for database 'SS_TESTDB_2023', file 'SS_PRODDB_log' on file 1.
RESTORE DATABASE successfully processed 3869 pages in 280.499 seconds (66.223 MB/sec).

SqlServer Error when creating Table : "Incorrect syntax near 'COLUMN_NAME' " due to QUOTED_IDENTIFIER OFF

Getting error while creating table in SQL Server database using SVN, SVN execute the statement through SQLCMD and by default QUOTED_IDENTIFIER  is OFF. 

The error was Incorrect syntax near 'EMP_ID'

13> CREATE TABLE EMPLOYEE(

14>     EMP_ID NUMERIC(18,0) NOT NULL,
15>     EMP_TYPE VARCHAR(10) NOT NULL,
16>     FIRST_NAME VARCHAR(40) NULL,
17>     LAST_NAME VARCHAR(40) NULL,
18>     EMAIL_ID VARCHAR(50) NOT NULL,
19>     MOBILE VARCHAR(40) NOT NULL,
20>  CONSTRAINT PK_EMPLOYEE  PRIMARY KEY ("EMP_ID", "EMP_TYPE")
21> );
22> GO
Msg 102, Level 15, State 1, Server DKBLAPT\MSSQLSERVER, Line 20
1>

Incorrect syntax near 'EMP_ID'.

I tried to run through SQL Server Management Studio, same statement is executed successfully because of by default QUOTED_IDENTIFIER is ON.

SSMS 

    --> Tools 

           --> Option 

                 --> Query Execution 

                     --> SQL Sever 

                        --> ANSI 

                           --> SET QUOTED_IDENTIFIER [check box selected] 

2> SET QUOTED_IDENTIFIER ON;

3> CREATE TABLE EMPLOYEE(

4>     EMP_ID NUMERIC(18,0) NOT NULL,

5>     EMP_TYPE VARCHAR(10) NOT NULL,

6>     FIRST_NAME VARCHAR(40) NULL,

7>     LAST_NAME VARCHAR(40) NULL,

8>     EMAIL_ID VARCHAR(50) NOT NULL,

9>     MOBILE VARCHAR(40) NOT NULL,

10>    CONSTRAINT PK_EMPLOYEE  PRIMARY KEY ("EMP_ID", "EMP_TYPE")

11> );

12> GO

(1 rows affected)

1>

Generally we create table without use of double quotes, we should use like below statement so that we could avoid error.

CREATE TABLE EMPLOYEE(

     EMP_ID NUMERIC(18,0) NOT NULL,

     EMP_TYPE VARCHAR(10) NOT NULL,

     FIRST_NAME VARCHAR(40) NULL,

     LAST_NAME VARCHAR(40) NULL,

     EMAIL_ID VARCHAR(50) NOT NULL,

     MOBILE VARCHAR(40) NOT NULL,

  CONSTRAINT PK_EMPLOYEE  PRIMARY KEY (EMP_ID, EMP_TYPE)

 );

 GO

 

SQL Server Installation Error : Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

After SQL Server 2019 installation on Windows 11 , SQL Server-2019 Service not getting started even I tried manually after installation

TITLE: Microsoft SQL Server 2019 Setup

------------------------------

The following error has occurred:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

When check the Error log found below error

Log location : C:\Program Files\Microsoft SQL Server\MSSQL15.SS2019\MSSQL\Log

log file showing error 2023-05-30 11:44:05.08 spid10s     Starting up database 'master'. 2023-05-30 11:44:05.09 spid10s     There have been 256 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER1\MSSQL\DATA\master.mdf.

SOLUTION :

PS C:\Users> Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes" Get-ItemProperty : Property ForcedPhysicalSectorSizeInBytes does not exist at path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device. At line:1 char:1 + Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\storn ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (ForcedPhysicalSectorSizeInBytes:String) [Get-ItemProperty], PSArgumentException + FullyQualifiedErrorId : System.Management.Automation.PSArgumentException,Microsoft.PowerShell.Commands.GetItemPropertyCommand PS C:\Users> New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes" -PropertyType MultiString -Force -Value "* 4095" ForcedPhysicalSectorSizeInBytes : {* 4095} PSPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\stornvme\Parameters PSChildName : Device PSDrive : HKLM PSProvider : Microsoft.PowerShell.Core\Registry PS C:\Users> Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes" ForcedPhysicalSectorSizeInBytes : {* 4095} PSPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\stornvme\Parameters PSChildName : Device PSDrive : HKLM PSProvider : Microsoft.PowerShell.Core\Registry

delete the installed SQL Server Instance which was not correctly configured

Reboot the server

Try to Install the SQL Server Instance again

Hope it will work, In my case it is working and successfully installed SQL Server Instance