Knowledge: Differences between temporary table and table variable.

Temporary Tables vs Table variables.

Temporary Tables	Temporary Table Variables
1) Following is the syntax for creating a temporary table. CREATE TABLE #temp_table (id INT, city VARCHAR(100))	1)Following is the syntax for creating a table variable. DECLARE @temp_table TABLE (id,INT, city VARCHAR(100))
2) All DML & DDL operations works on temporary table like on normal tables. Example - All DDL operations like ALTER, CREATE & DROP operations will work with temporary tables.	2)Table variables doesn't support DDL operations like ALTER, CREATE & DROP operations. It will support DML operations only. Basically once the table variable is created we can't drop or alter the structure of the table.
3) Temporary tables are created in tempdb database.	3) Table variables are also created in tempdb database.
4) Temporary tables support explicit transaction defined by the user.	4) Table variables doesn't support explicit transactions defined by the user.
5) Temporary tables are not allowed in user defined functions.	5) Table variables are always allwoed in user defined functions.
6) Indexes can be created on temporary tables if required.	6) Index creation on temporary variables is not supported.
7) Temporary tables can be created using single # which are referred as local temporary tables and double ## which are referred as global temporary tables.	7) Table variables are created using 'DECLARE' keyword and of type 'TABLE'.
8) The scope of local temporary tables is within the session in which it is created and will be dropped automatically once the session ends or it can be dropped explicitly. The scope of global temporary table is whith the session it created and also can be accessible by any other sessions. These tables can be dropped explicitly by any session if it is not actively being used or it will get dropped auotmatically when no session is using it.	8) The scope of temporary variable is witin the stored procesure it is declared and will be dropped automatically when the procedure execution completes. It can't be dropped explicitly.

Maintenance: Move Data / Log file location in SQL Server using Detach & Attach functionality.

Here we are going to move the transaction log physical location of database 'TEST1'.

1) Use following query to get the data file & log file locations,

 


USE [TEST1]
GO
sp_helpfile --Here the log location is 'E:\TransactionLogs\TEST1_Log.ldf'
GO

Output:

2) Now we got the current location of log file for database TEST1 as 'E:\TransactionLogs\TEST1_Log.ldf'. I'm moving the file location from 'E:\TransactionLogs\TEST1_Log.ldf' to different drive as 'L:\TransactionLogs\TEST1_Log.ldf'

3) First you need to set the database in single user mode to restrict any open connections to database. We use following query to do that,

 


ALTER DATABASE [TEST1] SET SINGLE_USER
GO

4) Once the database is set to single user mode detach the database using following query,

 
USE master
GO

sp_detach_db [TEST1]
GO

5) Once the database is successfully detached move the file from original location to required target location. Here we are moving to target location 'L:\TransactionLogs\TEST1_Log.ldf'.

6) Once the file has been moved to target location, use following command to re-attach the database,

 
USE master
GO

sp_attach_DB [TEST1], 
'D:\MSSQL\DATA\TEST1_Data.mdf',
'L:\TransactionLogs\TEST1_Log.ldf'
GO

7) Now verify whether the file has been successfully moved to new location with same query from step (1) as below,

USE [TEST1]
GO

sp_helpfile
GO

Output:

8) Note - Here we have moved only log file but you can follow same steps to change data file location as well.

Knowledge: SQL Server Release's / SQL Server Code Names

SQL Server Release	Code Name
SQL Server 4.21	SQLNT
SQL Server 6.0	SQL95
SQL Server 6.5	Hydra
SQL Server 7.0	Sphinx
SQL Server 2005	Yukon
SQL Server 2008	Katmai / Akadia
SQL Server 2008R2	Kilimanjaro
SQL Server 2012	Denali
SQL Server 2014 (In-Memory OLTP)	Hekaton

Monitor: How to check all database mail profiles in SQL Server using T-SQL

Use following query to find all database mail profiles that are configured,

USE msdb
GO

EXEC sysmail_help_profileaccount_sp;
GO

Output:

Maintenance: How to change SQL Server Agent Log location.

1) Open SQL Server SSMS and execute following script,

USE MASTER
GO
EXEC msdb..sp_get_sqlagent_properties
GO

Output:
Here you will get the current SQL Server agent log location as in below screenshot,

2) Now execute following script by updating the value to new location.

 
USE MASTER
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'D:\MSSQL10.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT'
GO

3) Now, you have to restart the SQL Server agent service in order to update the log location.

Maintenance: How to change SQL Server Error Log location.

1)  Open SQL Server configuration manager as below

• Start -> All Programs -> Microsoft SQL Server <2005/2008/2012> -> Configuration Tools
• you will see services as below,

              

2)  Now right click SQL Server service and select properties. Go to advanced tab and expand 'Startup Parameters'. Now update the error log new location which is followed by switch ' -e '. Sample screenshots are as below, (make sure there are no spaces added before or after the path, follow the default standards)

Before Change:

After Change: 

           Here the location has been updated from 'Q:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG' to

'D:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'  

           

3) The changes will take effect only after restarting SQL Server services. Now restart SQL Services and you should see the SQL Server error logs are being created at new location.

Monitor: How to check transaction log space details for SQL Serverdatabases.

There are two ways to get the details of transaction log space in SQL Server,

1) Using DBCC command as below. By default this will give log space information for all the databases.

DBCC SQLPERF(LOGSPACE)
GO

Sample Output:

2) Using DMV - sys.dm_os_performance_counters. With this we can query log details specific to the databases that we need. You can use this for SQL Server versions 2005 or later.

SELECT instance_name AS db_name, 
       [Data File(s) Size (KB)] AS [data_file_size_KB], 
       [LOG File(s) Size (KB)] AS [log_file_size_KB], 
       [Log File(s) Used Size (KB)] AS [log_file_used_size_KB], 
       [Percent Log Used] AS [percent_log_used_%] 
FROM 
( 
   SELECT * 
   FROM sys.dm_os_performance_counters 
   WHERE counter_name IN 
   ( 
       'Data File(s) Size (KB)', 
       'Log File(s) Size (KB)', 
       'Log File(s) Used Size (KB)', 
       'Percent Log Used' 
   ) 
     ---AND instance_name != '_Total' /*uncomment this section and comment next line if you want details for all databases*/
  AND instance_name IN ('master','msdb','tempdb')
) AS tbl_source
PIVOT 
( 
   MAX(cntr_value) 
   FOR counter_name IN 
   ( 
       [Data File(s) Size (KB)], 
       [LOG File(s) Size (KB)], 
       [Log File(s) Used Size (KB)], 
       [Percent Log Used] 
   ) 
) AS tbl_pivot 

Sample Output: