Here is one way to remove old backup files from the OS through a stored procedure.  The procedure can be compiled in any database.  Once compiled you can run the stored procedure from a scheduled job to remove old backup files from the OS.

IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Delete_OS_Files_By_Date' AND TYPE = 'P') 

DROP PROCEDURE dbo.usp_Delete_OS_Files_By_Date

GO

CREATE PROCEDURE dbo.usp_Delete_OS_Files_By_Date (@SourceDir varchar(255), @SourceFile varchar(255), @DaysToKeep int)

AS

BEGIN

/***********************************************************************************    

Description:  Delete operating system files older than n days                   ****      

Prototype:

EXEC dbo.usp_Delete_OS_Files_By_Date                                     

                                      @SourceDir = '\\smpprod01\Backups\SGC\'

                                    , @SourceFile = 'SGC_Backup_*'

                                    , @DaysToKeep = 3                 

                                                                                ****

History                                                                         ****

05/06/2011 Kim Tessereau      Created                                           ****

************************************************************************************/

SET NOCOUNT ON;

DECLARE @CurrentFileDate char(10),

@OldFileDate char(10),

@SourceDirFOR varchar(255),

@FileName varchar(255),

@DelCommand varchar(255);

SET @CurrentFileDate = CONVERT(char(10),getdate(),121);

SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121);

SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI';

-- Get OS File information from the OS

CREATE TABLE #Temp_01      ( [lineorder] int IDENTITY(1,1)  

                           , [OSInfo] varchar(255) );

INSERT INTO #Temp_01

EXEC master..xp_cmdshell @SourceDirFOR;

-- Put the OS File info in date order

CREATE TABLE #Temp_02      ( [lineorder] int   

                           , [TimeStamp] datetime

                           , [FileName] varchar(255) );

INSERT INTO #Temp_02 ([lineorder], [Timestamp], [FileName])

SELECT [lineorder]

      , CONVERT (char(10),SUBSTRING([OSInfo],1,10), 121) AS [TimeStamp]                     

      , SUBSTRING([OSInfo],21,255) AS [FileName]                      

FROM #Temp_01                  

WHERE [OSInfo] IS NOT NULL               

ORDER BY [lineorder];

-- Loop through OS Information and create delete command and execute

DECLARE DeleteCursor CURSOR READ_ONLY FOR 

SELECT [FileName]     

FROM #Temp_02

WHERE [TimeStamp] <= @OldFileDate;

OPEN DeleteCursor;

FETCH NEXT FROM DeleteCursor INTO @FileName;

WHILE (@@fetch_status <> -1)

BEGIN

      IF (@@fetch_status <> -2)   

            BEGIN     

            SET @DelCommand = 'DEL /Q "' + @SourceDir + @FileName + '"';    

            EXEC master..xp_cmdshell @DelCommand;

                  END 

FETCH NEXT FROM DeleteCursor INTO @FileName;

END;

CLOSE DeleteCursor;

DEALLOCATE DeleteCursor;

END;

GO

Can you explain normalization?

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• Meet all the requirements of the first normal form.
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• Meet all the requirements of the second normal form.
• Remove columns that are not dependent upon the primary key.

What is de-normalization?

What is a surrogate key?

Explain what a star schema is?

Explain snow flaking?

What features in SQL Server do you use to tune queries?

Are clustered index scans beneficial to an execution plan?

Can you explain the MERGE command and how it is used?

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Are there any performance issues with having a lot of indexes on a table?

What’s the difference in using a table variable and a temp table?

The first difference is that transaction logs are not recorded for the table variables.  The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution.  Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements.

What are the benefits or detriments to using cursors?

What is foreach-loop container? Give an example of where it can be used.

What is a sequence container? Give an example of where it can be used.

What task do you use to send an email?  Send Mail task.

Mention a few mapping operations that the Character Map transformation supports? 

The Character Map transformation applies string functions, such as conversion from lowercase to uppercase, to character data. This transformation operates only on column data with a string data type. The Character Map transformation can convert column data in place or add a column to the transformation output and put the converted data in the new column. You can apply different sets of mapping operations to the same input column and put the results in different columns. For example, you can convert the same column to uppercase and lowercase and put the results in two different columns.

Explain the functionality of: SCD transformation.

The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

• Matching incoming rows with rows in the lookup table to identify new and existing rows.
• Identifying incoming rows that contain changes when changes are not permitted.
• Identifying inferred member records that require updating.
• Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.
• Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

Explain the functionality of: Union All transformation.

What is the “Lookup” transformation used for?

What is the use of “package configurations” in SSIS?

What are the different ways in which configuration details can be stored?

How to deploy a package from development server to production server?

How to deploy packages to file system?

How to deploy packages to SQL Server? What database will packages be stored?

Error handling is something that is often forgot or misunderstood or put off until something goes awry and we try to back peddle and retro fit the error handling logic in the script, stored procedure or function. 

I think there are quite a few reasons we should engage in handling something other than the happy path of logic.  I’ve listed a few here and I’m sure you can think of some more.

Inform calling process of situation:  I’m going to pretend, just for a short moment, that I’m a developer.  Let’s say I’m tasked with the job of writing some new functionality in an existing system.  I want to be efficient and reuse some of the existing stored procedures already written in the system.  So I start looking at some of the logic with the procs and I realize that I can’t tell what the business rules are for a given process because there isn’t any controls, error handling, that helps me understand what is acceptable as far as data values are concerned.  So the calling process that I would be writing would only know about the happy path and not the so called “roads less traveled”.

Assist in handling transactions:  The first thing that comes to mind when I think about a transaction is an old example of the ATM machine transactions.  Let’s say that you are going to transfer $100 from your checking account to your saving account.  If you looked at the code to do this it would probably look something like this….

     UPDATE checking

            SET balance = balance - @transfer_amount

            WHERE account = @checking_account

     UPDATE savings

            SET balance= balance + @transfer_amount

            WHERE account = @savings_account

If these two UPDATE statements are run without any transaction or error handling logic enforced and the first one is successful and the second one fails, then it is going to only withdraw the money from the checking account and the process will stop and return an error to the call process, but the calling process won’t know what actually happened?  Did the transfer successfully take place? 

Alert user there is a problem:  One thing that I think unnerves a user more than anything is to receive an error that is totally cryptic to them.  And I can’t think of any couple of errors that sounds worse than “a constraint has been violated” or how about the ever popular, “you’ve been chosen as a deadlock victim”.  Sounds pretty impressive and could only end in disaster for the user.  They have no idea whether or not what they just did became permanent in the database or not.  With providing error logic in cases like this will give the user some idea of what just happened and what they need to do to correct the situation.  They would probably much rather see something like, “The customer you are trying to add already exists” or “System error – please resubmit your change”.  These are more descriptive and give the user answer to what happened and to what their next steps are going to be in order to correct the situation.

Handle unexpected results:  It might be difficult to plan for every error that comes up, but we can at least trap the unexpected and try to provide additional information about what was happening as the time of the error.  Additional information may include things like how many rows were affected, did the connection fail, was the previous transaction successful, etc… That’s all I have to say about that!

Helps provide data consistency:  Data consistency is part of what is called the ACID test.  By using error handling in conjunction with transactions, whether they are implicit or explicit, it can help to determine whether or not the data being manipulated is consistent or not.  Let’s brake apart the acronym ACID first.  A = Atomicity, C = Consistency, I = Isolation and D = Durability.  We are taking about the Consistency in this case.  What does it mean to be consistent?  The transaction must provide valid data to be written to the database.  You seen what happened in the above example with the ATM transaction, if the user doesn’t understand that there was an error and what that error was how can they be assured that the data lives in the database as a correct (consistent) state? 

There are a lot of constructs in SQL that help to assist us with handing errors.  They range from the following…

     @@Error global variable

     RETURN statement

     User-defined error messages

     RAISERROR command

     TRY/CATCH blocks

     XACT_ABORT

To see more details on these error handling techniques you can catch my new presentation on error handling titled "We ain't affraid of no errors", at the St. Louis SQL Server User Group meeting on June 14^th, 2011 and after the presentation my slide deck will be available at http://www.stlssug.org Hope to see you there!

I’ve been watching this one particular server that has been throwing alerts for high physical memory usage from Red Gate’s SQL Monitoring tool.  I was discussing this with a friend of mine, Clayton Hoyt and he mentioned that I might want to look at the “Optimize for Ad Hoc Workloads” advanced server configuration options.  I took him up on his advice and googled for the “Optimize For Ad Hoc Workloads” and “SQLSkills”.  I found a wonderful article from Kimberly Tripp on just this topic.  Here is the link.

 http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx

The article suggests running the below query to determine the number of plans in cache and the total space that they are taking up.  So I ran the query on my memory hungry server and the following is what happened......

SELECT objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

go

Initial Query Results:  This is what I started with.

CacheType   Total Plans   Total MBs     Avg Use Count Total MBs - USE Count 1  Total Plans 

----------- ------------- ------------- ------------- ------------------------ ------------

Adhoc       32750         2352.641181       927           947.289619                 9167

Proc        842           387.390625        169428        6.882812                   29

Prepared    576           69.460937         15176         6.234375                   64

Trigger     35            21.867187         6154          0.828125                   3

Check       31            0.812500          11453         0.023437                   1

View        327           36.875000         8057          0.000000                   0

UsrTab      4             0.468750          1473          0.000000                   0

Then I turned on “Optimized for Ad Hoc Workload” and watched the total number of plans decrease along with the total amount of space depleat.  I kept monitoring during this time as well using SQL Monitor and the only thing that I noticed was the Buffer Free Pages dropped from 1.7 million to .7 million during this 4 hours I spent watching this server.

4 hours had passed, Query Results: 

CacheType   Total Plans  Total MBs    Avg Use Count Total MBs - USE Count 1   Total Plans 

 ----------  -----------  ------------ ------------- ------------------------- ------------

Adhoc       42270        2264.423034        733           659.141784                11827

Proc        833          320.968750         173396        6.882812                  29

Prepared    653          84.187500          13557         2.773437                  26

Trigger     35           21.453125          6624          0.609375                  2

Check       31           0.812500           11582         0.023437                  1

View        327          36.875000          8159          0.000000                  0

UsrTab      4            0.468750           1490          0.000000                  0

Then I decided instead of waiting for the server to slowly correct itself I would go ahead and issue DBCC FREEPROCCACHE to clear the procedure cache.

After DBCC command had run, Query Results:

CacheType  Total Plans   Total MBs    Avg Use Count Total MBs - USE Count 1    Total Plans

---------- ------------- ------------ ------------- -------------------------- ------------

Proc       70            21.031250          20            5.804687                   17

Adhoc      215           5.745986           3             0.691299                   128

Prepared   21            1.625000           5             0.523437                   9

Trigger    3             1.140625           6             0.382812                   1

View       28            1.656250           8             0.000000                   0

Buffer Free Pages climbing from .7 million to 1.1 million. I thought I would see Buffer Free Pages go through the roof, but I’m guessing that data cache stepped in and took over what had become available.

What I expect to see in the next 12 hours is that the total plans and total MB’s will grow again, but not to the size that it was in the first query.  I’m going to bed now and I’ll check the server in the morning……

And...I’m back...

Results 12 hours after cache was cleared:

CacheType  Total Plans   Total MBs    Avg Use Count Total MBs - USE Count 1       Total Plans

---------- ------------- ------------ ------------- ----------------------------- ------------

Adhoc      61025         1456.307067        8             523.744567                    46369

Proc       348           138.406250         4961          8.828125                      40

Prepared   615           76.601562          279           3.718750                      46

Check      32            0.781250           279           0.218750                      11

Trigger    16            6.398437           1247          0.000000                      0

View       322           30.742187          299           0.000000                      0

UsrTab     3             0.250000           48            0.000000                      0

Conclusion:  The total number of Adhoc plans in cache were higher than the original baseline, however, the space being used for Adhoc plans was significantly less with the “Optimize For Ad Hoc Workloads” configuration option set on.  Because this server houses databases that are accessed with an unusual amount of Adhoc queries I will leave this option on.

·         Why do we need error handling?

 One of the first things that comes to mind is that the calling process needs to be informed if a stored procedure/script has failed or was successful.  It is also used to control the processing of transactions whether they are implicit or explicit.  The user needs to be alerted if there is a problem too.  Error handling allows us to be able to code for unexpected results and helps to provide data consistency.

• ·         @@ERROR Global Variable

 @@ERROR is known as a system variable to SQL Server.  It is read-only and contains the value of the error number that was generated in the previous transact SQL statement that just processed.  Every statement is evaluated to determine whether or not it was successful.  So for example:

USE master                             --@@ERROR contains 0

   SELECT * FROM sysobjects            --@@ERROR contains 0

USE model                              --@@ERROR contains 0

   SELECT * FROM sysdatabases          --@@ERROR contains 208

IF EXISTS (SELECT * FROM sysservers)   --@@ERROR contains 208

   SELECT 1=1                         --@@ERROR contains 102

IF @@ERROR > 0                         --@@ERROR contains 0

   GOTO somewhere                      --@@ERROR contains 0

somewhere:                             --@@ERROR contains 0

·         RETURN Statement

 The RETURN statement when executed returns control to the call process unconditionally.  It returns an integer value.  A warning will be issued it the value is NULL. 

·         User-Defined Error Messages

 The user-defined error messages are saved in master in sys.messages table.  They can be used across databases.  When a user-defined error message is called if can write to the Windows application log.  The message can be informational or error related, depends on severity level.  Severity level 20 thru 25 are fatal errors – processing has stopped.

·         RAISERROR Statement

The RAISERROR statement is used to raise user-defined error messages.  It can be used to raise dynamically built messages.  The message is returned as a server message.  It can also raise messages to a Catch block and insert them into the error and application log.  Here is an example of a RAISERROR statement.

DECLARE @oldname varchar(15),

         @newname varchar(15)

SET @oldname = 'Cato'

SET @newname = 'Tessereau'

RAISERROR (50001,  --user-defined error message number

               10, --severity level

               1,  --error state

               @oldname,

               @newname) WITH LOG;

·         TRY/CATCH Blocks

 These consist of two parts a TRY block and a CATCH block.  Logic control is passed to the CATCH block from the TRY block when an error is encountered.  They can be nested, but be careful when doing this because the logic can get very confusing the deeper you nest.  Severity 20 or higher not handled by TRY/CATCH because connection is closed.  Severity 10 or less not handled, these are informational only.  Here is an example of a simple TRY/CATCH block.

CREATE PROC usp_myproc

AS

  --just a silly little proc

  SELECT * FROM <table that does not exist>;

GO

BEGIN TRY

      EXEC usp_myproc

END TRY

BEGIN CATCH

      SELECT

            ERROR_NUMBER() as ErrNum,

            ERROR_MESSAGE() as ErrMsg;

END CATCH

GO

·         XACT_ABORT statement

 OFF is the default setting.  Compile errors are not affected by XACT_ABORT.

When set to ON

·         Processing stops when an error is encountered

·         When run-time error is raised, entire transaction is rolled back automatically, no need for ROLLBACK statement.

·         When combined with a TRY/CATCH block you get unexpected behavior

o    SQL Server treats statement level errors as batch level errors

Tuesday: My day started at Microsoft in St. Louis.  I got there nerdy early, as usual.  Microsoft is located on the 11th floor so I rode the elevator up and the doors opened up and there stood two guys and a bunch of bagels!  It was Jose and Austin from PragmaticWorks.  They were waiting for Brian Knight to open up the doors to the office so they could setup for the day.  Today was the first day of an SSAS Workshop and I was eager to get started.  People started filing in right away.  It was good to hook up with lots of folks from our SQL Server User Group in St. Louis, Julie Bloomquist, Pete Williams, John Dempsey, Rosa Johnson and Phil Milner.  And then a semi-familiar face came through the door, it was Bill Fellows from KC!  This was shaping up to be quite a social event as well as a technical one.   Brian Knight started right on que at 8:30am.  He has incredible energy, like you can tell when he is in the house!  Class started out fast and furious and Brian took us from 0 to 60 in no time at all.  Before we knew it we were brousing our first cube.  By the end of the day everyone's head was spinning with thoughts of what they could implement back at the ranch.

Wednesday: Back to Microsoft and I was early again (a little habit I've got).  Brian stopped by and chatted with me for a while and asked if I would help him with his first demo of the day.  Well, of couse, I was excited!  He likes to make things fun so he made up this website called Swank Dating Site.  I work for Swank Motion Pictures and everyone is always asking if it's legit!  We had a lot of fun with it and the class was in a roar most of the morning.  I learned a ton and refined most of my BI skillset, which reined from SQL Server 2005.  After class was over myself and John Dempsey invited Brian and his crew out for dinner and a drink at the local pub.  It was great spending time with these guys, they were so full of energy and so much fun!

Thursday: And we're back at Microsoft for another round with Brian and company!  This time it was an executive BI roundtable event.  And the event was being moderated by Jin Cho, Microsoft’s Business Intelligence Technology Solutions Professional.  He was great, giving us good information about how to solve business problems with BI.  Brian also kick in a demo on some of the front-end tools that are part of the Microsoft BI stack.  Lots of great conversation surrounded this event.

Friday: Back at Swank for the morning and then off at noon to pick up my SQL Saturday buddies!  John Dempsey, Sanil Mhatre and Danielle Beniot.  We were off to Chicago for the next couple of days!  We made great time getting up to Chicago and pulled into town about 5:30pm.  Danielle scouted out a great place for sushi, called Orchid, I thought it was a great name for a primary key!  After dinner it was back to the hotel for a few drinks.  Wendy Pastrick tweeted that she and others were at the pool so off we went to meet up with them.  It turned out to be kind of an unscheduled pre-party for #sqlsat67.   I had a great time seeing a few people I had met before, Wendy, Michelle Ufford and Tom LaRock.  And I got to meet Christina Leo and Jes Borland for the first time.  Well time slipped away from me and before I knew it, it was time to hit the hay.

Saturday: Up early and heading over to DeVry University for a great day with friends!  We were all very excited to be sitting in on some excellent sessions given by excellent presenter's.  The day went by so fast I could hardly believe it.  Before I knew it was lunch time and I was up to speak at the WIT Lunch panel.  I love being involved with the virtual chapter of Women In Technology group.  The topic for the day was "Energizing the Next Generation".  Jes did a great job of moderating and Wendy, Hope Foley and Miyaka Tabe were all so good!  And then, as fast as the morning had went so did the afternoon and there we stood as everyone cheered to close out the second, very successful SQL Saturday Chicago!  I was so proud to be a part of it!

Sunday: I think I had a SQL Server hangover after all of that!