• DBA

    How to use server side trace to capture running query T SQL

    Capturing queries by using server side trace In general, DBA will have a report most of time the server and application web is running slow. You have to find yourself what query is running slow and benchmark and tune it. Profiler trace is good to capture the running code. But it is a GUI and we have to start, capture and stop it. This will also make some performance slowness to the server and you it is hard to read and store data. The server side trace is another option and it is a lightweight and T-SQL code, we can create a stored procedure and schedule it on SQL server…

  • AlwaysON,  DBA

    Multi subnet sql alwayon cluster not connecting to sql database when application or alwayon failover

    Multi subnet SQL alwayon cluster not connecting to SQL database when application server restarts or alwayon failover. In multi subnet we will have more than two ips, the active IP will be online and other ips will be offline. https://techcommunity.microsoft.com/t5/SQL-Server-Support/Connection-Timeouts-in-Multi-subnet-Availability-Group/ba-p/318334 This Microsoft link will have more information. I just used this to give a workaround for one of our application. The application has MultiSubnetFailover=True. Open powershell as administrator. Run the following and pass the resource name. Get the resource name, it could be AG group name_Listener name – Get-ClusterResource Check the ‘RegisterAllProvidersIP’ value, if it is 1 change to 0 Get-ClusterResource “Cluster Resource Name” | Get-ClusterParameter   Change the ‘RegisterAllProvidersIP’…

  • DBA

    upgrade for database master failed because upgrade step  sqlagent100_msdb_upgrade.sql encountered error 200, state 7, severity 25.

    SQL upgrade from SQL Server 2008 R2 SP3 32 bit to SQL Server 2012 SQL upgrade from 2008 R2 to 2012 Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 200, state 7, severity 25. This is a serious error condition which  might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database,  it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective  actions and re-start the database so that the script upgrade steps run to completion.   2019-07-30 17:26:10.38 spid8s      Error:…

  • AutoMon,  DBA

    DBA AutoMon configure Database Centralized management server CMS

    Setup Database Centralized management server CMS DBA AutoMon This is a series of post which will have lot of posts and scripts, which will help DBAs to do proactive work – consolidation and quickly understand the environments. Some background and thanks to my senior DBA Roshan Joe Joseph, we have started together this automation and scripts in year 2008, when we don’t have much tools and where the client could not invest money to buy a tool. We started the DBA AutoMon and planned to build a front end GUI, we could not finish that due to family, personal life and different company career. In most of the service based…

  • DBA

    Log shipping LSN mismatch issue The log in this backup set begins at LSN , which is too recent to apply to the database

    How to troubleshoot LSN mismatch issue in log shipping I have got an email, how to fix for log shipping, I already have a post for alwayson https://www.sqlserverblogforum.com/alwayson/how-to-solve-the-lsn-mismatch-in-sql-server/ For log shipping: you will have entry like this in the job error Message 2019-12-21 21:09:45.39 *** Error: The file ‘\LAPTOP-ISGUKEUC\Backup_Copy\T_20191221153913.trn’ is too recent to apply to the secondary database ‘T’.(Microsoft.SqlServer.Management.LogShipping) *** 2019-12-21 21:09:45.39 *** Error:   The log in this backup set begins at LSN 31000000048600001, which is too recent to apply to the database. An earlier log backup that includes LSN 31000000048200001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** Following is the code to find…