NULL and aggregates
An aggregate in SQL Server will ignore NULL values. Check out this simple demonstration to learn how aggregates work with NULL values.
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.
Microsoft SQL Server
Map users to logins
“Why can’t I login after the database restore?”
As a DBA, this is one of the questions I get most.
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.
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.
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.
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.
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.
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.
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: