Skip to main content

Posts

Email Notification for DBA Monitoring and Alerts - Powershell

Last time I wrote about Powershell script to collect the SQL data collector and WMI data collector. Today I would like share one more Powershell script to send an email alerts for collected data statistics and some other counter queries.

1.Create a DBARepository database and following tables there

CREATE DATABASE [DBARepository] GO USE [DBARepository] GO CREATE TABLE [dbo].[SQLServerInstances]( [InstanceID] [smallint] IDENTITY(1,1) NOT NULL, [DBName] [varchar](100) NULL, [IsActive] [bit] NULL, [UserName] [nvarchar](200) NULL, [Password] [nvarchar](200) NULL, [ServerName] [varchar](100) NULL, [IsDataCollector] [bit] NOT NULL DEFAULT ((0)), [IsEmailAlert] [bit] NOT NULL DEFAULT ((0)), [IsIndexBackup] [bit] NOT NULL DEFAULT ((0)), [IsSQLObjectBackup] [bit] NOT NULL DEFAULT ((0)), [IsDatabaseObjectBackup] [bit] NOT NULL DEFAULT ((0)), [IsSchemaBackup] [bit] NOT NULL DEFAULT ((0)) ) ON [PRIMARY] GO CREATE TABLE [dbo].[EmailAlerts]( [AlertID] [smallint] IDENTITY(1,1) NOT N…
Recent posts

WMI Data Collector for DBA Monitoring and Alerts - Powershell

Sometime before i shared a script to collect data for SQL Server and databases using a power-shell script. Now i am sharing a same but it is for WMI data collectors instead of SQL data, which used for reporting, alert and monitoring purpose for all the Servers. Let me share a script here.

How to use?
1.Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository] GO USE [DBARepository] GO CREATE TABLE [dbo].[WMIServers]( [InstanceID] [smallint] IDENTITY(1,1) NOT NULL, [ServerName] [varchar](100) NULL, [IsActive] [bit] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WMICollectors]( [WMIID] [smallint] IDENTITY(1,1) NOT NULL, [WMIName] [varchar](100) NULL, [WMIItems] [varchar](4000) NULL, [PostExecScript] [varchar](4000) NULL, [IsActive] [bit] NULL, [Schedule] [varchar](30) NULL, [Occurrence] [smallint] NULL, [StartDate] [datetime] NULL, [LastRun] [datetime] NULL, [NextRun] [datetime] NULL, [InstanceIDs] [varchar](20) NULL, [WMITable] [varchar]…

Import Excel data using SSIS packages - SQL Server

I was working on data import using different different methods and one of the method i want to share here. This method is SSIS package to import data from Excel file.

I have created a SSIS package and captured screen shots for the steps, you can find below.
















I will share more methods to data import in SQL Server database. Enjoy data importing!

Use statement is not supported to switch between databases, Use a new connection to connect to different database - SQL Azure Error

A long ago, when I was worked with SQL Azure database, I received below error.

Use statement is not supported to switch between databases. Use a new connection to connect to different database.
It is due to, I was directly trying to make a different database connection using USE statement in query analyzer where MASTER database connection already made. See below screen shot of error.


After I changed a database connection as below and it was succeeded.



Let's share experience of SQL Azure.

SQL Server Data Collector for DBA Monitoring and Reporting - Powershell

Recently I have worked with Powershell script to collect required data from specified databases and specified servers, which used for reporting and monitoring purpose in SQL Server. Let me share a script here.

How to use?
1.Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository] GO USE [DBARepository] GO CREATE TABLE [dbo].[SQLServerInstances]( [InstanceID] [smallint] IDENTITY(1,1) NOT NULL, [ServerName] [varchar](100) NULL, [DBName] [varchar](100) NULL, [IsActive] [bit] NULL, [IsActive] [nvarchar](200) NULL, [Password] [nvarchar](200) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[DataCollectors]( [DCID] [smallint] IDENTITY(1,1) NOT NULL, [DCName] [varchar](100) NULL, [DCScript] [varchar](4000) NULL, [PostExecScript] [varchar](4000) NULL, [IsActive] [bit] NULL, [Schedule] [varchar](30) NULL, [Occurrence] [smallint] NULL, [StartDate] [smalldatetime] NULL, [LastRun] [smalldatetime] NULL, [NextRun] [smalldatetime] NULL, [InstanceIDs] […

Take care while using Function or Sub Query in Select List - SQL Server

At the time of development, we are just looking for it to be functionally work. For easy development, we are applying any code sometimes. But we never look for the performance issue at the time of the development. We look forward to the performance on the second stage. At this stage we find the issue and resolve it.

I want you to go through on one example where I have used one function and sub query in the select list to get the data in the script. Some time inner join is best instead of function and sub query, But it totally depends on the data and the script which you write.

How can we review?
We have a script to check all the stuffs here,
-- Creating tables IF (OBJECT_ID('ItemTypes','U') > 0 ) DROP TABLE ItemTypes GO IF (OBJECT_ID('ItemDetails','U') > 0 ) DROP TABLE ItemDetails GO CREATE TABLE ItemTypes ( ItemType varchar(100), ItemTypeDesc varchar(100) ) GO CREATE TABLE ItemDetails ( ItemId int not null, ItemName varchar(100),…

Impact of Nonclustered Index without Clustered Index - SQL Server

We have read and learn so many times from online sources as it is not best practice to create a non-clustered index without any clustered index created on the table. We should have clustered index on the table.

Have you practically seen that best practice? What will be the impact on query when table have a non-clustered index but not clustered index?. Without a clustered index on the table the query execution plan sometime used non-clustered index and sometimes not as depends on where condition used in the query.

I will show you here, how it behaves with and without clustered index. We have a two shot of the show and will see this behavior with normal non-clustered index and additional covering non-clustered index. Let we created a sample table and get insert some records in it.
--- Creating tables IF (OBJECT_ID('ItemDetails','U') > 0 ) DROP TABLE ItemDetails GO CREATE TABLE ItemDetails ( ItemAutiId int identity(1,1), ItemId int not null, ItemName varchar(…