Principal "username" is not able to access the database "db" under the current security context.

The server principal "username" is not able to access the database "dbname" under the current security context.
.Net SqlClient Data Provider in SQL Server Management Studio.

This is because the MS SQL Server is attempting to execute a T-SQL query to retrieve a list of databases along with additional information about those databases. One of those pieces of information is "Collation", which you will not have permission to action for all databases as you’re in a shared hosting environment and you only have access to your database alone.

Solution:
Step 1: In Object Explorer, click Databases
Step 2: Display Object Explorer Details (F7) or View > Object Explorer Details
Step 3: Right click the column headers and de-select "Collation"
Step 4: Refresh Databases.

Counters I use for Baselining SQL Server performance

<Counter>\Memory\Available MBytes</Counter>
<Counter>\Memory\Pages/sec</Counter>
<Counter>\MSSQL$SQLINS01:Access Methods\Full Scans/sec</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Buffer cache hit ratio</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Page life expectancy</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Page reads/sec</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Page writes/sec</Counter>
<Counter>\MSSQL$SQLINS01:General Statistics\User Connections</Counter>
<Counter>\MSSQL$SQLINS01:Latches\Average Latch Wait Time (ms)</Counter>
<Counter>\MSSQL$SQLINS01:Locks(_Total)\Lock Waits/sec</Counter>
<Counter>\MSSQL$SQLINS01:Locks(_Total)\Number of Deadlocks/sec</Counter>
<Counter>\MSSQL$SQLINS01:Memory Manager\Target Server Memory (KB)</Counter>
<Counter>\MSSQL$SQLINS01:Memory Manager\Total Server Memory (KB)</Counter>
<Counter>\MSSQL$SQLINS01:Transactions\Free Space in tempdb (KB)</Counter>
<Counter>\MSSQL$SQLINS01:Transactions\Transactions</Counter>
<Counter>\PhysicalDisk(3 M:)\Avg. Disk Queue Length</Counter>
<Counter>\PhysicalDisk(3 M:)\Avg. Disk sec/Read</Counter>
<Counter>\PhysicalDisk(3 M:)\Avg. Disk sec/Write</Counter>
<Counter>\PhysicalDisk(3 M:)\Disk Reads/sec</Counter>
<Counter>\PhysicalDisk(3 M:)\Disk Writes/sec</Counter>
<Counter>\Processor(_Total)\% Processor Time</Counter>
<CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
<CounterDisplayName>\Memory\Pages/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Access Methods\Full Scans/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Page life expectancy</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Page reads/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Page writes/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:General Statistics\User Connections</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Latches\Average Latch Wait Time (ms)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Locks(_Total)\Lock Waits/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Locks(_Total)\Number of Deadlocks/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Transactions\Free Space in tempdb (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Transactions\Transactions</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Avg. Disk Queue Length</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Avg. Disk sec/Read</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Avg. Disk sec/Write</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Disk Reads/sec</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Disk Writes/sec</CounterDisplayName>
<CounterDisplayName>\Processor(_Total)\% Processor Time</CounterDisplayName>

Percent_Complete – detailed output

Percent_Complete is readily available in the sys.dm_exec_requests table. I have enhanced it used if you want to track events based on time remaining and duration

SELECT 
percent_complete
,command
,session_id
,start_time 
,getdate() as 'Time Now' 
,DATEDIFF(mi, start_time, getdate()) as 'Duration (Mins)'
,    (((100/percent_complete)*DATEDIFF(mi, start_time, getdate()))
    -DATEDIFF(mi, start_time, getdate()))/60 as 'Hours Remaining' 
,t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE percent_complete > 0

Find Linked Server Usage in SQL

Very useful script if wanting to know where Linked Servers are being called:

SET NOCOUNT ON;
DECLARE @VName VARCHAR(256)
DECLARE Findlinked CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name AS name
    FROM sys.servers
    WHERE is_linked = 1
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @VName, OBJECT_NAME(OBJECT_ID) 
        FROM sys.sql_modules 
        WHERE Definition LIKE '%'+@VName +'%' 
        AND (OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 
        OR OBJECTPROPERTY(OBJECT_ID, 'IsView') = 1);
 
    FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT j.name AS JobName,js.command 
        FROM msdb.dbo.sysjobsteps js
            INNER JOIN msdb.dbo.sysjobs j
                ON j.job_id = js.job_id
        WHERE js.command LIKE '%'+@VName +'%'
    FETCH NEXT FROM Findlinked INTO @VName;
END
 
CLOSE Findlinked
DEALLOCATE Findlinked

SSMS: Failed to connecr to an IPC Port (mscorlib) Opening DTA

Failed to connect to an IPC Port: The system cannot find the file specified.  (mscorlib)

1. started DTA.

2. stopped DTA during process.

3. Got error message when trying to restart the DTA on a query.

Solution was to open the Windows Task Manger and Killing DTAShell.exe and the error messsage went away

SQL Agent Log Cycle Fails

Recycling SQL Server Agent errorlog frequently fails with following error:

SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022)

When manually ran sp_cycle_agent_errorlog the error below gets reported.

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: 32.

The issue is a locked agent – resolve by restarting the SQL server agent.

Refresh Intellisense Cache

There are cases where the local cache used by IntelliSense becomes stale. Refreshing the cache is easy but not necessarily obvious. There are two ways to refresh the cache:

1)  Go to Edit -> IntelliSense -> Refresh Local Cache

2)  Ctrl+Shift+R