Menu Bar

Tuesday, December 27, 2011

Examining what's causing blocking


"I prepared Explosive Ru ..." **BOOM** - Vaarsuvius, Order of the Stick webcomic
We have had some fairly basic monitoring for blocking that lasts for an extended period of time running on our primary servers for some time now. In the last few days, we've started getting some alerts in the wee hours of the morning, every night, pretty much like clockwork.

It doesn't last long, but it's routine - and at 1 AM each morning, it's annoying.

I wasn't quite sure how to track this down, so I threw out a quick tweet to #sqlhelp and @SQLSoldier suggested capturing some wait information.

First, I created a table that mirrored sys.dm_os_waiting_tasks, with an extra column up front for the Date/Time. Next, I created the following little stored procedure.

create procedure [dbo].[GetWaitingTasks]
as
begin
insert into prod_waiting_tasks 
select getdate(), * from prod.sys.dm_os_waiting_tasks
end;

I scheduled that to run, every 1 minute in SQL Server Agent, between 12:30 AM and 1:30 AM.

When I queried this table the next morning, I saw a bunch of these messages:

pagelock fileid=3 pageid=8969941 dbid=11 id=lock1b2c6500 mode=IX associatedObjectId=72057594959429632

Checking into sys.databases, I confirmed that dbid=11 was my prod database.
Checking into sys.master_files, I confirmed that the file was file containing most of the data files in the darabase.

I also had a page number in the datafile. The problem was how to work out what object it belonged to.

That's where an undocumented feature came to my rescue. Paul Randall blogged about DBCC PAGE here.

dbcc traceon (3604)
dbcc page (11, 3, 8969941, printopt=1) 

On of the pieces of output from this was the object id.

Follow that up with:

select * from sys.objects where object_id = 738101670

Once I had run that and had an object that was the cause of my nightly blocking, I was able to backtrack through the job history tables that the application provides to work out where my locking was coming from.

Tuesday, December 13, 2011

T-SQL Tuesday #025 - Tips and Tricks


"Let me explain ... no there is too much, let me sum up" - Inigo Montoya, The Princess Bride
T-SQL TuesdayWhen I created this blog, I made a promise to myself that I would attempt to update it regularly. The last month has been very busy with some tight deadlines and I haven't been able to keep that promise. I looked at the calendar today and saw that it was the second Tuesday of the month and thought "Ack! It's T-SQL Tuesday!" - so I'm forcing myself to spend a little time to throw something together. A complete lack of time to prepare something is a shame, but there's two things I'd like to share.

Allen White (blog|twitter) is hosting this month's event and the topic du jour is "Sharing your Tricks". The theme is to share some of the things that make your job easier.

Batter up! (However, owing to the shortage of time I talked about above, I'm going to have to cheat and recycle a previous post or two because they are a couple of my favorite tricks).

One of the things that I was used to, as an Oracle DBA on Unix for years before adding a SQL Server cap to the many other hats I wear was being able to use Unix shell scripting to parse my alert logs and the like, and also script out information like free space remaining in my tablespaces and what not.

Not being able to get this information out of SQL Server bugged me. There were ways of course, but I wasn't aware of them since I got thrown into the deep end of the SQL Server pool very suddenly a few years back.

When I attended SQL Saturday #94 here in Salt Lake City back in September, I attended Chris Shaw's session on his Utility Database and immediately got some return on investment. I sat down for a few hours and expanded upon it. One of the things my customers had recently asked was an estimate on growth of their databases - how much data was being added on a monthly basis so there could be some growth planning. I took what Chris started and expanded on it to suit my needs. The end result was a monthly report showing data file growth in a month (physical footprint on disk) as well as data growth (size of data in each file group) in a month and the average growth per day.

It's all described in this post in detail.

The other thing I needed to work out how to do was to extract information out of SQL Server via a Windows CMD script. One of the applications I help maintain has a very active batch system - the users can request to run reports out of the online application and feed parameters to a request record. The batch report executes, picks up these parameters and runs the report. Sometimes, at the end of the report / update process there's something else that needs to be done.

I found myself needing to be able to pick up a Unique Identifier from the operating system that identified the job, query the database for the parameters, return one of the parameters to the OS and act upon it (one example was to query the parameters to find an email address and then email a particular piece of output to that person).

So, I ended up writing a Windows CMD script to pass in the UUID, assign the output to another environment variable and be able to work on that. The details of that trick are in this post. (For the SQL Server folks reading this, ignore the second half of the article where I go into Unix / Oracle specifics) :)

Apologies for the lack of "new content" for this one, but I find the above two items have helped me a lot and were worthy contenders for this month's T-SQL Tuesday.

Friday, November 18, 2011

Flattening Your Data - How to Make Multiple Rows into Columns


"Mercy is the mark of a great man ... <stab> ...Guess I'm just a good one ... <stab> ... Well, I'm alright" - Captain Malcolm Reynolds, Firefly - 'Shindig'.

I recent had to extract some data from my database. The general format of the tables was like so:

Supplier Table:

supplier_code     char(6)
supplier_name     char(40)
supplier address  char(40)
sup_status        char(1)
etc


Colloquial Name Table:

supplier_code     char(6)
colloquial_name   char(40)


Email Address Table:

supplier_code     char(6)
sup_email_address char(40)

The base requirement was to output one supplier per line with the base information on each supplier from the first table and from 0 through 6 colloquial names for the supplier, depending on how many were present. There was also an optional email address. Finally, there was a status code on the table and I had to include every supplier with a status of "3" or less.

I'll be the first to admit that I do not fully understand the depth of the OVER / PARTITION BY syntax but I get the gist of it. I ended up needing to get some help on this one but the end query looked like this:

select a.supplier_code, a.supplier_name, a.supplier_address, 
       d.sup_email_addr, g.colloq_1, g.colloq_2, g.colloq_3, 
       g.colloq_4, g.colloq_5, g.colloq_6
FROM SUPPLIER a
LEFT OUTER JOIN SUP_EMAIL D on a.supplier_code = d.supplier_code
LEFT OUTER JOIN 
(
select a.supplier_code, 
 MAX(CASE WHEN ClqOrder = 1 THEN Colloquial_name ELSE ' ' END) AS colloq_1,
 MAX(CASE WHEN ClqOrder = 2 THEN Colloquial_name ELSE ' ' END) AS colloq_2,
 MAX(CASE WHEN ClqOrder = 3 THEN Colloquial_name ELSE ' ' END) AS colloq_3,
 MAX(CASE WHEN ClqOrder = 4 THEN Colloquial_name ELSE ' ' END) AS colloq_4,
 MAX(CASE WHEN ClqOrder = 5 THEN Colloquial_name ELSE ' ' END) AS colloq_5,
 MAX(CASE WHEN ClqOrder = 6 THEN Colloquial_name ELSE ' ' END) AS colloq_6
FROM (
SELECT s.*, B.Colloquial_name, B.ClqOrder
FROM SUPPLIER S
INNER JOIN
(SELECT supplier_code, Colloquial_Name, 
        ROW_NUMBER() OVER (PARTITION BY supplier_code ORDER BY Colloquial_name) AS ClqOrder
   FROM sup_colloq) B
on S.SUPPLIER_CODE = B.SUPPLIER_CODE) A
GROUP by a.Supplier_CODE
) G on a.supplier_CODE = g.supplier_CODE
where a.sup_status <= '3'

So this all works, but how does it break down ?

For simplicity's sake, and the limited width of the blog page, we're going to shorten all those char(40) columns to something much shorter. Let's start with making the tables and populating some base data.

SQL> create table supplier
  2  (supplier_code char(2),
  3   supplier_name char(5),
  4   supplier_address char(5),
  5   sup_status char(1));

Table created.

SQL> create table sup_colloq
  2  (supplier_code char(2),
  3   colloquial_name char(5));

Table created.

SQL> create table sup_email
  2  (supplier_code char(2),
  3   sup_email_addr char(5));

Table created.

SQL> insert into supplier values ('01','Sup#1','Addr1','1');

1 row created.

SQL> insert into supplier values ('02','Sup#2','Addr2','1');

1 row created.

SQL> insert into supplier values ('03','Sup#3','Addr3','1');

1 row created.

SQL> insert into sup_email values ('01','a@a');

1 row created.

SQL> insert into sup_email values ('02','b@b');

1 row created.

SQL> insert into sup_colloq values('01','Cql01');

1 row created.

SQL> insert into sup_colloq values('01','Cql02');

1 row created.

SQL> insert into sup_colloq values('01','Cql03');

1 row created.

SQL> insert into sup_colloq values('01','Cql04');

1 row created.

SQL> insert into sup_colloq values('01','Cql05');

1 row created.

SQL> insert into sup_colloq values('01','Cql06');

1 row created.

SQL> insert into sup_colloq values('03','Cql07');

1 row created.

SQL>  insert into sup_colloq values('03','Cql08');

1 row created.

Running the big query gives us the expected output:

SQL> select a.supplier_code, a.supplier_name, a.supplier_address, 
  2         d.sup_email_addr, g.colloq_1, g.colloq_2, g.colloq_3, 
  3         g.colloq_4, g.colloq_5, g.colloq_6
  4  FROM SUPPLIER a
  5  LEFT OUTER JOIN SUP_EMAIL D on a.supplier_code = d.supplier_code
  6  LEFT OUTER JOIN 
  7  (
  8  select a.supplier_code, 
  9   MAX(CASE WHEN ClqOrder = 1 THEN Colloquial_name ELSE ' ' END) AS colloq_1,
 10   MAX(CASE WHEN ClqOrder = 2 THEN Colloquial_name ELSE ' ' END) AS colloq_2,
 11   MAX(CASE WHEN ClqOrder = 3 THEN Colloquial_name ELSE ' ' END) AS colloq_3,
 12   MAX(CASE WHEN ClqOrder = 4 THEN Colloquial_name ELSE ' ' END) AS colloq_4,
 13   MAX(CASE WHEN ClqOrder = 5 THEN Colloquial_name ELSE ' ' END) AS colloq_5,
 14   MAX(CASE WHEN ClqOrder = 6 THEN Colloquial_name ELSE ' ' END) AS colloq_6
 15  FROM (
 16  SELECT s.*, B.Colloquial_name, B.ClqOrder
 17  FROM SUPPLIER S
 18  INNER JOIN
 19  (SELECT supplier_code, Colloquial_Name, 
 20          ROW_NUMBER() OVER (PARTITION BY supplier_code ORDER BY Colloquial_name) AS ClqOrder
 21     FROM sup_colloq) B
 22  on S.SUPPLIER_CODE = B.SUPPLIER_CODE) A
 23  GROUP by a.Supplier_CODE
 24  ) G on a.supplier_CODE = g.supplier_CODE
 25  where a.sup_status <= '3'
 26  
SQL> /

SU SUPPL SUPPL SUP_E COLLO COLLO COLLO COLLO COLLO COLLO
-- ----- ----- ----- ----- ----- ----- ----- ----- -----
01 Sup#1 Addr1 a@a   Cql01 Cql02 Cql03 Cql04 Cql05 Cql06
03 Sup#3 Addr3       Cql07 Cql08
02 Sup#2 Addr2 b@b

So now we see what it did, let's break it down. The first small select gets our colloquial names with row numbers in an inline table. These row numbers will help us later.

SQL> SELECT supplier_code, Colloquial_Name, 
  2          ROW_NUMBER() OVER (PARTITION BY supplier_code ORDER BY Colloquial_name) AS ClqOrder
  3     FROM sup_colloq;

SU COLLO   CLQORDER
-- ----- ----------
01 Cql01          1
01 Cql02          2
01 Cql03          3
01 Cql04          4
01 Cql05          5
01 Cql06          6
03 Cql07          1
03 Cql08          2

8 rows selected.

When we grab the next chunk of code, with the CASE / MAX, we can query on each of the ClqOrder columns and turn that into something a little more friendly to what we need:

SQL> select a.supplier_code, 
  2   MAX(CASE WHEN ClqOrder = 1 THEN Colloquial_name ELSE ' ' END) AS colloq_1,
  3   MAX(CASE WHEN ClqOrder = 2 THEN Colloquial_name ELSE ' ' END) AS colloq_2,
  4   MAX(CASE WHEN ClqOrder = 3 THEN Colloquial_name ELSE ' ' END) AS colloq_3,
  5   MAX(CASE WHEN ClqOrder = 4 THEN Colloquial_name ELSE ' ' END) AS colloq_4,
  6   MAX(CASE WHEN ClqOrder = 5 THEN Colloquial_name ELSE ' ' END) AS colloq_5,
  7   MAX(CASE WHEN ClqOrder = 6 THEN Colloquial_name ELSE ' ' END) AS colloq_6
  8  FROM (
  9  SELECT s.*, B.Colloquial_name, B.ClqOrder
 10  FROM SUPPLIER S
 11  INNER JOIN
 12  (SELECT supplier_code, Colloquial_Name, 
 13          ROW_NUMBER() OVER (PARTITION BY supplier_code ORDER BY Colloquial_name) AS ClqOrder
 14     FROM sup_colloq) B
 15  on S.SUPPLIER_CODE = B.SUPPLIER_CODE) A
 16  GROUP by a.Supplier_CODE;

SU COLLO COLLO COLLO COLLO COLLO COLLO
-- ----- ----- ----- ----- ----- -----
01 Cql01 Cql02 Cql03 Cql04 Cql05 Cql06
03 Cql07 Cql08

Finally, adding in the outer joins as show at the top, we pick up the grand combination of everything. This was definitely something I've had to do before and thought it was worth preserving here. It falls squarely int the "for my edification" bucket that was talked about in the first post :)

Monday, November 7, 2011

T-SQL Tuesday #024: Prox 'n' Funx


"It doesn't matter if you win or lose, as long as you look cool doing it." - Julio Scoundrél - Order of the Stick webcomic.
T-SQL TuesdayI only recently became aware of T-SQL Tuesday and considering my last post on Joins, if I had known about it, that post would have made a good submission for last month's T-SQL Tuesday. However, that aside, it's not last month, it's this month.

Brad Schulz (blog) is hosting this month's party and his topic of choice is "Prox 'n' Funx" (Procedures and Functions).

So here we go.

Anyone who spent any time learning anything about just about any kind of programming language (there are exceptions there too) should have learned early on about the usefulness of procedures and functions. The Wikipedia entry on this topic defines it as follows: "a subroutine (also called procedure, function, routine, method, or subprogram) is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code". This definition holds true in SQL Server - however instead of being a part of a larger program, they are stored in the database as executable pieces of code - and these can be called by any other T-SQL program in the database - indeed you can even call them across databases (let's face it, the majority of system procedures are stored in a system database).

With Programming 101 out of the way, I thought I'd write a little about some of the differences between Oracle and SQL Server in Procedure and Function definition for those people (like me), who have to work in both database engines. Since I recently had to port some PL/SQL code to T-SQL, it seems like a good time to write about it.

The differences aren't so much in how the procedure is defined as much as how you call it.

The basic construct for both languages is the same:

CREATE [or replace] PROCEDURE schema.procname(parameters)
AS

The schema part of the declaration is optional. In SQL Server it defaults to the default schema (usually dbo) and in Oracle it's the user name you're logged in as. (If you're not familiar with Oracle's security model you'll have to trust me, that's a post for another time and I won't go into it here). Oracle also allows the "OR REPLACE" syntax to overlay an existing stored procedure - as opposed to SQL Server where you need to drop an object before recreating it (or you can use "alter procedure" to replace the procedure so that permissions on the object are not lost).

Definition of the parameters varies only slightly.

For Oracle, list the parameter names and types, each declaration separated by a comma:

CREATE PROCEDURE myproc1 (param1 char(10), param2 integer, param3 date)
AS

The SQL Server syntax is almost exactly the same, except you need to prefix the parameter names with the "@" sign.

CREATE PROCEDURE myproc1 (@param1 char(10), @param2 int, @param3 datetime)
AS

The function definitions are also very close. The syntax for the returning of the function value differs only slightly. In Oracle it looks like this:

CREATE FUNCTION myfunc1 (param1 date) RETURN DATE
AS

The function definitions in SQL Server looks like this:

CREATE FUNCTION myfunc1 (@param1 datetime) RETURNS DATETIME
AS

The big difference between the two is how they are called. This was something that tricked me up the first time I tried converting code between the two. Both expect a comma separated parameter list, however, Oracle expects the parameters to be enclosed in parentheses, this SQL Server does not. What we come down to, is this for Oracle: The function definitions are also very close. The syntax for the returning of the function value differs only slightly. In Oracle it looks like this:

EXEC myproc1 (param1, param2, param3);

while in SQL Server it looks like this:

EXEC myproc1 @param1, @param2, @param3;

This all gets a little murkier when you realize that in SQL Server user functions are called differently. A user function has to be called using 2 part names (you have to call it as dbo.myfunc and not just myfunc) *and* parameters are passed in parentheses, not as strings passed after a procedure call. This is yet another "gotcha" for the cross-database person.

Example time. In SQL Server it looks like this.

create procedure dbo.test1 (@date1 datetime, @days1 int)
as
  declare @result1 datetime;
  select @result1 = dateadd(dd, @days1, @date1);
  print 'Input : ' + convert(varchar(10), @date1, 101);
  print 'Output: ' + convert(varchar(10), @result1, 101);
go

Command(s) completed successfully.

declare @indate1 datetime;
select @indate1=getdate();
exec dbo.test1 @indate1,2

Input : 11/07/2011
Output: 11/09/2011

And the same kind of thing in Oracle:

create or replace procedure test1 (date1 date, days1 integer)
as
  result1 date;
begin
  result1 := date1 + days1;
  dbms_output.put_line('Input : ' || to_char(date1, 'MM/DD/YYYY'));
  dbms_output.put_line('Output: ' || to_char(date1, 'MM/DD/YYYY'));
end;
/

Procedure created.

SQL> set serveroutput on
SQL> exec test1(sysdate, 2);

Input : 11/07/2011
Output: 11/09/2011

PL/SQL procedure successfully completed.

SQL> 

The result are the same, but there's the little syntactical tricks that are needed in order to make it work.

Finally, same treatment for similar functions. SQL Server first.

create function dbo.func1 (@date1 datetime, @days1 int) 
returns datetime
as
begin
  declare @result1 datetime;
  select @result1 = dateadd(dd, @days1, @date1);
  RETURN @result1
end
GO

Command(s) completed successfully.

declare @indate1 datetime;
declare @outdate1 datetime;
select @indate1=getdate();
select @outdate1=dbo.func1(@indate1, 3)
print 'Input : ' + convert(varchar(10), @indate1, 101);
print 'Output: ' + convert(varchar(10), @outdate1, 101);

Input : 11/07/2011
Output: 11/10/2011

As noted above, if you replace "dbo.func1" with "func1", you get this error message:

Msg 195, Level 15, State 10, Line 4
'func1' is not a recognized built-in function name.

And finally, for Oracle:

create or replace function func1 (date1 date, days1 integer)
return date
as
  result1 date;
begin
  result1 := date1 + days1;
  return result1;
end;
/
Function created.

SQL> select 'Input Date = '||sysdate||' / Output Date = '||func1(sysdate, 3)
  2      as Input_Output_Date from dual;

INPUT_OUTPUT_DATE
------------------------------------------------
Input Date = 07-NOV-11 / Output Date = 10-NOV-11

SQL> 

When working in two different databases, it's important to keep in mind the differences in syntax between them when working on whatever solution you are working on. Little things like a missing pair of parentheses, or missing @'s can cause you just enough irritation to make you want to pull out what in my case, is the very little hair left on your head. The bottom line, especially for people like me who walk both sides of the Oracle / SQL Server line is to make sure you understand the subtle differences in the syntax you're working with.

As an "end of post" bonus, just because we're talking about functions, there's something else I'd like to throw out there. Here's a post I wrote in September and how a compatibility setting can "break" a Dynamic Management Function - sys.dm_db_index_physical_stats is broken !?

Sunday, November 6, 2011

Addendum to "My database went down during a hot backup"


"This place gives me an uncomfortableness." - Jayne Cobb, Firefly - 'Safe'
In checking the archives of my blog, I noticed this posting from September.

There's another way to fix this and for completeness, thought I should post it

Once you've attempted to start the database, you'll get as far as "mounted' before the error message appears. If you can check your alert logs and take the affected tablespaces out of backup mode:

alter tablespace MYTS end backup;

all you'll need to do is to issue "ALTER DATABASE OPEN;" and the database will open.

Either method will work. I've had the opportunity to use both in the last couple of months.

Monday, October 31, 2011

Another way of looking at JOINs


"No one would surrender to the Dread Pirate Westley." - Westly, The Princess Bride
I recently had a call from a colleague asking me about how to get some information out of the database. Since the query was using an Oracle database, it was using the (+) outer join syntax on the predicate level which to me, has never been what you'd call intuitive, particularly when joining multiple tables together.

For those people not familiar with it, a simple left outer join would read as follows:

select a.col1, b.col2
from tab1 a, tab2 b
where a.col1 = b.col1(+)

The (+) syntax is used in the side of the predicate where NULL's would be expected in the event of an outer join. So this example would indicate a left outer join.

The query was more complicated than this simple example and I suggested using ANSI standard syntax to build the query instead.

In SQL Server, in order to perform an outer join, you need to use the ANSI standard syntax since it's the only way an outer join is supported. I suggested to my colleague that he learn it as well, for future benefit.

What is an outer join? An outer join is where you join two tables on a predicate and if the table being outer joined doesn't match, the row is returned anyway, but with NULL's in the result set in any columns from the joined table where a match was not found.

Going back to Elementary School math, think of a Venn Diagram as follows (We will call the intersection in the middle "C"):

An Inner Join is only going to return the data shown as "C" - those rows common to both "A" and "B" across the join predicate.

A Left Outer Join will show all rows from "A" as well as "C" (non matching data from "B" will return as NULL)
A Right Outer Join will show all rows from "B" as well as "C" (non matching data from "A" will return as NULL)

So, with that explained, on to the syntax. The Syntax of a join, when done using the ANSI standard syntax is:

SELECT <list of columns>
FROM <TABLE1>
[LEFT|RIGHT] [OUTER|INNER] JOIN <TABLE2>
ON <JOIN CONDITION>
WHERE <EXTRA PREDICATES>

So let's say we have a table with a purchase order header and a table with PO Items and we want a report of all of the PO's for a particular vendor. There are two ways we could right this code:

SELECT a.PO_NO, b.PO_ITEM_NO, b.QTY_ORDERED, b.UNIT_PRICE
FROM PO_HDR a, PO_ITEM b
WHERE a.PO_NO = b.PO_NO
AND a.VENDOR = 'ACME'
ORDER BY a.PO_NO, b.PO_ITEM_NO

The same query written using ANSI JOIN syntax would be like this:

SELECT a.PO_NO, b.PO_ITEM_NO, b.QTY_ORDERED, b.UNIT_PRICE
FROM PO_HDR a
JOIN PO_ITEM b
ON a.PO_NO = b.PO_NO
WHERE a.VENDOR = 'ACME'
ORDER BY a.PO_NO, b.PO_ITEM_NO

The two queries would generate the exact same output.

The above example would return only those records in the "C" subset on our Venn Diagram above.

Let's try something different. Let's say we have a table containing all Employees (one of the columns is the Department that the employee belongs to) and a table of all Departments. If you wanted to find all employees who had not yet been assigned a department (This example courtesy of Wikipedia):

SELECT a.EmployeeID, a.DepartmentID, b.DepartmentID, b.DepartmentName
FROM   employee a
LEFT OUTER JOIN department  b
ON a.DepartmentID = b.DepartmentID;

This would return a list of all employees and their departments. Any employee not assigned a department would show as NULL in columns 3 and 4. If the same query were executed as a Right Outer join, we'd get a list of Departments without Employees assigned and columns 1 and 2 would be null if no employees were found for any given department.

LEFT and RIGHT, in the case of an outer join, define which table of the join, the first (LEFT) or the second (RIGHT) should always return data. The other side returns the NULL in the case of something not matching.

The final part I want to talk about is the logical structure of building a query. If you ever find yourself writing a query that is joining multiple tables, I've sometimes found that making sure all the joins are correct can be burdensome at times.

I find that the easiest way to build a complex query is one table at a time. Write a simple select statement and build it up one table at a time. As you add in more tables, think of the whole query you currently have as the "table" you're joining your next table into, so you can consider you join conditions. This advice is probably more for those who are not deeply familiar with SQL, but I've found this invaluable when analyzing a complex query that is performing badly.

So we could start with a list of employees:

SELECT EmployeeID, EmployeeName
FROM EMPLOYEES

Then add in their departments, including employees not in a department at the moment

SELECT a.EmployeeID, a.DepartmentID, b.DepartmentID, b.DepartmentName
FROM   employee a
LEFT OUTER JOIN department  b
ON a.DepartmentID = b.DepartmentID;

Then add in the description of the pay group that each employee is in - only listing those that have a pay group

SELECT a.EmployeeID, a.DepartmentID, b.DepartmentID, b.DepartmentName, 
       a.PayGroup, c.PayGroupDesc
FROM   employee a
LEFT OUTER JOIN department  b
ON a.DepartmentID = b.DepartmentID
INNER JOIN paygroup c
ON a.PayGroup = c.PayGroup

And then finally add in their physical location description. As a final selection criteria, only those employees that are not terminated.

SELECT a.EmployeeID, a.DepartmentID, b.DepartmentID, b.DepartmentName, 
       a.PayGroup, c.PayGroupDesc, d.PhysicalLocDesc
FROM   employee a
LEFT OUTER JOIN department  b
ON a.DepartmentID = b.DepartmentID
INNER JOIN paygroup c
ON a.PayGroup = c.PayGroup
INNER JOIN physlocation d
ON a.PhysicalLoc = d.PhysicalLoc
WHERE a.terminated = 'N'

When looking at a complex query this way, it's easy to keep track of what your query is doing, rather than attempted to add every column you need to that select list right at the start and trying to build it all at once. The more tables you're trying to join together, the more likely you are to forget a join condition and end up with a nasty Cartesian join in there somewhere.

Tuesday, October 18, 2011

Expanding on Chris Shaw's Utility Database


"Well look at this. Appears we got here just in the nick of time. What does that make us?"
"Big damn heroes, Sir"
"Ain't. We. Just" - Mal and Zoe, Firefly - 'Safe'

When I went to SQL Saturday #94 here in Salt Lake City back in September, Chris Shaw (blog|twitter) did a presentation on a Utility Database - a database you as the DBA create to track certain things (disk free space, high use sprocs, a parse of the daily error log etc).

With the gracious permission of Mr. Shaw, I took what he started and modified it for my particular needs, and this posting is about what I've done and how I'm using it.

Chris presented the same topic at the PASS Summit 2011 in Seattle in October. After the Summit, Buck Woody (blog|twitter) tweeted a #sqlchallenge to blog about something you learned at the PASS Summit.

So, I'm cheating a little, since what I'm writing about, while presented at the Summit, I actually learned about a few weeks before.

The Utility Database is a DB you create. In it, there are a few stored procedures to collect some data daily and email it to you every day to alert you to certain things that may be happening in your system. It also keeps a historical log of these alerts. To quote from his slide deck on what this is: "A user defined, created database. Keeps information on your server about your server. Keeps information you want as long as you want it. Presents information the way you want to see it. This is your database. Acts as a repository for you the DBA".

I had been sending myself some emails along these lines every day. I hadn't thought about recording the data for historical purposes or reporting from it in order to predict disk usage trends, etc.

The Original scripts associated with this presentation did the following:

  • Created two tables, the Daily Check List table (for today's issues) and a historical table to keep anything not from today.
  • Gathered information on the local drives to record free space on each local drive on the server
  • Captured the size of each datafile in each database
  • Read the Error Log and parsed out certain messages that we don't want to see the records the rest
  • Queried Report Server Log to get information on number of reports executed
  • Queries the index physical stats DMO to report index fragmentation
  • Gather statistics about execution of stored procedures and the reads and writes and elapsed times on each
  • Calls a CLR Procedure to gather processor utilization information
  • Emails a summary to the DBA

He also went on to set up a basic report in Report Server to email a pretty formatted report to himself each morning with all the information from the Daily Check List.

This did some of what I wanted, didn't record all of what I wanted and was performing extra steps that I just didn't need.

I'd never be able to get a CLR Routine DLL past change management (code unseen from an unknown third party), so that was dropped. Index Maintenance is a scheduled task each month already, so I really didn't need that. The application I'm primarily responsible for doesn't use Report Server and has an extremely limited (single digit, if that) number of stored procedure calls. That also went.

The file information displayed also wasn't showing everything I wanted and I needed the output a little different. So I set out to make my version of this Utility Database.

First, a disclaimer. I'm relatively new to SQL Server and the work I've been doing in it has certainly not been in T-SQL - I'm a DBA, not a developer. I'm not necessarily doing things the most elegant way, but for once daily or once a month reporting, what I have below is working for me.

I've got all of this in one large script for my implementation, but I'll break it down and annotate it for the purposes of this article.

First step: Create the database.


-- This script defines the objects needed to track daily reporting
-- against all databases installed in this instance of SQL Server
--
-- Ideas in this script have been culled from a presentation by
-- Chris Shaw at SQL Saturday 94 in Salt Lake City (@SQLShaw on twitter,
-- http://chrisshaw.wordpress.com) and from a series of articles 
-- on SQLServerCentral.com called SQL Overview.  
-- The last article in the series is linked here and it
-- refers back to all of the others.
-- http://www.sqlservercentral.com/articles/Monitoring/69650/
--
-- The SQL Overview series gave lots of good information, but didn't keep
-- any of it for trending analysis.  Chris Shaw's presentation gave ideas 
-- on how to keep that in line.
-- 

-- First, create the database

CREATE DATABASE [UtilityDB] 
    ON  PRIMARY 
( NAME = N'UtilityDB', 
  FILENAME = N'G:\MSSQL\Data\UtilityDB.mdf' , 
  SIZE = 102400KB , 
  MAXSIZE = UNLIMITED, 
  FILEGROWTH = 102400KB )
    LOG ON 
( NAME = N'UtilityDB_log', 
  FILENAME = N'G:\MSSQL\Data\UtilityDB_log.ldf' , 
  SIZE = 51200KB , 
  MAXSIZE = 2048GB , 
  FILEGROWTH = 51200KB )
GO

Next step was to create the tables that I'd be using for this database. I decided to create clustered indexes on them purely for performance on queries down the road. Over time, if never archived, these tables may grow to a significant size and having a clustered index would hopefully assist in performance since most of the queries are date based.

First couple of tables are the Daily Check List and the Historical Check List tables and the tables for storing disk free space and file growth.


-- Create the tables used in this utility

USE [UtilityDB];

-- This Table contains the daily information emailed to the DBA.
-- It is cleared out first thing each morning and moved to the 
-- cumulative history table

CREATE TABLE [dbo].[DayCheckList](
 [InstanceName] [varchar](50) NOT NULL,
 [EventStatus] [varchar](50) NOT NULL,
 [EventDate] [datetime] NULL,
 [EVENT] [varchar](2000) NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX IX_DayCheckList
    ON DayCheckList(EventDate)
GO

-- This is the cumulative history table.  It contains the complete
-- history of events captured by this utility in the range of from
-- yesterday back until when data capture started.

CREATE TABLE [dbo].[HistoryCheckList](
 [InstanceName] [varchar](50) NOT NULL,
 [EventStatus] [varchar](50) NOT NULL,
 [EventDate] [datetime] NULL,
 [EVENT] [varchar](2000) NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX IX_HistoryCheckList
    ON HistoryCheckList(EventDate)
GO

-- This table holds a history of the daily size of your datafiles.

CREATE TABLE [dbo].[FileSizeGrowth](
    [InstanceName] [varchar](50) NOT NULL,
    [DBName] [varchar] (50) NOT NULL,
    [FileName] [varchar] (50) NOT NULL,
 [PhysicalName] [varchar](200) NULL,
 [SizeInMB] [int] NULL,
 [SizeInGB] [int] NULL,
 [ReadingDate] [datetime] 
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX IX_FileSizeGrowth
    ON FileSizeGrowth(ReadingDate)
GO

-- Not a huge fan of default values myself, but we can place this 
-- here just in case.

ALTER TABLE [dbo].[FileSizeGrowth] 
  ADD  DEFAULT (getdate()) FOR [ReadingDate]
GO

-- This is a work table used when populating the historical FileFreeSpace 
-- table.  It is truncated each day after calculating what is needed

CREATE TABLE [dbo].[FreeSpaceWork](
    [InstanceName] [varchar] (50),
    [DBName] [varchar] (50),
    [FileName] [varchar] (50),
 [PhysicalName] [varchar](200) NULL,
 [TotalSizeInMB] [int] NULL,
 [FreeSizeInMB] [int] NULL
) ON [PRIMARY]
GO

-- This table keeps the daily tracking of the percentage of free space
-- in the data files.  This data will be used in the monthly reporting
-- for size trending.

CREATE TABLE [dbo].[FileFreeSpace](
    [InstanceName] [varchar] (50),
    [DBName] [varchar] (50),
    [FileName] [varchar] (50),
 [PhysicalName] [varchar](200) NULL,
 [TotalSizeInMB] [int] NULL,
 [FreeSizeInMB] [int] NULL,
    [PctFreeSpace] [int],
 [ReadingDate] [datetime]
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX IX_FileFreeSpace
    ON FileFreeSpace(ReadingDate)
GO

-- Again, not a huge fan of default values myself, but we can place this 
-- here just in case.

ALTER TABLE [dbo].[FileFreeSpace] 
  ADD  DEFAULT (getdate()) FOR [ReadingDate]
GO

One of the things I added to my version of this was an indication in the daily email that a database hadn't been backed up in a certain number of days. For most user databases, one would expect that you'd want a backup at least every 24 hours - but maybe you're happy only backing up your system databases once a week. The database control table below lists, for each database you are going to report on, how far in the past (in days) the most recent backup can be before we get upset and alert about it.

Also, there may be databases on the server that you don't want reported. Maybe they are test or development or training databases and you don't particular care about them. The ExcludeFromReporting table just contains the database name of any database you never want to see reported. Then, I went an populated the seed data into these tables.


-- This table defines the maximum numbers of days than can elapse after
-- a database is backed up before it's reported as missing a backup.
-- For example, system databases may be backed up weekly, but others
-- are daily

CREATE TABLE [dbo].[DatabaseBackupControl](
 [DBName] [char](50) NOT NULL,
 [MaxDaysBetweenBackups] [int] NOT NULL
) ON [PRIMARY]

-- We want the data in this table to be unique, so put a unique key here.

CREATE UNIQUE CLUSTERED INDEX IX_DatabaseBackupControl 
    ON DatabaseBackupControl (DBName)
GO

-- This table defines any databases we don't want included in reporting,
-- such as test or development databases.

CREATE TABLE [dbo].[ExcludeFromReporting](
 [DBName] [char](50) NULL
) ON [PRIMARY]

-- We want the data in this table to be unique, so put a unique key here.

CREATE UNIQUE CLUSTERED INDEX IX_ExcludeFromReporting 
    ON ExcludeFromReporting (DBName)

-- Populate initial seed information into control tables.

-- Control settings for checking for missing backups

INSERT INTO DatabaseBackupControl VALUES ('myprod', 1)
INSERT INTO DatabaseBackupControl VALUES ('master', 7)
INSERT INTO DatabaseBackupControl VALUES ('model', 7)
INSERT INTO DatabaseBackupControl VALUES ('msdb', 7)
INSERT INTO DatabaseBackupControl VALUES ('monitoring', 7)
INSERT INTO DatabaseBackupControl VALUES ('ReportServer', 1)

-- Control settings for databases to exclude from checking

INSERT INTO ExcludeFromReporting VALUES ('mydev');
INSERT INTO ExcludeFromReporting VALUES ('mytst');
INSERT INTO ExcludeFromReporting VALUES ('mytrn');
INSERT INTO ExcludeFromReporting VALUES ('tempdb');
INSERT INTO ExcludeFromReporting VALUES ('LiteSpeedLocal');
INSERT INTO ExcludeFromReporting VALUES ('ReportServerTempDB');
INSERT INTO ExcludeFromReporting VALUES ('RSExecutionLog');

Moving along! The next thing I created was a view to make reporting a little easier. I also wanted my report to show me each day any SQL Server Agent jobs that had failed in the previous day.


-- In order to simplify some of the reporting, views may be 
-- created that are snapshots of system catalog tables or 
-- views from msdb or master.

-- This view summarizes any SQL Server Agent job that failed yesterday.
-- The view is set up so that it can just be inserted into the daily
-- notification table when the daily procedures runs.

CREATE VIEW [dbo].[YesterdayFailedJobs] as
SELECT @@ServerName as ServerName, 
       'Job Failed' as Event_Status, 
       getdate() as Event_Date, 
       cast(run_date as char(8)) + '/' + cast(run_time as char(6)) + 
            ' Job: '+name + ' failed with ' + message as EventMsg
FROM msdb..sysjobs a
JOIN (SELECT * from msdb..sysjobhistory where sql_severity <> 0) b
ON a.job_id = b.job_id
WHERE b.run_date = cast(convert(char(8), getdate()-1, 112) as int)

OK that defines all the tables and views. Next are the individual stored procedures that gather the various data aspects. First couple gather free space on local drives and the size of the data files.


-- This stored procedure gathers information about the free space on 
-- the drives attached to the server.

CREATE PROCEDURE [dbo].[GetDriveSpaceFree]
AS
DECLARE @driveSpace TABLE (drive CHAR(2), MBFree int)
INSERT INTO @driveSpace
EXEC sp_executesql N'xp_fixeddrives'
INSERT INTO UtilityDB..DayCheckList
 SELECT @@ServerName, 
           'Drive Space', 
           GETDATE(), 
          'Free space on ' + drive + ' is ' + 
          CONVERT (VARCHAR(20), MBFree/1024) + ' Gigs' 
 FROM @driveSpace
GO

-- This stored procedure gathers information about the growth of your 
-- data files.

CREATE Procedure [dbo].[GetFileSizeGrowth]
AS
insert into FileSizeGrowth
  Select @@ServerName, 
         db_name(database_id),
         name,
         physical_name,
         SizeInMB = (size * 8 / 1024),
         SizeInGB = (size * 8 / 1024 / 1024),
         GETDATE()
  from sys.master_files
GO

CREATE Procedure [dbo].[GetFileFreeSpace]
AS
declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
declare @yesterday datetime
declare @today datetime

set @command1 = 'use ?
    Select 
    @@ServerName, 
    ''?'',
    name,
 physical_name,
 TotalSizeInMB = size/128, 
 FreeSizeInMB = size/128.0 - 
             CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0
 from ?.sys.database_files'

Insert Into FreeSpaceWork (InstanceName, DBName, FileName, PhysicalName, 
      TotalSizeInMB, FreeSizeInMB)
 exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1;

Insert Into FileFreeSpace (InstanceName, DBName, FileName, PhysicalName, 
      TotalSizeInMB, FreeSizeInMB, PctFreeSpace, ReadingDate)
select InstanceName, 
       DBName,
       FileName, 
       PhysicalName, 
       TotalSizeInMB, 
       FreeSizeInMB, 
       FreeSizeInMB * 100 / CASE 
                            WHEN TotalSizeInMB = 0 
                            THEN 1 
                            ELSE TotalSizeinMB 
                            END,
       GETDATE()
from FreeSpaceWork
where DBName not in (select DBName from ExcludeFromReporting);

select @yesterday = dateadd(day, datediff(day, 0, getdate()-1), 0)
select @today = dateadd(day, datediff(day, 0, getdate()), 0)

insert into DayCheckList
select InstanceName, 'Free Space ('+DBName+')', 
       ReadingDate, 
       rtrim(ltrim(cast(PctFreeSpace as Char(3)))) + '% Freespace in ' + 
         PhysicalName
from FileFreeSpace
where ReadingDate between @yesterday and @today;

truncate table FreeSpaceWork;
GO

The next stored procedure reads the SQL Server error log and filters out things we don't want to see before recording the rest in the log. I'm also recording a startup for any database started in the last 7 days


-- This stored procedure reads the SQL Server Error Log and parses
-- out information we don't want to see and reports the rest.

Create Procedure GetFilteredErrorLog
AS
DECLARE @Errorlog TABLE (LogDate datetime, 
                         ProcessorInfo VARCHAR (100),
                         ErrorMSG VARCHAR(2000))

INSERT INTO @Errorlog
EXEC sp_executesql N'xp_readerrorlog'

Delete 
FROM @Errorlog 
WHERE ErrorMSG LIKE '%Log was backed up%'
   OR ErrorMSG LIKE '%Setting database option COMPATIBILITY_LEVEL%'
   OR ErrorMSG LIKE '%were backed up%'
   OR ErrorMSG LIKE '%DBCC TRACEON%'
   OR ErrorMSG LIKE '%without errors%'
   OR ErrorMSG LIKE '%\ERRORLOG%'
   OR ErrorMSG LIKE '%Attempting to cycle errorlog%'
   OR ErrorMSG LIKE '%Errorlog has been reinitialized.%' 
   OR ErrorMSG LIKE '%found 0 errors and repaired 0 errors.%'
   OR ErrorMSG LIKE '%without errors%'
   OR ErrorMSG LIKE '%This is an informational message%'
   OR ErrorMSG LIKE '%WARNING:%Failed to reserve contiguous memory%'
   OR ErrorMSG LIKE '%The error log has been reinitialized%'
   OR ErrorMSG LIKE '%Setting database option ANSI_WARNINGS%';
 
INSERT INTO UtilityDB..daychecklist
SELECT @@ServerName, 'Error Log', Logdate, SUBSTRING(ErrorMSG, 1, 2000) 
FROM @Errorlog 
WHERE LogDate > DATEADD(dd, -1, GETDATE()) 
 
INSERT INTO UtilityDB..daychecklist
SELECT @@ServerName, 'DB Restart',Logdate,ErrorMSG 
FROM @Errorlog 
WHERE ErrorMSG LIKE '%Starting up database%'
AND LogDate > DATEADD(dd, -7, GETDATE()) 
GO

Next: Let's check for failed SQL Server Agent jobs and backups outside of our tolerances


CREATE PROCEDURE [dbo].[CheckFailedJobs]
AS
declare @Count as int
select @Count=count(*) from YesterdayFailedJobs

if @Count > 0
Begin
  INSERT INTO daychecklist
  SELECT *
  FROM YesterdayFailedJobs
END
GO

-- This stored procedure checks recent backups against the control tables.
-- This query is a UNION of three selects, each checking different things.
-- If a database is found that has a backup and is not defined on the 
-- control table, it is reported (first select)
-- If a database is found where the last backup is outside the recovery 
-- window, it is reported (second select)
-- If a database is found that is not defined in either backup control 
-- table, it is reported (third select)

CREATE PROCEDURE CheckRecentBackups 
AS
insert into DayCheckList
select @@ServerName, 
       'Missing Backup', 
       getdate(), 
       a.database_name + ' last backed up ' + 
       convert(char(20), max(backup_finish_date), 100) + 
       ' and control record is not defined'
from (select * from msdb.dbo.backupset where type <> 'L') a
JOIN sys.databases b
ON a.database_name = b.name
LEFT OUTER JOIN DatabaseBackupControl c
ON a.database_name = c.DBName
where database_name not in (select DBName from ExcludeFromReporting)
and MaxDaysBetweenBackups is NULL
group by database_name, MaxDaysBetweenBackups
UNION
select @@ServerName, 
       'Missing Backup', 
       getdate(), 
       a.database_name+ ' last backed up '+ convert(char(20), 
       max(backup_finish_date), 100) + ' limit is ' + 
       cast(MaxDaysBetweenBackups as char(2)) + ' day(s)'
from (select * from msdb.dbo.backupset where type <> 'L') a
JOIN sys.databases b
ON a.database_name = b.name
LEFT OUTER JOIN DatabaseBackupControl c
ON a.database_name = c.DBName
where database_name not in (select DBName from ExcludeFromReporting)
group by database_name, MaxDaysBetweenBackups
having getdate() > 
    dateadd(day, MaxDaysBetweenBackups, max(backup_finish_date))
UNION
select @@ServerName, 
       'Missing Backup', 
       getdate(), 
       name + 
       ' not defined in either Control Table or Exclude table.   ' +
       'Please investigate.'
from sys.databases
where name not in (select DBName from ExcludeFromReporting)
and name not in (select DBName from DatabaseBackupControl)
GO

Almost there. This next procedure is the one that will query the daily error log, format it nicely into an HTML table and email it to you.


-- This stored procedure will take the contents of the Daily log table
-- and email it formatted as an HTML table to interested parties.

CREATE PROCEDURE [dbo].[SendDailyEmail] 
as
 DECLARE @Subject     varchar (100)
 SET @Subject='SQL Server - Daily Event Log'

declare @Count as int
select @Count=count(*) from DayCheckList

IF @Count > 0 
Begin

DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
        N'<table border="1">' +
    N'<tr><th>InstanceName</th>' +
    N'<th width="150">EventStatus</th>' +
    N'<th width="180">EventDate</th>' +
    N'<th>Event</th>' +
    N'</tr>' +
    CAST ( ( SELECT td=[InstanceName],''
      ,td=[EventStatus],''
      ,td=[EventDate],''
      ,td=[Event],''

  from DayCheckList
order by InstanceName, EventStatus
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'SQL Server Agent Mail Profile',
    @recipients = 'me@mydomain.com',
    @subject = @Subject,
    @body = @tableHTML,
    @body_format = 'HTML' ;
END
GO

Almost done. This Stored Procedure will be run daily via a SQL Server Agent job to generate the email


-- Finally, this is the stored procedure that ties it all together.
-- This procedures should be executed daily as a job

CREATE PROCEDURE [dbo].[DayCheck]
AS
------------------------Maintenance Work---------------------------------
INSERT INTO HistoryCheckList
 SELECT * FROM DayChecklist

IF @@error = 0
 BEGIN
  Delete UtilityDB..DayCheckList
 END
---------------------------Disk Space------------------------------------
EXECUTE GetDriveSpaceFree
EXECUTE GetFileSizeGrowth
EXECUTE GetFileFreeSpace
--------------------------Read Error Log---------------------------------
EXECUTE GetFilteredErrorLog
--------------------------Check Failed Jobs------------------------------
EXECUTE CheckFailedJobs
--------------------------Check Recent Backups---------------------------
EXECUTE CheckRecentBackups
--------------------------Send Daily Email-------------------------------
EXECUTE SendDailyEmail
GO

Finally, set up a SQL Server Agent job to run this last Stored Procedure (DayCheck) at some time that works for you.

So, collecting all this data is wonderful, but what about using some of the data file data to generate a report with some trending data for data growth etc. I created this query that I run on the first of each month to show me growth of files and growth of data within the data files.

Note the commented lines at the bottom of the query. There are all sorts of things you can do here. You can exclude the databases included in the ExcludeFromReporting table. You can ignore log files (assuming they have been named *.LDF). You could also only pick up those records where there was growth of some sort etc


Use UtilityDB
go
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'MonthlyReport')
    DROP TABLE MonthlyReport;

DECLARE @Today   DATETIME,
        @BDATE   DATETIME,
        @EDATE   DATETIME,
        @MINDATE DATETIME,
        @MAXDATE DATETIME,
        @DAYS    DECIMAL;

-- Today's date, modified to be midnight.
SELECT @Today = dateadd(dd, datediff(dd, 0, getdate())+0, 0)     
-- One Month back from today
SELECT @Today = DATEADD(month, -1, @Today);
-- First of last month
SELECT @BDATE = 
   DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))  
-- First of this month   
SELECT @EDATE = 
   DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))  
-- Calculate days in the month   
SELECT @DAYS = 
   DAY(DATEADD (m,1,DATEADD(d,1-DAY(GETDATE()),GETDATE()))-1) 
-- Calculate the first and last dates in the date range
-- and adjust both to be midnight
SELECT @MINDATE = min(ReadingDate) 
  from FileFreeSpace 
  where ReadingDate >= @BDATE and ReadingDate < @EDATE;
SELECT @MAXDATE = max(ReadingDate) 
  from FileFreeSpace 
  where ReadingDate >= @BDATE and ReadingDate < @EDATE;
SELECT @MINDATE = dateadd(dd, datediff(dd, 0, @MINDATE)+0, 0)
SELECT @MAXDATE = dateadd(dd, datediff(dd, 0, @MAXDATE)+0, 0)

select a.InstanceName, a.DBName, a.FileName, a.PhysicalName, 
       a.TotalSizeInMB as StartSize, b.TotalSizeInMB as EndSize,
       (b.TotalSizeInMB - a.TotalSizeInMB) as Growth,
       (a.TotalSizeInMB - a.FreeSizeInMB) as StartUsed,
       (b.TotalSizeInMB - b.FreeSizeInMB) as EndUsed,
       (b.TotalSizeInMB - b.FreeSizeInMB) - 
          (a.TotalSizeInMB - a.FreeSizeInMB) as DiffUsed,
       CAST((((b.TotalSizeInMB - b.FreeSizeInMB) -   
              (a.TotalSizeInMB - a.FreeSizeInMB)) / 
              @Days) as DECIMAL(9,2)) as AvgGrowthDay,
       (b.FreeSizeInMB * 100) / 
       CASE b.TotalSizeInMB 
       WHEN 0 
       THEN 1 
       ELSE b.TotalSizeInMB 
       END as PctFreeEOM
into MonthlyReport
from (select * 
      from FileFreeSpace 
      where dateadd(dd,datediff(dd,0,ReadingDate)+0,0) = @MINDATE) a,
     (select * 
      from FileFreeSpace 
      where dateadd(dd,datediff(dd,0,ReadingDate)+0,0) = @MAXDATE) b
where a.InstanceName = b.InstanceName
and a.DBName = b.DBName
and a.FileName = b.FileName
and a.PhysicalName = b.PhysicalName
and a.DBName not in (select DBName from ExcludeFromReporting)
and upper(a.PhysicalName) not like '%.LDF'
order by a.DBName, a.FileName
GO
-- and DBName not in (select DBName from ExcludeFromReporting)
-- Having ((max(FreeSizeInMB) - min(FreeSizeInMB)) / @Days)
-- and upper(a.PhysicalName) not like '%.LDF'

DECLARE @Subject     varchar (100)
SET @Subject='SQL Server - Monthly File Growth Statistics'

declare @Count as int
select @Count=count(*) from MonthlyReport

IF @Count > 0 
Begin

DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
        N'<table border="1">' +
    N'<tr><th>InstanceName</th>' +
    N'<th>DBName</th>' +
    N'<th>FileName</th>' +
    N'<th>PhysicalName</th>' +
    N'<th>StartSize</th>' +
    N'<th>EndSize</th>' +
    N'<th>Growth</th>' +
    N'<th>StartUsed</th>' +
    N'<th>EndUsed</th>' +
    N'<th>DiffUsed</th>' +
    N'<th>AvgGrowthDay</th>' +
    N'<th>PctFreeEOM</th>' +
    N'</tr>' +
    CAST ( ( SELECT td=[InstanceName],''
      ,td=[DBName],''
      ,td=[FileName],''
      ,td=[PhysicalName],''
      ,td=[StartSize],''
      ,td=[EndSize],''
      ,td=[Growth],''
      ,td=[StartUsed],''
      ,td=[EndUsed],''
      ,td=[DiffUsed],''
      ,td=[AvgGrowthDay],''
      ,td=[PctFreeEOM],''
  from MonthlyReport
order by DBName, FileName
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'SQL Server Agent Mail Profile',
    @recipients = 'me@mydomain.com',
    @subject = @Subject,
    @body = @tableHTML,
    @body_format = 'HTML' ;
END
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'MonthlyReport')
    DROP TABLE MonthlyReport;


So this is an example of the kind of things that I'm using this sort of data for. Hopefully someone else will find this useful too.

Any suggestions for improvement, please leave comments!

Thursday, October 13, 2011

Things I've learned at PASS Summit 2011


"Wow". "Wow!" "WOW!" - Flt Lt Hendley, Capt Hilts and another American PoW, The Great Escape
Today's not so random movie quote pretty much sums up my feelings about the PASS Summit 2011 so far and the SQL Server community in General.

I've been working in SQL Server for a couple of years but I've only just recently discovered PASS and all the goodies that come with it.

I attended SQL Saturday 94 in Salt Lake City last month and got some great information out of that. I joined Twitter and started watching #sqlpass and #sqlhelp (In fact I had an issue at midnight my time the other night, tweeted to #sqlhelp and had a couple of good ideas inside 15 minutes.

Right after SQL Saturday, I started this blog to record assorting technical musings.

This was all leading up to the PASS Summit and I'm amazed at what I've found. In the last few weeks, I signed up for the Big Brothers / Big Sisters program. I was assigned a Big Brother and a group of fellow First Timers. We swapped some emails and shared a little bit of info.

Arriving at the Summit on Monday for my first precon, I sat in and listened while Grant Fritchey and Gail Shaw talked about Execution Plans. I learned a great deal in those 7 hours. I don't know that there's much I can immediately take back to work (since the software I work with is provided by a third party vendor and there's not a lot you can do to modify the code), but my big take away was wanting to learn more about the optimizer and execution plans. One thing that I specifically remember learning was the importance of Referential Integrity constraints in the Optimizer's simplification phase. The app I work with has all the relationships in the business logic and doesn't put the RI constraints on the database - I never knew that this could have an adverse affect on optimization.

Tuesday's precon was with Adam Machanic who talked about Performance Tuning. Lots of good stuff came out of this one, including a wonderful stored procedure called sp_whoisactive. This is going on all my servers the moment I set foot in the door at work next Monday. It slices, it dices and it makes getting active session data out of SQL Server easy.

The Welcome Reception was interesting - or at least the entry was. Ever wanted to feel like you were on the field at the Super Bowl ? The First Timers (there were hundreds of us) were brought into the main ball room to cheering Alumni, spotlights, balloons and smoke. Very glitzy. Of course, the MVP on stage, who shall remain nameless, probably disappointed half the crowd by not knowing the name of the 4th Star Trek Movie during the Quiz Bowl :)

Official day 1 of the conference, with the smaller concentrated sessions had me in a jam. So many things to see and do and only one of me. Thankfully the audio for each session will be included on the session DVDs which I have on order, which will allow a nice chance to review later. Today I saw a couple of great sessions on I/O subsystems, attended the Lightning Talk sessions (Rob Farley can sing, play the guitar and write a nifty little song, who knew?) and watched Brent Ozar unveil his latest BLITZ! script.

This has all been information overload, but some of the best has been meeting and hanging out with some new people. I've enjoyed the company of a couple of new friends from my first timer's group (Go Team Honey Badgers!) and hope that after the event is over we can continue to keep in touch.

The Midnight DBA's put on a fun party tonight, "Inappropriate Sessions" which was as entertaining as it was inappropriate.

I look forward to the rest of the Summit and seeing what else I can take away from this week - not just new knowledge, but new friends and relationships that hopefully will last a long time.

Saturday, October 8, 2011

Tricks to sending email from SQL Server


"This is the Captain. We have a little problem with our entry sequence, so we may experience some slight turbulence and then... explode." - Captain Malcolm Reynolds, Serenity

I had a requirement recently on SQL Server 2005 to write a trigger to call a stored procedure to send an email when a certain event happened. So I logged into SQL Server Management Studio and coded up my trigger and stored procedure and executed the update statement that would fire the trigger and I get the nice "Mail queued" message in the results window at the bottom.

Next thing was to execute the application that executed the same update. Upon trying it there, it failed with no readily apparent error code. Now being an old Oracle guy, my grasp of some of the finer points of T-SQL error trapping are not up to par. I'm still learning some of the finer points to the fine art of T-SQL coding - so my rudimentary attempt at error trapping wasn't doing what it was supposed to do.

It suddenly hit me that since the application runs under another user to what my ID was that it must be a permissions error. So I granted execute on the stored procedure to the application owner and tried again. Same thing.

Next step was to queue up SQL Profiler, and trace the execution and I could see that the failure was occurring at the execution of sp_send_dbmail. The next brilliant idea in the thought process was to login to SSMS as the application owner and see what happened.

That's when I got this message:

EXECUTE permission denied on object 'sp_send_dbmail', 
database 'msdb', schema 'dbo'.

Did a little research on this error message and was reminded that in order to use sp_send_dbmail, a user needs to be granted execute on msdb.dbo.sp_send_dbmail. In order to do that a user needs to be created in msdb with the same name as the user in the target database that we're running against. Also, the user needs to be a member of the DatabaseMailUserRole.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', 
   @membername = '' ;
GO

So, set that up and ran it again. Still failed. Ah, the pain. There was a different message this time though:

Profile not valid

A little further investigation and I realized that I needed to grant access to the email profile to the user in msdb.

Getting all that done finally made it work.

The long and short of this rambling story is that in order to use sp_send_dbmail, there are several pieces that need to be set up.

  • A user needs to be set up in msdb matching the user in the calling database
  • The msdb user needs to be added to the DatabaseMailUserRole in msdb
  • The user in msdb needs to be granted access to the Mail Profile via the Database Mail Configuration Wizard
  • If called from a Stored Procedure, the user needs to be granted execute on the Stored Procedure (but then, that's a given).

References: Troubleshooting Database Mail: Permission denied on sp_send_dbmail Troubleshooting Database Mail: Profile not valid

Thursday, September 29, 2011

Dealing with Dates (Part 2)


"We're trained professionals. (Well, we're semi-trained quasi-professionals, at any rate.)" -Roy Greenhilt, Order of the Stick webcomic
Last time I wrote about converting to and from temporal data types in Oracle. Being how every time I do something for Oracle I have to do the same thing for SQL Server, it's time to delve into temporal data type manipulations in SQL Server.

SQL Server really only has the one function of note that does it all: CONVERT. Personally, I don't find the conversion modifier for convert to be anywhere near as intuitive as to_date or to_char with the format strings that Oracle does - without the chart from the documenation, I'd never be able to remember any of it.

My personal feelings aside, let's hit the base syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

(HTML for this table from SQL Server 2008 Books Online, see link at bottom of post)


Date and Time Styles

When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

Without
century (yy)1

With
century (yyyy)

Standard

Input/Output 3

-

0 or 100 (1,2)

Default

mon dd yyyy hh:miAM (or PM)

1

101

U.S.

mm/dd/yyyy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yyyy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106 (1)

-

dd mon yy

7

107 (1)

-

Mon dd, yy

8

108

-

hh:mi:ss

-

9 or 109 (1,2)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

mm-dd-yy

11

111

JAPAN

yy/mm/dd

12

112

ISO

yymmdd

yyyymmdd

-

13 or 113 (1, 2)

Europe default + milliseconds

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (2)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (2)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126 (4)

ISO8601

yyyy-mm-ddThh:mi:ss.mmm (no spaces)

-

127(6, 7)

ISO8601 with time zone Z.

yyyy-mm-ddThh:mi:ss.mmmZ

(no spaces)

-

130 (1, 2)

Hijri ((5)

dd mon yyyy hh:mi:ss:mmmAM

-

131 (2)

Hijri (5)

dd/mm/yy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

Important

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.


So, example time!

select convert(char(8), getdate(), 112)

returns

20110929

Shortening the char(8) to char(4) would get us:

select convert(char(4), getdate(), 112)

returns

2011

Changing the convert code from 112 to 12, shortens this to a 2 digit year:

select convert(char(8), getdate(), 12)

returns

110929

References:
SQL Server Books Online: CAST and CONVERT

Tuesday, September 27, 2011

Dealing with Dates (Part 1)


"I suggest a new strategy, R2. Let the Wookiee win" - C-3PO, Star Wars Episode IV: A New Hope
Doesn't matter what the DB is that you're working on, working with Temporal data types is a royal pain in the rear end.

I can see this being an exceptionally long post, so I think I'm going to break it up into multiple parts. Let's start with Oracle today and how to transform Date data types into Text for display and the reverse, taking a text representation of a temporal value and placing it into a Date data type.

Oracle provides a long list of built in functions to deal with the conversion of data from one data type to another. You can see them in this list in the last column - they all start with "to_".

Oracle has two data types dealing with date and time. The first is simply DATE: a representation of a date and time to the second. The second is TIMESTAMP: a representation of date and time to fractions of a second. The default has a precision of 6 decimal places, but can go as high as 9. (There is a third option, TIMESTAMP WITH {LOCAL} TIMEZONE which is a timestamp with a built in time zone offset, but for the sake of what we're talking about here, this is just a TIMESTAMP).

So, this gives us three functions to play with:

  • to_date
  • to_timestamp (there's also to_timestamp_tz, but I'm not touching that here)
  • to_char

All three functions take a formatted string and converts it to a date or timestamp or character representation. The format of the function is:

function(string1, [format_string], [nls_parameter])
The format_string is optional. If it is not specified, the string needs to be in the default date format as defined implicitly by the NLS_TERRITORY initialization parameter or explicitly set by the NLS_DATE_FORMAT parameter. Best practice dictates that you always specify the format string. The length of the format string may not exceed 22 characters. Capitalization of a Format Element carries over to the output. A Format Element on "DAY" would output "MONDAY", where as a Format Element of "Day" would output "Monday".

The nls_parameter specifies the language the date is bring formatted to. It must be of the format 'NLS_DATE_LANGUAGE = language'

The to_char function can also be used to convert numbers to character or hex representations of ASCII codes to characters, but we're only talking about dates here.

The format string parameter can be any of the following values: (HTML for this table from Oracle Documentation, see references link at end of document)

Element Description
/
,
.
;
:
"text"
Punctuation and quoted text is reproduced in the result.
AD
A.D.
AD indicator with or without periods.
AM
A.M.
Meridian indicator with or without periods.
BC
B.C.
BC indicator with or without periods.
CC
SCC
Century.
  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D Day of week (1-7). This element depends on the NLS territory of the session.
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DL Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy'.

Restriction: You can specify this format only with the TS element, separated by white space.

DS Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR'.

Restriction: You can specify this format only with the TS element, separated by white space.

DY Abbreviated name of day.
E Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9] Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats.

Examples: 'HH:MI:SS.FF'

SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;

FM Returns a value with no leading or trailing blanks.
FX Requires exact matching between the character data and the format model.
HH
HH12
Hour of day (1-12).
HH24 Hour of day (0-23).
IW Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard.
J Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI Minute (0-59).
MM Month (01-12; January = 01).
MON Abbreviated name of month.
MONTH Name of month.
PM
P.M.
Meridian indicator with or without periods.
Q Quarter of year (1, 2, 3, 4; January - March = 1).
RM Roman numeral month (I-XII; January = I).
RR Lets you store 20th century dates in the 21st century using only two digits.
RRRR Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
TS Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.

Restriction: You can specify this format only with the DL or DS element, separated by white space.

TZD Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.

Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).

TZH Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.

Example: 'HH:MI:SS.FFTZH:TZM'.

TZM Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.

Example: 'HH:MI:SS.FFTZH:TZM'.

TZR Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.

Example: US/Pacific

WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X

Local radix character.

Example: 'HH:MI:SSXFF'.

Y,YYY Year with comma in this position.
YEAR
SYEAR
Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY
SYYYY
4-digit year; S prefixes BC dates with a minus sign.
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.

All of this is great, what about examples?

select to_char(sysdate, 'Day, DD Month, YYYY "@" HH24:MI:SS') from DUAL;

returns an error because the format string is over 22 characters. The elements are all valid, but combined they are too long.

select to_char(sysdate, "Day, DD Month, YYYY @ HH24:MI:SS") from DUAL;

returns

Tuesday, 27 September, 2011

Next: The SQL Server CONVERT function

References:
Oracle 11g SQL Language Reference: to_date function
Oracle 11g SQL Language Reference: Format Models

Friday, September 23, 2011

Passing a parameter to a SQL Script and parsing the output


"I have a bad feeling about this..." - Lots of people, over 6 Star Wars movies

Not so long ago, I had a requirement to be able to have a scheduled job that ran a SQL script, took the output and did something with it in Windows CMD shell. Not only that, I had to feed a parameter in.

I'd done this many times in my previous life, taking a value from a SQL script in Oracle, passing it to the Unix shell and running with it.

Having to do it in Windows was a bit more of a challenge.

Took quite a bit of research to find out how to do it, so it's worthy of preserving here.

First, we need the SQL we're going to execute:

set NOCOUNT ON
select column1
from table1
where column2 = '$(var1)'

Next, we need to call it and pass the variable in: (let's assume I've setup a user called 'ro' with a password of 'read' that only has select access on the table I'm selecting from. The script (above) is sitting in 'c:\sql' and I'm writing the output to 'c:\sql\out'. Finally, the process that calls this script is passing the parameter. (In the real world this is all on one line. A blog is only so wide ...). Also, in my example this script is called by another process passing in a job number as the parameter - so the %1 is the job number parameter that is being passed in.

sqlcmd -S SRV01 -d myprd1 -U ro -P read -h-1 -i c:\sql\query1.sql 
    -o c:\sql\out\query1.txt -v var1="%1"

End result is that I now have the value of column1 from table1 sitting in a text file.

Next up, we need to be able to interrogate that value in order to be able to do something else with it.


for /f "usebackq delims=" %%i in (`cat c:\sql\out\query1.txt`) do set H001=%%i

At the end of that, we now have the value from the text file sitting in variable H001, that we can use for whatever.

Of course a clean system is a good system, and you should always clean up after yourself:

if exist c:\sql\out\query1.txt (erase c:\sql\out\query1.txt)

So far, so good. But, what is done on SQL Server in Windows should also be done in Unix and Oracle, right ? Second verse, same as the first (except for the whole change of OS / RDBMS thing).

Going to change this one up just a little. Let's say we need two values, the first being 8 characters and the second being 4.

set verify off
set echo off
set feedback off
set heading off
spool /tmp/query1.out
select substr(column1,1,8)||substr(column3,1,4)
from table1
where column2 = '&1';
spool off
exit

The same requirements for calling the script. And again, this is called by another processing passing in a parameter in the first position.

export ORACLE_SID=myprd1
$ORACLE_HOME/bin/sqlplus ro/read @/scripts/query1.sql $1

Alternatively, and actually my preferred way of doing it is to put the username and password as the first line of the script and to remove the user/pass from the invocation line. This way, in the event of someone trying to execute 'ps -ef | grep sqlplus' at just the magic moment, won't see your database credentials in the process list.

Once we have our output, we need to get it into the environment variable.

H001=`cat /tmp/query1.txt | grep -v ^$ | cut -c1-8`
H002=`cat /tmp/query1.txt | grep -v ^$ | cut -c9-12`

There's other ways to get the same information too, I'll list just one more here.

H000=`$ORACLE_HOME/bin/sqlplus -s @/scripts/query1.sql`
H001=`echo $H000 | cut -c1-8`
H002=`echo $H000 | cut -c9-12`

That would assign the whole 12 character string to H000 and you could extract the other two variables from it.

Wednesday, September 21, 2011

Setting up a new SQL Server


"Don't be too proud of this technological terror you've constructed" - Darth Vader, Star Wars Episode IV: A New Hope

A couple of years ago, I was leaving my former employer. I could see the writing on the wall that the Oracle work I was doing was going to be diminishing and SQL Server was the way of the future. I didn't want to work with SQL Server, so I went and found another job with my current employer.

One of the first things they told me was that I was going to be looking after a SQL Server for a new customer. Argh! Note to self: next time ask more questions about responsibilities and duties, I suppose :)

Almost 4 years down the road, I've learned quite a bit about this database - but I still have a lot to learn.

One of the things I still need to learn a lot about is best configuration options for SQL Server and the Windows box that it sits on. One great guide that's collected a lot of the information I'd picked up in bits and pieces around the place is collected neatly in two posts on the site of Brent Ozar PLF.

These fine people have put together two blog posts discussing setup of a new SQL Server. Rather than steal their work, I'll just link directly to their pages. (This post falls squarely in the area of "Posts I make mainly as a personal reference for my own benefit").

Since I'm singing the praises of the information on this site, there's one more thing I just have to list. In addition to having to set up new servers, sometimes you just inherit them. Brent has a one hour blitz script to have a look at the setup of a database server you've just inherited.

It's sitting right here.

I learned a lot from watching that presentation and crunching through the script. Definitely worth the price of admission ...

Scripting users and their permissions (part 2)


"A man walks down the street in that hat, people know he's not afraid of anything." - Wash, to Jayne - Firefly, "The Message"

In my last post I talked about scripting out users and their permissions in Oracle.

Since his is a blog about things both Oracle and SQL Server, it only stands to reason that the following post should be about replicating this for SQL Server.

For those more familiar with Oracle than SQL Server, there's two layers of objects between you and your data. Since a SQL Server instance has multiple databases in the one instance (which is completely different to the way a lot of Oracle databases are set up), you first need a login name to log in to the instance. This can be a local SQL Server login, or a Windows Account.

Once authenticated to the SQL Server instance, there are then users in each database that are linked back to logins. So if you have Prod, Test and Dev databases on the one server, you could login as "myuser" and possibly have full database ownership on Dev, Read and Update to Test and Read Only to Prod. The one login gets you into the instance, but the users connected to the login in each database have varying permissions.

So, with that out of the way, the first thing we need to get is a list of logins. Login information is stored in the master database of the instance.

The easiest way to get this information is to go straight to the source. Microsoft provides a script to extract this information from you database using this Knowledge Base article. The script is too long to reproduce here, but run it to create two stored procedures in master and then "exec sp_help_revlogin" to generate a list of logins on your instance.

Once we have the list of logins, the next thing we're going to need is a list of users in the database. You run this out of the database you're extracting the information from

use ellprd
go
select 'create user ' + QUOTENAME(a.name) +
CASE WHEN SUSER_SNAME(a.sid) is NULL 
     then '' 
     ELSE ' for login ' + QUOTENAME(SUSER_SNAME(a.sid)) 
END +
CASE WHEN default_schema_name is NULL 
     then '' 
     ELSE ' with default_schema = ' + QUOTENAME(a.default_schema_name) 
END
from sys.database_principals a, sys.sysusers b
where (type_desc like '%USER' or type_desc = 'WINDOWS_GROUP')
and a.name = b.name
and b.altuid is null
and b.sid is not null

Finally, we need to get a list of all the object permissions in the database.

For this, the magic of Google comes to my aid yet again. Found this post which has a lovely script that generates user permissions. There are two variables at the top - @OldUser and @NewUser - these are set when you want to replicate permissions from one user to another. To just list what you have, make them both the same value.

That covers basic SQL Server users. But there's one thing missing. What if you have someone connecting via their Windows identification token ?

The following script was developed (to the best of my knowledge) by a site DBA at one of my customer's sites. The variable at the top, @DatabaseFilter needs to be set to the name of the database you're running this against.

/* Security Audit Script - SQL Server 2005 & 2008 Compatible */
USE master
GO

SET NOCOUNT ON;

/***************SPECIFY DATABASE HERE*****************/
DECLARE @DatabaseFilter varchar(255)
SET @DatabaseFilter = 'myprod1'

/* Report Date & Time */
SELECT CONVERT(varchar, GETDATE(), 107)+' @ '+
       LEFT(CONVERT(varchar, GETDATE(), 108),5) AS [Report Run Date-Time]


/* Windows Logins - ALL */
SELECT a.name, a.sid, a.type_desc 
INTO #server_logins
FROM sys.server_principals a
LEFT JOIN sys.sql_logins b ON b.sid = a.sid
WHERE a.type IN ('U','G')

/* Find Windows Logins w/ Server-Wide Roles */
SELECT d.name AS [Server Role], a.name AS [Server Login], 
       a.type_desc AS [Logon Type]
FROM #server_logins a
LEFT JOIN sys.server_principals b 
       ON b.sid = a.sid
LEFT JOIN sys.server_role_members c 
       ON c.member_principal_id = b.principal_id
LEFT JOIN sys.server_principals d 
       ON d.principal_id = c.role_principal_id
WHERE d.name IS NOT NULL
ORDER BY a.name

/* Database List */
CREATE TABLE #databases 
             (row_id int identity(1,1), [name] varchar(255), id int)
INSERT INTO #databases ([name], id)
SELECT DISTINCT a.name, a.database_id
FROM sys.databases a
WHERE a.database_id > 4 --Skip System Databases
AND DATABASEPROPERTYEX(a.[name],'Status') = 'ONLINE' --Online Only
AND a.name = @DatabaseFilter --Single Database Filter

/* Looking for Database Users */
CREATE TABLE #database_logins 
             ([Database] varchar(255), 
              [Database User] varchar(255), 
              [Database Role] varchar(255), 
              [Server Login] varchar(255))

DECLARE @counter int, @max_rows int, @database varchar(255)
SELECT @counter = 1, @max_rows = COUNT(*) FROM #databases

WHILE @counter <= @max_rows
BEGIN

 SELECT @database = [name] FROM #databases WHERE row_id = @counter

 EXEC('INSERT INTO #database_logins
  SELECT '''+@database+''' [Database], 
         b.name AS [Database User], 
         c.name AS [Permission], 
         IsNULL(d.name,''ORPHANED'') AS [Server Login]
  FROM ['+@database+'].sys.database_role_members a
  LEFT JOIN ['+@database+']..sysusers b 
         ON b.uid = a.member_principal_id
  LEFT JOIN ['+@database+'].sys.database_principals c 
         ON c.principal_id = a.role_principal_id
  LEFT JOIN #server_logins d ON d.sid = b.sid
  WHERE b.name <> ''dbo'' AND b.issqlrole = 0')

 SET @counter = @counter + 1
END

SET @counter = 1

WHILE @counter <= @max_rows
BEGIN

 SELECT @database = [name] FROM #databases WHERE row_id = @counter
 EXEC('INSERT INTO #database_logins
  SELECT '''+@database+''' [Database], a.name AS [Database User], 
         ''Object-Based'' AS [Database Role], 
         ISNULL(b.name,''ORPHANED'') AS [Server Login]
  FROM ['+@database+']..sysusers a
  LEFT JOIN #server_logins b 
         ON b.sid = a.sid 
         AND b.name COLLATE DATABASE_DEFAULT = 
             a.name COLLATE DATABASE_DEFAULT 
  WHERE a.name NOT IN (''public'',''dbo'',''guest'',
  ''INFORMATION_SCHEMA'',''sys'',''db_owner'',''db_accessadmin'',
  ''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',
  ''db_datareader'',''db_datawriter'',''db_denydatareader'',
  ''db_denydatawriter'')
  AND a.issqlrole = 0 AND a.name NOT IN (
   SELECT b.name
   FROM ['+@database+'].sys.database_role_members a
   LEFT JOIN ['+@database+']..sysusers b 
          ON b.uid = a.member_principal_id
   LEFT JOIN ['+@database+'].sys.database_principals c 
          ON c.principal_id = a.role_principal_id
   LEFT JOIN #server_logins d  
          ON d.sid = b.sid
   WHERE b.name <> ''dbo'')')

 SET @counter = @counter + 1
END

SELECT a.[Database], a.[Database User], a.[Database Role]
FROM #database_logins a
ORDER BY a.[Database], a.[Database User], a.[Database Role]

/* Taking out the trash */
DROP TABLE #server_logins
DROP TABLE #databases
DROP TABLE #database_logins

Try it out, see what you think.