Read more about the article Introduction to Automate BPA Server
Automate BPA Server

Introduction to Automate BPA Server

If you are using SQL Agent together with SSIS and if you have a batch job solution with hundreds of interdependent SQL Agent steps spread across dozens of servers, you may have run in to some maintenance problems.

For complex batch solutions, a Business Process Automation software can be of great help. In this blog post I’ll give a brief introduction to Automate BPA Server from Network Automation.

(more…)

Continue ReadingIntroduction to Automate BPA Server
Read more about the article sp_cycle_errorlog
Microsoft SQL Server

sp_cycle_errorlog

Sometimes the error log files in SQL Server get too large to view and manage in a practical way in Management Studio. SQL Server keeps a number of log files (between 6 and 99), and a new error log file is created each time the SQL Server Service is restarted.

If you want to cycle the logs more frequently without a service restart, you can do so with the system stored procedure sp_cycle_errorlog. This way, you can get smaller and more log files.

(more…)

Continue Readingsp_cycle_errorlog
Read more about the article Logging changes to sys.configurations
Microsoft SQL Server

Logging changes to sys.configurations

Some server wide settings can have major negative impact if they are changed in your SQL Server environment. So as a DBA it is a good practice to monitor changes to these setting, and to raise alerts when they are changed. To do this, the system view sys.configurations can be monitored for changes.

This blog post will demonstrate a way to monitor SQL Server configuration settings. The solution will also give you get the complete history for the different settings in sys.configurations.

Note that database mail needs to be configured for the current solution to work properly.

(more…)

Continue ReadingLogging changes to sys.configurations
Read more about the article Deny updates on a table but allow inserts
Microsoft SQL Server

Deny updates on a table but allow inserts

Sometimes it is nessesary to deny updates on a table, but allow inserts. This is the case when the history must never be changed, for instance in a table with accounting data.

This can be done in two different ways. The first method is to use permissions on the table and make sure everyone is denied update permissions on the table. The other method is to use an “instead of update” trigger.

Here I’ll show both methods.

(more…)

Continue ReadingDeny updates on a table but allow inserts
Read more about the article List SQL server roles by users
Microsoft SQL Server

List SQL server roles by users

SQL Server roles can be used to give users server wide privileges. The server roles are sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin. The mapping between users and server roles can be found in the system view sys.server_role_members.

Here I’ll show a SQL script that lists all users and their server roles.

(more…)

Continue ReadingList SQL server roles by users
Read more about the article A replacement for syslogins
Microsoft SQL Server

A replacement for syslogins

The system table sys.syslogins was marked as deprecated in SQL Server 2005, and is included up until 2012 for backwards compability only.

So what is the replacement of sys.syslogins? As it turns out, there is no exact match in SQL Server 2005 and later. Instead there are various system views that can be combined to gather the same information.

I created a SQL statement to combine those system views into a single view, matching the old sys.syslogins.

(more…)

Continue ReadingA replacement for syslogins
Read more about the article Copy logins between SQL Server instances
Microsoft SQL Server

Copy logins between SQL Server instances

If you are using SQL Server log shipping (or mirroring), you probably want to copy the logins from the primary server to the secondary.

When databases are log shipped, the users go along with their respective databases. But those users are connected to a login on the primary server. Those logins are not copied automatically to the secondary. Users without connection to a login are called orphaned users.

This has the effect that users may not be able to login on the secondary in case of a failover.

One solutions to this problem is to transfer logins manually. That may be the best solution if there are very few logins to manage. But if there are more than just a few logins, an automatic solution is required.

There are many methods available to handle this. In this blog post I’ll combine a couple of the best solutions available, and add a few features that I found missing.

(more…)

Continue ReadingCopy logins between SQL Server instances
Read more about the article Tables larger than tempdb
Microsoft SQL Server

Tables larger than tempdb

If your database contains very large tables, querying or managing those tables may fill tempdb. When tempdb is full, an error is raised and the transaction is rolled back. If this will happen or not depends on how large your tempdb can get. Even if the size of tempdb isn’t limited by turning off autogrowth or by setting a max size, the disk space available is the upper limit for how big tempdb can get. Check out this blog post to see how to calculate the maximum possible size of tempdb.

Large tables can be a latent problem, since there may be no problems at all until someone, for instance, tries to sort the table in a query. SQL Server uses tempdb (as the name implies) as a temporary workspace for data, and if SQL Server can’t handle all the data in memory tempdb is used.

To avoid this problem you need to know if there are very large tables that won’t fit in tempdb when at its maximum size. Here is a script to look for tables larger than tempdb:

(more…)

Continue ReadingTables larger than tempdb