In this post, I discuss 3 methods (of varying complexity) to carry out some disk space monitoring with SQL server. This can be ad-hoc or you can build it into SQL agent jobs to run on a schedule. Now the first thing I’d like to say is this isn’t a replacement for good monitoring software, it’s more of a compliment to monitoring software. It’s a double check in case something goes wrong with your monitoring. Having some handy scripts is useful as well, because it lets you find out info on an ad-hoc basis, rather than relying on alerts or checking manually. [Read more…]
The EXCEPT operator in T-SQL
The except operator in t-sql
When I first read about the except operator in t-sql and saw how simple it was to use, it knew I would be using it a lot. And I have.
I first came across it when studying for the 70-461 exam (Querying Microsoft SQL Server 2012). I keep trying to tell my colleagues about it but it seems they already know. So the only logical thing to do was write a blog post about it.
What does it do?
The EXCEPT OPERATOR compares two sets of data and performs set difference. In other words, it returns all of the rows from query 1 EXCEPT when an identical row is returned from query 2. This makes EXCEPT a quick and easy way to compare two sets of data without using 3rd party tools or more complicated JOIN syntax.
What is the syntax?
How to write a PIVOT query in T-SQL
In this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is
The central point, pin, or shaft on which a mechanism turns or oscillates.
So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.
Create the table and data which we will use in out PIVOT query
Lets create a database table which records the size of different tables within a database over time.
[sql]
CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)
[/sql]
Style over substance
When it comes to managing emails, I can easily receive about 600 a day. This is probably far too many (although that’s a subject for another day). The challenge with receiving so many emails, especially so many system generated errors and warnings is that they can fail to stand out. Important information can be missed. A lack of formatting can also make it difficult to understand what the problem is, if any.
Here is an example of a boring system generated message.
To get around this, I always try to use some pretty HTML in my output just to help with the appearance and to make the message easier to read. I don’t go overboard, just a header tag, something like <h4> is usually enough (unless you want it really big) and of course I like to include my output in a table, usually with a splash of colour in the table header.
An alternative to sp_msforeachdb
It’s well known that the sp_msforeachdb stored procedure is undocumented however it can be handy. The trouble with the proc is that it is unreliable. What’s more, it could be dropped from future SQL Server releases causing all your automated scripts to fail.
This little script shows you a more reliable solution and saves you from relying on an undocumented procedure.
USE Master
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SELECT name,0 as completed
INTO #tmp
FROM sys.databases
WHERE name NOT IN('tempdb','Training2012') -- A list of DBs you don't wish to include
DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)
WHILE EXISTS(SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN
SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname
END
Let’s break up what the script actually does:
- DROP temp table if it already exists
- [sql]IF OBJECT_ID(‘tempdb..#tmp’) IS NOT NULL DROP TABLE #tmp[/sql]
- Select into a temporary table with two columns. (Name and Completed). We set completed=0 for each row created.
- [sql]SELECT name,0 as completed INTO #tmp FROM sys.databases[/sql]
- The WHERE clause is optional
- [sql]WHERE name NOT IN(‘tempdb’,’Training2012′)[/sql]
- Create a @dbname variable (for each DB name) and a @cmd variable (for building up the T-SQL command).
- [sql]DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)[/sql]
- [sql]DECLARE @dbname sysname
- The LOOP (Create and execute a new statement for each DB name and exit the loop once all DBs have been handled).
- [sql]WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN
SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) — You can ORDER BY name if you care about the order
SET @cmd = ‘BACKUP DATABASE ‘+@dbname+’ TO DISK = ”D:\Backup\’+@dbname+’.Bak”’
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname
END[/sql]
- [sql]WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
This approach is simple and reliable and it avoids the use of undocumented stored procedures and cursors. The example above demonstrates a native backup but this command can be anything you like.