Query to study Transactional Changes in MSSQL Database

Query to study Transactional Changes in MSSQL Database

When you have a Management System or any other type of Database application without technical documentation, you may be having a difficulty to find out and making the transactional flow of a procedure. There is a way to make this work out easy. You can use following query to run after a transaction. This query will give you the name of tables that will be affected with respect to time. Then you can easily judge the flow of a transaction. Always record the time at every trial.

 sys.dm_db_index_usage_stats i JOIN
 sys.tables t ON (t.object_id = i.object_id)
 database_id = db_id()
 ORDER BY last_user_update DESC
Query to study Transactional Changes in MSSQL Database

Find out the size of MSSQL Database using SQL

This MSSQL Query returns the records containing the sizes of log, primary data files and secondary data files for a database. Change the DatabaseName in where clause with your database.

SELECT DB_NAME(database_id) AS DatabaseName,
 Name AS Logical_Name,
 Physical_Name, CONVERT(VARCHAR,(size*8)/1024) + ' MB' AS Size
 FROM sys.master_files
 WHERE DB_NAME(database_id) = 'DatabaseName';

You can also find out the sizes of all databases using this query

 DB_NAME(database_id) AS DatabaseName,
 CONVERT(VARCHAR,Sum((size*8)/1024)) + ' MB' AS Size
 FROM sys.master_files
 GROUP BY database_id

You can also do the same task with this transact-sql built-in function

sp_helpdb 'DatabaseName'

To learn more about it, visit at http://msdn.microsoft.com/en-us/library/ms178568.aspx