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.

SQL Jobs – Complete Information – SQL 2000/2005/2008/R2

SQL Job details – The script can be executed across all SQL version from SQL 2000 onwards.

The below are the columns listed in the output

  1. ServerName
  2. ScheduleID
  3. ScheduleName
  4. IsEnabled
  5. ScheduleType
  6. Occurrence
  7. Recurrence
  8. Frequency
  9. ScheduleUsage
  10. StartDate
  11. ScheduleUsage
  12. EndDate
  13. ScheduleCreatedOn
  14. ScheduleLastModifiedOn

For more information about the columns refer the below the…

Read more

0 comments, 164 reads

Posted in PowerSQL By Prashanth Jayaram on 11 March 2014

SQL – Backup Report

The simple query to find the database backup status

The select statment consists of

  1. ServerName
  2. dbname
  3. BackupStartDate
  4. BackupFinishDate
  5. BackupAge
  6. Size
  7. status
  8. Type

The query will only run from sql 2005 and later version.

Select  
 SERVERPROPERTY('ServerName'), 
 db.name, 
 CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14) backup_start_date, 
 CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   +…

Read more

2 comments, 188 reads

Posted in PowerSQL By Prashanth Jayaram on 5 March 2014

SQL – Backup Report

The simple query to find the database backup status

The select statment consists of

  1. ServerName
  2. dbname
  3. BackupStartDate
  4. BackupFinishDate
  5. BackupAge
  6. Size
  7. status
  8. Type

The query will only run from sql 2005 and later version.

Select  
 SERVERPROPERTY('ServerName'), 
 db.name, 
 CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14) backup_start_date, 
 CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   +…

Read more

2 comments, 27 reads

Posted in PowerSQL By Prashanth Jayaram on 5 March 2014

INVENTORY – SQL- SSAS – SSRS – Excel Output – Email

Inventory

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s…

Read more

2 comments, 27 reads

Posted in PowerSQL By Prashanth Jayaram on 3 March 2014

INVENTORY – SQL- SSAS – SSRS – Excel Output – Email

Inventory

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s…

Read more

2 comments, 283 reads

Posted in PowerSQL By Prashanth Jayaram on 3 March 2014

PowerShell – SSRS Inventory – Automatic – Excel File – EMAIL

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.

Download the file and save as SSRSInventory.PS1.

It has five mandatory parameters

  1. InputFileName – Text File contains a list of SQL Servers  -C:\Server.txt(Example)
  2. DirectoryToSave – Folder…

Read more

0 comments, 291 reads

Posted in PowerSQL By Prashanth Jayaram on 27 February 2014

PowerShell – Export SQL Data to Excel with Powershell

SQL Server  Export  to Excel with Powershell is very useful for all sorts of activities – ETL and reporting are some examples.
This post is to Export SQL Data to Excel with Powershell.In this example, a connection is made to a SQL server Instance using DSN and then SQL output…

Read more

5 comments, 2,525 reads

Posted in PowerSQL By Prashanth Jayaram on 24 February 2014

PowerShellGUI Tools – Disk,Memory and Process – Modules

I’ve created a script which gives details of Disk, Memory and Process and its represented graphically.

Here, the scripts are enclosed in module. At its simplest, a module is just a PowerShell script, contained in a file with a .PSM1 extension

A module is some chunk of code that you… Read more

0 comments, 853 reads

Posted in PowerSQL By Prashanth Jayaram on 20 February 2014

Top 10 Process Listing By Memory Consumption – GUI Tool

This Powershell GUI tool is used to list Top 10 memory consumption process of a given computer[local /Remote]. In the screen that follows, enter the computer name that you would like to get the top 10 processes and hit Get Process button.

This pulls out the memory consumed by top… Read more

0 comments, 182 reads

Posted in PowerSQL By Prashanth Jayaram on 7 February 2014

PowerShell – SQL Inventory – Automatic – Excel File – EMAIL

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.

Download the file and save as SQLInventoryExcel.PS1.

It has Five mandatory parameters

  1. InputFileName – Text File contains a list of SQL Servers -c:\Server.txt(Example)
  2. DirectoryToSave – Folder…

Read more

7 comments, 418 reads

Posted in PowerSQL By Prashanth Jayaram on 4 February 2014

PowerShell – Memory Details GUI Tool

This Powershell Memory GUI tool is used to fetch memory information from a computer. In the screen that follows, enter the computer name that you would like to get the memory details for and click on Get Memory Button.
This pulls out the usage of memory of the listed computer.… Read more

1 comments, 1,455 reads

Posted in PowerSQL By Prashanth Jayaram on 27 January 2014

PowerShell – Program List – 32/64 Bit – Local/Remote Machies

This Powershell script list all the installed application on both 32 and 64 bit applications, particularly useful for people managing both 32-bit and 64-bit applications.   This code also contains an exclusion array where you can exclude list of program that you don’t want to show.

  • The function allows -ComputerName parameter…

Read more

4 comments, 217 reads

Posted in PowerSQL By Prashanth Jayaram on 20 January 2014

T-SQL to check Data and Log Files are on same drive or not

The below query help us in finding the physical existence of data and log files are on same drive or not of all the database in a SQL Instance.

;WITH LogCTE AS 
( 
SELECT DISTINCT LD.Database_Name 
FROM 
( 
SELECT DB_NAME(database_id) AS [Database_Name], LEFT(Physical_Name, 1) AS [Drive_Letter] FROM sys.master_files WHERE type_desc…

Read more

0 comments, 114 reads

Posted in PowerSQL By Prashanth Jayaram on 13 January 2014

T-SQL – Monitoring DiskSpace of Multiple Servers

This post talks about step by step approach to capture the disk space using T-SQL. This is a request from one of SQL enthusiast. The requirement is to do with T-SQL to monitor disk space of remote servers.

Pre-requisites are

  1. Enable XP_CMDShell 
  2. Enable Ole automation on all servers

Step by… Read more

0 comments, 201 reads

Posted in PowerSQL By Prashanth Jayaram on 10 January 2014

SSRS – Identify a scheduled SSRS Report

Initiating subscription is a tedious task in SQL Server Reporting Services. Each subscription in Reporting Services is setup as a SQL Server Agent job, but the job names are Unique Identifier its hard to remember and re-call. Have complete information in single query makes one easy to validate and execute… Read more

0 comments, 165 reads

Posted in PowerSQL By Prashanth Jayaram on 10 January 2014

Powershell – Age of File Report – Local or Remote

Here’s a very quick post by request.I just had a request for searching the file system for files matching certain criteria such as file age based on the date created. The function Get-FileAgingReport uses get-childitem cmdlet. This function uses two mandatory input parameters Computername and Folder Path.The function is validated… Read more

0 comments, 350 reads

Posted in PowerSQL By Prashanth Jayaram on 8 January 2014

PowerShell – Find OS architecture (32 bit or 64 bit) of local or remote machines Using Powershell

Method 1: Environment Variable

PS:\>$ENV:PROCESSOR_ARCHITECTURE

Method 2: Using Win32_OperatingSystem

ps:\>$computername=’abcd’

ps:\>(Get-WmiObject Win32_OperatingSystem -computername $computername).OSArchitecture

 Method 3: Using Win32_Processor

ps:\>$computername=’abcd’

ps:\>Get-WmiObject -Class Win32_Processor -ComputerName $ComputerName| Select-Object AddressWidth

Method 4:[IntPtr]::Size - Gets the size of this instance.  The value of this property is 4 in a 32-bit process, and 8 in a… Read more

0 comments, 399 reads

Posted in PowerSQL By Prashanth Jayaram on 6 January 2014

PowerShell- Monitoring Multiple Service on a Group of Servers

I got a request to monitor multiple services and send an email to intended recipients. This post explains how to monitor a multiple services on a group of servers. The function Get-ServiceStatusReport comprises of various cmdLets and function to monitor services on a list of servers.

  • Get-Service
  • HTML Ouptut
  • Email…

Read more

10 comments, 332 reads

Posted in PowerSQL By Prashanth Jayaram on 4 December 2013

Disk Space GUI Tool – Multiple Servers with GridView and Email Output

This tool helps you get the disk space information on the computers of your choice. Either you can type the path of an input file or hit ‘Browse Button’ to select the file using dialog option.

Put the select input file and hit ‘GetDisk’. It will query the disk space… Read more

5 comments, 201 reads

Posted in PowerSQL By Prashanth Jayaram on 3 December 2013

Newer posts

Older posts