In this blog post, we shall take a look at how to access Activity Monitor in the last 3 Major Versions of SQL Server, SQL Server 2000, 2005 and 2008 (of course it has not changed in SQL 2008 R2).
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.
— Bru Medishetty
Monitoring the size of Transaction Log files is one of those important tasks for a SQL Server Database Administrator. I monitor regularly in order to ensure that my database log files do not grow tremendously in size and potentially run out of space. The script in this article will give the list of Databases and their Transaction Log files size in MB in the descending order.
Script used in this blog…
SELECT INSTANCE_NAME AS [DATABASE],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC
You may also take a look at one of my previous blogs related to transaction logs. Find Transaction Log Space Used
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.
One of the important tasks a SQL Server DBA does is database backup. While maintaining the backups of the important database is important, the need to monitor that the exists a valid Full backups for the databases is equally important. This blog will try to explain how to find the list of databases which do not have a Full backup in the recent few days. (8 days in this example). You can obviously change the value in DATEADD function to suit your choice for # of days to look for.
The code used in this example is found at the end of this blog.
First, I run the script to find the list of databases which do not have a full backup. The very first statement you see in the script (SELECT @@VERSION ) is not required, I included it my example to show the SQL Server Version on which I am executing these scripts. The reason for including it is, a slight change in the script. In SQL Server 2005 and 2008, I am querying sys.databases and in SQL Server 2000 it does not exist, so I would need to use master..sysdatabases.
The query returns list of the databases on my instance which do not have a full backup in the last 8 days.
I then perform a backup of Adventureworks database, which is one of the database missing a full backup. the picture below is the script I run to create a backup of Adventureworks DB.
The next thing I do is run the same script that was run at the beginning to find out the databases without full backups and this time displays only the database Snapshot_Source.
The following is the script that works for SQL Server 2000. Note there is no database missing full backups, indicating I have at least full backup of all databases on that server.. Based on the importance of the data in the databases, other backups types have to be configured and monitored. However, a full database backup would always be the base of any database recovery process and should be as recent as possible to avoid delays in recovery process.
Script used in this example…
SELECT @@VERSION AS [SQL SERVER VERSION] SELECT NAME FROM sys.databases WHERE NAME != 'TEMPDB' AND NAME NOT IN ( SELECT DISTINCT database_name FROM msdb..backupset WHERE backup_start_date > DATEADD(DAY,-8,GETDATE()) AND TYPE = 'D' )
–Bru Medishetty