There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Category: Database Admin
Articles related to the administrating SQL Server 2008 R2.
Searching Stored SQL – Part 2
There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…
Searching Stored SQL – Part 1
Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…
Full-Text Search – Part 1
Today, I want to go over why text searching for patterns is an expensive query operation. I am going to create a [WILD LIFE] database that contains a table called [ANIMALS]. A identity column called [ID] will be populated by the system automatically and a [NAME] column will be loaded with 445 animals names that I grabbed from WIKIPEDIA. The idea is to search for animals that have a root word in common. The snippet below creates the database and table. The full SQL script including insert statements is at…
Server Settings – Part 3
Tonight, I will be talking about default database paths that a DBA can change either manually or programatically. The automated process is to use undocumented extended system stored procedures to read from and write to the registry. The manual process is to use the Server Properties dialog box in SQL Server Management Studio (SSMS). I am going to start with the database settings page that contains the default data file and log file directories. This setting determines where the data files (*.mdf, *.ndf) and log files (*.ldf) are stored. Just…
Server Settings – Part 2
Some of the settings that are listed in Server Properties dialog box in SQL Server Management Studio (SSMS) are stored in the local registry. These registry settings can be read from and written to by TSQL code. Thus, scripts can be created to standardize settings for all servers that you manage. The purpose of today’s talk is talk about security page of the Server Properties dialog box and how a DBA can change the settings. All of the settings can be changed by selecting the correct radial button, check box…
Server Settings – Part 1
The simplicity of installing SQL Server out of the box is very appealing to the accidental database administrator (DBA). Run the install program, select default options, and you have a running database. The ease of installation is a double edge sword. You now have a database management server (DBMS) that might be using settings that are not optimal for your company. The purpose of today’s talk is to introduce settings that a DBA can change to make the DBMS perform better. I will be showing where information can be found…
Testing Alerts
Having set up system and performance alerting, it is now time to test those darn alerts to make sure they work! Alerting can notify a operator via email, paging, or a net send messages. Now adays, most people have a smart phone, not an old fashion pager. Therefore, I usually create a Active Directory group that has both the regular company email of the DBA group and their cell phone eqivalents which are usually the phone number @ provider name dot com. Today, I am going to talk about two…
Performance Alerts
Today, I am going to talk about setting up performance alerts so that you can notified when a server has issues. Basically, there are two types of performance alerts, SQL Server and WMI. I am going to use the sp_add_category system procedure to define a peformance category.
|
1 2 3 4 5 6 7 8 9 10 |
-- Add a custom category for alerts EXEC msdb.dbo.sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'[Alert By Performance]' ; GO -- Show Alert categoryes EXEC msdb.dbo.sp_help_category @class=N'ALERT' GO |
The sp_add_alert stored procedure is used to create the alert and the sp_add_notification stored procedure is used to associate the alert with an operator. This process is used below to show examples of both SQL and WMI performance alerts. The first type…
System Alerts
This article assumes that you configured both database mail and system operators. Please see my previous articles if you have not done these tasks already. Today, I am going to talk about setting up alerts for system error messages that might occur with your SQL server database. Since alerts have been around for a long time, most of this information is backward compatible with older software versions. The database engine defines the importance of an error messages with a severity level. Low importance errors are defined by levels 0 to…