In the context of database transaction processing, the acronym ACID refers to the four key properties of a transaction: • Atomicity • Consistency • Isolation • Durability Atomicity : All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account. Consistency : data is in a consistent state when a transaction starts and when… Read more
Category: TSQL
Best Practices while creating Stored Procedures
1. SET NOCOUNT ON: Always use ‘SET NOCOUNT ON’ statement at the begening of your code inside the SP to reduce unnecessary network round trips. 2. PARAMETER SNIFFING: Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Prameter Sniffing. To avod this assign the parameter values to local variables and then use them with SQL queries. 3. Use table variables: Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible. 4. Use of Temp Tables: If you think that the temporary… Read more
Windows Disk Space Alert From SQL SERVER JOB
In SQL server when the auto growth option is set to true and when we have defined the autogorwth in MB, some times we may face the issue like windows disk space is full, during that time SQL Server cannot increase the auto growth size of the mdf or ldf files. As Best practice to have the alert for the drives free space. We can create a job for the below script. schedule it for every 15 or 30 minutes based on your application usage / load.
How to Find SQL Server Database Size
if convert(varchar(20),SERVERPROPERTY(‘productversion’)) like ‘8%’ SELECT [name], fileid, filename, [size]/128 AS ‘Total Size in MB’, [size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’, CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’, (100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’ FROM sysfiles else SELECT [name], file_id, physical_name, [size]/128 AS ‘Total Size in MB’, [size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’, CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’, (100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’ FROM sys.database_files
SQL SERVER 2012 Hierarchy of Database startup
1.Master 2. MSDB 3. mssqlsystemresource 4. User Databases 5. Distribution 6. Model 7. TempDB
How to Find SQL Server Started date & Time – 4 Scripts to Find SQL Server Started Time
There are so many way of finding the sql server started date time. 1. SQL Server Log 2. using DMV 3. Using TempDB Creation date (as it is recreated when SQL Server is started every time) 4. Start Time of Default Trace Examples : 1. SQL Server Log : SQL Server Management Studio –> Management –> SQL Server Logs –> We need to Search for the “SQL SERVER Starting….” or sp_readerrorlog 0,1,’Copyright (c)’ 2. Using DMV : SELECT sqlserver_start_time FROM sys.dm_os_sys_info; 3. Using TempDb Creation Date select crdate from sys.sysdatabases where name=’tempdb’ 4. Default Trace : SELECT login_time FROM sys.dm_exec_sessions… Read more