catalog

Most frequently used articles

AZURE – SQL – Frequently User SQL Commands: https://vijredblog.wordpress.com/2016/10/27/sql-commands-in-azure/

Azure – comonly used Powershell Commands:
Frequently used Azure SQL Powershell Commands: https://vijredblog.wordpress.com/2017/07/27/frequently-used-azure-powershell-commands/

Posted in Azure, Powershell, SQL, Uncategorized | Tagged , | Leave a comment

SQL Server – What to collect for troubleshooting root cause of a problem

when we have unexpected behavior from SQL Server impacting SQL availability, it will be crucial to understand if we want to restore the service ASAP or troubleshoot to find the root cause.

When we switch from troubleshooting to service restoration it is critical to collect information that can be used for problem diagnosis. Following are the 2 potential options for that!

  1. When the problem that took place recently, not active we can collect logs before logs are overwritten.
    1. Diagnostic tool log collection
      1. Typically Microsoft provides the tool (EXE):
    2. https://support.microsoft.com/en-in/help/926079/frequently-asked-questions-about-the-microsoft-support-diagnostic-tool
    3. Manually collect SQL Error Logs, Cluster Logs , System/Application event logs
    4. Custom script for data collection: https://raw.githubusercontent.com/vijred/Powershell/master/SQLServerRelated/Collect_Cluster_System_SQL_Logs.ps1
  2. PSSDiag: When the problem is currently in place (Performance issues are best fit for PSSDiag collection)
    1. Microsoft recommends custom PSSDiag to collect specific data. However you can create your own PSSDiag and collect information or you can create one for generic purpose data collection and use it.
    2. Ref: https://support.microsoft.com/en-in/help/830232/pssdiag-data-collection-utility

Posted in Uncategorized | Tagged , , , , | Leave a comment

Find actively running SQL statements using Querystore-QueryID

TSQL to Find actively running SQL statements using QueryID

-- Find actively running SQL Statements based on QueryID (Querystrore - QueryID)

SELECT s.host_name, s.last_request_start_time ,s.last_request_end_time, r.start_time, * from sys.dm_exec_requests r
join sys.dm_exec_sessions s on s.session_id  = r.session_id
join [sys].[query_store_query] qsq on qsq.query_hash = r.query_hash
WHERE r.query_hash IS NOT  NULL
AND query_id = 78139
--
Posted in Performance, Querystore, Querystore, SQL, SQL Query, SQL Server | Tagged , | Leave a comment

MSSQL Server – Monitors I recommend to run once a month

Though most of our monitors are configured to run from same tool at same interval, it is work to evaluate how frequently we run them based on its a few factors.

in this post I list a few monitors/ checks that are worth running/validating once a month:

  • Data file size
    • Max size of data file is only 16 TB. Create a new file before you approach the limit;
      • Note: If you face error while adding a new file, stop the Backup job!

–Msg 3023, Level 16, State 2, Line 5
–Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

  • Sample TSQL to add new file

ALTER DATABASE [MyDatabase] ADD FILE ( NAME = N'AMyDatabasefile2', FILENAME = N'M:\SQLMountPoint\SQLData\USERDATA\MyDatabaseFile2.ndf' , SIZE = 8192KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [PRIMARY]
GO

  • Reaching Integer limit
    • A few Auto-Increment columns defined as Int reach its limit. It is worth validating their latest value

Posted in SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

How to run a command using System credentials

PsExec can be downloaded from https://docs.microsoft.com/en-us/sysinternals/downloads/psexec and execute with -sid parameters!

 

Why do we need this?

When a script is scheduled using Scheduled tasks and configured to run as System same credentials will be used. This process is to simulate same permissions!

 

Example:

.\PSTools\PsExec.exe -sid cmd

 

Note: run it with Admin credentials

Posted in Script, Uncategorized | Leave a comment

Resolved – Setup Doesnot Start For Sql Upgrade/Patch – 0x80004005

Installing SQL Service Pach or Hotfix does nothing;

In general all the install logs will be available at Setup Bootstrap (Example: C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log ), however you will not be able to see any additional infomration.

Before moving QL setup logs, temporary logs will be stored at %temp%, locate sqlsetup.log file from %temp% folder and check for error!

Sample Error:

x80004005

Fix is to reset security policy setting using below command (Ref: https://docs.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/bk67a586(v=vs.100)?redirectedfrom=MSDN )

caspol –machine –reset

 

Note:

  • You will not be able to execute the command from Powershell but use command prompt
  • The tool is available in multiple .net framework locations but choose the correct one (Example, resetting .NET 4.5 did not help to fix the problem but resetting from v2.0x helped to fix the problem)
  • Sample Screenshot:
    • caspol

 

Posted in SQL, SQL Error, SQL Server, Uncategorized | Tagged , , , , | Leave a comment

Python 101 – may be 100.01 :)

 

While investigating a problem related to Python programs with open transactions I end up experimenting with Python today!

It took a few hours to install Python, required libraries, execute first program and complete my actual investigation related to pyodbc library!

If you would like to experiment, here is the best documentation to start with:

https://code.visualstudio.com/docs/python/python-tutorial#_prerequisites

 

Note:

If you have SSL certificate problems during installation, workaround is to dd these url as trusted site

Could not fetch URL https://pypi.org/simple/pyodbc/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host=’pypi.org’, port=443): Max retries exceeded with url: /simple/pyodbc/ (Caused by SSLError(SSLCertVerificationError(1, ‘[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1108)’))) – skipping

WARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by ‘SSLError(SSLCertVerificationError(1, ‘[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1108)’))’: /simple/pyodbc/

Example: python -m pip install –trusted-host pypi.org –trusted-host files.pythonhosted.org  matplotlib

 

Note2: pip3 installs required version for V3 but not pip

 

Note-3: How to install .whl

pip install C:/temp/mylibrary.whl

 

Posted in Powershell, Python, Uncategorized | Tagged , | Leave a comment

Maximum capacity specifications for SQL Server/ Azure DB/ Automation

Ever wonder what are the Maximum capacity specifications for SQL Server?
Here you go: https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

SQL Server Limits: https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

 

Azure Service and subscription limits: https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/azure-subscription-service-limits

 

 

 

Posted in Productivity, SQL, SQL Server, Uncategorized | Tagged , | Leave a comment

SQL – AlwaysOn Availability Group synchronization delay troubleshooting

I learned detailed step by step performance metrics and xevents that can be used to troubleshoot  AlwaysOn Availability Group synchronization delay.

 

https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/troubleshooting-high-hadr_sync_commit-wait-type-with-always-on-availability-groups

 

Some network configuration that can be updated to address network challenges:

https://docs.microsoft.com/en-us/windows-server/networking/technologies/network-subsystem/net-sub-performance-tuning-nics

 

 

Posted in Performance, Referene, SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Very Large Database in SQL Server – How is it different

Following are a few considerations while managing a very large database on Microsoft SQL Server. I am not recommencing any configuration as different configurations are good fit for different workloads and business requirements but these are the items that can be reviewed!

  • I am referring to the databases with at least with 2 TB in size with average of a few thousand transactions a second, and at least 16 Logical CPUs.
  • Maintenance duration: Based on the database size and transaction volume duration for maintenance can be significantly, that needs customization.
  • Backups:  Backup strategy can be different for VLDB considering the amount of time it takes to backup. introduce Striped Backups to save time! Think innovative of how Full, Differential and transactional backups frequency can be changed based on Recovery Point and Recovery time requirements.
  • DBCC CheckDB: DBCC CheckDB may fail at times, https://support.microsoft.com/en-us/help/2002606/os-errors-1450-and-665-are-reported-for-database-data-files is a very good article about different options to address the problem. Consider using Physical-Only option, change number of threads.
  • MAXDOP / Cost Threshold for Parallelism can be different from normal workload databases.
  • Data file and Log file growth size or %: Review growth patterns to understand the impact and correct these settings. Use Instant File Initialization.
  • Index Maintenance configuration settings: Choose correct thresholds for Index Re-Organize and Rebuild. ReOrg is single threaded and may take long and replacing with Rebuild can use parallel threads to reduce Index maintenance duration.
  • Synchronization challenges in High performance servers: Control flow can result in replication delays. Ref: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver15#flow-control-gates
  • VMWare – Paravirtual SCSI settings if running on VM – enhance network performance by adjusting  queue depth – Ref:  https://kb.vmware.com/s/article/2053145
  • Number of database files – Consider creating maximum of ~1TB per file, create multiple data files
  • Database compatibility level: Argeed after long debate, at least on a specific workloads latest compatibility level can impact performance, using legacy cardinality estimator can improve performance.
  • Partitioning: Consider partitioning the data and mark old partitions as read-Only.
  • Archive Strategy: Re-Consider archive strategy as well
  • Full Factor for very large Tables/Indexes: Updating fill factor settings to change Storage, Fragmentation levels, Performance. Verify how Fill Factor can be modified for very large tables.
  • Storage / mount point size: By default Bytes Per Cluster configuration is 4K, meaning maximum size of the disk/mount point will be 16 TB. If we have to increase the size beyond 16 TB this value needs to be updated. I recommend configuring Bytes Per Cluster to 64 KB to avoid 16TB size limit and potential dbcc integrity check failures. Note: This change needs disk formatting, incurs downtime! Bytes Per FileRecord Segment value to be updated to 4096 while formatting (Update command: Format <Drive:> /FS:NTFS /L, Verify command: fsutil fsinfo ntfsinfo <Drive>:)
  • MTU: Consider increasing Server MTU Size to large value from default settings, This helps to minimize fragmentation to send large packets across the netowrk. (Make sure the setting is across all SQL Servers in replication and MTU Limit should be setup on all network devices that the packet travels through)
Posted in Performance, SQL, SQL Error, Uncategorized | Tagged , , , | Leave a comment

MSSQLSERVER Error Catalog

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15

Posted in SQL, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment