SansSQL

Thursday, August 28, 2014

T-SQL to get running sessions and its progress details

Here is a handy T-SQL to get running sessions and its progress details.

SELECT 
  ExecSessions.session_id AS SessionID
 ,ExecRequests.blocking_session_id AS BlockingSessionID
 ,db_name(ExecRequests.database_id) AS DatabaseName
 ,ExecSessions.login_name AS LoginName
 ,ExecSessions.memory_usage AS MemoryUsage
 ,CONVERT(dec(6,3), (ExecRequests.wait_time/60000.00)) AS [WaitTime (mins)]
 ,ExecRequests.[status] AS [Status]
 ,ExecRequests.percent_complete AS PercentComplete
 ,[Text]
FROM sys.dm_exec_sessions AS ExecSessions INNER JOIN sys.dm_exec_requests AS ExecRequests 
ON ExecSessions.session_id = ExecRequests.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) 
WHERE ExecSessions.status = 'running' AND ExecSessions.session_id <> @@SPID
ORDER BY ExecSessions.session_id
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Friday, August 22, 2014

A connection was successfully established with the server, but then an error occurred during the login process.

Recently I came across an issue when connecting to SQL Server instance using SQL Authentication.
The error says,
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error:0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error:233)


To fix the issue, Disable "Shared Memory" protocol in the SQL Server Configuration Manager.


To find the status of SQL Server Protocols using T-SQL, refer to the below link
http://www.sanssql.com/2011/04/t-sql-query-to-find-sql-server.html

Monday, June 30, 2014

The connected user is not an Analysis Services server administrator

Consider a situation where you have installed the SQL Server Analysis Services and during installation, you forget to add the Analysis Services server administrator.
When you connect to the analysis services and try to do some administrative tasks then you are presented with the error "The connected user is not an Analysis Services server administrator."
So now you do not have administrative privilege and no one is added into this rights and how to fix it?
  • Start SQL Server Management Studio as administrator >> "Run as Administrator" 
  • Go to "Security" Page
  • Add user to the "Server Administrators" group

Tuesday, May 27, 2014

Reporting Services Error - The report server installation is not initialized. (rsReportServerNotActivated)

Recently, I had a task for restoring the Reporting Services database from a live environment to a test environment.
Right after the successful restore, I tried opening the Report Manager website and I was presented with the error "The report server installation is not initialized. (rsReportServerNotActivated)"

This is because of the mismatch in the encryption key.
To fix this, either restore the encryption key from the live environment or delete the encryption key from the restored reporting services database.
The restoration or deletion of encryption key can be done by using the Reporting Services Configuration Manager.


Monday, May 26, 2014

T-SQL to find backup location

Here is a T-SQL query to find the location of the backup files.

SELECT database_name AS DBName
    ,physical_device_name AS BackupLocation
    ,CASE WHEN [TYPE]='D' THEN 'FULL'  
    WHEN [TYPE]='I' THEN 'DIFFERENTIAL' 
    WHEN [TYPE]='L' THEN 'LOG'
    WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
    WHEN [TYPE]='G'  THEN 'DIFFERENTIAL FILE'
    WHEN [TYPE]='P' THEN 'PARTIAL'
    WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
  END AS BackupType
    ,backup_finish_date AS BackupFinishDate
FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily
ON(backupset.media_set_id=backupmediafamily.media_set_id)
ORDER BY backup_finish_date DESC