I have recently come across a wonderful Video on SQL Server performance tuning and troubleshooting checklist by Kevin Kline. and created the notes below for my reference. Troubleshooting Che...

1. Geckoboard Executive CEO dashboard CMO dashboard CFO dashboard Management dashboard SaaS company dashboard Company dashboard Startup dashboard Pirate metrics (AARRR) dashb...

To search for a table across databases in a server EXEC sp_MSforeachdb <span class="hljs-string">'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like '...

Problem: I was generating a query using dynamic SQL and copied the result from grid and pasted into a query window and executed the same. This resulted in error. This is the error I got when...

Problem: I have logged in to the SQL Server using winodws authentication and created a new login with SQL authentication. Then tried login with the new login and got the error as login faile...

Error: Log reader agent has failed with an error Login failed for user 'sql-user-account'. Solution: Right click on the publication name and open properties window. Go to Agent Security p...

Error Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 188 This database is not enabled for publication. I have used the below script to enable the database for repli...

Error: The request failed or the service didn't respond in a timely fashion consult the event log or other applicable error logs for details. Solution: Go to Administrative Tools and cl...

List out all the indexes defined on a table and column details. USE test_db; GO SELECT OBJECT_NAME(i.object_id) AS table_name ,i.type_desc AS index_type ,i.<span class="h...

This query gets tcp/ip settings of the SQL Server from registry. <span class="hljs-keyword">SELECT</span> registry_key, value_name, value_data <span class="h...

You can execute the below query to get the list of SQL Server agents jobs and when it was last executed and time it took to complete the job and when it is scheduled to run next. <span cl...

Problem Unable to rename the database in SQL Server. Msg 5030, Level 16, State 2, Line 17 The database could not be exclusively locked to perform the operation. Solution This error occur...

I have created a function to split a string based on the delimiter value and return the result as a single column of values in a table. <span class="hljs-keyword">CREATE</...

Problem: When executing a SQL script file, I have got the below error in PowerShell. I was using PowerShell v5 in Windows 10. Short Error message: Get-ChildItem : Can not find path 'file pa...

SELECT rs.destination_database_name, rs.restore_date, bs.backup_start_date, bs.backup_finish_date, bs.database_name <span class="hljs-keyword">a...

Generate Number table with cross join and row_number function <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span>...

Write a SQL query to find the list of customers who never placed an order. There are two tables. customers orders Sample data: <span class="hljs-keyword">USE</span>...

I have created a function to split a string based on the delimiter value and return the result as a single column of values in a table. <span class="hljs-keyword">CREATE</...

To search whether the list of words contains in a column, you can implement it using a series of conditions with LIKE and OR operators. But to search for additional word, you need to add ano...

Undo last local commit but keep files changes. git <span class="hljs-keyword">reset</span> <span class="hljs-keyword">HEAD</span>~<span class=&...

I was trying to get all the SQL Server replication's publication that has subscriptions. Then for each publication, post a tracer token thru cursor. I encountered the below error. Error: Ms...

You can use the combination of stuff, charindex and len function to replace the first occurrence of the string. <span class="hljs-keyword">declare</span> @database_nam...

Table variable syntax:``` declare @my_table_variable table( id int, name varchar(50) ); select * from @my_table_variable; go 2. Table variable is a local variable with a special data type...

Problem: I have configured the distributor and then trying to create a publication on the publication server using sp_addpublication stored procedures with respective parameters. I have got...

Stored procedure can call a function. But function can not call a stored procedure. Stored procedure can use temporary tables and table variables. Function can use table variable but not te...

Write a SQL query to concatenate multiple rows into a single string? <span class="hljs-keyword">USE</span> TestDB; GO <span class="hljs-keyword">DRO...

You have an employee table with employee details. You need to get 3rd highest salary get the employee details with 3rd highest salary. Here is the sample data from employees table. You ca...

You have joined a bank as their data analyst and you have received the below requirement and need to provide an ad-hoc report to the management. There is an ETL job which loads the data into...

Write a SQL query to de-group the data and provide the output as below. Let us first create the test data to demo <span class="hljs-keyword">USE</span> TestDB; GO &...

To find out the list of replicated transaction, query the MSrepl_transactions table in distribution database. Copy the sequence number of the transaction (i.e., xact_seqno ) which entered th...