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.
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]
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.
The SQL Server service keeps its own error logs outside of SQL Server in a number of text files. The location of the SQL Server error log is specified in a startup parameter for the SQL Server service.
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.
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.
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.
