Not Normalized Rotating Header Image

Truncate Tables but Preserve Foreign Keys

May 23rd, 2014
by Todd.
No comments yet

During a Data Mart deployment there was a requirement to remove all data from the target database while leaving the schema intact.

The development team working on the issue made a couple of different attempts at solving the requirement.  Their approaches worked but had drawbacks in the areas of performance and maintainability.

The First Approach – DELETE *

The first attempt utilized DELETE, placing all of the tables in the correct order to avoid foreign key constraint errors.  Like mentioned before – this worked so long as the tables were in the correct order.; Otherwise they would receive this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

The other problem with this approach is that DELETE statements delete rows one at a time and is fully logged.  It was also not very fast.

The Second Approach – TRUNCATE *

While attempting to implement the second approach utilizing TRUNCATE they team realized that they could not truncate data in a table that was referenced by a foreign key. No problem – they manually scripted out the drop of the foreign keys at the beginning of the command followed by the truncate commands followed by the creation of the foreign keys at the end of the command.

This worked great. Truncate takes the whole table at a time – and is minimally logged. It was fast. Life was good. Until the next deployment when there were a whole bunch of new tables in the Data Mart and it was time to rescript this procedure.

My Solution – Truncate all the Tables but Preserve Foreign Keys

Wait – that’s what they did in the second approach right ? Well Yes – and No. My solution is dynamic and automatically preserves all the FK constraints at the beginning of the script before performing the truncation and finally reapplying the FK constraints.

Without further ado – here is the script I generated.

?Download TruncateALLDataPreserveFKs_SQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

USE [MyDatabase]
GO
SET NOCOUNT ON
GO
 
PRINT 'Preparing FK Drop / Create Commands.....'
	CREATE TABLE [##FKCommands_Drop]   ([RowId] [INT] IDENTITY(1,1) NOT NULL, [CmdText] VARCHAR(2000) NOT NULL) ON [PRIMARY] ;
	GO
	CREATE TABLE [##FKCommands_Create] ([RowId] [INT] IDENTITY(1,1) NOT NULL, [CmdText] VARCHAR(2000) NOT NULL) ON [PRIMARY] ;
	GO
 
	INSERT INTO [##FKCommands_Drop] ([CmdText]) 
	SELECT  'IF EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N''' + '[' + [f].[name] + ']'') AND parent_object_id = OBJECT_ID(N''' + '[' + OBJECT_NAME(f.parent_object_id) + ']''))' + ' ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + f.name + '] ;'
	FROM    [sys].[foreign_keys] AS [f]
	INNER JOIN [sys].[foreign_key_columns] AS [fc]
	ON      [f].OBJECT_ID = [fc].constraint_object_id ;
 
	INSERT INTO [##FKCommands_Create] ([CmdText]) 
	SELECT  'IF NOT EXISTS (SELECT * FROM [sys].[foreign_keys] WHERE [object_id] = OBJECT_ID(N''' + '[' + [f].[name] + ']'') AND parent_object_id = OBJECT_ID(N''' + '[' + OBJECT_NAME(f.parent_object_id) + ']''))' + ' ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id) + ']' + ' ADD CONSTRAINT ' + '[' + f.name + ']'
	+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')' + ' REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+');'
	FROM    [sys].[foreign_keys] AS [f]
	INNER JOIN [sys].[foreign_key_columns] AS [fc]
	ON      [f].OBJECT_ID = [fc].constraint_object_id ;
PRINT 'Preparing FK Drop / Create Commands..... [Completed].'
 
PRINT 'Dropping FK Constraints.....'
	DECLARE @myCurId INT ;
	SET     @myCurId = 1 ;
	DECLARE @myMaxId INT ;
	SELECT  @myMaxId = MAX([RowId]) FROM [##FKCommands_Drop] ;
	DECLARE @myCmdText VARCHAR(2000) ;
	SET     @myCmdText = '' ;
 
	WHILE @myCurId <= @myMaxId
		BEGIN
			SELECT  @myCmdText = [CmdText] FROM [##FKCommands_Drop] WHERE [RowId] = @myCurId ;
			EXECUTE(@myCmdText) ;
			SET @myCurId = @myCurId + 1 ;
		END
PRINT 'Dropping FK Constraints..... [Completed].'
 
PRINT 'Truncating All Data.....'
	EXECUTE sp_msforeachtable 'TRUNCATE TABLE ? ;' ;
PRINT 'Truncating All Data..... [Completed].'
 
PRINT 'Making Needed Changes.....'
-- Insert logic here for DDL changes, etc.
PRINT 'Making Needed Changes..... [Completed].'
 
PRINT 'Creating FK Constraints.....'
	SET     @myCurId = 1 ;
	SELECT  @myMaxId = MAX([RowId]) FROM [##FKCommands_Create] ;
	SET     @myCmdText = '' ;
 
	WHILE @myCurId <= @myMaxId
		BEGIN
			SELECT  @myCmdText = [CmdText] FROM [##FKCommands_Create] WHERE [RowId] = @myCurId ;
			EXECUTE(@myCmdText) ;
			SET @myCurId = @myCurId + 1 ;
		END
PRINT 'Creating FK Constraints..... [Completed].'   
 
PRINT 'Cleaning Up.....' 
	DROP TABLE [##FKCommands_Drop] ;
	DROP TABLE [##FKCommands_Create] ;
PRINT 'Cleaning Up..... [Completed].'

Posted in: SQLServer.

Delete an ASP.Net Account from Membership Provider

May 21st, 2014
by Todd.
No comments yet

The following is one method for deleting users accounts / information from an ASP.Net Membership Provider database.

Step 1 – Find the account.

?View Code TSQL
1

SELECT * FROM [dbo].[aspnet_Users] WHERE [UserName] LIKE '%TestUser%' ;

FindAspNetUser

Step 2 – Delete the account.

?Download DeleteASPNETAccount_SQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Set the target Username.
DECLARE @myUserName NVARCHAR(256);
SELECT  @myUserName = 'TestUser';
 
-- Find the Application Name for the user.DECLARE @myApplicationName NVARCHAR(256);
SELECT  @myApplicationName = [a].[ApplicationName]
FROM    [dbo].[aspnet_Applications] [a]
WHERE   [a].[ApplicationId] = 
    ( SELECT [u].[ApplicationId]
      FROM   [dbo].[aspnet_Users] [u]
      WHERE  [u].[UserName] = @myUserName ) ;
 
-- Determine how much to delete.  Sum of all bits (15) deletes all user information.
--     1 = aspnet_membership
--     2 = aspnet_userinroles
--     4 = aspnet_profile
--     8 = aspnet_users
DECLARE @myTablesToDeleteFrom INT ;
SELECT  @myTablesToDeleteFrom = 15 ; 
 
-- Delete.  The output from the stored procedure will indicate how many tables information 
--    was removed from.
DECLARE @myNumTablesDeletedFrom INT ;
EXECUTE [dbo].[aspnet_Users_DeleteUser] @myApplicationName
,                                       @myUserName
,                                       @myTablesToDeleteFrom
,                                       @myNumTablesDeletedFrom OUTPUT
;
SELECT [NumberOfTablesDeletedFrom] = @myNumTablesDeletedFrom  ;

DeleteAspNetUser_Result

Posted in: ASP.NET, SQLServer.

TFS 2010 Database + Automated Builds = Large Database

May 19th, 2014
by Todd.
No comments yet

The project team was using TFS2010 and running nightly automated builds causing extra growth in their TFS SQL Server database. 

In researching why this database was so large I came across a MSDN blog by Buck Hodges discussing how to clean up test attachment data.

His blog post pointed me to the Test Attachment Cleaner of Visual Studio located here in the visual studio gallery.

The tool works great – and enabled us to reduce the size of the database in question by well over 90%.  The team isn’t doing automated builds at the current time – but if they were – or if they start up again – this tool could be run periodically to keep growth in check.

The Test Attachment Cleaner Tool installed includes some sample settings files (XML) that are placed in the install directory for reference.  Review those samples for to see ways you can use the tool to clean out your database(s).

Test Attachment Cleaner – Sample Command Lines

  1. Get Help
      ?View Code TEXT
      1

      C:\Program Files (x86)\Microsoft\Test Attachment Cleaner\tcmpt.exe
  2. Preview Mode
      ?View Code TEXT
      1

      c:\Program Files (x86)\Microsoft\Test Attachment Cleaner\tcmpt.exe attachmentcleanup /collection:&quot;http://MyTeamProjectCollectionURL&quot; /settingsFile:myScenarioFile.xml /teamProject:MyTeamProject /mode:Preview

After reviewing the results of the preview the command can be executed “for real” by changing the mode to “/mode:Delete”.

Posted in: TFS.

TSQL CTE For DNN SiteLog Statistics

Nov 26th, 2012
by Todd.
No comments yet

DotNetNuke is an open source content management system / application development framework that leverages ASP.NET / Microsoft.NET. The DNN Database schema contains the SiteLog table where all page hits are logged. Every visit to the DNN site results in a row being inserted into the SiteLog table. Depending on how busy the site is this table can get quite large.

SiteLogTableProperties

The image above shows a sample SiteLog table with nearly 2 million rows and over 1 GB in size. In contrast the entire DNN database including the SiteLog table is about 1.4 GB.

Joining the data in the SiteLog table with the Tabs table or better yet the vw_Tabs view allows for the analysis of hit statistics per tab/page. Note that query performance could be greatly improved if appropriate indexes on the SiteLog table existed; particularly on the DateTime and TabId columns. However, since this is a write-heavy table, performance of the site could be affected negatively if additional indexes are added to the SiteLog table. (Remember that every insert to the table would need to update any indexes as well which could be a performance hit depending on how busy the site is.)

Since the SiteLog table is written to for every site hit directly querying the SiteLog table, especially during times where the site is busy, can degrade performance that will manifest in blocking of the query.  Use caution when querying the SiteLog table on a production system. With that background and associated cautions out of the way – let us look at a tsql script that queries the DNN SiteLog using a CTE. (MSDN CTE Documentation)

Three parameters need to be set at the beginning of the script: @BeginDate, @EndDate, and @TabId.  The script starts by creating a temporary table, #HitCount, containing each TabId, along with Hit Count, and Unique Hit Count from the SiteLog table where the DateTime column values are GTE @BeginDate and LT @EndDate.  The CTE that follows can then join on the temporary table instead of the live SiteLog table improving performance.  The CTE utilizes a third parameter, @TabId, to determine the starting point for recursion.

?Download TSQL_CTEForDNNSiteLogStatistics_sql.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

 
USE [DotNetNuke]
GO
 
DECLARE @BeginDate DATETIME ;
DECLARE @EndDate DATETIME ;
DECLARE @TopTabId INT ;
 
SET @BeginDate = '01-Oct-2012' ; -- Change this to the desired Begin Date.
SET @EndDate   = '01-Nov-2012' ; -- Change this to the desired End Date.
SET @TopTabId  = 54 ; -- Change this to the desired starting / top Tab Id.
 
SELECT  [sitelog].[TabId] ,
        COUNT([sitelog].[SiteLogId]) AS [HitCount] ,
        COUNT(DISTINCT [sitelog].[UserHostAddress]) AS [UniqueHitCount]
INTO    [#HitCount]
FROM    [dbo].[sitelog] (NOLOCK)
WHERE   [sitelog].[DateTime] >= @BeginDate AND [sitelog].[DateTime] < @EndDate
GROUP BY [sitelog].[TabId]
ORDER BY [sitelog].[TabId] ASC ;
 
WITH    MenuTabs ( [TabId], [TabName], [ParentId], [MenuLevel], [Level], [Title], [TabPath], [HasChildren], [IsVisible], [HitCount], [UniqueHitCount] )
          AS ( SELECT   [VT1].[TabId] ,
                        [VT1].[TabName] ,
                        [VT1].[ParentId] ,
                        0 AS [MenuLevel] ,
                        [VT1].[Level] ,
                        [VT1].[Title] ,
                        [VT1].[TabPath] ,
                        [VT1].[HasChildren] ,
                        [VT1].[IsVisible] ,
                        [H1].[HitCount] ,
                        [H1].[UniqueHitCount]
               FROM     [dbo].[vw_Tabs] [VT1]
                        INNER JOIN [#HitCount] [H1] ON [VT1].[TabId] = [H1].[TabId]
               WHERE    [VT1].[TabId] = @TopTabId
                        AND [VT1].[IsVisible] = 1
               UNION ALL
               SELECT   [VT2].[TabId] ,
                        [VT2].[TabName] ,
                        [VT2].[ParentId] ,
                        [M].[MenuLevel] + 1 ,
                        [VT2].[Level] ,
                        [VT2].[Title] ,
                        [VT2].[TabPath] ,
                        [VT2].[HasChildren] ,
                        [VT2].[IsVisible] ,
                        [H2].[HitCount] ,
                        [H2].[UniqueHitCount]
               FROM     [dbo].[vw_Tabs] [VT2]
                        INNER JOIN [#HitCount] [H2] ON [VT2].[TabId] = [H2].[TabId]
                        INNER JOIN MenuTabs [M] ON [VT2].[ParentID] = [M].[TabId]
               WHERE    [VT2].[IsVisible] = 1
             )
    SELECT  [TabId] ,
            [TabName] ,
            [ParentId] ,
            [Level] ,
            [Title] ,
            [TabPath] ,
            [HasChildren] ,
            [IsVisible] ,
            [HitCount] ,
            [UniqueHitCount]
    FROM    [MenuTabs]
    ORDER BY [TABPATH] ;
 
DROP TABLE [#HitCount] ;

If repeated queries of the data are desired; a better idea may be to offload / transform the data from the SiteLog table periodically into another table where additional indexes can be added. A follow-up post will explore options along these lines.

* Note that my experiences with DNN and the corresponding data structures are related to version 5.x.  Version 6.x is in mainstream release and version 7.0 is in beta at the time of this writing.

“DNN is a leading Web Content Management Platform (or CMS) for Microsoft, powering over 700,000 production web sites worldwide. The flexible DNN open source CMS platform also functions as a web application development framework. Depending on your role within your organization, DNN provides powerful benefits to support your Web initiatives.” http://www.dotnetnuke.com/

Posted in: DotNetNuke, SQLServer.

Account Lockout Notification – Asp.Net Membership Provider

Nov 16th, 2012
by Todd.
No comments yet

A while back I was working with an Asp.Net Application that leveraged the Asp.Net Membership Provider as the basis for its security.  The Asp.Net Membership provider has a security mechanism that locks out accounts after a specified / configurable amount of failed logins within a period of time.

While this is good for security – it can be confusing for support / helpdesk staff that take a call from an end user who is having trouble logging into the application – mainly because they are unaware that there is an account lockout situation involved.

In order to give the support / helpdesk staff direction an “after update” trigger on the aspnet_Membership table was written that emails the helpdesk any time an account becomes locked out.  This way when the user calls in they know that the solution starts with unlocking the account.

Here is the code for the trigger.

?Download trg_ASPMembershipAfterUpdate_SQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

CREATE TRIGGER [dbo].[trg_aspnet_MembershipAfterUpdate] ON [dbo].[aspnet_Membership]
    AFTER UPDATE
AS
  DECLARE @OldIsLockedOut    BIT ;
  DECLARE @NewIsLockedOut    BIT ;
  DECLARE @EmailRecipients   NVARCHAR(MAX) ;
  DECLARE @EmailCCRecipients NVARCHAR(MAX) ;
  DECLARE @EmailSubject      VARCHAR(100) ;
  DECLARE @EmailContent      VARCHAR(100) ;
  DECLARE @EmailFormat       VARCHAR(20) ;
  DECLARE @EmailImportance   VARCHAR(6) ;
  DECLARE @UserName          NVARCHAR(256) ;
 
  IF UPDATE(IsLockedOut)
    BEGIN
      SET @EmailRecipients   = '' ; -- Email Address Intentionally Removed 
      SET @EmailCCRecipients = '' ; -- Email Address Intentionally Removed 
      SET @EmailFormat       = 'HTML' ;
      SET @EmailImportance   = 'HIGH' ;
 
      SELECT @NewIsLockedOut =  i.IsLockedOut,
             @OldIsLockedOut =  d.IsLockedOut,
             @UserName       =  au.UserName
      FROM   inserted i
      INNER JOIN deleted d on i.UserId = d.UserId
      INNER JOIN dbo.aspnet_Users au on i.UserId = au.UserId ;
 
      IF (@NewIsLockedOut) = 1 AND (@OldIsLockedOut) = 0
        BEGIN
          SELECT @EmailSubject = @@servername + ' - Portal Application Account (' + @UserName + ') Lock Out' ;
          SELECT @EmailContent = 'Portal Application User ' + @username + ' has been locked out.' ;
          EXECUTE msdb.dbo.sp_send_dbmail @recipients      = @EmailRecipients,
                                          @copy_recipients = @EmailCCRecipients,
                                          @subject         = @EmailSubject,
                                          @body            = @EmailContent,
           	                          @body_format     = @EmailFormat,
                                          @importance      = @EmailImportance ;
        END
    END
RETURN

Posted in: ASP.NET, SQLServer.

Linked Server Connectivity / Availability

Nov 9th, 2012
by Todd.
No comments yet

A query that attempts to access data from a linked server intermittently fails. The Linked Server is configured properly and the query that utilizes the Linked Server has been tested and verified previously.

Try / Catch Logic can be used to trap the failure – but wouldn’t it be great if there was a way to be able to test the Linked Server in order to know that it was available before attempting to connect to it?

There Is !  Best of all, unlike other methods I have seen it does not require the use of xp_cmdshell.

Introduced in SQL 2005, sp_testlinkedserver tests the connection to a linked server.  Although the documentation shows that the return code values are 0 (success) or 1 (failure), at this time a failure will always be catastrophic and a return code will not be set.

According to this Microsoft Connect Item, the only update planned for this issue is to fix the documentation to note the behavior.  Ideally the return values would work as indicated – so feel free to go to the Microsoft Connect Site and make your opinion known if you agree.

The sample code below uses sp_testlinkedserver and should work with both the current implementation of the procedure and in the case that the procedure is updated to return the values as currently documented.

?Download LinkedServerTest_SQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

BEGIN TRY
      DECLARE @LSIsAvail BIT ;
      EXECUTE @LSIsAvail = sp_testlinkedserver @servername = N'MyLinkedServer' ;
END TRY
BEGIN CATCH
      -- Logic to handle Linked Server being unavailable.
      PRINT 'Linked Server is Unavailable' ;
      RETURN ;
END CATCH
 
IF @LSIsAvail = 0 -- Successful/Linked Server Okay
   BEGIN
         BEGIN TRY
               -- Select Data from Linked Server
               SELECT *
               FROM   OPENQUERY(MyLinkedServer, 'SELECT [EmployeeId]
                                                 ,      [FirstName]
                                                 ,      [LastName]
                                                 FROM   [dbo].[Employee]
                                                 ;') ;
         END TRY
         BEGIN CATCH
               -- Logic to handle Linked Server query error.
               PRINT 'Linked Server Query Error' ;
               RETURN ;
         END CATCH ;
   END
      ELSE
   BEGIN
      -- Logic to handle Linked Server being unavailable.
      PRINT 'Linked Server is Unavailable' ;
      RETURN ;
   END

Posted in: SQLServer.