Search

Monday, May 26, 2014

Session Details

Use below queries to get the session details

SELECT S.Session_ID AS [Session ID], S.Status, S.Host_Name AS [Host], C.Client_Net_Address AS [Client Net Address], S.Login_Time AS [Login Time], CASE WHEN S.Login_Name = S.Original_Login_Name THEN S.Login_Name ELSE S.Login_Name + ' (' +S.Original_Login_Name + ')' end AS [Login Name], S.Program_Name AS [Program], ST.Text AS [Query Text], 
CASE S.Transaction_Isolation_Level WHEN 0 THEN 'UNSPECIFIED'  WHEN 1 THEN 'READUNCOMITTED' WHEN 2 THEN 'READCOMMITTED' WHEN 3 THEN 'REPEATABLE' WHEN 4 THEN 'SERIALIZABLE' WHEN 5 THEN 'SNAPSHOT' ELSE CAST(S.Transaction_Isolation_Level AS VARCHAR(32)) end AS [Transaction Isolation Level],
S.Memory_Usage AS [Memory Usage Pages], S.CPU_Time AS [CPU Time MS], S.Row_count AS [Row Count], S.Last_Request_Start_Time, S.Last_Request_end_Time, S.Prev_Error, C.Net_Transport, C.Protocol_Type, S.Language, S.Date_Format
FROM SYS.DM_Exec_Sessions AS S FULL OUTER JOIN SYS.DM_Exec_Connections C ON C.Session_ID = S.Session_ID CROSS APPLY SYS.DM_Exec_SQL_Text(C.MOST_RECENT_SQL_HANDLE) ST
WHERE S.Session_ID IS NULL OR S.Session_ID > 50 
ORDER BY S.Session_ID

Monday, May 19, 2014

Move TempDB files to different location

Use below query to move TempDB data and log files to some other location/drive:

ALTER DATABASE Database_Name MODIFY FILE ( NAME = Logical_Name, FILENAME = 'New_Location\File_Name' )

In above query Logical_Name you can get from below query:

SELECT db_name(dbid) AS DataBaseName, * FROM sys.sysaltfiles WHERE dbid = db_id('TempDB')

AND

New_Location\File_Name is new path and file file for TempDB database.

After moving data and log files you had to restart SQL Server .

Tuesday, May 13, 2014

Stop logging successful backup of databases in SQL Server error log

If you want to stop all database successful backups message in error log than use below DBCC command. This will not record successful backup message in error log but store all other message 

DBCC TRACEON(3226,-1)

Now if you want to record both success and failure backup databases entry in SQL Server error log, than run below DBCC command. 
By default SQL Server log you can see both success and failure backup entries.

DBCC TRACEOFF(3226,-1)