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.

A Myth - Truncate and Drop operation can't be rolled Back

We all know Delete is a DML command and it can be rolled back bu using it in the transaction.
But always we have heard, read, practiced and believed to be a MYTH that Truncate and Drop operation can't be rolled Back. But in reality these two operations can also be rolled back and yes it is against to the RDBMS concept itself.

Follow the below steps to witness this.

1. Create a table

 Use AdventureWorks2012

GO 

CREATE TABLE [dbo].[Drop_test](
[id] [int]  NOT NULL,
[name] [varchar](100) NULL
) ON [PRIMARY]

GO


2. Insert a dummy data

Insert into [dbo].[Drop_test]
values (1, 'a') 



3. Check the row count

select * from [dbo].[Drop_test]

4. Truncate the table by using it within a transaction.

begin tran 

truncate table [dbo].[Drop_test]

--commit tran 

Check the row count

select * from [dbo].[Drop_test]

6. Roll Back the transaction and you can get back all the data which was deleted.

Rollback tran 

7. Same steps can be used to verify the Drop Table operation.

begin tran 

drop table  [dbo].[Drop_test]

ROllback tran