MSSQL Server : Claim DB Space After Deleting Records in Table - Reduce DB Space

Claim DB Space After Deleting Records in Table - Reduce DB Space
Recently I have delete 2 million unwanted records from my sql server database table, what i realise is even after deleting records, space used by database is not reducing.

After browsing help available on Internet, I found out

1) Whenever we delete records from table, sql server doesn't reduce size of database immediately.
2) Even after deleting table , sql server doesn't reduce size of database.
3) Instead of Freeing space for deleted records, sql server marks pages containing deleted records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.

So In order to claim database space after deleting records in Table, go through following steps:

1) Check what is Size of your Database using following command?
Exec sp_spaceused

2) Delete Records from table, If you have already did that skip this step.

3) Run below command to claim unused database space.
DBCC SHRINKDATABASE(0)

DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.

Best Practise to use this command
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
More reading on this command
http://msdn.microsoft.com/en-us/library/ms190488.aspx


Few other things of Interest
If you have Created, Alter or Drop any Database table recently then run below command.
DBCC UPDATEUSAGE(0)

DBCC UPDATEUSAGE(0) - Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

More reading on this command
http://msdn.microsoft.com/en-us/library/ms188414.aspx


Example showing how this command helps me to reduce size of my database after deleting records from table.

1) Take Backup of your Production Database.

2) Take Backup of Table Scripts of your Production Database.

3) Create Test Database in Local Environment

5) Run Tables creation script

6) Restore Production Database to Test Database in local environment

I am assuming you are familiar with above steps, actual steps begin after this.
I am also assuming that you have already deleted unwanted records in table.

7) Check Size of your Database
Exec sp_spaceused



8) Run Update Usage command
DBCC UPDATEUSAGE(0)



9) Check Size of your Database
Exec sp_spaceused



10) Run Shrink Database command
DBCC SHRINKDATABASE(0)


11) Check Size of your Database
Exec sp_spaceused

If everything goes smooth then you would see that your database size is reduced.

MSSQL SQL : UDF | User Defined Functions – Scalar, Table Valued (TVF), MultiStatement (MTVF)

UDF or User Defined Functions are a set or batch of code where one can apply any SQL logic and return a single scalar value or a record set.

According to MS BOL UDFs are the subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. These reusable subroutines can be used as:
– In TSQL SELECT statements at column level.
– To create parametrized view or improve the functionality of in indexed view.
– To define a column and CHECK constraints while creating a table.
– To replace a stored procedures and views.
– Join complex logic with a table where a stored procedure fails.
– Faster execution like Stored procedures, reduce compliation cost by caching the execution query plans.

Apart from the benefits UDF’s has certain limitations:
– Can not modify any database objects, limited to update table variables only.
– Can not contain the new OUTPUT clause.
– Can only call extended stored procedures, no other procedures.
– Can not define TRY-CATCH block.
– Some built-in functions are not allowed here, like:GETDATE(), because GETDATE is non-deterministic as its value changes every time it is called. On the other hand DATEADD() is allowed as it is deterministic, because it will return same result when called with same argument values.

A UDF can take 0 or upto 1024 parameters and returns either a scalar value or a table record set depending on its type.
SQL Server supports mainly 3 types of UDFs:
1. Scalar function
2. Inline table-valued function
3. Multistatement table-valued function

1. Scalar function: Returns a single value of any datatype except text, ntext, image, cursor & timestamp.

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

-- Example: --// Create Scalar UDF [dbo].[ufn_GetContactOrders] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_GetContactOrders](@ContactID int) RETURNS varchar(500) AS BEGIN     DECLARE @Orders varchar(500)       SELECT @Orders = COALESCE(@Orders + ', ', '') + CAST(SalesOrderID as varchar(10))     FROM Sales.SalesOrderHeader     WHERE ContactID = @ContactID       RETURN (@Orders) END   --// Usage: -- Used at COLUMN level with SELECT SELECT ContactID, dbo.ufn_GetContactOrders(ContactID) FROM Person.Contact WHERE ContactID between 100 and 105 -- Output below   -- Used while defining a computed column while creating a table. CREATE TABLE tempCustOrders (CustID int, Orders as (dbo.ufn_GetContactOrders(CustID)))   INSERT INTO tempCustOrders (CustID) SELECT ContactID FROM Person.Contact WHERE ContactID between 100 and 105   SELECT * FROM tempCustOrders -- Output below   DROP TABLE tempCustOrders

Output of both the selects above:
ContactID	OrdersCSV
100		51702, 57021, 63139, 69398
101		47431, 48369, 49528, 50744, 53589, 59017, 65279, 71899
102		43874, 44519, 46989, 48013, 49130, 50274, 51807, 57113, 63162, 69495
103		43691, 44315, 45072, 45811, 46663, 47715, 48787, 49887, 51144, 55310, 61247, 67318
104		43866, 44511, 45295, 46052, 46973, 47998, 49112, 50215, 51723, 57109, 63158, 69420
105		NULL

Note: If this was a temp(#) table then the function also needs to be created in tempdb, cause the temp table belongs to tempdb. The tables in function should also have the database name prefixed, i.e. [AdventureWorks].[Sales].[SalesOrderHeader]

2. Inline table-valued function: Returns a table i.e. a record-set. The function body contains just a single TSQL statement, which results to a record-set and is returned from here.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

-- Example: --// Create Inline table-valued UDF [dbo].[ufn_itv_GetContactSales] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_itv_GetContactSales](@ContactID int) RETURNS TABLE AS RETURN (     SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate],     h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID]     FROM Sales.SalesOrderHeader AS h     JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID     JOIN Production.Product AS p ON p.ProductID = d.ProductID     WHERE ContactID = @ContactID )   --// Usage: SELECT * FROM ufn_itv_GetContactSales(100)

3. Multistatement table-valued function: Also returns a table (record-set) but can contain multiple TSQL statements or scripts and is defined in BEGIN END block. The final set of rows are then returned from here.

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

-- Example: --// Create Multistatement table-valued UDF [dbo].[ufn_mtv_GetContactSales] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_mtv_GetContactSales](@ContactID int) RETURNS @retSalesInfo TABLE (     [ContactID] INT NOT NULL,     [SalesOrderID] INT NULL,     [ProductID] INT NULL,     [Name] NVARCHAR(50) NULL,     [OrderDate] DATETIME NULL,     [DueDate] DATETIME NULL,     [ShipDate] DATETIME NULL,     [TotalDue] MONEY NULL,     [Status] TINYINT NULL,     [SalesPersonID] INT NULL) AS BEGIN     IF @ContactID IS NOT NULL     BEGIN         INSERT @retSalesInfo         SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate],                h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID]         FROM Sales.SalesOrderHeader AS h         JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID         JOIN Production.Product AS p ON p.ProductID = d.ProductID         WHERE ContactID = @ContactID     END     -- Return the recordsets     RETURN END   --// Usage: SELECT * FROM ufn_mtv_GetContactSales(100)

— Output:

TVF & MVF output