SELECT*

I got the below error in one of my production environments one day after patching. Below are the error message. You are unable to bring the database engine and agent online, and you will face the below error messages in the error log of SQL Server and eventvwr of Windows server. Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25. This is a serious error condition that might interfere with regular operation and the database will be taken offline. If the error happened during the upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. This error is misleading us don't go for rebuild master database You can also find the same information in the error log SQL server. Worka...

; WITH CTE_Backup AS ( SELECT   database_name , backup_start_date , type , physical_device_name , has_backup_checksums        , Row_Number () OVER ( PARTITION BY database_name , BS . type         ORDER BY backup_start_date DESC ) AS RowNum FROM     msdb .. backupset BS JOIN     msdb . dbo . backupmediafamily BMF ON       BS . media_set_id = BMF . media_set_id ) SELECT D . name , cte . has_backup_checksums            , ISNULL ( CONVERT ( VARCHAR , backup_start_date ), 'No backups' ) AS last_backup_time            , D . recovery_model_desc            , state_desc ,             CASE WHEN type = 'D' THEN 'Full database'      ...

SID mismatch of logins happens between two servers if it is Always On between two replicas. The main thing that we need to remember here is SID mismatch happens only between SQL Server Authentication logins not between Windows authentication logins. In this case there is a mismatch between primary and secondary replica SID’s.   So I am matching with Primary replica and creating logins in secondary , even the logins appears under secondary replica end, we need to drop and recreated with new SID.   use AdventureWorks2012 GO SELECT * FROM sys . sysusers GO --Get the info from Primary replica SELECT * from AdventureWorks2012 . sys . sysusers users INNER JOIN sys . sql_logins   logins ON users . sid = logins . SID WHERE logins . principal_id not in ( 257 , 390 ) --Run the below query in secondary replica. This SID is we can get it above join query CREATE LOGIN LoginNameHere  WITH PASSWORD = N'uuHEN6O0a9aXwytQ7c9Awt1DAtsuqZStXqucIhpDyUw=' , SID = 0x1B12074761...

The below query will truncate the virtual log file count if it is greater than 10, I have chosen 10 here for my testing purpose. --variables to hold each 'iteration' SET NOCOUNT ON declare @query varchar(100)   declare @dbname sysname   declare @vlfs int  DECLARE @LogFileName varchar(1000) DECLARE @VirtualLogFile TABLE (Sno INT IDENTITY(1,1),dbname VARCHAR(1000),VLFCOUNT INT) DECLARE @I INT DECLARE @Count INT DECLARE @Stmt VARCHAR(4000) DECLARE @DatabaseName VARCHAR(1000) DECLARE @DatabaseFiles TABLE (IDNo INT IDENTITY(1,1),LogFileName VARCHAR(2000),DatabaseName VARCHAR(2000)) DECLARE @LastTable TABLE (Sno INT IDENTITY(1,1),dbName VARCHAR(1000),LogFileName VARCHAR(1000)) --table variable used to 'loop' over databases   declare @databases table (dbname sysname)   insert into @databases   --only choose online databases   select name from sys.databases where state = 0      --tabl...

Though you can see the below error in SQL Server for SQL SERVER AUDIT This is might mislead us some times. In my environment, there is no SQL Server Service account permission issue or no space issue though the below-mentioned error keeps on occurring. In the respective  E drive path(mentioned in the error message below)(see the second  image) if the count of files is more than mentioned in the Maximum files . Even at this time, there is a chance that this error might occur. EXEC sp_readerrorlog 0 , 1 , 'TARGET_CREATION_FAILED' GO EXEC sp_readerrorlog 0 , 1 , 'AuditNameHere' Audit: Server Audit: 65543 , State changed from: STARTED to: TARGET_CREATION_FAILED go SQL Server Audit failed to create the audit file 'E:\FolderName1\FolderName2\AuditNameHere_3F69545B-8B6C-4BEF-A69D-759A5E167D19.sqlaudit' . Make sure that the disk is not full and that the SQL Server service account has the required permissions to cre...

For example, I am executing the below query by providing 3 different values to the ProductID column and see the execution plans of these. There is no change in the query except the value that we are providing to the ProductID column and this ProductID column already has a non-clustered index. I am going to execute the same query with 3 different results. So What query optimizer would do is it will compile each time and produce a new query plan for each individual queries which has different values and place it in the cache plan of the buffer pool. I am executing the queries with actual execution plans(Include Actual Execution plan) The 897 query produced two rows and see the execution plan. SELECT SalesOrderDetailID , OrderQty FROM Sales . SalesOrderDetail WHERE ProductID = 897 ; Now I am running the  same query  with different values, this time there are 257 rows got affected, but query optimizer used the same que...