SQL Server Login Properties USE [master] GO SELECT [name] AS [SQL_User] ,LOGINPROPERTY([name], ‘PasswordLastSetTime’) AS [PasswordLastResetDT] ,LOGINPROPERTY([name], ‘BadPasswordCount’) AS [BadPasswordCount] ,LOGINPROPERTY([name], ‘BadPasswordTime’) AS [BadPasswordDT] ,LOGINPROPERTY([name], ‘HistoryLength’) AS [HistoryLength] ,LOGINPROPERTY([name], ‘IsExpired’) AS [IsExpired] ,LOGINPROPERTY([name], ‘IsLocked’) AS [IsLocked] ,LOGINPROPERTY([name], ‘IsMustChange’) AS [IsMustChange] ,LOGINPROPERTY([name], ‘LockoutTime’) AS [LockoutTime] FROM [sys].[sql_logins] …
Truncate Log – Rebuild Indexes – Statistics
Change FULL Recovery Model to SIMPLE and Shrink Log SELECT sys.databases.name , SUM(size) * 8 / 1024 AS [Total_disk_pace_MB] FROM sys.databases JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id GROUP BY sys.databases.name ORDER BY 2 DESC , sys.databases.name; USE master; DECLARE @isql VARCHAR(2000) , @dbname VARCHAR(64) , …
Auditing Columns Add
Auditing Columns Add: I had a need to answer “who”,”where” and “when” questions about the data we add and update. We didn’t have Auditing columns on all our tables. So I thought about coming up with easy way of Adding Auditing Columns. Auditing Columns: CreatedBy …
Attaching Volume to EC2 Instance
Attaching Volume to EC2 Instance To list the partitions $ cat /proc/partitions major minor #blocks name 202 1 52428800 xvda1 Create a Volume in EC2 Instance and attach it to the instance. $ cat /proc/partitions major minor #blocks name 202 …
SQL Server or MySQL for our Business Needs?
Business Need: Currently we are in the cross-roads of growing from small-mid sized company to large scale K-12 online Test Delivery and reporting Solution provider. We primarily operate on K-12 Summative Market and its absolutely critical for us to provide 100 % accuracy and availability during Student Test windows. …
SQL Server 2014 – Memory Optimized Tables
Our Team Need : We have about 5-500 GB Databases and very few tables ( ~ About 10 tables ) are high volume high concurrency tables. At peak volume, we may write few thousands per second per table. We are in the business of providing …
XACT_ABORT Settings and Connection Timeout Nuances
We have .NET Application connecting to SQL Server database. I have noticed the timeout from the Application while calling the stored Procedure. We didn’t configure any specific timeout limit , so we were using default 30 Sec Timeout. Its pretty big red flag, that the …
SQL Handle to SQL Text
SQL Handle to SQL Text with Statement Start and Statement End: DECLARE @sql_handle VARBINARY(64) , @stmtstart INT , @stmtend INT SELECT @stmtstart = 3268 , @stmtend = 5102 , @sql_handle = 0x03000800d8c3dd70e2f83f01e2a300000100000000000000 SELECT SUBSTRING(qt.text, s.statement_start_offset / 2, ( CASE WHEN s.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), …
Deadlocks : Count , Analysis and Event Notification
SQL Server : Number of Deadlocks since Last SQL Server Restart SELECT sqlserver_start_time AS ServerRestartTime FROM sys.dm_os_sys_info SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = ‘Number of Deadlocks/sec’ AND instance_name = ‘_Total’ — Trace Flags to Capture Deadlock Events DBCC TRACESTATUS(1222) DBCC TRACEON(1222,-1) DBCC TRACEOFF(1222,-1) Extended …
SQL Server – Read Errorlog
Enable Audit log to capture successful and failed Logins — To Enable Failed and Successful Logins — Need to Restart the Server for this to effect EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’AuditLevel’, REG_DWORD, 3; GO — Query for Successful Logins EXEC sys.sp_readerrorlog @p1 = 0, — …