Development Matters Blog

I had a chance to work with a 3rd-Party tool that dumps data into SQL Server.  An issue came up because it only works with SQL Server 2008. In order to support a customer case, I had to have that data in SQL Server 2005 (it doesn’t happen often to take you too much time to migrate data from a newer version of SQL Server to the older one).

Luckily, SQL Server 2008 has a great feature. The feature is Generating Scripts. It makes it possibile for users to generate script for creation of any scope of database objects very easily.

The feature is accessed by right clicking the database then choosing “Tasks >> Generate Scripts”. The wizard is very intuitive and enables the creation of script compatible even with SQL Server 2000 – which generates the whole database, with all tables, data in tables, indexes, triggers, stored procedures, collations, and anything else that comes to mind.

In my case, I was not able to do this through Access, Excel or any other way. Without this feature I, the case would be stuck.

A while ago I was assisting a client who ran into a problem with extreme ‘tempdb’ database growth – over 80 Gb. The only remedy for this is to determine what exactly is causing this growth and to deal with the cause. This may sometimes be difficult, but a couple of neat scripts can help this. However, before that, we should capture the moment of database growth, to help us identify the problem.

Email alert functionality is probably one of the most underused functionalities SQL server has. It is easy to use and configure:

1. Make sure that SQL Server Agent is up and running.

2. Under SQL Server Agent, right click on Alerts and select New Alert…

3. In the Name field, type in something like tempdb over 40GB. Make sure that Enable is checked.

4. From Type, select SQL Server performance condition alert.

5. Under Object drop down list, select atabases.

6. Under Counter, select Data File(s) Size(s) (KB).

7. Under Instance, select tempdb.

8. Under Alert if counter, select rises above.

9. In Value field, type in 41943040 (this is for 40 GB, change as needed).

10. Click Response page (upper left).

11. Make sure to have only Notify Operators checked.

12. Click New Operator button.

13. In Name field, type in Send an email.

14. In the Email name field, type in the email address you want to send the email to.

15. Click OK. Window closes.

16. Operator is displayed in the operator list. Select it and check E-mail field.

17. Click Options page.

18. Configure Delay between responses.

19. Click OK button.

20. Repeat steps 3-19 to create another alert for Log File(s) Size(s) (KB) – see step 6.

Then, make sure the SQL Agent has its mail profile defined:

1. Right click on SQL Server Agent, select Properties.

2. Select Alert System.

3. Make sure that Enable mail profile is checked and that Mail system is SQLMail and the Mail profile is defined. You can test this by clicking Test button. If there aren’t any profiles defined, you have to configure them.

SQLMail uses one of the existing account profiles. If not already configured, maybe you would like to create a separate account just for this, configure Outlook on that machine and then configure SQL Server Agent. This is the account FROM which alert emails will be sent from.

You can play around with this in order to use different objects and counters, but the principle is the same.

Note: You can configure SQL Server Agent to auto start (it is not auto started by default). This service is required for sending emails.