Monday, July 3, 2017

Powershell interview questions.

1.    What are cmdlets in PowerShell?

Cmdlets are lightweight commands used in PowerShell to perform some action. Like Get-Date to get the current date, Get-Help displays information about PowerShell commands, Get-Process displays list of processes that are running on the computer and so on.

2.    What are the comparison operators in PowerShell?

There are several comparison operators in PowerShell to compare the values like ‘-eq’ is equals operator, ‘-gt’ is greater than, ‘-lt’ is less than and ‘-ne’ not equal. Examples like 2 –gt 1 this returns True, 3 –ne 2 returns True, 3 –eq 2+1 returns True and so on.

3.    Can you name any other operators of PowerShell?

Yes. There are other operators such as ‘-like’, ‘-notlike’, ‘-replace’ and so on.
Eg: In below example, we are replacing ‘hi’ with ‘hello’. Check both the outputs.

4.    How will you declare variables in PowerShell?

We can declare like below:

$x = 1. This declares a variable named X and assigns 1 as a value to it.

5.    Are there loops and conditional objects in PowerShell?

Yes. We can use all of these in PowerShell  ‘for’, ‘while’, ‘do..while’,
 ‘if’, ‘elseif’, ‘switch’ and so on.

6.    What is the use of pipeline in PowerShell?

Pipeline is used for joining two or more commands so that output 
of the first command will be sent as input to the second 
command and so on.

7.    How to display the list of command lets in PowerShell?

Get-command displays the list of cmdlets available.

8.    Do we have arrays in PowerShell?

Yes. Arrays are used to assign multiple values to a variable. Arrays can be created using ‘@’ symbol.


9.    How to list all SQL related service details in PowerShell?

Get-service sql*

10. How to run SQL queries in PowerShell?

We can use ‘Invoke-Sqlcmd’ to perform this. Like Invoke-Sqlcmd  
-query “select * from employee”.

11. How to restart services in PowerShell?

Stop-service for stopping a service.
Start-Service is for starting a service.

12. What is an SMO and use of it?

SMO stands for SQL Server Management Object. These assemblies are used for managing SQL Server without using SSMS. First, we need to load these libraries before using these. These can help in automating many of the database activities.

Thanks VV!!

Thursday, March 23, 2017

Log file full issue on Always On Availability Group database.

We are in SQL Server 2014 and using Always on Availability Group and all I got was ‘Disk is FULL and one log file has consumed entire disk space’. Where ever you go, log file full issues follow J .

The below resolution steps have helped me to fix the log file full issue in my environment. This may not help in every situation and for every environment. Please do proper testing on your test servers before using the same method in production environments. Shrinking the log will have a direct effect on your Index performance and point in time recoveries so please DO NOT follow below steps without knowing their complete impact.

Basic points to remember while using Always on Availability Group:                                                                                                                       

1.   We cannot change the recovery model of database involved in AAG. AS AAG works on FULL recovery only.

2.   We need to shrink the log file of the database of the AAG Primary node only.

Shrinking of the log file is not recommended at all because until the root cause has been figured and fixed the issue keeps recurring. In my case also as an immediate resolution, the log file has been shrunk.

First, identify what is causing the log file not to be re-used. It was ‘LOG_BACKUP’ in my case.

Select log_reuse_wait_desc,* from sys.databases

You can use DBCC LOGINFO as well and if you see the status as ‘2’ then the virtual log files cannot be re-used. Means we cannot shrink the log.

I took log backup of the database on the secondary node where usually backup jobs are configured. If you have backups running on primary perform the log backup in primary node.


Once log backup completes, shrink the log file:


Now the file got shrunk.

Make sure regular backups are happening for the database especially when involved in AAG. In my scenario, the log backups were running once in a day and obviously, that has been the issue.

Please share how you have fixed the log file full issue while using Always On Availability Group in the comments section below.

Thanks VV!!
#Log file full, #Always On Availability log file full, Always On Availability log file