Sql studio see all datbases i have access to11/30/2022 ![]() ![]() ![]() Or you can open Perfmon and watch it there as well. You can query the sys.dm_os_performance_counters for Transactions/sec and run this several times to see if the count is increasing or not. WHERE name NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’)Ĭhecking to see if the number of transactions are increasing for a database is another way to see if it is being used. For the reverse of that, if you want to see which database have not had the indexes used since the last server reboot, run this statement: The statement above will show you the date and time the indexes for your database were last used. WHERE db_name(database_id) = ‘AdventureWorks2016’ –insert your database name here Information on index usage is held in the sys.dm_db_index_usage_stats table since the last server reboot, and can be queried using this statement which can be tailored to select the data you need. Depending upon how long you run the Profiler or Audit, these files can take up a considerable amount of space so be sure to monitor your disk space carefully.Īnother way to see if your database is in use is to look and see if the indexes are being used. Sql studio see all datbases i have access to how to#For a good explanation on how to perform both of these tasks click here. You can also create a SQL Server Audit to record successful logins, but these are at the server level, not the database level. Simply run the trace, export it as a definition file, and import it into a table to query the results. In order to gather this data over time, you could create a trace through SQL Server Profiler. It might be more beneficial to watch the connections to a database over a period of time instead of looking at the current connections only. WHERE DB_NAME(p.dbid) = ‘AdventureWorks2016’ –insert your database name here INNER JOIN sys.sysprocesses AS p ON s.session_id = p.spid INNER JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id –Active Connections to Database with connecting IP address GROUP BY dbid, spid, loginame, login_time, last_batch, status WHERE DB_NAME(dbid) = ‘AdventureWorks2016’ –insert your database name here –Find number of active connections to database The following statements will filter data relating to a specific database: The info in this view returns data about both client and system processes running on the instance. ![]() This system view will be deprecated in future releases of SQL. Information about processes on your instance can also be derived from sys.sysprocesses. You can run exec sp_who2 to return all sessions belonging to the instance, or you can filter to return only the active sessions: Sp_who2 is a well-known stored procedure that returns information about current users, sessions and processes. Find dependencies with Jobs or other DBs.Capture login/connections over period of time.There really is no straightforward way to determine this, but there are several things we can look at to get a better idea. But there should be more definitive ways to do this, right? Well, I guess that’s one way to find out if a database is being used. One person suggested we just not move them, and see if anybody screamed or if it broke anything. ![]() Nobody seemed to know whether several of the databases were used or what they were for. During the migration of a client’s SQL environment to a new hosting provider it was questioned whether all of the databases needed to be migrated. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |