one of the best monitor sql scripts to get detail information for you sql server transactions
Archiv für den 26. März 2012
currently active transaction
Following SQL will return currently active transaction. SELECT * FROM sys.dm_tran_session_transactions
which index is on which filegroup/file?
if you are going to take over a sql server and you must know which index is on specific filegroup this little sql script will give you the answer select 'table_name'=object_name(i.id) ,i.indid ,'index_name'=i.name ,i.groupid ,'filegroup'=f.name ,'file_name'=d.physical_name ,'dataspace'=s.name from sys.sysindexes i ,sys.filegroups f ,sys.database_files d ,sys.data_spaces s where objectproperty(i.id,'IsUserTable') = 1 and f.data_space_id = i.groupid and [...]
bad index?
to find good and bad indexes, more writes then reads could be a problem for your sql server — List unused indexes use yourDB go declare @dbid int select @dbid = db_id() select object_name(s.object_id) as ObjName , i.name as IndName , i.index_id , user_seeks + user_scans + user_lookups as reads , user_updates as writes , [...]