While planning to configure always on availability group for first time on a multi subnet cluster found following use articles, it provides clear picture in terms of design and architecture, our requirement was to build SQL 2012 cluster on DC1 along with a reporting standalone SQL instance in same DC1, along with it, we want to have DR solution in DC2 where two standalone SQL instances are present on their respective server, to achieve this […]

On a recently built SQL Server 2016 observed following errors every 5 mins,   Login failed for user ‘DOMAIN\COMPUTER$’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] Error: 18456, Severity: 14, State: 5        As part of troubleshooting, tried following,   Created login DOMAIN\COMPUTER$ with public access as well as SA access but error continue to arrive Validated all SQL agent jobs, OS scheduler tasks, etc. but found nothing […]

During my evaluation of SQL Server 2014, I happened to face a problem, when I hit the F5 key in the Object Explorer, the SSMS was starting the Debug action instead of Refresh.Usually most of them will be happy for F5=Refresh and F5=Query Execution. Here is the procedure to Re-assign F5 key to refresh action in SQL Server Management Studio. Open Management Studio Go to Tools >> Options Expand Environment >> Keyboard >> Keyboard In […]

As we know the solution for SQL Server Data at rest encryption is TDE (Transparent Data Encryption) but what if application need End to End data encryption for PII fields, the solution is Always Encrypt columns in SQL 2016 Ent. Ed., from support DBA perspective it’s more of setting it up and supporting it but major liability lies with application developer, they need to validate and implement solution in first place; certainly there are few […]

As a DBA we encounter situations when CPU is high on a DB server but DB is not consuming any CPU, we can see the processes consuming high CPU but don’t know how to identify that what process is doing causing issue and in cases where process is an encapsulated process like wscript.exe or any other system process we generally recommend server reboot but that generally washes out all logging; now as a DBA we […]

To change server collation following steps needs to be done,   Use the SQL Server set up, and run the following command from the folder where set up file is located,   Imp Note: this will rebuild master database and subsequently all information like user db, logins, jobs (msdb) will be lost, script out everything as described below before triggering the command.   <sql server media folder>:/Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=INSTANAMEHERE /SQLSYSADMINACCOUNTS=DOMAINNAME\SERVICEACCOUNNTNAME /SAPWD= SAPASSWORD /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI (The […]

After struggling a lot and working with vendor, identified list of exclusions for SQL Server related to Anti Virus Software (McAfee, Symantec, etc.), the details can be found in this link, last one is important as it impacts performance.   https://support.microsoft.com/en-us/help/309422/how-to-choose-antivirus-software-to-run-on-computers-that-are-running-sql-server   Directories and file-name extensions to exclude from virus scanning When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. Doing this […]

Issue: While trying to restart SQL Service, it failed and following error found in error logs,   yyyy-mm-dd hh:mm:ss.xx Server Error: 17182, Severity: 16, State: 1. yyyy-mm-dd hh:mm:ss.xx Server TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol’s ‘IPAll’ configuration key. The data is invalid.   yyyy-mm-dd hh:mm:ss.xx Server Error: 17182, Severity: 16, State: 1. yyyy-mm-dd hh:mm:ss.xx Server TDSSNIClient initialization failed with error 0xd, status code […]

While deleting a task in task manager, received following error reported by my team,   Error: “transaction support within the specified resource manager is not started or was shutdown due to an error”     We tried multiple options to modify job and delete but nothing worked, On further google search found that issue is similar to found on following forum,   http://answers.microsoft.com/en-us/windows/forum/windows_7-performance/transaction-support-within-the-specified-resource/fba64f34-4c16-4551-82d7-f22169be0d97?auth=1   Executed following command on server, fsutil resource setautoreset true c:\   […]

Came across weird situation where received alert for SQL Server transaction log file full and found log_reuse_wait_desc=replication from sys.databases   Now before mentioning troubleshooting step, setup of the database is question is little bit complex because Database is part of SQL Server replication where database publisher is at vendor side and we don’t have access to publisher DB 🙁 Oracle Golden Gate replication is configured from this database to Oracle DB Database in question is […]