Category Archives: SQL

PowerSQL – List SQL Server instance configuration details and sending an automated email

Posted on May 1, 2013 by Prashanth Jayaram

This article walks through how to list SQL Server configurable and non-configurable instance settings using PowerShell and sending an automated email to a listed users. The Function SendEmail has MailServerName as one of its parameter. Please change accordingly.The following code loads … Continue reading →

Posted in PowerShell, SQL | Tagged Database configuraiton, SQL Configuration | 1 Comment

PowerSQL -Recycle SQL Instance (Remote or Local)

Posted on April 29, 2013 by Prashanth Jayaram

We have a scheduled maintenance window to recycle SQL SERVER Instance once in 30 days hence created a PowerShell job where function being placed along with parameters(servername and instancename) and executed it across all SQLinstance. (Named or Default instance) Default instance-> MSSQLSERVER and … Continue reading →

Posted in PowerShell, SQL | Tagged Recycle SQL Instance | Leave a comment

PowerSQL – Generate Only Index Script of All Tables or Specific Tables

Posted on April 24, 2013 by Prashanth Jayaram

Background of this implementation-As a part of Performance tuning, We noticed huge avg_disk_queue_length on F drive after analysis, we’ve recommended to move two tables(data and index)to different drive all together a different filegroup. This would give the best IO performance. Those … Continue reading →

Posted in PowerShell, SQL | Tagged index script generation | 2 Comments

PowerSQL – Database Backup Report across all Servers – Centralized Approach

Posted on April 23, 2013 by Prashanth Jayaram

This topic provides and describes the quickest way to find when and what databases are being backed up recently.Backups are most important part of a recovery strategy. In this case, I’m explaining this process in three steps 1) Instance names … Continue reading →

Posted in PowerShell, SQL | 5 Comments

SQL – Table data – HTML Format & Email Sending using T-SQL

Posted on April 22, 2013 by Prashanth Jayaram

1) Configure DBMail – In this case the ‘TEST’ profile being created for the demo 2) Create Table – TLOG_Metrics 3) Insert the dumnmy values or You can schedule a job to store the data in TLOG_Metrics table 4) Execute … Continue reading →

Posted in SQL | 3 Comments

SQL – DBCC CLEANTABLE – DROP UNUSED COLUMNS

Posted on April 15, 2013 by Prashanth Jayaram

Drop those columns which are no more in use. For example, After Upgrade from MOSS 2007 to SharePoint Server 2010, we have noticed that all of the upgraded databases in SharePoint Server 2010 were almost 40% – 60% larger in size … Continue reading →

Posted in SQL | Leave a comment

PowerShell and SQL – Easy and Different Ways to Find Number of Rows of all Tables in a database

Posted on April 11, 2013 by Prashanth Jayaram

SQL Example: DECLARE @TableRowsCount table ( Tablename varchar(50), cnt int) insert into @TableRowsCount EXEC sp_MSforeachtable @command1=” select ‘?’,count(*) from ?” select * from @TableRowsCount PowerShell Example: Load SQL PowerShell by typing “SQLPS” in PowerShell console PS:\>SQLPS Change Directory to the … Continue reading →

Posted in PowerShell, SQL | Leave a comment

SQL 2005 or above – Find Transaction Rate on a busy OLTP Database

Posted on April 11, 2013 by Prashanth Jayaram

Change Database name of a below mentioned query DECLARE @cntr_value1 bigint DECLARE @cntr_value2 bigint SELECT ‘BEFORE’ SELECT @cntr_value1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘transactions/sec’ AND object_name = ‘SQLServer:Databases’ AND instance_name =’ABCD‘ /*DB NAME*/ select ‘ABCD’ DBNAME, @cntr_value1 ‘Lower … Continue reading →

Posted in SQL | Leave a comment