Memory usage per Microsoft SQL database

The following script can be used to determine the memory usage per database:

 

 

Delen via: Facebooktwitterpinterestlinkedinmail

Script to backup all database Microsoft SQL node

The following script can be used to backup all databases on an Microsoft SQL node:

 

Delen via: Facebooktwitterpinterestlinkedinmail

Shrink all database logfiles Microsoft SQL server

Recently I ran into a problem with a specific backup vendor. Microsoft SQL was not noticed when backups occur. This caused the SQL logfiles not to auto-shrink after a backup. The logfiles of all the databases on the cluster were rapidly growing. The database were in full logging mode because the databases are mirrored between SQL nodes with SQL Availability Groups.

To make the shrinking an easy job I have written an SQL script that generates an SQL script to shrink all logfiles on a specific SQL node. The following scripts generates an output script of all shrink actions:

The script generates a script that performs these 5 actions on every logfile on the cluster:

  • Backup database
  • Backup logfile
  • Shrink logfile
  • Backup logfile
  • Shrink logfile

The output of the script can also partially be used to perform scripted shrinking on a specific database.

Delen via: Facebooktwitterpinterestlinkedinmail

Activate mail in Microsoft SQL

Recently I have written some SQL scripts. When these scripts are ran I would like to know if the steps in the script are completed. For this I want to send an email from an SQL script.

To get this working a SQL mail account needs to be created and the stored procedure for email needs to be activated.

The first step is to create an SQL mail account. This can be done with the following script:

The next step is to activate the email stored procedure. This van be done with the following script:

Mail has been configured. A test email can be send with the following script:

 

 

 

 

 

 

Delen via: Facebooktwitterpinterestlinkedinmail