| In 2012, how to create a fully contained database? Posted: 11 Jul 2013 12:21 AM PDT based on a previous post here, someone had a quesiton on Contained databases, so i decided to create one myself to follow along.BOL says the settings for the database containment can be NONE, PARTIAL or FULL.When you create a database via the GUI, you only get a selection for PARTIAL or NONE.so how do you create a fully contained database?[img]http://www.sqlcoffee.com/images/SQLServer2012_0009_0003.PNG[/img]if i use the scripting option, and try to modify the script to say FULL, i get a syntax error.[quote][color="#FF0000"]Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'FULL'.Msg 911, Level 16, State 1, Line 1Database 'FullyContained' does not exist. Make sure that the name is entered correctly.[/color][/quote][code]CREATE DATABASE [FullyContained] CONTAINMENT = FULL ON PRIMARY ( NAME = N'FullyContained', FILENAME = N'F:\SQLData\SQL2012\FullyContained.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )by the way, my Google-Fu is strong, and everything i see in BOL so far shows changing a database from None to Partial, but nothing for Partial to FULL or None to FULL LOG ON ( NAME = N'FullyContained_log', FILENAME = N'F:\SQLData\SQL2012\FullyContained_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)[/code] |
| How to overcome a Deadlock specifically a Key Lock Posted: 10 Jul 2013 07:54 AM PDT Legend:Storefront = the glass & entry door portion of the front of a store like 7-11Bay = the different section of glass that are divided by aluminum verticals that span from the ground to the ceilingHorizontal = A piece of aluminum that spans from the left to right of the inside of a bay.I've got an application that deletes a record from time to time just like what a database does and everything is fine.But I've got a case now where we've added a feature to where 1 - n many of rows could be deleted. What my app is doing is calling a static method for each newly deleted item because that is the way the client is sending the request to my server.It's a loop that does a delete request for each removed / deleted item from the client, and when these request arrive at the server, they arrive one at a time in order, so my static delete method is called for each newly delete request; therefore, it creates a new process (SPID) for each time my stored procedure is called to delete that row/ record, and this leads to a key deadlock.Keep in mind my fellow professionals I'm fairly good at SQL but I'm no guru; however, if someone can point me in the right direction I would be grateful!Let me give you some background on what I'm developing. I working on a CAD program that the CAD UI is written JS, so my brain is already mushy!For this specific situation here is what is happening. I'm allowing the user to add a door to a bay that has horizontals lower than what the door is in height; therefore, the new door covers up the existing horizontals. So, the solution is to remove all horizontals from a bay that are lower to the ground than what a door is in height.[b]Here is a portion of what the CAD UI looks like. I've got the steps in order.[/b]1.) A bay with horizontals that are lower than what the doors height is2.) The door is added to the bay and everything looks as if the horizontals were removed.3.) I removed the door from that bay and the horizontals are still in place, they were never moved because the deadlocks would not allow it.[b]A snap shot reflecting adding a door to the bay with horizontals and then removing the door to see no changes ever took place because of deadlocks[/b][img]http://www.alumcloud.com/h/deadlock-keylock-help.png[/img][b]Diagram of the important portion of this deadlock[/b][img]http://www.alumcloud.com/h/deadlock-keylock-diagram.png[/img][b]A few of the deallock graphs that I was able to capture.[/b][img]http://www.alumcloud.com/h/deadlock-keylock-graph.png[/img][b]The script relevant to this question[/b][url=http://www.alumcloud.com/h/deallock-keylock-script.txt][/url]I've got to get this resolved extremely quickly so any and all suggestions would be great.I'm needing to know how to keep sql from deadlocking when a batch of deletes need to take place on one table.------------------------------------------------------------------------------------------------------Here is the script in text format rather that that link.USE [StorefrontSystem]GO/****** Object: StoredProcedure [Storefront].[proc_DeleteHorizontal] Script Date: 7/10/2013 3:16:10 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [Storefront].[proc_DeleteHorizontal]@HorizontalID INTASSET NOCOUNT ON;BEGIN BEGIN TRAN DeleteHorizontal DECLARE @SizeID INT, @FinishID INT, @SitelineID INT; SELECT @SizeID= h.SizeID, @FinishID = h.FinishID, @SitelineID = h.SitelineID FROM Storefront.Horizontal h with(nolock) WHERE h.ID = @HorizontalID DELETE FROM [Storefront].[Horizontal] WHERE [Storefront].[Horizontal].ID = @HorizontalID; DELETE FROM Storefront.Size WHERE ID= @SizeID; DELETE FROM Storefront.Finish WHERE ID= @FinishID; DELETE FROM Storefront.Siteline WHERE ID= @SitelineID; COMMIT TRAN DeleteHorizontalEND;SET NOCOUNT OFF;GO/****** Object: Table [Storefront].[Finish] Script Date: 7/10/2013 3:16:10 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Storefront].[Finish]( [ID] [int] IDENTITY(23,1) NOT NULL, [Left] [varchar](30) NULL, [Right] [varchar](30) NULL, [Top] [varchar](30) NULL, [Bottom] [varchar](30) NULL, [Type] [varchar](8) NOT NULL, [Note] [varchar](150) NULL, CONSTRAINT [PK_Finish_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** Object: Table [Storefront].[Horizontal] Script Date: 7/10/2013 3:16:11 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Storefront].[Horizontal]( [ID] [int] IDENTITY(11,1) NOT NULL, [Position] [tinyint] NOT NULL, [Name] [varchar](15) NULL, [Floor] [decimal](8, 4) NOT NULL, [SizeID] [int] NOT NULL, [IsFiller] [bit] NOT NULL, [FinishID] [int] NOT NULL, [SitelineID] [int] NOT NULL, [Note] [varchar](150) NULL, CONSTRAINT [PK_Horizontal_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** Object: Table [Storefront].[Siteline] Script Date: 7/10/2013 3:16:11 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [Storefront].[Siteline]( [ID] [int] IDENTITY(1,1) NOT NULL, [LeftCompID] [int] NULL, [RightCompID] [int] NULL, [TopCompID] [int] NULL, [BottomCompID] [int] NULL, [Top] [decimal](6, 4) NULL, [Bottom] [decimal](6, 4) NULL, [Left] [decimal](6, 4) NULL, [Right] [decimal](6, 4) NULL, CONSTRAINT [PK_Siteline_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [Storefront].[Size] Script Date: 7/10/2013 3:16:11 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Storefront].[Size]( [ID] [int] IDENTITY(231,1) NOT NULL, [HeightFeet] AS ([HeightInches]/(12)) PERSISTED, [HeightInches] [decimal](8, 4) NOT NULL, [HeightPercent] [decimal](7, 4) NOT NULL, [HeightPixels] AS (([HeightInches]/(12))*(25)), [WidthFeet] AS ([WidthInches]/(12)) PERSISTED, [WidthInches] [decimal](8, 4) NOT NULL, [WidthPercent] [decimal](7, 4) NOT NULL, [WidthPixels] AS (([WidthInches]/(12))*(25)), CONSTRAINT [PK_Size_ID] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [Storefront].[Size] SET (LOCK_ESCALATION = DISABLE)GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Left] DEFAULT (' ') FOR [Left]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Right] DEFAULT (' ') FOR [Right]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Top] DEFAULT (' ') FOR [Top]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Bottom] DEFAULT (' ') FOR [Bottom]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Type] DEFAULT ('Anodized') FOR [Type]GOALTER TABLE [Storefront].[Finish] ADD CONSTRAINT [DF_Finish_Note] DEFAULT (' ') FOR [Note]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Position] DEFAULT ((0)) FOR [Position]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Floor] DEFAULT ((0)) FOR [Floor]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_IsFiller] DEFAULT ((0)) FOR [IsFiller]GOALTER TABLE [Storefront].[Horizontal] ADD CONSTRAINT [DF_Horizontal_Note] DEFAULT (' ') FOR [Note]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Left] DEFAULT ((0)) FOR [LeftCompID]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Right] DEFAULT ((0)) FOR [RightCompID]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Top] DEFAULT ((0)) FOR [TopCompID]GOALTER TABLE [Storefront].[Siteline] ADD CONSTRAINT [DF_Siteline_Bottom] DEFAULT ((0)) FOR [BottomCompID]GOALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Finish_FinishID] FOREIGN KEY([FinishID])REFERENCES [Storefront].[Finish] ([ID])GOALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Finish_FinishID]GOALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Siteline_SitelineID] FOREIGN KEY([SitelineID])REFERENCES [Storefront].[Siteline] ([ID])GOALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Siteline_SitelineID]GOALTER TABLE [Storefront].[Horizontal] WITH CHECK ADD CONSTRAINT [FK_Horizontal_Size_SizeID] FOREIGN KEY([SizeID])REFERENCES [Storefront].[Size] ([ID])GOALTER TABLE [Storefront].[Horizontal] CHECK CONSTRAINT [FK_Horizontal_Size_SizeID]GO |
