Check Mirroring Status in SQL Server 2012

We can check mirroring status of the SQL Server 2012 using below command.

 SELECT   
     @@SERVERNAME as Server_Name,  
     DB_NAME(database_id) as Database_Name,   
     mirroring_state_desc,  
     mirroring_role_desc,  
     mirroring_safety_level_desc  
 FROM   
     sys.database_mirroring  
 WHERE   
 mirroring_role IS NOT NULL  

Getting free space information in SQL Server VM

We can get freespace information in a SQL Server VM using below TSQL command.

 SELECT DISTINCT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
   volume_mount_point [Disk],   
   file_system_type [File System],   
   logical_volume_name as [Logical Drive Name],   
   CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes  
   CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],   
   CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]   
 FROM sys.master_files   
 CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)  

Alternate way for FULL OUTER JOIN

Below is an one more way of doing FULL OUTER JOINs involving multiple tables. This gives a simpler approach, instead of having multiple OR conditions in subsequent tables.

1:  DECLARE @a table(id int, data int)  
2:  DECLARE @b table(id int, data int)  
3:  DECLARE @c table(id int, data int)  
4:    
5:  insert into @a values(1, 9), (2, 8),(3,7)  
6:  insert into @b values(2, 6), (3, 5)  
7:  insert into @c values(1, 4), (2, 5)  
8:    
9:  ;WITH CTE_IDs as  
10:  (  
11:  SELECT ID from @a  
12:  UNION  
13:  SELECT ID FROM @b  
14:  UNION  
15:  SELECT ID FROM @c  
16:  )  
17:  SELECT c.ID,t.* FROM CTE_IDs as c  
18:  cross apply  
19:  (  
20:  VALUES((select data from @a where id = c.id),  
21:  (select data from @b where id = c.id),  
22:  (select data from @c where id = c.id))   
23:  ) as t(a_data,b_data,c_data)  

How to create covering Index to fix performance Issue

We had a performance issue, where we had to create  covering index to solve the problem, as we were querying many columns in the table in a single query. 

Problem Statement:

we are having a table with 10 columns. We have 9 columns in a query, for getting clustering key (identity column here). 

1:  SELECT IdentityValue FROM TableWithIdentity   
2:  WHERE col1 = @col1 , col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5, col6 = @col6, col7 = @col7, col8 = @col8, col9 = @col9  

It is a SQL Server 2012 database. We had to create a covering index with 10 columns. How do I decide the order of columns in the covering index for efficient index ?

Solution Adopted:

1. Get the statistics names associated with table columns. 

1:  SELECT s.stats_id StatsID,  
2:   s.name StatsName,  
3:   sc.stats_column_id StatsColID,  
4:   c.name ColumnName  
5:  FROM sys.stats s  
6:   INNER JOIN sys.stats_columns sc  
7:    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
8:   INNER JOIN sys.columns c  
9:    ON sc.object_id = c.object_id AND sc.column_id = c.column_id  
10:  WHERE OBJECT_NAME(s.object_id) = 'TableWithIdentity'  
11:  ORDER BY s.stats_id, sc.column_id;  

2. Get the statistics for each column and note down the density of each column in the first result of the SHOW_STATISTICS. 

1:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName1');  
2:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName2');  
3:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column3Statistics');  
4:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column4Statistics');  
5:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column5Statistics');  
6:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumn6Statistics');  
7:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column7Statistics');  
8:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column8Statistics');  
9:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column9Statistics');  

3. Now, we know the density of each column. We have to choose the column with least density (meaning more unique column) and followed the next least density etc. 

0.00	column1
0.00	column2
0.01	column3
0.10	column4
0.33	column5
2.09	column6
3.09	column7
5.55	column8
6.46	column9

4. Now, we created covering index based on the uniqueness of the columns order

1:  CREATE INDEX IDX_TableWithIndentity_covering on dbo.TableWithIdentity(column1, column2,column3, column4, column5, column6, column7, column8, column9)   
2:  WITH  
3:  (  
4:  SORT_IN_TEMPDB = ON,  
5:  DROP_EXISTING = OFF  
6:  )  
7:  ON [PRIMARY]  

5. Now, the index is very helpful for fixing the performance Issue.

Getting detailed Performance issue metrics using sp_whoisactive

 

The below sp_whoisactive query can be executed to get detailed performance metrics for slowly running query, which can be later analyzed for troubleshooting performance issue

1:  EXEC sp_WhoIsActive @get_plans = 1, @get_locks = 1, @get_task_info = 2, @show_system_spids =1,  
2:  @show_own_spid =1, @show_sleeping_spids = 1  

Getting SQL Server Instance Services Information

Below query can be run to get SQL Server instance services information:

1:  SELECT DSS.servicename,  
2:  DSS.startup_type_desc,  
3:  DSS.status_desc,  
4:  DSS.last_startup_time,  
5:  DSS.service_account,  
6:  DSS.is_clustered,  
7:  DSS.cluster_nodename,  
8:  DSS.filename,  
9:  DSS.startup_type,  
10:  DSS.status,  
11:  DSS.process_id  
12:  FROM sys.dm_server_services AS DSS;  

Steps to follow for renaming Stored Procedures

There are two approaches to rename stored procedure. Dropping and Recreating Procedure : The problem is it will lead to losing of permissions. sp_rename of Procedure : Permissions will remain intact. The stored procedure will be renamed. But, sys.sql_modules will still be having the old definition. I prefer the second approach. I followed the below steps:

1. Have copy of the stored Procedure content Rename the stored procedure

1:  EXEC sp_rename 'dbo.OldStoredProcedureName','NewStoredProcedureName'  
2:  GO  

2. ALTER PROCEDURE to have the modified code of the procedure in the sys.sql_modules

1:  ALTER PROCEDURE dbo.NewStoredProcedureName  

3. Now, Stored procedure name is also updated and code is refreshed in sys.sql_modules and permissions are also intact.

Loading CSV file to SQL Server table

Loading CSV file to database using BULK INSERT

Below are the step by step approach to easily load data from CSV file into SQL Server database table.

1. First create the table in the database corresponding to CSV file

CREATE TABLE NameList
(
FirstName varchar(40),
LastName varchar(40)
)

2. Now, generate format file for the above table using BCP Format option

C:\Users\venkat>BCP master.dbo.NameList format nul 
-x -S ServerName -c -f c:\dev\NameListFormat.xml -t, -T

3. Now, load data into the table using BULK INSERT

TRUNCATE TABLE dbo.NameList; -- (for testing)
BULK INSERT dbo.NameList
FROM 'c:\dev\NameList.csv'
WITH (FORMATFILE = 'C:\dev\NameListFormat.xml');
GO

4. Now, data is successfully loaded from CSV to SQL Server table.

Getting All SQL Agent Job Statuses

We can use below script to get status of all SQL Agent jobs, whether they are running or not. If they are running, we will get how many steps are there, what is the currently running step and elapsed time.


SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Never ran'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus',
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then js.StepCount else null end As TotalNumberOfSteps,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then ISNULL(sja.last_executed_step_id+1,js.StepCount) else null end as currentlyExecutingStep,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then datediff(minute, sja.run_requested_date, getdate()) ELSE NULL end as ElapsedTime
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
CROSS APPLY (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps as js WHERE js.job_id = sj.job_id) as js(StepCount)
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
ORDER BY RunStatus desc



Finding IP address details of SQL Server machine

The below query can be used get IP address details of SQL Server machine.

SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

Generating ALTER TABLE Script for MASKING COLUMNS from existing database

If we are having a database with masking columns(Dynamic Data Masking) and not sure about how to get all the masking columns information as a ALTER TABLE script,  we can use below script for the same.

SELECT 'ALTER TABLE ' + SCHEMA_NAME(t.schema_id) + '.' + object_name(c.object_id) + ' ALTER COLUMN ' + c.name + ' ' + UPPER(tp.name) +

                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')

                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'

                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')

                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'

                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')

                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'

                     WHEN tp.name = 'decimal'

                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'

                    ELSE ''

                END + ' MASKED WITH (FUNCTION='''+ c.masking_function COLLATE SQL_Latin1_General_CP1_CI_AS + ''')'+ CASE c.is_nullable WHEN 1 THEN ' NULL;' ELSE ' NOT NULL;' END

FROM sys.masked_columns as c

JOIN sys.tables AS t

ON c.object_id = t.object_id

JOIN sys.types as tp

on c.user_type_id = tp.user_type_id

Getting Sql Service Account Information

We can use below query to get information about Sql Service Account information.

1:  SELECT value_data   
2:  FROM  sys.dm_server_registry   
3:  WHERE value_name = 'ObjectName'   
4:  AND   registry_key = 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER'   

For Sql Server Agent, we can use below query.

1:  SELECT value_data   
2:  FROM  sys.dm_server_registry   
3:  WHERE value_name = 'ObjectName'   
4:  AND   registry_key = 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVERAGENT'   

There are other approaches to get this information.

1:  SELECT DSS.servicename,   
2:      DSS.startup_type_desc,   
3:      DSS.status_desc,   
4:      DSS.last_startup_time,   
5:      DSS.service_account,   
6:      DSS.is_clustered,   
7:      DSS.cluster_nodename,   
8:      DSS.filename,   
9:      DSS.startup_type,   
10:      DSS.status,   
11:      DSS.process_id   
12:  FROM  sys.dm_server_services AS DSS  

Issues during installing SQL Server 2008

This blog post talks about my problems with installing SQL SERVER 2008 Developer Edition in my Windows XP box.

After two failed attempts, I was finally able to install SQL SERVER 2008 in my dev box.

The below are the erros occurred in my previous attempts in installation and the root cause and corresponding solutions for them.

1. Error: “Condition:Feature dependency condition for action…… There are 10 dependant features. The feature is tested for results: ValidateResult, Result." did not pass as it returned false and true was expected.”

Root Cause: Presence of Visual Studio 2008 in my system.

Solution: The blog post of George P. Alexander Junior talks in detail about Failure of SQL Server 2008 installation due to Visual Studio 2008 presence. After reading this blog, I uninstalled all Visual Studio 2008 components to enable clean install of SQL Server 2008 in my system.

2. Error: “Access Denied”

Root Cause: Rights missing for “Debug Programs” for administrative account installing Sql Server.

Solution: The microsoft forum discussion Solution to Access Denied Error in SQL Server 2008 installation contains the solution. We have to change group policy accordingly.

After correcting the root cause issues, finally I am able to install SQL Server 2008 in my system. I am very happy about it. I think Microsoft would be including these constraints check prior to install of SQL Server 2008.

/Venkat