Df not updating after delete
The primary key is based on Purchase Order ID and Purchase Order Detail ID and this query is deleting based on the primary key.In the previous two scripts we are deleting 5000 rows at a time, but in some cases that wont avoid blocking issues.
At this point we have seen different options to delete small amounts of data at a time, but we need to figure out a way to accomplish this task without the need to be in front of the computer the entire time. If you perform the delete inside a cursor your transaction log could grow disproportionally, even if your database is using the simple recovery model.
You can use the GO batch terminator with a value to have the statement run over and over again as shown below, but you will need to know how many times the batch needs to execute to delete the data.
Also, since there is not an index on Due Date this can cause blocking issues while this runs.
For the purpose of this tip I used the Purchase Order Detail table of the Adventure Works database and inserted a lot of data. USE [master] GO CREATE DATABASE [Test DB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Test DB_file1', FILENAME = N'E:\MSSQL\Test DB_1.mdf', SIZE = 128MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB) LOG ON ( NAME = N'Test DB_log_file1', FILENAME = N'E:\MSSQL\Test DB_1.ldf', SIZE = 64MB, MAXSIZE = 2048GB, FILEGROWTH = 32MB) GO USE Test DB GO CREATE TABLE[Purchase Order Detail] ( [Purchase Order ID] [int] NOT NULL, [Purchase Order Detail ID] [int] NOT NULL IDENTITY(1, 1), [Due Date] [datetime] NOT NULL, [Order Qty] [smallint] NOT NULL, [Product ID] [int] NOT NULL, [Unit Price] [money] NOT NULL, [Line Total] AS (isnull([Order Qty]*[Unit Price],(0.00))), [Received Qty] [decimal] (8, 2) NOT NULL, [Rejected Qty] [decimal] (8, 2) NOT NULL, [Stocked Qty] AS (isnull([Received Qty]-[Rejected Qty],(0.00))), [Modified Date] [datetime] NOT NULL CONSTRAINT [DF_Purchase Order Detail_Modified Date] DEFAULT (getdate()) ) ON [PRIMARY] GO ALTER TABLE [Purchase Order Detail] ADD CONSTRAINT [CK_Purchase Order Detail_Order Qty] CHECK (([Order Qty]=(0.00))) GO ALTER TABLE [Purchase Order Detail] ADD CONSTRAINT [PK_Purchase Order Detail_Purchase Order ID_Purchase Order Detail ID] PRIMARY KEY CLUSTERED ([Purchase Order ID], [Purchase Order Detail ID]) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Purchase Order Detail_Product ID] ON [Purchase Order Detail] ([Product ID]) ON [PRIMARY] GO USE Test DB; GO INSERT INTO dbo.
Purchase Order Detail ( Purchase Order ID , Due Date , Order Qty , Product ID , Unit Price , Received Qty , Rejected Qty , Modified Date ) SELECT Purchase Order ID , DATEADD( YEAR, 6, Due Date) , Order Qty , Product ID , Unit Price , Received Qty , Rejected Qty , Modified Date FROM Adventure Works2012. Purchase Order Detail a CROSS JOIN sys.objects b CROSS JOIN sys.objects c Suppose we need to delete rows from Purchase Order Detail for any record that has a Due Date prior to January 1, 2014.
The CHAR(13) CHAR(10) will write the output to a new line.