Frequently Used Queries In Sql Server



There are various queries we want to identify some quick info about our servers. So this is a 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)

Database / Table - Space / Rowcount


  • How to find the size of all databases In SQL Server (Click Here)
  • How to Get size, row count and used space of all tables in the database (Click Here)
  • How to Get size, row count and used space of all tables for all database (Click Here)
  • How to check all database size and Free Space in SQL Server (Click Here)
  • List all pages of the table in SQL Server (Click here)
  • How to find triggers and its status in the database (Click here)
  • When table data is last accessed and modified in SQL Server (Click Here)


Backup / Restore / Space / Counts / Objects / Disk
  • When backup \ restore will finish (ETA)  (Click Here)
  • Script for multiple restores in SQL Server (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)
  • Create backup audit report (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 a database or multiple databases (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 files, date and size inside a folder using 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)
  • What is "update cache" folder in SQL server installation directory (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 the table in SQL Server (Click Here)
  • Table Belongs to Which Databases (Click Here)
  • Create backup audit report (Click Here)

Performance \ Blocking \ Deadlock



  • 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 a 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 



SQL Server Features:


  • Contained databases (demo)
  • Connect R with SQL Server (demo)
  • Execute sql query from batch file (demo)




8 comments: