Skip to main content

Posts

Showing posts from April, 2012

Performance Dashboard

SQL Server 2008 provide graphical reports for monitoring system health and performance. While there have always been various queries, stored procedures,  Dynamic Management Views, to gather system statistics, none were graphical. These new reports provide a quick easy way to print off a professional looking report for presentations or discussions. 
This is an additional add-on feature of  SQL Server Management Studio which is also known as Performance Dashboard.


We Can Start Report from Below :





When select Stand Reports, below built in reports will be displayed. Disk Usage by Top TablesDisk Usage by TableDisk Usage by PartitionBackup and Restore EventsAll TransactionsAll Blocking TransactionsTop Transactions by AgeTop Transactions by Blocked Transactions CountTop Transactions by Locks CountResource Locking Statistics by ObjectsObject Execute StatisticsDatabase Consistency historyIndex Usage StatisticsIndex Physical Sta…

3 Easy Steps to Configure Database Mail

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine.  I am going to use Database Mail to Configure Automation Failure Notification.. Create an e-mail profile, specify its SMTP accounts and Specify profile security.Configure Operator.Assign Operator on Jobs/Schedulers.



In First Step: Create an e-mail profile, specify its SMTP accounts.



To Varify check TSQL   use msdb SELECT*FROM [msdb].[dbo].[sysmail_profile]

Now Enable Database Mail service.
USE master Go EXECsp_configure'show advanced options', 1 Go RECONFIGURE Go EXECsp_configure'Database Mail XPs', 1 Go RECONFIGURE Go EXECsp_configure'show advanced options', 0 Go RECONFIGURE Go
In 2nd Step : Configure Operator.


To Varify TSQL   use msdb

Searching Hard Coded Values

Searching Hard Coded Values from Entire Database-Objects is very Interesting Task for me. My last Blog (Search Dynamic Values) was for Dynamic Values from Tables. Today I want to show how I am searching Hard Coded values (E-mail Ids and Mobile numbers) from all Procedures or Views from our Database.We should avoid the Hard codes in objects. To manages Hard codes in objects  i was simply Converting all procedures and Views in script and after that using find -replace i was managing changes...It was very time consuming and bothering work form me... Below Query help and save my lot of time for learning extras.....declare @text varchar(100) declare @dbname varchar(100) set @text ='sumank' set @dbname ='masterdatabase'
if @dbname isnull begin --enumerate all databases. DECLARE #db CURSORFORSelect Name frommaster..sysdatabases declare @c_dbname varchar(64)
OPEN #db FETCH #db INTO @c_dbname while@@FETCH_STATUS<>-1 --and @MyCount < 500 begin execute find_text_in_sp@text, @c_dbname …