Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

Archives: April 2013

PowerSQL – Find Size, Name, LastAccess and Last Modified time of all bak files across ALL Servers.

We came across a scenario where database bak files being created from long time which are no more in use and hence cleanup those files since we’ve backup tool is in place. We’ve many *.BAK (database backup files) created across ‘N’ number of servers. We have decided to cleanup all… Read more

0 comments, 240 reads

Posted in PowerSQL By Prashanth Jayaram on 30 April 2013

PowerSQL -Recycle SQL Instance (Remote or Local)

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 Named Instance ->MSSQL`$KAT ( To… Read more

0 comments, 363 reads

Posted in PowerSQL By Prashanth Jayaram on 29 April 2013

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

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 two tables have ‘N’ indexes. Manually scripting ‘N’ index… Read more

1 comments, 386 reads

Posted in PowerSQL By Prashanth Jayaram on 24 April 2013

PowerSQL – Database Backup Report across all Servers – Centralized Approach

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 are stored in a table [SQLInstances] and Database [ Read more

1 comments, 469 reads

Posted in PowerSQL By Prashanth Jayaram on 23 April 2013

PowerShell – Get an UNC of a remote folder

To Read/Write/Rename/Copy/Delete a file configured across ‘N’ of servers in the same path, this script is going to be very handy. One example – Citrix Migration – IMA data store

Input – ServerName and Input folder path.

$server =”Server01

$InputPath=”C:\Program Files\Citrix\Independent Management Architecture

Execute the below… Read more

0 comments, 470 reads

Posted in PowerSQL By Prashanth Jayaram on 22 April 2013

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

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 the script

STEP1:

CREATE TABLE [dbo].[TLOG_DBMetrics](
[ServerName] [varchar](1000) NULL,
[userconnections]… Read more

2 comments, 865 reads

Posted in PowerSQL By Prashanth Jayaram on 22 April 2013

PowerSQL – Find and Replace string on multiple file of local or remote machine

Copy and Paste the below content in Set-FindandReplaceString.PS1. Please read the instructions carefully. Try to do a dry run on some test folders for better results.

#############################################################################
# Description : Find and Replace string on any local or remote machine
# Input : Be specific with your string.Its a recursive… Read more

0 comments, 321 reads

Posted in PowerSQL By Prashanth Jayaram on 17 April 2013

Power Shell or Windows – Rebooting of servers local or remote computers

Rebooting of servers local or remote Using PowerShell and Windows

Powershell command

Start->Run->Powershell press enter

PS:\>RESTART-COMPUTER -COMPUTERNAME SERVER01, SERVER02,LOCALHOST

Windows Command

Start->Run->cmd press enter

c:\>shutdown /m \\server01 /r /f /t 02

/m \\Server A remote computer to shutdown – server01
/r Shutdown and Restart
/f Force running applications to close.… Read more

0 comments, 216 reads

Posted in PowerSQL By Prashanth Jayaram on 15 April 2013

SQL – DBCC CLEANTABLE – DROP UNUSED COLUMNS

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 than before the upgrade.

We’ve decided to DROP those… Read more

0 comments, 382 reads

Posted in PowerSQL By Prashanth Jayaram on 15 April 2013

PowerShell – Find Disk Drive Details of local or remote Server

Name of the Function : Get-DiskDriveDetails

Input Parameter : ServerName

Ouptut : Grid view, you can sort and force condition on specific output column

Function call : Get-DiskDriveDetails HQDB001

In this case HQDB001 is Name of the Server

Code:

Function Get-DiskDriveDetails([String]$server)
{
Get-WMIObject Win32_LogicalDisk -filter “DriveType=3″ -computer $server| Select SystemName,DeviceID,VolumeName,@{Name=”Size(GB)”;Expression={[decimal](“{0:N3}”… Read more

0 comments, 400 reads

Posted in PowerSQL By Prashanth Jayaram on 15 April 2013

PowerShell – Find top 10 Largest files of a local or remote Drive

Name of the function: Get-LargestFile

The Input parameters are ServerName and Drive.

Function call

PS:\>Get-LargestFile HQDB001 E

In this case HQDB001[ServerName] and E[Drive]

****************************

Code:-

***********************

Function Get-LargestFile([String]$server,[char]$drive)
{
Get-ChildItem \\$server\$drive$ -recurse -force -ErrorAction SilentlyContinue | Select-object Name,DirectoryName, @{Label=’Size’;Expression={($_.Length/1GB).ToString(‘F04′)}} | Sort Size -descending | select -First 10
} Read more

2 comments, 417 reads

Posted in PowerSQL By Prashanth Jayaram on 15 April 2013

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

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 desired database

PS SQLSERVER:\ cd SQL\HQDB001\default\databases\DummyDBName Read more

0 comments, 303 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2013

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

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 Limit’

WAITFOR DELAY ’00:00:30′

SELECT ‘AFTER’

SELECT @cntr_value2… Read more

0 comments, 202 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2013

PowerShell – CHECK, START and STOP SQL Services of a remote servers

Replace a valid server name in the following examples. Try to do a testing on your local machine and understand before you execute it on any Production Server.

Example 1: Find all SQL related services on HQSQ001

Get-Service -ComputerName HQSQ001 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

Example 2: Find… Read more

0 comments, 393 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2013

Find SQL Server Installation – Date and Time – 2005 Onwards

SELECT create_date as ‘SQL Server Install Date’ FROM sys.server_principals WHERE name=’NT AUTHORITY\SYSTEM’


Read more

0 comments, 222 reads

Posted in PowerSQL By Prashanth Jayaram on 10 April 2013

PowerShell – Pattern Search – Local or Remote Folder

#Description : List all the files of a local or remote drive, run this PowerShell script using PowerShell-ISE.exe for better execution and output.

Steps:- Copy the content and  paste it in Get-AllFilesSearchbyPattern.PS1  and Execute.

#Input Parameter List - Server Name, Drive Name, Extension and Pattern. If you want to… Read more

0 comments, 246 reads

Posted in PowerSQL By Prashanth Jayaram on 10 April 2013