Tips
Automate Monitoring SQL Server Error Logs with Email Alerts
As a SQL Server DBA, monitoring SQL Server error logs is part of my daily routine. Manually reviewing the error logs on each server is a time consuming process, especially when working on other issues. I wanted to review the error logs when there are entries in them which need my attention. How can I accomplish this task in an automated manner by receiving emails? Read this tip to learn how to become a more proactive DBA.
Automate SQL Server Monitoring with Email Alerts
All DBA's perform some sort of monitoring of their SQL Server database instances as no one likes to find out from a user that there is an issue with the database. In an ideal world we would notice any issues before they occur and have a fix in place so the users of our systems don't even know there was a problem. This tip will extend a previous tip I wrote, which gave you queries that you could run manually to check on your system, by outlining a low tech way to monitor your SQL Server database instances without having to login yourself and run any T-SQL manually.
Collect SQL Server database usage for free space and trending
I have over 80 instances hosting 800+ databases that I support on my own. The only way I can keep track of all these databases is to centralize my monitoring so I have a single point of focus on my environment. I can not spend my entire day (and night) hopping from SQL instance to SQL instance in SQL Server Management Studio. I am not the only DBA out there in this situation. So overworked brothers and sisters, what are we to do? We cook up our own monitoring solution that best fits our environments. This tip is the first in a series presenting the building blocks of such a solution. My first tip will focus on monitoring database files and their free space on a single SQL instance.
Determining space used for each table in a SQL Server database
One thing that is often handy to know is how much space your tables are using within your database. It is helpful to know the number of rows, the data space used as well as the index space used. There are several ways that you can get this information, by reading the system tables, using the built-in reports in SQL Server 2005 or by using the Taskpad view in SQL Server 2000, but what is a simple way of retrieving this data without using the GUI or without having to write a query to gather this data from the system tables?
Getting operating system content into SQL Server
Every once in awhile you may have the need to access some data from the Windows operating system or from the contents of a file. The need may be to find out what files exist in a directory, to read the contents of a log file into SQL Server or for some other purpose. Whatever the need, what is a simple way of pulling this data into SQL Server?
How to setup SQL Server alerts and email operator notifications
Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. So how do you send an alert to an operator?
Install SQL Server 2012 Performance Dashboard Reports
SQL Server 2012 Performance Dashboard Reports are Custom Reports which Microsoft has developed to quickly give an overview of the overall performance of SQL Server 2012 instance. Keep in mind that in order to use the Performance Dashboard Reports you don’t need to install SQL Server Reporting Services. In this tip we will take a look at how to install and use these built-in SQL Server Reporting Services Reports.
Listing SQL Server Object Dependencies
When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?
Methods to determine the status of a SQL Server database
I think that just about all aspects of the SQL Server Management Studio can be exposed by using T-SQL instead of having to use the GUI. I have been trying to write some routines to determine whether a database is online as well as to check some other information about the status of a database. What options are there to get status information for a database without using the GUI?
Monitor, Start and Stop SQL Server services using xp_servicecontrol
As you know SQL Server runs using a bunch of different services in order for it to operate. These can be monitored, started and stopped using SQL Server Configuration Manager or Windows Services, but this is not always the most convenient way of checking the services. Is there any way this can be done within SQL Server Management Studio? In this tip we take a look at a undocumented stored procedure that will allow you to do this.
Monitoring SQL Server database transaction log space
Keeping track of your transaction log usage is key to understanding how your database transaction logs are being used as well as keeping track of how much space the transaction logs are using. Depending on the recovery model that is set within your database and also the backup jobs that you run, the size of your transaction log can vary quite a bit. So what is the best way to keep track of the transaction log usage?
Problems running sp_helpdb in SQL Server
One useful system stored procedure is sp_helpdb. This stored procedure returns information about all of your databases on your server such as the size, owner, when it was created and the database settings. One issue that you may run into is that the stored procedure does not provide data, but an error occurs instead. The error that you receive is "Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails."
Reading the SQL Server log files using TSQL
One of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript. Are there any other easy ways to search and find errors in the error log files?
Script to determine free space to support shrinking SQL Server database files
As a DBA for several servers, I get a lot of calls about disk space. The first thing I do is look for data files I can shrink to free up some space on the disk. Some of the servers I support contain several databases and it can take a while to figure out which data file will give me the most immediate relief.
SQL Server Command Line Tools To Manage Your Server
There are several useful commands and functions that are available in SQL Server, but not knowing what they are or where to find more information about them is sometimes a problem. Having these commands at your fingertips is very helpful when trying to solve a problem or for just doing general analysis on your database instances.
SQL Server Monitoring Checklist
Every good SQL Server DBA goes through some list of checks when they get to the office in the morning to make sure all of their systems are running smoothly. This tip will highlight some of the more important items that every DBA should be monitoring either manually or by using some form of scheduled scripting.
Storing Windows Performance Counter Values in SQL Server
I capture performance counter values to a CSV file using TYPEPERF.EXE as described in the tip How To Collect Performance Data With TYPEPERF.EXE. I would like to import this information into a SQL Server database. Can you provide an example of how to do this? Read this tip to find out.
What does my SQL Server data look like over the wire?
How can I see what the outgoing and incoming traffic to my SQL Server instance looks like? Are there any tools that can help with this? Check out this tip to learn more.
Top 10
Install SQL Server 2012 Performance Dashboard Reports
SQL Server 2012 Performance Dashboard Reports are Custom Reports which Microsoft has developed to quickly give an overview of the overall performance of SQL Server 2012 instance. Keep in mind that in order to use the Performance Dashboard Reports you don’t need to install SQL Server Reporting Services. In this tip we will take a look at how to install and use these built-in SQL Server Reporting Services Reports.
Reading the SQL Server log files using TSQL
One of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript. Are there any other easy ways to search and find errors in the error log files?
How to setup SQL Server alerts and email operator notifications
Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. So how do you send an alert to an operator?
SQL Server Monitoring Checklist
Every good SQL Server DBA goes through some list of checks when they get to the office in the morning to make sure all of their systems are running smoothly. This tip will highlight some of the more important items that every DBA should be monitoring either manually or by using some form of scheduled scripting.
Listing SQL Server Object Dependencies
When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?
Automate SQL Server Monitoring with Email Alerts
All DBA's perform some sort of monitoring of their SQL Server database instances as no one likes to find out from a user that there is an issue with the database. In an ideal world we would notice any issues before they occur and have a fix in place so the users of our systems don't even know there was a problem. This tip will extend a previous tip I wrote, which gave you queries that you could run manually to check on your system, by outlining a low tech way to monitor your SQL Server database instances without having to login yourself and run any T-SQL manually.
Monitoring SQL Server database transaction log space
Keeping track of your transaction log usage is key to understanding how your database transaction logs are being used as well as keeping track of how much space the transaction logs are using. Depending on the recovery model that is set within your database and also the backup jobs that you run, the size of your transaction log can vary quite a bit. So what is the best way to keep track of the transaction log usage?
SQL Server Command Line Tools To Manage Your Server
There are several useful commands and functions that are available in SQL Server, but not knowing what they are or where to find more information about them is sometimes a problem. Having these commands at your fingertips is very helpful when trying to solve a problem or for just doing general analysis on your database instances.
Determining space used for each table in a SQL Server database
One thing that is often handy to know is how much space your tables are using within your database. It is helpful to know the number of rows, the data space used as well as the index space used. There are several ways that you can get this information, by reading the system tables, using the built-in reports in SQL Server 2005 or by using the Taskpad view in SQL Server 2000, but what is a simple way of retrieving this data without using the GUI or without having to write a query to gather this data from the system tables?
Collect SQL Server database usage for free space and trending
I have over 80 instances hosting 800+ databases that I support on my own. The only way I can keep track of all these databases is to centralize my monitoring so I have a single point of focus on my environment. I can not spend my entire day (and night) hopping from SQL instance to SQL instance in SQL Server Management Studio. I am not the only DBA out there in this situation. So overworked brothers and sisters, what are we to do? We cook up our own monitoring solution that best fits our environments. This tip is the first in a series presenting the building blocks of such a solution. My first tip will focus on monitoring database files and their free space on a single SQL instance.
Last 10
Install SQL Server 2012 Performance Dashboard Reports
SQL Server 2012 Performance Dashboard Reports are Custom Reports which Microsoft has developed to quickly give an overview of the overall performance of SQL Server 2012 instance. Keep in mind that in order to use the Performance Dashboard Reports you don’t need to install SQL Server Reporting Services. In this tip we will take a look at how to install and use these built-in SQL Server Reporting Services Reports.
Automate SQL Server Monitoring with Email Alerts
All DBA's perform some sort of monitoring of their SQL Server database instances as no one likes to find out from a user that there is an issue with the database. In an ideal world we would notice any issues before they occur and have a fix in place so the users of our systems don't even know there was a problem. This tip will extend a previous tip I wrote, which gave you queries that you could run manually to check on your system, by outlining a low tech way to monitor your SQL Server database instances without having to login yourself and run any T-SQL manually.
SQL Server Monitoring Checklist
Every good SQL Server DBA goes through some list of checks when they get to the office in the morning to make sure all of their systems are running smoothly. This tip will highlight some of the more important items that every DBA should be monitoring either manually or by using some form of scheduled scripting.
What does my SQL Server data look like over the wire?
How can I see what the outgoing and incoming traffic to my SQL Server instance looks like? Are there any tools that can help with this? Check out this tip to learn more.
Storing Windows Performance Counter Values in SQL Server
I capture performance counter values to a CSV file using TYPEPERF.EXE as described in the tip How To Collect Performance Data With TYPEPERF.EXE. I would like to import this information into a SQL Server database. Can you provide an example of how to do this? Read this tip to find out.
Automate Monitoring SQL Server Error Logs with Email Alerts
As a SQL Server DBA, monitoring SQL Server error logs is part of my daily routine. Manually reviewing the error logs on each server is a time consuming process, especially when working on other issues. I wanted to review the error logs when there are entries in them which need my attention. How can I accomplish this task in an automated manner by receiving emails? Read this tip to learn how to become a more proactive DBA.
Monitor, Start and Stop SQL Server services using xp_servicecontrol
As you know SQL Server runs using a bunch of different services in order for it to operate. These can be monitored, started and stopped using SQL Server Configuration Manager or Windows Services, but this is not always the most convenient way of checking the services. Is there any way this can be done within SQL Server Management Studio? In this tip we take a look at a undocumented stored procedure that will allow you to do this.
How to setup SQL Server alerts and email operator notifications
Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. So how do you send an alert to an operator?
Script to determine free space to support shrinking SQL Server database files
As a DBA for several servers, I get a lot of calls about disk space. The first thing I do is look for data files I can shrink to free up some space on the disk. Some of the servers I support contain several databases and it can take a while to figure out which data file will give me the most immediate relief.
Methods to determine the status of a SQL Server database
I think that just about all aspects of the SQL Server Management Studio can be exposed by using T-SQL instead of having to use the GUI. I have been trying to write some routines to determine whether a database is online as well as to check some other information about the status of a database. What options are there to get status information for a database without using the GUI?
