Read more about the article SQL Server recovery time
Microsoft SQL Server

SQL Server recovery time

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

Continue ReadingSQL Server recovery time
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

Changing the database owner

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

Continue ReadingChanging the database owner