Rebuild All Database Indexes

Rebuild all the indexes within a Database Script: DECLARE TableCursor CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = ‘BASE TABLE’ and table_name not in (‘sysdiagrams’) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN print ‘DBCC DBREINDEX(‘+@TableName+’)’ DBCC DBREINDEX(@TableName) WITH NO_INFOMSGS FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor […]

Change Database Ownership

It’s a common issue with DBA’s to forget changing the database ownership after restoring the database.Below are couple of methods by which database ownership can be changed. Scripts: use master go EXEC sp_MSforeachdb ‘IF ”?” NOT IN (”tempDB”,”model”,”msdb”,”master”) EXEC [?]..sp_changedbowner ”sa”’ go Change database ownership for single database use database_name go sp_changedbowner ‘sa’ Change Database […]

sp_whoisactive

How to execute sp_whoIsActive? Create the procedure in master database.Execute the procedure from Query Analyser as below. Code Sample : use master go dbo.sp_WhoIsActive @get_plans=1, @get_transaction_info =1,@find_block_leaders=1,@get_locks=1 Attachment :WhoIsActive.sql

Reading Error Log (Parameterized)

Read SQL Server Error log with date time and string filter. SET DATEFORMAT DMY DECLARE @StartDateTime Varchar(30)=’15/11/2012 23:40:00′ DECLARE @EndDateTime Varchar(30)=’29/11/2012 23:59:00′ DECLARE @SearchString nvarchar(MAX)=” DECLARE @ReadCurrentSQLErrorLog TABLE (LogDate DATETIME, ProcessInfo VARCHAR(20), Text VARCHAR(500) ) INSERT INTO @ReadCurrentSQLErrorLog EXEC master.dbo.xp_readerrorlog 0 SELECT * FROM @ReadCurrentSQLErrorLog WHERE LogDate >= CONVERT(datetime,@StartDateTime) AND LogDate < CONVERT(datetime,@EndDateTime) AND text […]

Get Installed SQL Server Instance’s on a Server

List installed SQL Server Instance on a Server. To run this script user should have admin privileges or minimum permission to read OS registry on the server. DECLARE @Registry_Value VARCHAR(1000) DECLARE @SQL_In AS TABLE (Id NVARCHAR(MAX),InsName NVARCHAR(25)) INSERT INTO @SQL_In(Id,InsName) EXECUTE xp_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Microsoft SQL Server’,’InstalledInstances’,@Registry_Value OUTPUT SELECT @@ServerName [Server Name],InsName [Installed SQL Instance] FROM @SQL_In GO

Send Jobs Status Report using DB Mail as HTML

It is really a herculean task to have a consolidated report for all the jobs status for one or more server. My much time is saved by this report Steps to Create Report Configure Database Mail Profile, which will be used to send mail. Create Table for Job History USE [msdb] GO SET ANSI_NULLS ON […]

When was last time Stored Procedure Executed?

All SQL Statement sent to SQL Server Engine is Parsed,Compiled and Executed.The Compiled statements are stored in system tables and can be retried using DMV’s sys.dm_exec_query_stats:Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to […]

Create Indexed Views

An indexed view is a view that has been materialized. This means it has been computed and stored. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated Create the unique clustered […]

Restore Database(s) from backupfiles

The script restore the database backup (single or multiple) from the backup-file(s) stored at a location. The script only take backupfile path as a parameter , the database are restored with the same name as stored in the backupfiles EXEC [Usp_dba_RestoreDatabase] @BackupFilePath=’drive\foldername’ The database files will be restored at the same location as in the […]