Search This Blog & Web

Wednesday, November 18, 2015

Finding the procedure, text, triggers or text within any SQL Object from SQL SERVER

I am using syscomments table to find any query or text from within the procedures etc. When I try to find out a text value from within the trigger using syscomments system object. It is not returning the required results. After little research I find out another query that will search all objects in your database. I tried it and it is working fine. This query will work from SQL 2005 to all SQL Server versions.

Please try and share if it is helpful.

DECLARE @Search varchar(255)
SET @Search='SET @TypeID = 66'

SELECT DISTINCT AS Object_Name,o.type_desc
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

Tuesday, July 14, 2015

Step by Step - database partition SQL Server 2008 +

Data can be partitioned by two ways; one is dividing columns into multiple tables and second is dividing data into multiple filegroups. The data in partitioned tables is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables more manageable and scalable.

Partitioning large tables or indexes can have the following manageability and performance benefits.
·         You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection.
·         You can perform maintenance operations on one or more partitions more quickly. The operations are more efficient because they target only these data subsets, instead of the whole table.
·         You may improve query performance; the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.
·         When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.
·         In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.

Creating a partitioned table or index typically happens in four parts:
1.       Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.

2.       Create a partition function that maps the rows of a table into partitions based on the values of a specified column.

3.       Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.

4.       Create or modify a table and specify the partition scheme as the storage location.

5.       Insert sample data into SQLAudit table
6.       Partition wise count along with partition number can be returned from following query

7.       You can query partition wise data using following query.

8.       Partition information can be viewed using following query.

9.       Performance can be increased by adding index on Partition.

Monday, June 8, 2015

Step by Step setting up Database Mail and Creating alerts in SQL Server 2008 R2

Database Mail
Database Mail is designed for reliability, scalability, security, and supportability.

·         Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. You can use Database Mail without installing an Extended MAPI client on the computer that runs SQL Server.
·         Cluster support. Database Mail is cluster-aware and is fully supported on a cluster.
·         Multiple accounts: Each profile can contain multiple failover accounts. You can configure different profiles with different accounts to distribute e-mail across multiple e-mail servers.
·         SQL server database engine define rules for attachment, type and size limitation.
·         Database Mail keeps copies of messages and attachments sent in the msdb database. You can easily audit Database Mail usage and review the retained messages.

Database mail architecture

Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. You can view mail log using following commands on msdb database

FROM sysmail_mailitems

FROM sysmail_log

Setting up Database Mail
Database Mail can be setup using SQL Server management studio and scripts. I am doing this using management studio

In database instance , go to the management and Configure database Mail.

If database mail is disabled, you will get message to enable it before configuration. You need to have Admin rights on msdb database.

During data mail wizard, You will setup your Profile name and SMTP setup. You can use Windows Auth and password to verify email account.

As a last step you can configure File size, retry attempts etc as shown in figure, After finishing all these steps, You will receive successful installation message.

To verify your configuration. You can send test email by right clicking on your profile name and press Send test email. You can Audit you mail history by using sysmail_log and review your profile setting using  sysmail_mailitems table in msdb.

Once you finish setting up Database Mail profile, To send SQL Job Notification or Alert you have to create new Operator.
Creating Operator
Create new operator using SQL Server management studio. 

You can setup schedule for your Operator by using Pager on Duty Scheduler 

Setup SQL Agent 
You have to enable and setup your Profile account along with SQL Agent to send email.

Press Include body of email in notification message will send message detail as email in case of error or alert. You can setup Fail-safe operator in case there is any problem in primary profile.

Once all setting has been completed. restart SQL Agent service to implement changes.

Creating Alert
Alert will send an email when TEMPDB database gets larger than 0 KB. 

Right Click Alerts and select New Alert

On the General tab properties are set to 

In Response Tab, You can run Job to accommodate Alert changing i.e Database shrinking job or you can generate email notification.

You can add error message in Alert body or Custom message can also be added

You can see last email time to verify either Alert is working fine. You can also view history using command given above in msdb.

Stop the Test
To stop the Alert from being sent every two minutes, you have two options disable the alert or provide a more appropriate size and response times.
To disable alert
  • Right click on Alert name and select Disable
Email SQL Job Status
You can attach operator to send email notification on the failure, successful or completion of any SQL Agent job. In following image you can see email notification as well as Windows event log is being written on different status of SQL Job.

I tried to cover all possible scenario's about Database Mail setup and attaching it to send email notification in different ways. Normally we find all this information in number of posts. Hopefully it will help.

Monday, March 16, 2015

Certification Update — SQL 2012 to SQL Server 2014

With the launch of SQL 2014 some new and powerful features introduced in this version. Some of them are
  • In-Memory OLTP
  • New Cardinality Estimator
  • Cluster shared volume
  • Delayed durability
  • SSD buffer pool extension
  • Resource Governor for I/O
  • Incremental statistics
  • Lock priority of online operations

and many more....

To help students stay current with upcoming release of SQL Server 2014, all MCSE-level SQL exams (464 through 467) will be updated on April 24, 2014 to cover solutions based on both SQL Server 2012 and 2014 products.  

The MCSA: SQL Server 2012 credential and associated exams (461/462/463) will remain in market without alterations. No MCSA: SQL Server 2014 credential will be released.

MCSE-level SQL exams (464 through 467) is updated to cover solutions based on both SQL Server 2012 and 2014 products.  

Details on the exam updates, in the form of side-by-side comparisons of exam objectives, are now available for review through the "content covering SQL Server 2014" link in the Skills Measured section of each Exam Detail web page:
Note While the exam numbers will not change, the product version (2012) will be removed from the titles of the exams. 

FAQs about upgrading 2014 SQL Server is as follows

SQL Server 2014 certifications will only be offered at the MCSE level. The new product features focus on performance tuning and high availability and are best suited for the MCSE level. As a result, the following updates are planned:
  • MCSE: Data Platform (464/465) and MCSE: Business Intelligence (466/467) exams will be appropriately updated with SQL Server 2014 topics. The exam numbers will remain the same.
  • Details on the original and revised exam objectives are available for review in the Skills measured section of each exam detail webpage.

Sunday, January 11, 2015

How to get Database engine ( SQL Server ) setting information

I checked this query against 2008 and above. You can view all sql server server information using following built in procedure.

Exec xp_msver

You can change setting by using advance setting option.

Index Name Internal_Value  Character_Value