Read more about the article SQL Server Default Trace
Microsoft SQL Server

SQL Server Default Trace

The default setting in SQL Server 2005 and later is to keep a trace running all the time that captures basic information about the instance. This is called the Default Trace. The created trace files can be viewed in Performance Monitor or by using T-SQL and they can be very useful in some troubleshooting scenarios.


Continue ReadingSQL Server Default Trace
Read more about the article Default collation different from instance default
Microsoft SQL Server

Default collation different from instance default

If you have databases with another default collation than the instance default, you may get something like the following error:

[red_box]Msg 468, Level 16, State 9, Line 30 Cannot resolve the collation conflict between “Finnish_Swedish_CI_AS” and “Finnish_Swedish_100_CI_AS” in the equal to operation.[/red_box]


Continue ReadingDefault collation different from instance default
Read more about the article sp_cycle_errorlog
Microsoft SQL Server


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.


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.


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.


Continue ReadingChanging the database owner