Database Administration | Data Engineer Techs

2021-05-052021-05-05

How to set up mail notification for SQL Server job failures

When we set up SQL Server Job, its is a very common requirement ‘need to get email notification once Job is failed’. It’s not difficult, just need few clicks in SQL Server Management Studio to implement this requirement.

Overview Steps

Configure Database Mail.
Set up SQL Server Agent Alert System .
Create an Operator.
Set up SQL Server job notifications

Configuring Database Mail

Open SQL Server Management Studio (SSMS), expand Management and right-click on Database Mail and then click Configure Database Mail.

Select “Set up Database Mail by performing the following tasks” and Click Next.

Click Yes and then click Next if mail hasn’t been configured yet.

Give the New Profile a name and then click Add.

Fill in the correct SMTP information as below

click Next

Check the option to make the new profile Public. If the profile isn’t set to Public

Review the System Parameters , please keep the default settings if your are not sure how to adjust them. Click Next and Finish the Mail set up.

You should see that each Action was completed and has a Status of Success. Click Close.

Set up SQL Server Agent Alert System

Right-click SQL Server Agent and select Properties.

Click on Alert System

· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.

· Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…

Type in the recipient email address in the E-mail name and click OK.

Set up SQL Server job notifications

Expand SQL Server Agent and click on Jobs. Find the job you want to change, right click it and select Properties.

Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operator we just created and change the job to email When the job fails. Click OK.

2020-04-252020-12-01

Configure File & Folder Permissions for SQL Server

Since SQL Server 2012 permissions are assigned to the per-service SID for each of its instance. This blog will show how to configure file & folder permission for the SQL Server Database Engine. SQL Server must have permission of the Windows file system to access the file folder where database files are stored. The default SQL server data and backup folder permission are configured during setup. But if your database files saved in a different folder, you might need to follow below steps to setup SQL Server permission to that folder

Configure folder permission for SQL Server instance

Using Windows Explorer, navigate to the file system location where the database files are stored. Right-click the file system folder, and then click Properties.
On the Security tab, click Edit, and then Add.
In the Select Users, Computer, Service Account, or Groups dialog box, click Locations, at the top of the location list, select your computer name, and then click OK.
In the Enter the object names to select box, type the name of instance. If you database use default name then use NT SERVICE\MSSQLSERVER, or for named instance please use NT SERVICE\MSSQL$InstanceName
Click Check Names to validate the entry
In the Group or user names box, select the name added at step 4, and then in the Permissions for box, select the Allow check box for Full control.
Click Apply, and then click OK twice to exit.

2020-02-192020-02-19

SQL SERVER BCP UTF8 import and export, support UTF8 after SQL SERVER 2014SP2

The bulk copy program utility (bcp) is a very powerful bulk copy tool between database and flat file. Normally used for large number of data import and export. But if you sql server version is older then 2014 SP2, unlucklly you cannot process UTF8 data even if there is a code page parameter -C , but it doesn’t support UTF8. Refer to Microsoft community document

code_page

Specific code page number; for example, 850.

** Important ** SQL Server does not support code page 65001 (UTF-8 encoding).

If you want to process UTF8, then must need to upgrade SQL Server to version 2014 SP2 or later. Once you installed the upgrade, then you can use BCP to import or export UTF8 as below:

To import UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users in “D:\test.csv” -c -C 65001

To export UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users out ” D:\test.csv ” -c -C 65001

2020-02-132020-03-19

SSIS: Reduce SSISDB database size

The SSISDB database is automatically created when we create SSIS catalog. All the SSIS projects, packages and execution history are saved in this database. So if you have a package that run every few minutes you could see the database size is growing exponentially. I deployed one project at AWS EC2 server with limited disk storage size (100GB SSD), the package was automatically executed every 3 minutes by SQL Agent Job. After one weeks later, I noticed the disk size almost full. After investigation, I notice the size of SSISDB is very big already. How can I control SSISDB to a minimum size?

SSIS created a maintenance job already when we create the catalog, it’s SIS Server Operation Records Maintenance.

Looking the job steps, you will notice it calls a stored procedure internal.cleanup_server_retention_window, in this stored procedure you will see it is using catalog.catalog_properties to do database maintain.

Now is solution is easy, I just need to update RETENTION_WINDOW property of catalog_properties .

exec catalog.configure_catalog RETENTION_WINDOW , 1

After this change, then just waiting for SSIS Maintenance Job to do database cleaning task.

If you don’t want to do this via T-SQL. You can right click on your catalog then click Property to change settings from Catalog Properties window:

After you reduced Database size, you maybe will notice the log file size is going up now. For the log file, if it’s not very critical system, you can used DBCC SHRINKFILE to shrink files directly:

USE SSISDB;
GO
–use below script to find ssisdb log file id
–SELECT file_id, name FROM sys.database_files;
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE SSISDB
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);
GO
— Reset the database recovery model.
ALTER DATABASE SSISDB
SET RECOVERY FULL;
GO