SQL Server use ASPState database to store the session information for SQL Server Session mode. ASPState database having two tables ASPStateTempApplications and ASPStateTempSessions. ASPStateTempApplications  table contains Application ID and Application Name which is specific to each and every application which are using SQL Server session mode on the particular database. ASPStateTempSessions table having numbers of fields to store the session related information which includes [SessionId], [Created], [Expires], [Timeout]  etc. [SessionItemShort] and [SessionItemLong] actually contains the session data for every users. If the session data size is < = 7000 KB it will be stored in SessionItemShort field and anything > 7000 KB will be stored in SessionItemLong field.



Once you have some session data stored inside ASPStateTempSessions  table, you can easily get the size of session data by running below SQL Query

use ASPState

select [sessionid],[created], datalength(SessionItemLong) as SessionDataSize from ASPStateTempSessions



Note : While running SQL query, you have to make sure you are calculating the size of proper field, if the session data size may vary for different user then you can try to find the data size for both  SessionItemLong and SessionItemShort field.

Introduction

Transparent Data Encryption is a new feature in SQL Server 2008. The TDE feature provides real time encryption of both data and log files. Encryption basically working in the following way; initially the data is encrypted before it’s being written to the disk and it is decrypted before it is being read from the disk. When you are using the Transparent Data Encryption feature of SQL Server 2008 the encryption is performed by the SQL Server 2008 Database Engine and the SQL Server clients will not be aware of this change. However, before implementing this feature in Production environment I would request you to validate the solution completely in the Test Environment.

To enable Transparent Data Encryption Feature of SQL Server 2008 on a database, the DBA needs to perform the below mentioned four steps as described in Books Online:-

1. Create a master key
2. Create or obtain a certificate protected by the master key
3. Create a database encryption key and protect it by the certificate
4. Set the database to use encryption

Create a Master Key

The initial step will be to identify if there is any Master Key already created in the Instance of SQL Server 2008 where you want to implement this feature. You can verify the same by executing the below mentioned TSQL code.

USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

If there are no records found, then it means there was no predefined Master Key on the SQL Server 2008 Instance. To create a Master Key, you can execute the below mentioned TSQL code.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass@word1'
GO

Create or obtain a certificate protected by the Master Key

Once Master Key is created then the next step will be to Create or obtain a certificate protected by the master key. This can be achieved by executing the below mentioned TSQL code.

Use master
GO
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'SQL Server TDE Certificate'
GO

/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'TDECertificate'
GO

Next step will be to create a new database. Once the database is created you can create a database encryption key and protect it by the certificate by executing the below mentioned TSQL code.

Create a database encryption key and protect it by the certificate

Use master
GO
CREATE DATABASE TryEncryption
GO

Use TryEncryption
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
GO

Once the Database Encryption Key is created successfully you need to take a backup of the Certificate and the Private Key by executing the below mentioned TSQL code.

BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\TDECertificate.cert'
WITH PRIVATE KEY (
FILE = 'D:\TDE\EncryptPrivateKey.key',
ENCRYPTION BY PASSWORD = 'Certific@tePass@word')
GO

Set the database to use encryption

The final step will be to enable encryption on the user database by executing the below mentioned TSQL code

ALTER DATABASE TryEncryption SET ENCRYPTION ON
GO

Verify Database Encryption Status

You can verify the database encryption status by executing the below mentioned TSQL code.

SELECT
       DB_NAME(database_id) AS DatabaseName
      ,Encryption_State AS EncryptionState
      ,key_algorithm AS Algorithm
      ,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

SELECT
       NAME AS DatabaseName
      ,IS_ENCRYPTED AS IsEncrypted
FROM sys.databases where name ='TryEncryption'
GO

Advantages of Transparent Data Encryption

1. Physical Security of Database Files
2. When Transparent Database Encryption feature is used all the backups of the TDE enabled database are encrypted

Disadvantages of Transparent Data Encryption

1. As Encryption is CPU intensive and it is performed at I/O level, any server with higher I/O and higher CPU load should avoid using this feature
2. This feature is only available in Enterprise and Developer Editions of SQL Server 2008
3. TDE encrypted database cannot be attached or restored in other edition of SQL Server 2008
4. If the certificate is lost then the data will be unreadable. Hence you need to protect the certificate and master key along with the database backup files
5. If you are using FILESTREAM feature, then be informed that only FILESTREAM enabled database is encrypted and not the actual files which are residing on the servers file system will be encrypted
6. There won’t be much of a benefit if you planning to use Database Backup Compression feature of SQL Server 2008
7. As TempDB database is automatically encrypted once you have enabled encryption on any of the user databases. This resulted in slow query performance for non encrypted databases which may use TempDB

For more information you can check the following link

The programmers are getting this error message when there is high workload on the server. And servers are experiencing high memory pressure.

In this error theire are some additional symptoms also.

1. When connecting to server will get the error message as "Login Failed".

2. Will get disconnected from server.

3. CPU usage will be very high.

4. if running "select * from sysprocesses" SPIDs will have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.

5. The System Monitor object SQLServer:Memory Manager displays a non-zero
value for Memory Grants Pending.

6. SQL Profiler displays the event "Execution Warnings" that includes
the "Wait For Memory" or the "Wait For Memory Timeout" text.

Reasons for this error is memory intensive queries are getting qued and are not getting resources before timout period. And after timout period and getting timout. By default query wait period is -1 by setting non-negative number you can improve the query wait time.

Other reasons for this errors are not properly optimised queries, memory allocation for sql server is too small.

Solutions for this error include the following.

1. Optimise the performance of queries using sql profiler.

2. Distrybution statistics should be uptodate.

3. Watch the system monitor trace to see the memory usage of sql server.

4. If you are running SQL Server 7.0, test disabling parallelism for SQL Server 7.0 by turning the max degree of parallelism configuration option off.