.NETADO.NETAgileAngularArchitectureASP.NETAuthenticationAzureAzure FunctionBIBig DataBlazorBusinessC#Cloud ServiceCosmosDBCSSDebuggingDesignEncryptionEntity FrameworkFYIGoogleHardwareInternetInterviewsJavascriptjQueryLeadershipLINQManagementMicrosoftMobileMS OfficeMS SQLNetworkingNoSQLOOPProject ManagementSoftware DevelopmentSource ControlSSISSSRSSyncfusionTeamTestingTSQLUncategorizedVendor SoftwareVisual StudioWebWeb ServicesWindowsWordPress
Using SQL Job via Powershell script to remove backup files older than x days. Create the job… Powershell script to remove all .bak files… The following script delete all files… The following script delete .7z file from one folder and .bak from another folder on the same drive. Note: Make sure certain account have modify... » read more
Statistics are used by the Query Optimizer to create execution plans based on statistical distribution of required values, the information is stored in BLOBs (binary large objects). Outdated or non-existent statistics can lead to poor performing queries or sub-optimal execution plans. This runs the UPDATE STATISTICS command against all user defined tables in the current... » read more
Once we KILL SPID in SQL Server, it becomes an uncompleted transaction. SQL Server must undo all changes performed by the uncompleted transaction and database objects should be returned to the original state before these transactions. Due to this reason, you can see the status of SPID as KILLED\ROLLBACK once the rollback is in progress.... » read more
Viewing SPIDs Blocking SPID Status Sources:
Execution Plans An execution plan is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. Query Optimizer The query optimizer is essentially a piece of software that “models” the way in which the database relational engine works. Using the query processor... » read more
An index hint forces the query optimizer to use the index specified in the hint to retrieve the data . Index hints can be nice to use in the short-term for investigating, testing, and debugging. However, they are almost never the correct long-term solution for fixing query performance. It’s better to look for the root-cause... » read more
Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers. When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks,... » read more
Error UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). Problem If you do index maintenance using DMO,... » read more
Running scripts from SQL Compare Create table objects first then create rest of database objects. Not NULL column definition – put in a default value. Object Reference error – Drop referenced object and then drop the object. Recreate object and then recreate the referenced object. Errors in general – comment out specific sections until run... » read more
The ideal database drives setup. Drive Use C: Windows OS System D: SQL Server, Database Engine, SQL Services X:\MSSQL\Data Data Files L:\MSSQL\Log Transaction Log Files I:\MSSQL\ Index Files T:\MSSQL\Data TempDB H:\MSSQL\Backup Backups