On a recent slow Friday afternoon I was practicing my Origami skills with approved expense reports and consolidating hundreds of queries that I'd collected over the past 10 years as a DBA. I was suddenly struck with what I thought was a good idea: why not put my most-used DBA queries on one of my Red-Gate thumb drives so as to have them always available? Because of the imminent deadline of a presentation for an upcoming SQL Server Users group meeting, I figured I would then share these queries with other SQL souls. Excitedly, I set about creating the DBA script thumb for the presentation. I dubbed it the "Green Thumb", subtitled the "Top 1000 Most Used Queries for the DBA".

I was left with a slight uneasiness. Could it be that the task of trudging through 1000 queries in would be impossible to do in a one-hour presentation? Was it possible to describe one every four seconds? No. So I narrowed it down to five. Of the five that I will present here, some can be demonstrated in less than one minute, others may take a bit longer. These are queries that DBAs may stumble upon in their course of their careers, and find that they are useful enough to pass on to others. So here are the 1000 queries, now reduced by 995, that I either:

* Developed
* Enhanced
* Use daily
* All of the above.

These five queries were designed to:
* Instantly find sizes of all databases on your servers (for the inquisitive server administrator)
* List the last good database backups
* Query a trace file with SQL
* Read error logs with T-SQL – not the error log viewer
* GO more than once


Database Sizes
The first query is one that I created from scratch, when I needed to monitor the space on a server, whether transaction logs or data files. This query will display all the information you immediately need and works for all versions of SQL from 2000 onwards. I have used this query to quickly ascertain which log files have grown to the point of eating most of the disk drive, and so must be reined in. This event is easily avoided, and so is very embarrassing to the DBA when it happens; but it does happen more often that I would like to admit.

The query uses the xp_fixeddrives and sp_MSForEachDB stored procedures to populate and query temp tables. Listing 1 shows the full query (prettified with RedGate Refactor's "Layout SQL" function):

Listing 1
Set NoCount On
--Check to see the temp table exists
IF EXISTS ( SELECT  Name
            FROM    tempdb..sysobjects
            Where   name like '#HoldforEachDB%' )
--If So Drop it
    DROP TABLE #HoldforEachDB_size
--Recreate it
CREATE TABLE #HoldforEachDB_size
    (
      [DatabaseName] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS
                                    NOT NULL,
      [Size] [decimal] NOT NULL,
      [Name] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS
                            NOT NULL,
      [Filename] [nvarchar](90) COLLATE SQL_Latin1_General_CP1_CI_AS
                                NOT NULL,

    )
ON  [PRIMARY]

IF EXISTS ( SELECT  name
            FROM    tempdb..sysobjects
            Where   name like '#fixed_drives%' )
--If So Drop it
    DROP TABLE #fixed_drives
--Recreate it
CREATE TABLE #fixed_drives
    (
      [Drive] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS
                        NOT NULL,
      [MBFree] [decimal] NOT NULL
    )
ON  [PRIMARY]
--Insert rows from sp_MSForEachDB into temp table
INSERT  INTO #HoldforEachDB_size
        EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName,
 Case When [?]..sysfiles.size * 8 / 1024 = 0 
 Then 1 Else [?]..sysfiles.size * 8 / 1024 End
AS size,[?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'
--Select all rows from temp table 
(the temp table will auto delete when the connection is gone.

INSERT  INTO #fixed_drives
        EXEC xp_fixeddrives


Select  @@Servername
print '' ;
Select  rtrim(Cast(DatabaseName as varchar(75))) as DatabaseName,
        Drive,
        Filename,
        Cast(Size as int) AS Size,
        Cast(MBFree as varchar(10)) as MB_Free
from    #HoldforEachDB_size
INNER JOIN #fixed_drives ON LEFT(#HoldforEachDB_size.Filename, 1)
 = #fixed_drives.Drive
GROUP BY DatabaseName,
        Drive,
        MBFree,
        Filename,
        Cast(Size as int)
ORDER BY Drive,
        Size Desc
print '' ;
Select  Drive as [Total Data Space Used |],
        Cast(Sum(Size) as varchar(10)) as [Total Size],
        Cast(MBFree as varchar(10)) as MB_Free
from    #HoldforEachDB_size
        INNER JOIN #fixed_drives ON LEFT(#HoldforEachDB_size.Filename, 1)
         = #fixed_drives.Drive
Group by Drive,
        MBFree
print '' ;
Select  count(Distinct rtrim(Cast(DatabaseName as varchar(75))))
 as Database_Count
from    #HoldforEachDB_size 

Figure 1 shows the output of the query, with the multiple result sets that provide an at-a-glance view of how much free space is available on the disk, and how much space is taken by all of the SQL database files on each drive:

As you can see, there is no worry of running out of space on my laptop SQL Server, where this was run. However, if you discover (as I have, on occasion) that the MB_Free field is in double digits, then it is time to take some action.

Last Good Database Backups
Whether you use a third-party backup tool to backup your databases and log files, or you choose to use native SQL backups, the MSDB database that stores backup history can provide a wealth of information to you, as the DBA responsible for your company's data. As the DBA, you may be managing over 100 SQL instances using standard backup scripts, so it is tempting to assume (or pray) that all backups are working successfully. Of course, if a backup fails you'd typically receive an alert via e-mail. However, Development and QA systems are not as stringently monitored as production.

I quite often use the simple query shown in Listing 2 to interrogate the MSDB database, searching for databases that have not been backed up, either in the past x number of days or ever. Jaws drop when the latter shows up, but you would be surprised what you may find. This query will not only inform you what has and has not been backed up, but will also tell you what type of backup has been performed, full database (D), transaction log(L) or differential(I).

Listing 2
SELECT  sd.name,
        bs.TYPE,
        bs.database_name,
        max(bs.backup_start_date) as last_backup
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim
        (bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf
         ON bs.media_set_id = bmf.media_set_id
Group by sd.name,
        bs.TYPE,
        bs.database_name
Order by sd.name,last_backup

The output of the query from Listing 2 can be seen in Figure 2, where there are obviously several databases that have not been backed up, as indicated by a NULL value in the last_backup column. This is not something a DBA would want to see, especially when master and msdb are included in the list of databases not backed up:

Full article...