How to Manage Big tables in SQL server? 

Managing or Maintenances of Big tables need proper attention. Below are few of the scenarios when it required proper attention, other it can bring down the DB server itself. 

Case-1 : Alter table to modify the column data type
Case-2 : Data Purge
Case-3 : Adding Index

1. Check the Size of the Table, No of Rows exe SP_SpaceUsed 'Tablename' 

2. Check the Free space of Data and Log Files of the database of the Table

3. Check the Free space of Data and Log Files of TempDB database

4. Check the Data\Log and TempDB drives free space and ensure they have sufficient free space. 

5. Make sure Index creation is ONLINE=ON 

Practical experience : 
I had to change the data type of the column in a table but Primary Key was defined on that column. 
In that case, I had to drop the Primary Key, change the column data type and create the Primary back on the same table. 
I tried all three steps in single transaction but Creating of Primary Key took very long time. Table was around 34GB in size. During the Index creation process, Log file started growing rapidly. There was a point where Log drive had zero free space. Immediately, disabled the growth of existing Log file and created a new Log file on other drive. In this scenario, it would have affected the other databases in the server. Once the Index creation completed, shrunk the Log files and released some space on the drive. Enabled the growth on the old log file and removed the new Log file which was created. 

 Change the Default Collation of SQL server, Database 

I had installed a SQL server with default collation SQL_Latin1_General_CP1_CI_AS but later realized it should be SQL_Latin1_General_CP850_CI_AS. I followed the below steps. 

1. Change SQL Server (Instance) Collation

⚠️ You cannot change the server collation without rebuilding the system databases, which means reinstalling SQL Server components.

Steps:

• Backup all user databases and settings

• Use setup.exe from installation media with REBUILDDATABASE flag

Example Command:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=domain\user /SAPWD=StrongPassword /SQLCOLLATION=New_Collation_Name

🔁 Example collation: SQL_Latin1_General_CP1_CI_AS

🧷 This will remove all system data, including logins and jobs. Use with caution.

INSTANCENAME= MSSQLSERVER for Default Instance

              ServerName\Instance for named instance

SQLSYSADMINACCOUNTS = Must provide windows account

SAPWD = Its going to be new password for 'SA' account.

SQLCOLLATION = New collation to be changed

2. Change Database Collation

You can change a database's default collation only if no tables exist, or you'll have to recreate all objects manually.

Example (for empty or new DB):

ALTER DATABASE YourDatabaseName COLLATE New_Collation_Name;


3. Change Column Collation
To change collation for specific columns:
ALTER TABLE YourTable
ALTER COLUMN YourColumn VARCHAR(100) COLLATE New_Collation_Name;
⚠️ This can fail if the column is part of an index or constraint.

4. Specify Collation in Queries
For joining or comparing across different collations:

SELECT * FROM TableA JOIN TableB ON TableA.Name 
COLLATE Latin1_General_CI_AS = TableB.Name COLLATE Latin1_General_CI_AS; 

Resolving Disconnected Secondary Replica or Database in Always On Availability Groups

It’s not uncommon to encounter situations where either the secondary database or the secondary replica enters a disconnected state in an Always On Availability Group (AG). This typically occurs during initial AG configurations or after events like OS/SQL patching, network issues, or failovers.

Recently, I faced a scenario where not only was the secondary database in a disconnected state, but the entire secondary replica was also marked as disconnected in the AG dashboard. As a result, the transaction log (T-log) started growing due to unsynchronized data movement.

Below are the steps I followed to troubleshoot and resolve the issue:

Steps to Resolve:

1. Connected to both Primary and Secondary replicas via SSMS.

2. On the Secondary node, navigated to:
   Always On High Availability > Availability Groups (AVG) > [Your AG Name]

3. Under Availability Databases, right-clicked the affected database, then:
   Suspend Data Movement > Resume Data Movement

   Note: This did not resolve the issue.

4. Restarted the HADR Endpoint on both replicas:

   
   SELECT * FROM sys.endpoints;
   
   ALTER ENDPOINT [Hadr_endpoint] STATE = STOPPED;
   ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
   

   This also did not resolve the issue initially.

5. Granted CONNECT permissions to the SQL Server service account (used by the secondary replica) on both nodes:

   
   GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount];
   

6. After applying the endpoint permission, I re-executed Step 4 (restarting endpoints).
   This time, the secondary replica successfully reconnected, and data synchronization resumed.

SQL server DB status runnable, sleeping, suspended, running, pending and background meanings

Some of the most seen status of SPID's in SQL Server and what do they mean:

RUNNING:

This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. What this actually means is, the client connected to SQL Server using this session has already submitted a query for SQL Server to process and SQL Server is currently processing the query. The query could be anywhere between generating a parser tree to performing a join to sorting the data... and it is consuming the CPU (Processor) cycles currently.

SUSPENDED:

It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT it can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

RUNNABLE:

The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the scheduler. This means that requests got a worker thread assigned but they are not getting CPU time.

The RUNNABLE queue can be likened to a grocery analogy where there are multiple check out lines. The register clerk is the CPU. There is just one customer checking out e.g. “RUNNING” at any given register. The time spent in the checkout line represents CPU pressure. So this SPID is waiting for that customer who is running (with register clerk) to get out so that it can start RUNNING. You can use the query SELECT wait_type,waiting_tasks_count,signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY signal_wait_time_ms DESC to find out the difference between the time the waiting thread was signaled and when it started running. This difference is the time spent in RUNNABLE queue. Some of the waits on the top of the list can be safely ignored.

PENDING:

The request is waiting for a worker to pick it up. This means the request is ready to run but there are no worker threads available to execute the requests in CPU. This doesn't mean that you have to increase 'Max. Worker threads", you have to check what the currently executing threads are doing and why they are not yielding back. I personally have seen more SPID's with status PENDING on issues which ended up in "Non-yielding Scheduler" and "Scheduler deadlock".

BACKGROUND:

The request is a background thread such as Resource Monitor or Deadlock Monitor.

SLEEPING:

There is no work to be done.

Database in Suspect Mode

Yesterday I had mail to check the database health after the recovery of servers from Storage crash issue. I found one particular database was in 'Suspect' mode. Performed steps to fix this. 

Database will go to Suspect mode usually if there is any issue with Server Reboot, Storage issue, Database Corruption due to Network issue etc. In this case Storage SAN drive crashed, restored the storage from the Snapshot. 

1. Check the database state

select state_desc, * from sys.databases where state_desc<>'online'

2. Found database was in Suspect Mode. 

First step is to Restore the database from the latest Backup file. and Run the DBCC Integrity command to check it. Made sure everything fine on that.          

As a part of troubleshoot on the issue database.

               

            Brought the database to Emergency Mode, and do the DBCC Integrity check.

Use Master

go

ALTER DATABASE DBNAME SET EMERGENCY

DBCC CHECKDB (DBNAME )

3. Based on the DBSS Result\suggestion performed the DBCC REPAIR_ALLOW_DATA_LOSS.  Depending on the DBCC Results and suggestions 

Use Master

go

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

4. I was not able to access the database into single user mode, transaction was ending in the Deadlock. I changed the Deadlock priority and later was able to take the database into Single user mode.

SET DEADLOCK_PRIORITY HIGH 

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

or 

SET DEADLOCK_PRIORITY 10

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

5. Then performed the the DBCC REPAIR_ALLOW_DATA_LOSS. Database went to Recovery mode. DBCC Repair Allow data Loss should have fixed the issue by allowing some data loss. But it didn't fix the issue. Hence continued using the Restore database. Later I just dropped the database and renamed the Restored database to original name. 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBNAME'

GO

USE [master]

GO

/****** Object:  Database [DBNAME]    Script Date: 3/27/2019 12:29:38 PM ******/

DROP DATABASE [DBNAME]

GO

Unable to Kill SPID

                  

                       Recently one user reported he is facing some issue while deleting the table records. When I checked it there was a blocking. Immediately I tried to kill the session which was causing the issue. I used  KILL spid to kill the session. Though it was executed successfully, session was still running with 'suspended' status by holding a blocking on sessions. 

In order to kill the session, I alter the database to Single User mode. 

USE MASTER 

GO

ALTER DATABASE (DBNAME) SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

ALTER DATABASE (DBNAME) SET MULTI_USER WITH ROLLBACK IMMEDIATE 

GO

This resolved the issue by killing all the sessions. But it was on a NonProduction database. When doing it on Production database please be aware of affected users and the work to be redone. 

Shrink TempDB database

Its very common issue on day to day DBA activities, where TempDB databases will go out of space. Sometime it is cumbersome to shrink the database files to release the space. 

Below are some on the Methods to release the space on TempDB database. 

1. Shrink Database files 

use tempdb

GO

CHECKPOINT

GO

dbcc shrinkfile ('tempdev') -- shrink db file

dbcc shrinkfile ('templog') -- shrink log file

2. Clean up the Cache to and shrink the database files. 

DBCC FREEPROCCACHE -- clean cache

DBCC DROPCLEANBUFFERS -- clean buffers

DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb

After this again Run the shrink commands

dbcc shrinkfile ('tempdev') -- shrink db file
dbcc shrinkfile ('templog') -- shrink log file
GO

3. Restart the SQL service
This is should be a last option. 

SQL SERVER CLR Error

Error:

Msg 6263, Level 16, State 1, Line 721

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Solution ; We need to enable the CLR in the SQL Server configuration.

sp_configure 'show advanced options',1

go

sp_configure 'clr enabled',1

go

reconfigure

go

Error:

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

Solution 1 : When the databases is restored\attached from the different instance this error might occurred as the CLR objects will be looking for the same owner SID which was created at the source. Changing the databases owner to the same as source and giving the required permission would fix the issue.

Alternatively we can use db owner as ‘sa’ also.

USE <DatabaseName>

GO

EXEC sp_changedbowner 'sa'

Solution 2 : The quickfix would be to set the database to trustworthy: 

ALTER DATABASE <DatabaseName> SET TRUSTWORTHY ON

Solution 3 : You can also navigate to Assemblies of a respective databases and change the Assembly property.  Set the permission to ‘External Access or unrestricted’

Error 945 : Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details

This is one of the common issue which I have seen. Today I received a ticker from QA team saying one of the database is not accessible. I checked with the team on what exactly they are doing on the database. I came to know the team was processing the data which increased the many databases sizes which lead to Disk space issue. 

Due to there was no free space on the disk for the database to grow further, one of the database went to shutdown mode. You can check the database status from below query. 

use master
select databaseproperty('DBName', 'isShutdown')

Fix/Solution/WorkAround:

1. Took the database offline and brought back online. Query for the same 

use master
alter database DBName set offline


use master
alter database DBName set online

2.  If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.

3.  Check if the database is set to Autogrow on.

4. Check if the account which is trying to access the database has enough permission to perform operation.

5. Make sure that .mdf and .ldf file are not marked as read only on operating system file system level.

How to restore TDE eanabled database

Create a database master key and certificate in the master database.

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';

 USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO

Create a backup of the server certificate in the master database. 

BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'
 ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
   
Enable the TDE on user Database

use dbname
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO

ALTER DATABASE skyProduction
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GO

Backup the Encryption key

USE master
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\temp\dbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\dbcert_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'j3tt4IL!2' )
GO


-- Use to check status
USE master
GO

SELECT * FROM sys.certificates

-- encryption_state = 3 is encrypted
SELECT * FROM sys.dm_database_encryption_keys
  WHERE encryption_state = 3;
 
  SELECT *
FROM sys.dm_database_encryption_keys;

SELECT * FROM sys.databases


--- Restore database on other machine (Decrypt the Database)

--1. Create the Master Key on Target server (Password need not be same)

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2'


--2. After a master key has been created, create a certificate by importing the certificate we created earlier (Take the Backup of the certificate).
--Here the ‘Decryption By Password’ parameter is same as that of source server.

CREATE CERTIFICATE TDECert
FROM FILE = 'E:\Backup\Production Server keys\dbCerts\dbcert.cer'    
WITH PRIVATE KEY (FILE = 'E:\Backup\Production Server keys\dbCerts\dbcert_Key.pvk',
DECRYPTION BY PASSWORD = 'j3uwgq#)hqI*2')

--3. Restore the database as regular.


Using SQL Server Management Studio

1. Create a database master key and certificate in the master database. For more information, see Using Transact-SQL below.
2. Create a backup of the server certificate in the master database. For more information, see Using Transact-SQL below.
3. In Object Explorer, right-click the Databases folder and select New Database.
4. In the New Database dialog box, in the Database name box, enter the name of the new database.
5. In the Owner box, enter the name of the new database's owner. Alternately, click the ellipsis (…) to open the Select Database Owner dialog box. For more information on creating a new database, see Create a Database.
6. In Object Explorer, click the plus sign to expand the Databases folder.
7. Right-click the database you created, point to Tasks, and select Manage Database Encryption.
   The following options are available on the Manage Database Encryption dialog box.
   Encryption Algorithm
   Displays or sets the algorithm to use for database encryption. AES128 is the default algorithm. This field cannot be blank. For more information on encryption algorithms, see Choose an Encryption Algorithm.
   Use server certificate
   Sets the encryption to be secured by a certificate. Select one from the list. If you do not have the VIEW DEFINITION permission on server certificates, this list will be empty. If a certificate method of encryption is selected, this value cannot be empty. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.
   Use server asymmetric key
   Sets the encryption to be secured by an asymmetric key. Only available asymmetric keys are displayed. Only an asymmetric key protected by an EKM module can encrypt a database using TDE.
   Set Database Encryption On
   Alters the database to turn on (checked) or turn off (unchecked) TDE.
8. When finished, click OK.

Database Mail Issue with .Net Framework 3

We all know how to configure the database mail on SQL server and troubleshoot for any error. Below are some of the common issues and trouble shoot methods.

Install .NET Framework 3 to make the Database engine to work. 

One of the rarest issue faced with SQL server database Mail is, problem with .Net Framework 3. If server is not installed with .Net Framework 3, database mail may not work at all. 

Troubleshooting Database Mail: General steps

Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order.

To determine if Database Mail is enabled

1. In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code:
   sp_configure 'show advanced', 1; 
   GO
   RECONFIGURE;
   GO
   sp_configure;
   GO
   

   In the results pane, confirm that the run_value for Database Mail XPs is set to 1.
   If the run_value is not 1, Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration.
2. If you decide that it is appropriate to enable Database Mail, execute the following code:
   sp_configure 'Database Mail XPs', 1; 
   GO
   RECONFIGURE;
   GO
   

3. To restore the sp_configure procedure to its default state, which does not show advanced options, execute the following code:
   sp_configure 'show advanced', 0; 
   GO
   RECONFIGURE;
   GO
   

To determine if users are properly configured to send Database Mail

1. To send Database Mail, users must be a member of the DatabaseMailUserRole. Members of the sysadmin fixed server role and msdbdb_owner role are automatically members of the DatabaseMailUserRole role. To list all other members of the DatabaseMailUserRole execute the following statement:
   EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
   

2. To add users to the DatabaseMailUserRole role, use the following statement:
   sp_addrolemember @rolename = 'DatabaseMailUserRole'
      ,@membername = '<database user>';
   

3. To send Database Mail, users must have access to at least one Database Mail profile. To list the users (principals) and the profiles to which they have access, execute the following statement.
   EXEC msdb.dbo.sysmail_help_principalprofile_sp;
   

4. Use the Database Mail Configuration Wizard to create profiles and grant access to profiles to users.

To confirm that the Database Mail is started

1. The Database Mail External Program is activated when there are e-mail messages to be processed. When there have been no messages to send for the specified time-out period, the program exits. To confirm the Database Mail activation is started, execute the following statement.
   EXEC msdb.dbo.sysmail_help_status_sp;
   

2. If the Database Mail activation is not started, execute the following statement to start it:
   EXEC msdb.dbo.sysmail_start_sp;
   

3. If the Database Mail external program is started, check the status of the mail queue with the following statement:
   EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
   

   The mail queue should have the state of RECEIVES_OCCURRING. The status queue may vary from moment to moment. If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp and then starting the queue using sysmail_start_sp.

Note
Use the length column in the result set of sysmail_help_queue_sp to determine the number of e-mails in the Mail queue.

To determine if problems with Database Mail affect all accounts in a profile or only some accounts

1. If you have determined that some but not all profiles can send mail, then you may have problems with the Database Mail accounts used by the problem profiles. To determine which accounts are successful in sending mail, execute the following statement:
   SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;
   

2. If a profile which is not working does not use any of the accounts listed, then it is possible that all the accounts available to the profile are not working properly. To test individual accounts, use the Database Mail Configuration Wizard to create a new profile with a single account, and then use the Send Test E-Mail dialog box to send mail using the new account.
3. To view the error messages returned by Database Mail, execute the following statement:
   SELECT * FROM msdb.dbo.sysmail_event_log;
   

Note
Database Mail considers mail to be sent, when it is successfully delivered to a SMTP mail server. Subsequent errors, such as an invalid recipient e-mail address, can still prevent mail from being delivered, but will not be contained in the Database Mail log.

To configure Database Mail to retry mail delivery

1. If you have determined that the Database Mail is failing because the SMTP server cannot be reliably reached, you may be able to increase your successful mail delivery rate by increasing the number of times Database Mail attempts to send each message. Start the Database Mail Configuration Wizard, and select the View or change system parameters option. Alternatively, you can associate more accounts to the profile so upon failover from the primary account, Database Mail will use the failover account to send e-mails.
2. On the Configure System Parameters page, the default values of 5 times for the Account Retry Attempts and 60 seconds for the Account Retry Delay means that message delivery will fail if the SMTP server cannot be reached in 5 minutes. Increase these parameters to lengthen the amount of time before message deliver fails.