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.

BACKUP LOG <DATABASE NAME> TO DISK=’D:\BACKUPS\LOG\’

Once log backup completes, shrink the log file:

DBCC SHRINKFILE(FILENAME,SIZE(MB))

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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&LinkId=20476


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!!

Tuesday, October 4, 2016

SQL Server AlwaysOn Availability group interview questions.

Which High Availability features can be used along with AlwaysOn Availability group feature?
»    AlwaysOn Availability group can be combined with Log shipping and Replication.
»    DB mirroring cannot be combined with AlwaysOn Availability group.

Do we need shared storage to create AlwaysOn Availability group?
»    No. Shared storage is not mandatory for creation of AlwaysOn Availability group.

Can we take differential backup in the secondary replica of AlwaysOn Availability group?
»    No. We can only take a log and copy only FULL backups.

How many replicas we can have in AlwaysOn Availability GROUP?
»    1 Primary and 8 secondaries in SQL Server 2016/2014.
»    1 Primary and 4 secondaries in SQL Server 2012.

Which High Availability features support Automatic page repair, if any page becomes unreadable.
»    AlwaysOn Availability and DB mirroring.

How many ALWAYSON Availability groups and AlwaysOn Availability group databases we can have for instance?
»    There is no upper limit as such and it depends on your server configuration. But MS has tested with 10AGs and 100DBs per physical machine.

After adding ndf file to Primary of AlwaysOn Availability group my secondary went to suspended state. What could be the reason and how to fix it.
»    Reason could be file path differences between primary and secondary AlwaysOn Availability groups. If the same file path does not exist in secondary replica then that replica will be SUSPENDED and goes to NOT SYNCHRONIZING state.
»    To fix this: remove DB from the secondary replica of AlwaysOn Availability groups, take FULL backup of PRIMARY database and restore it to secondary, same way restore log backup as well, after restoring all pending log backups then add the secondary DB back to AlwaysOn Availability groups.

How to install service pack when Alwayson Availability Group feature is enabled in SQL instance.

»    Disable automatic failover.
»    First install the service pack in secondary replica.
»    Wait until AlwaysOn Availability groups state becomes SYNCHRONIZED.
»    Manually failover the DB to the secondary replica.
»    Now upgrade the earlier primary server.
»    Failover the DB to the initial primary server.
»    Make the AlwaysOn Availability groups state is SYNCHRONIZED at the end.

What are different availability modes in AlwaysOn Availability groups?

»    Asynchronous-Commit mode:
·         Ideal for DR scenario.
·         High performance.
·         No Automatic failover.

»    Synchronous-Commit mode:
·         High data availability and data protection.
·         Automatic and manual failovers are supported.

References:


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

#AlwaysOn Availability group, #AG, #Alwayson, #AvailabilityGroups

Thanks VV!!