SQL Server: Not able to connect to SQL Server from SSMS

Error Message while connecting thru SSMS

 Cannot connect to <<SQL SERVER Machine Name>>.

ADDITIONAL INFORMATION:

Login failed for user '<<User Name>>'. (Microsoft SQL Server, Error: 18456)

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

Error Message in Event Log

Login failed for user '<<User Name>>'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: <local machine>]

Solution:

Open the SQL Server Management Studio (SSMS) as administrator.

SQL SERVER: How to refresh local cache in SQL Server Management Studio

When working on SQL Server Management Studio, when any new tables are created or columns are added, the local cache is not refreshed immediately and display as error for the new objects or columns in the Query Window. To avoid this we need to update local cache of SSMS.

If you need to refresh the local cache of SSMS you can use two methods

First Method:
       Press the following combination of keys on your keyboard by selecting SSMS

CTRL + Shift + R

Second Method:
    Select the Query window in SSMS
    In the Edit Menu
          Go to IntelliSense
          Press on Refresh Local Cache

SQL Server: Error Msg 701, Level 17, State 130, Line 12 There is insufficient system memory in resource pool 'default' to run this query

How to resolve insufficient system memory issue in SQL Server

Msg 701, Level 17, State 130, Line 12
There is insufficient system memory in resource pool 'default' to run this query

This error occurs when SQL Server uses Maximum memory allotted to SQL Server. You can check the Maximum memory allotted to the SQL Server by checking the server properties.

If RAM is available on the Server where SQL Server is installed you can increase the MAX RAM size by connecting the SQL Server using DAC (Dedicated Administrator Connection and execute the following commands. Change the RAM size based on the RAM available on the server


EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'298844'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

SQL SERVER: How to open DAC (Dedicated Administrator Connection) to SQL Server Management Studio (SSMS)

DAC connection is used to SQL Server when you are not able to connect SQL Server.

Only one DAC connection is allowed for SQL Server instance.

1. Open the SQL Server Management Studio as Administrator
2. Click on “Database Engine Query” (icon next to New Query) on the toolbar.
3. In the “Server Name” box enter the server name in the following formatADMIN:local
4. Connect the server and execute the required commands.

For more details check the technet article.

SQL Server: How to release unused memory from SQL Server

Often times, we need to release unused memory from SQL Server. SQL Server not releases unused memory immediately. One way to release the memory is to restart the SQL Server Service. This is not always possible in Production environment.

To release SQL Server unused memory in Production environments use the following commands by connecting the server using DAC (Dedicated Administrator Connection) Connection

Run the following commands to release the unused memory from SQL Server.



/*Use maximum of 10 GB of RAM and release rest of Memory*/

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'102400'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

/*Run the following command after releasing the memory. Make sure you changed the Memory values based on the Server RAM (MAX 85%)  */

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'max server memory (MB)', N'298844'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

GO

How to retreive random number of rows from a table

USE THE TABLESAMPLE KEYWORD AFTER THE TABLE NAME.

Following queries retrieve random number of rows each time

SELECT * FROM TABLE_NAME TABLESAMPLE(100 ROWS)

SELECT * FROM TABLE_NAME TABLESAMPLE(10 PERCENT)

How to get all tables row count in a database

The more effective way of getting all tables row count in a database is using sysindexes table. This will not do any table scans to get the values.

Following is the code to get the table name and row counts


select so.name,si.rows from sysindexes si
inner join sysobjects so
on si.id = so.id
where si.indid=0 and so.type='u'
order by so.name



How to bring database in SQL Server from Restore mode to normal

Change the database_name to the database name having issues.

USE master
GO

 ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

 ALTER DATABASE database_name ONLINE
GO

Not able to drop sql server database

Error Message:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot drop the database '' because it is being used for replication. (Microsoft SQL Server, Error: 3724)


Resolution:
  Execute the following command then drop the database. 

sp_removedbreplication ''

Improving Sql Server Performance

How to create Missing Indexes based on the DMV values

http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

How to Identify missing Indexes based on DMV values

http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

How to know when the database is last restored

 SELECT [rs].[destination_database_name], 
[rs].[restore_date], 
[bs].[backup_start_date], 
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name], 
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf 
ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [rs].[restore_date] DESC
 

How to drop database when used by other users.

When ever we are droping or deleting the databse if it is used by other users. Sql server displays the following error message.

"cannot drop database "Database_Name" because it is currently in use"

To avoid this error message and close all the existing connections and drop immedeiately you need to use the following syntax.

Change the Database_Name with your Database name to drop.


IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Database_Name')
BEGIN
PRINT 'DROP DATABASE [Database_Name']'
ALTER DATABASE [Database_Name'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Database_Name']
END
GO

All SQL Server Session information in a SQLServer

How to get all session id's information which are connected to the database



select * from sysprocesses

How to display System database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid =1

How to display User Created Database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid <> 1

How to restore master (system) database

• How to restore master database backup.
o Stop the Sql Server
o Start the sql server in Single user mode using the command sqlservr.exe – m from command prompt.
o Restore the master database

• How to restore master database when the master database is corrupted.
o Execute the following command by placing setup disk with the following options by navigating the folder where setup.exe is present.
start /wait \setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=
o After executing the above command, stop the sql server.
o Start the SQL Server in single user mode using the command sqlservr.exe –m
o Restore the master database from the backup.

SQL SERVER 2012 -- IIF function

msdn link
IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

In earlier versions of SQL Server, we have used IF..ELSE and CASE..ENDCASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE

In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value.

DECLARE @A INT=40
DECLARE @B INT=30
SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')
GO;

Executing the above T-SQL will return the following result:
-------------------
A IS GREATER THAN B (1 row(s) affected)

In this example, we will evaluate the age of John and Julie and identify who is older between them. Please observe the use of sub functions within IIF.
DECLARE @JOHN_AGE INT=35
DECLARE @JULIE_AGE INT=29
SELECT IIF(@JOHN_AGE > @JULIE_AGE
            , 'JOHN IS OLDER THAN JULIE BY '+LTRIM(STR(@JOHN_AGE-@JULIE_AGE))+' YEARS'
, 'JULIE IS OLDER THAN JOHN BY '+LTRIM(STR(@JULIE_AGE-@JOHN_AGE))+' YEARS')
GO;

Executing the above statement will return the following result:
--------------------------------------------
JOHN IS OLDER THAN JULIE BY 6 YEARS (1 row(s) affected)

Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.

How to know installed Sql Server instances on a server

We can install multiple instances of Sql Server on a given server.
How to know what are the sqlserver instances present on the server.

Open the Run command and type Regedit and then press enter.
go to the following location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
one entry is present for each instance.

Another way is from the services and find the services as below.
Open the Run command and type services.msc and then press enter.
It will open the Services window and then look for the following
"Sql Server (Instance Name)".

How to enable CLR features in SQL Server

To enable CLR feature set the value to 1.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


To disable CLR feature set the value to 0.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO


How to change table level compression in SQL Server

If we want to remove table level(ROW/PAGE) compression.
You can use the following syntax.

ALTER TABLE Table_Name
REBUILD PARTITION = ALL
WITH( DATA_COMPRESSION = NONE)

Replace "Table_Name" with actual table name before executing the script.

To learn more about Table compression use the following MSDN link Table Compression