January 17, 2012 by Leave a Comment

image

Filed under Uncategorized

January 17, 2012 by Leave a Comment

image

Filed under Uncategorized

Latest Backups with TSQL

January 10, 2012 by Leave a Comment

After rebooting a SQL Server, for whatever reason, it is always good to confirm that the system is running good.  There are many things that I check, one of which is to confirm that backups are running.

You can always go to the server browse to the various directories holding backups, and check on the latest files.  This can be difficult for servers with many databases.

Here is my quick check to confirm which backups are running on SQL Server. The query below gives you the 20 most recent backups that have been run.

SELECT TOP 20 database_name,
              type,
              backup_start_date,
              backup_finish_date,
              compressed_backup_size,
              backup_size
FROM   msdb.dbo.backupset
ORDER  BY backup_set_id DESC; 

In order to get the exact results you are looking for it may require you to add a where clause to filter on specific databases, or to expand the number of results in the TOP statement.

I hope that you find this as useful as I do.

Filed under DBA, Recovery

Update available for the SQL Server Health Reports

January 8, 2012 by Leave a Comment

I am now getting back into things after the holidays.  Today I fixed a number of small layout related bugs in the SQL Server health reports.  The updated reports are now available for download.

 

Here is a sample of the Fragmented Indexes report.

Filed under Server Health

2 Sessions Accepted for SQL Saturday 114 in Vancouver

January 8, 2012 by Leave a Comment

I just checked the schedule and 2 of the sessions that I submitted for SQL Saturday 114 in Vancouver BC on March 17th have been accepted.

The first one is on Unleashing Common Table Expressions in SQL Server, and the second session is Using SSRS reports to analyze SQL Server health.

This should be fun!

Filed under Classes, CTE, Server Health, SQL Saturday

Multiple CTE’s in a single Query

December 14, 2011 by Leave a Comment

When working on a recent presentation on CTE’s I realized that I didn’t have a good generic example of using multiple CTE’s in a single query. Here is what I came up with as a multiple CTE example that is database agnostic.


-- First CTE
;WITH fnames (name) AS
(SELECT 'John' UNION SELECT 'Mary' UNION SELECT 'Bill'),

– Second CTE
minitials (initial) AS
(SELECT ‘A’ UNION SELECT ‘B’ UNION SELECT ‘C’),

– Third CTE
lnames (name) AS
(SELECT ‘Anderson’ UNION SELECT ‘Hanson’ UNION SELECT ‘Jones’)

– Using all three
SELECT f.name, m.initial, l.name
FROM fnames f
CROSS JOIN lnames AS l
CROSS JOIN minitials m;

Filed under CTE, SQL Saturday

← Older posts