System Stored Procedures Archives

Microsoft SQL Server

SQL Server recovery time

Post author:Tomas Lind
Post published:2015/10/27
Post category:Error log Failover Cluster System Stored Procedures
Post comments:0 Comments

SQL Server Recovery time is the time it takes for SQL Server to rollback or roll-forward transaction not yet persisted to the database after a service restart. Transactions that are uncommitted are rolled back, and transactions that are committed but not yet persisted are rolled forward.

Depending on the volume of transactions that needs to be handled during recovery, recovery may take a long time to perform. This affects the time it takes for a SQL Server restart. Another example when recovery times become a factor is when a failover is done in a SQL Server cluster.

So how can we know how long the recovery time will be?

(more…)

Microsoft SQL Server

SQL Agent job history gets deleted

Post author:Tomas Lind
Post published:2014/10/23
Post category:Monitoring SQL Agent System Stored Procedures
Post comments:0 Comments

Ever wondered why the SQL Agent job execution history gets deleted at regular intervals?

(more…)

Microsoft SQL Server

T-SQL function SERVERPROPERTY

Post author:Tomas Lind
Post published:2014/05/06
Post category:Configuration System Stored Procedures
Post comments:0 Comments

The T-SQL function SERVERPROPERTY returns a lot of properties about the current SQL Server instance. The list of parameters it accepts can be found on Technet. On the top of the page, choose the version of SQL Server you are running.

(more…)

Microsoft SQL Server

Map users to logins

Post author:Tomas Lind
Post published:2014/03/11
Post category:Permissions Security System Stored Procedures
Post comments:0 Comments

“Why can’t I login after the database restore?”

As a DBA, this is one of the questions I get most.

(more…)

Microsoft SQL Server

sp_cycle_errorlog

Post author:Tomas Lind
Post published:2014/03/06
Post category:Configuration System Stored Procedures
Post comments:3 Comments

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…)

Microsoft SQL Server

Logging changes to sys.configurations

Post author:Tomas Lind
Post published:2014/03/04
Post category:Configuration Dynamic Management Objects System Stored Procedures
Post comments:0 Comments

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…)

Microsoft SQL Server

sp_MSforeachdb

Post author:Tomas Lind
Post published:2014/01/30
Post category:System Stored Procedures
Post comments:1 Comment

sp_MSforeachdb is an undocumented system stored procedure that lets you iterate through all databases on an instance and execute a T-SQL statement. This can be a very handy tool for the DBA. For instance, let us create a script that lists all tables and their size on a server instance.

(more…)

Microsoft SQL Server

An alternative to xp_fixeddrives

Post author:Tomas Lind
Post published:2014/01/28
Post category:Dynamic Management Objects System Stored Procedures
Post comments:15 Comments

Want to know the disk space available on your database server from T-SQL? This can be done in many ways, here I'll present two of them. The first way is…

Changing the database owner

Post author:Tomas Lind
Post published:2014/01/23
Post category:Configuration Permissions System Stored Procedures
Post comments:1 Comment

When a database is created, the logged in user is set as the database owner as default. Often, this user account is a physical person and he or she may get more permissions in the database than intended. Normally, a system account should be used as database owner. For instance sa.

Note that when you change the database owner the old account may get less permissions than before, not being the owner (who can do anything) anymore. So check how and where the old account is used before changing.

(more…)

xp_logininfo

Post author:Tomas Lind
Post published:2013/12/20
Post category:System Stored Procedures
Post comments:2 Comments

This system stored procedure returns information on windows accounts or groups in SQL Server. One of the more useful features of this procedure is that it’ll show any group membership that allows access to the account.

(more…)