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

Thursday, October 6, 2016

SQL Server Log Shipping Interview Questions.

Which jobs get created after enabling log shopping?
4 jobs get created. Backup job on Primary Server, copy job on Secondary server, restore job on Secondary server and alert job on the Secondary server.

A user started complaining he is not able to access database once failover is done from primary server to secondary server? What is the reason and how to fix it?
The user’s corresponding login might not exist on the Secondary server. We need to create the login of the user in secondary server similar to the login in the primary server.

What are TUF and WRK files?
Transaction Undo File. This file gets created only if the Secondary server is in STANDBY mode. This file holds all uncommitted transactions and SQL Server will use this file to check which earlier uncommitted transactions are committed/rolled back. Accordingly, it will write the data to disk.

Transaction Log backups file extensions will be changed to ‘.WRK’ extension while getting copied from Primary server to the Secondary server. Once the backup file gets copied to the secondary server fully then the log backup file extension will be changed back to ‘.trn’. This helps in blocking restores to happen when the file is getting copied.

What happens if TUF file will corrupt?
Log shipping will fail if TUF file gets corrupted. We need to set up log shipping again to fix the issue.

Can we add new data file to primary database? What happens after we try to add?
Yes, file can be added to the primary database. After adding the file to the primary database, if the same path exists in the secondary server then the file gets added to the secondary database as well. If the same file path doesn't exist in the secondary server then log shipping fails.

Can we add new data file to secondary database? What happens after we try to add?
No, we can't add a new file to the secondary database directly as it will be either in restoring or standby (read only) mode.

Does log shipping support all recovery models?
No. Only FULL and Bulk-Logged recovery models are supported.

How to failover database from primary to secondary?

»    Disable all jobs (backup, copy and restore) on primary and secondary servers.
»    Apply all pending log backups to the secondary server by first copying and then restoring them to the secondary database.
»    Take tail log backup of the primary database with NORECOVERY option. Primary database will go into restoring state after this.
»    Restore all the pending log backups to secondary and finally restore the tail log backup as well with RECOVERY option.
»    This will bring the secondary database ONLINE, now configure log shipping in secondary (to make this as primary).
»    Now secondary (current primary) will start acting as a primary database for log shipping.
»    Now run all the new backup, copy and restore jobs that got created. This will make the old primary as a secondary database for log shipping.

How to find what was the last restored transaction log in log shipping?

There are several ways to find this out:
»    We can use the table “select * from msdb..log_shipping_monitor_secondary”
»    We can check ‘Transaction Log shipping reports’ report in standard reports at the instance level.
»    We can manually check ‘backup and restore reports’ in standard reports at the database level.
»    We can manually verify the backup and restore jobs timings and their history to check when was the last time they executed successfully.

How to apply Service Pack / Hot Fixes when Log Shipping is enabled?
How to install service packs and hotfixes on an instance of SQL Server that is configured to use log shipping?

»    First apply Service Pack on Monitor Server (if you have a Monitor Server).
»    Next apply the same update on Secondary Server (if more than one secondary apply on all secondary’s) of Log shipping.
»    Finally apply the same update on Primary Server.

Can we have different versions of primary and secondary servers in log shipping?
Can we configure log shipping between different SQL Server versions? Like Primary on SQL Server 2012 and Secondary on SQL Server 2014?

Yes, we can configure log shipping from lower version of SQL Server to higher version. But in case of failover you will have issues like we cannot configure log shipping from higher to lower version.

We can configure log shipping from SQL 2012 to SQL 2014. We will receive error similar to below when we try to configure log shipping from higher version to lower version of SQL Server:

Restore failed for Server 'SQL2012'.  (SqlManagerUI)
For help, click:
System.Data.SqlClient.SqlError: The database was backed up on a server running version 12.00.2269. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)
For help, click:

I will keep adding new questions to this list. Please share the questions you are aware of.

#LogShipping Questions, #Log Shipping Interview Questions, log shipping interview questions, Log Shipping, log shipping questions

Thanks VV!!