Deadlocks in Azure SQL Database : Recently we were working with Azure Logic Apps to invoke Azure Functions. By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values. So Logic App…
Deadlocks in Azure SQL Database : Recently we were working with Azure Logic Apps to invoke Azure Functions. By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values. So Logic App…
Stored Procedure Error Logging Create table to capture Errors Create Stored Procedure to Log Error Invoke Stored Procedure in TRY/CATCH Block USE TEMPDB; GO IF OBJECT_ID(‘APM.ErrorLog’) IS NOT NULL DROP TABLE APM.ErrorLog; GO CREATE TABLE APM.ErrorLog ( ErrorLogID INT IDENTITY(1,…
Monitor Memory Optimized Table Space Usage : ; WITH system_allocated_memory ( system_allocated_memory_in_mb ) AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb) + SUM(TMS.memory_allocated_for_indexes_kb) ) / 1024.00) FROM [sys].[dm_db_xtp_table_memory_stats] TMS WHERE TMS.object_id <= 0 ), 0.00) ), table_index_memory ( table_used_memory_in_mb,…
Brute force attack on SQL Server If your business needs the SQL Server to be accessible on public network, you may be very vulnerable for brute force attacks. Following query will help you identify the failed login attempts and you…
When Does SQL Server Trial Edition Expire? I installed SQL Server Docker Container on Linux Machine running on EC2. I took the AMI Image and launched the existing container and wanted to know when will the SQL Server licence expires…
Remove Procedure Cache and Reset Wait Stats Remove all elements from the plan cache for the entire sql server instance DBCC FREEPROCCACHE; Flush the cache and suppress the regular completion message DBCC FREEPROCCACHE WITH NO_INFOMSGS; Remove all elements from the…
Configurations : Web Config or Database? Web Config: Database Connection Strings. OfCourse. You can’t connect without that details. Changes you want trigger Application Pool refresh. Things need to be available when the database is unavailable. (such as a list of…
Microsoft Link : https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx Locks held by Current Processes: SQL Server 2008 R2 https://technet.microsoft.com/en-us/library/ms189497(v=sql.105).aspx SQL Team Primer on Locks : http://www.sqlteam.com/article/introduction-to-locking-in-sql-server Kalen Delany Crisp explanation on Update Locks: http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update-locks.aspx UPDATE locks are not really a separate kind of lock, but…
More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP): Common sense tells us , having more CPU’s mean more processing power , that means things would be done faster. Hold on for a sec..Its SQL Server. Common…
How do you find the last time a database was accessed and size? We had about 108 databases on one of our Test Servers and it was being used by multiple teams and we don’t have comprehensive list of what’s…
Database Professional – Interview Questions What factors do you usually consider for your datastore selection ? Data Volume , Variety and Velocity. Whats CAP Theorem?. Consistency , Availability and Partition Tolerance and you can’t all three together. In theoretical computer science,…