use AdventureWorks2012 go SELECT DB_NAME(ddius.[database_id]) AS database_name , OBJECT_NAME(ddius.[object_id], DB_ID(‘AdventureWorks2012’)) –<< replace db name AS [object_name] , asi.[name] AS
Continue readingCategory: SQL Server -DBA
SQL Server -DBA, SQL Server Services, who dropped or altered tables,procedures or databases in SQL Server, What in SQL Server
How to identify where the bulk of the connections originate in SQL SERVER ?
SELECT dec.client_net_address , des.program_name , des.host_name , –des.login_name COUNT(dec.session_id) AS connection_count FROM sys.dm_exec_sessions AS des INNER JOIN sys.dm_exec_connections AS
Continue readingHow to list all empty tables in your database in SQL Server
Use AdventureWorks2012 go ;WITH Empty AS ( SELECT OBJECT_NAME(OBJECT_ID) [Table], SUM(row_count) [Records] FROM sys.dm_db_partition_stats WHERE index_id = 0 OR
Continue readingALL DMV’s In SQL SERVER
SELECT * FROM SYS.dm_os_memory_allocations –<< Notice that all DMVs starts with SYS.DM SELECT * FROM SYS.dm_db_xtp_nonclustered_index_stats SELECT * FROM SYS.dm_db_mirroring_past_actions
Continue readingWhat are Dynamic Management Views (DMVs)
What are Dynamic Management Views (DMVs) Another tool at your disposal to measure performance and view details about the SQL
Continue readingWhat is Index ?
What Is an Index? · An index can be best described as a pointer to data in a table.
Continue readingHow to Move tempDB to another drive in Sql Server ?
Step 1: first we have to check the location of TempDB:- USE tempdb GO SELECT * FROM sys.database_files Step 2:
Continue readingHow to Set Maximum and minimum memory in sql server ?
Use master go sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘max server memory’, 4096; —provide memory want to
Continue readingHow to find all open transactions in Sql Server ?
SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name], DB_NAME (s_tdt.database_id) AS [Database], [s_tdt].[database_transaction_begin_time] AS [Begin Time], [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes], [s_tdt].[database_transaction_log_bytes_reserved] AS
Continue readingHow to find When database was last accessed in Sql Server ?
SELECT d.name, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i JOIN sys.databases
Continue reading