How To View Data in Hidden System Tables.

SQL Server has System base tables which are hidden cannot be directly queried.

For example:

[sql]
SELECT * FROM sys.sysrscols
[/sql]

Output:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysrscols’.

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.

In order to connect using Dedicated Administrator Connection, Add ADMIN: before the name of the server\instance in SQL Server Management studio.

Dedicated Administrator Connection

Now you can query all the System tables. 🙂

[sql]

——————————————————————————————————————————————————
SELECT * FROM sys.sysrscols
GO
SELECT * FROM sys.sysrowsets
GO
SELECT * FROM sys.sysallocunits
GO
SELECT * FROM sys.sysfiles1
GO
SELECT * FROM sys.syspriorities
GO
SELECT * FROM sys.sysdbfrag
GO
SELECT * FROM sys.sysfgfrag
GO
SELECT * FROM sys.syspru
GO
SELECT * FROM sys.sysbrickfiles
GO
SELECT * FROM sys.sysphfg
GO
SELECT * FROM sys.sysprufiles
GO
SELECT * FROM sys.sysftinds
GO
SELECT * FROM sys.sysowners
GO
SELECT * FROM sys.sysdbreg
GO
SELECT * FROM sys.sysprivs
GO
SELECT * FROM sys.sysschobjs
GO
SELECT * FROM sys.syslogshippers
GO
SELECT * FROM sys.syscolpars
GO
SELECT * FROM sys.sysxlgns
GO
SELECT * FROM sys.sysxsrvs
GO
SELECT * FROM sys.sysnsobjs
GO
SELECT * FROM sys.sysusermsgs
GO
SELECT * FROM sys.syscerts
GO
SELECT * FROM sys.sysrmtlgns
GO
SELECT * FROM sys.syslnklgns
GO
SELECT * FROM sys.sysxprops
GO
SELECT * FROM sys.sysscalartypes
GO
SELECT * FROM sys.systypedsubobjs
GO
SELECT * FROM sys.sysidxstats
GO
SELECT * FROM sys.sysiscols
GO
SELECT * FROM sys.sysendpts
GO
SELECT * FROM sys.syswebmethods
GO
SELECT * FROM sys.sysbinobjs
GO
SELECT * FROM sys.sysaudacts
GO
SELECT * FROM sys.sysobjvalues
GO
SELECT * FROM sys.sysclsobjs
GO
SELECT * FROM sys.sysrowsetrefs
GO
SELECT * FROM sys.sysremsvcbinds
GO
SELECT * FROM sys.sysxmitqueue
GO
SELECT * FROM sys.sysrts
GO
SELECT * FROM sys.sysconvgroup
GO
SELECT * FROM sys.sysdesend
GO
SELECT * FROM sys.sysdercv
GO
SELECT * FROM sys.syssingleobjrefs
GO
SELECT * FROM sys.sysmultiobjrefs
GO
SELECT * FROM sys.sysguidrefs
GO
SELECT * FROM sys.syschildinsts
GO
SELECT * FROM sys.syscompfragments
GO
SELECT * FROM sys.sysftstops
GO
SELECT * FROM sys.sysqnames
GO
SELECT * FROM sys.sysxmlcomponent
GO
SELECT * FROM sys.sysxmlfacet
GO
SELECT * FROM sys.sysxmlplacement
GO
SELECT * FROM sys.sysobjkeycrypts
GO
SELECT * FROM sys.sysasymkeys
GO
SELECT * FROM sys.syssqlguides
GO
SELECT * FROM sys.sysbinsubobjs
GO
SELECT * FROM sys.syssoftobjrefs
GO
[/sql]

Who Killed My SQL Server Process?

Who Killed My SQL Server process – session – connection – spid?
If you are sharing single database server with multiple users you might many times end up your process being force killed and an error message shown below comes up.

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

You might be interested in policing as to who killed your process?
Was it done intentionally or some server side error occurred?

In this post let us analyze a technique to sport the user who killed your process:

Using SQL Server error Log:
SQL Server logs errors to the sql server error log, It can be found that it also logs the killing of processes in its error log.

So first query the latest error log for the word kill.
[sql]
EXEC sys.xp_readerrorlog 0,1,’kill’
[/sql]
If you cannot find any results then continue with the next error log
[sql]
EXEC sys.xp_readerrorlog 1,1,’kill’
[/sql]

You can find the logdate,the killed SPID ,Host name and host processid that killed the process in the result

Killed Processes

Now find out the record for your processid in the result.

To find the user who killed your process run the below queries replacing the host process id with the value that you found out in the above step.

[sql]
select login_name,* from sys.dm_exec_sessions where host_process_id = ‘[host process ID]’
[/sql]

The login_name in the result gives you the login name of the user who killed your process.

Hope this post helps..
Applicable to sql server 2005,sql server 2008,sql server 2008 r2,sql server 2012
Also read: Read SQL Server Error Log Using TSQL Query

Do Comment if you wanted to discuss further or have any issues.