Frequently Used Queries In Sql Server


There are various queries we want to identify some quick info about our servers. So this is collection of those queries.

These cant be directly copied as post size will be huge, so you can directly go to required link

User / Login / Dac / Sessions / Blocking / Deadlock
  • When My SQL Login Password Last Reset (Click Here)
  • Who Is Connected Using DAC In SQL Server  (Click Here)
  • Sleeping Sessions in SQL Server from last 30 Mins (Click Here)
  • Fix Orphan user for all databases in SQL Server (Click Here)

Backup / Restore / Space / Counts / Objects / Disk
  • Script for multiple restore in SQL Server (Click Here)
  • Copy all files from one drive to other via SQL Server Script xp_cmdshell (Click Here)
  • Copy all files from one drive to other via SQL Server Script using Robocopy (Click Here)
  • How to kill all SPID in database or multiple databases (Click Here)
  • How to find / retrieve SQL Server database backup history (Click Here)
  • Find database restore history In SQL Server  ( Click Here )
  • Backup \ Restore scripts For litespeed in SQL Server (Click Here)
  • How to find size of all databases In SQL Server (Click Here)
  • How to Get size ,row count and used space of all tables in database (Click Here)
  • How to list files ,date and size inside a folder using SQL Server ( Click Here )
  • Script to get available and free disk space from SQL Server (Click here)
  • List all pages of table in SQL Server (Click here)
  • How to find to find triggers and its status in database (Click here)
  • When table data is last accessed and modified in SQL Server (Click Here)
  • List files ,date and size inside a folder using SQL Server (Click here)
  • How to check all database size and Free Space in SQL Server (Click Here)

Configuration
  • When SQL Server Evaluation Edition is Expiring ?? ( Click Here )
  • Find SQL Server Details Using Query Analyzer (Click Here)
  • How to find On Which Port SQL Server Is Running  ( Click Here )
  • How To view Registered SPN "Existing Service Principal Name" ( Click Here )
  • Find Version of Double-Take From Registry Using SQL Query ( Click Here )
  • Which SP_configure Options Require Reboot In SQL Server (Click here)
  • Currently Running Queries on SQL Server (Click here)
  • How to automatically execute SQL queries with a batch file (OSQL UTILITY) (Click Here)

Audit
  • ALL SQL Server Details Using Query Analyzer ( Click Here )
  • Find SQL Server Hardware details using SQL Query ( Click Here )
  • When Table Data is Last accessed and Modified (Click Here)
  • When Column Was added in table in Sql server (Click Here)
  • Table Belongs to Which Databases (Click Here)

Performance \ Blocking \ Dead lock


  • Which Transaction Filling Log Space (Click here)
  • How to collect performance metrics from SQL Server query analyzer (Click Here)
  • How to find detailed blocking information with query information  (Click Here)
  • How to find top 5 cpu-consuming statements in Sql Server  (Click Here
  • How to find last queries executed on Sql Server  (Click Here)


Index / Stats / Shrink / Tlog


  • How to find list Of indexes for tables  (Click Here)
  • How to find statistics on specific table or column  (Click Here)
  • How to find statistics for table \ column In SQL Server database (Click Here)
  • Find "Last Checkdb Run For All Databases" in SQL server ( Click Here )
  • Shrink all database files together (Click Here
  • Procedure To Shrink Log Files Of Specific Database (Click Here)
  • Delete Transaction log file in SQL server (Click here)
  • How to find all active transactions filling log file (Click Here)


SQL Agent / Jobs
  • How to find SQL agent job status in SQL Server using query (Click Here)
  • SQL Server Job details (Click here)
  • How to call a SQL Agent Job from another Job on a remote server (Click Here)

Replication \


  • How to find Publisher , Publisher server and Publisher databases in Replication (Click Here)
  • How to find Distributor , Distributor server and Distributor databases in  Replication (Click Here
  • SQL Server PDW queries (Click Here)

Adhoc 







8 comments: