ASP.Net, C#, SQL, JQuery, Sharepoint: February 2011

Tuesday, February 08, 2011

Delete All Stored Proc from SQL Server DB

If you need to delete all stored proc from sql server db, following script would be useful.

I have found this useful script from Mattberther's Blog

I have observed that his script won't delete stored procedure which has space in it.
Example: If stored procedure name is like "Category Insert" i.e. Procedure which has space in its name.

I have make line bold wherein i have add bracket to support this.

declare @procName sysname

declare someCursor cursor for
   select name from sysobjects where type = 'P' and objectproperty(id, 'IsMSShipped') = 0

open someCursor
fetch next from someCursor into @procName
while @@FETCH_STATUS = 0
begin
   exec('drop proc [' + @procName + ']')
   fetch next from someCursor into @procName
end

close someCursor
deallocate someCursor
go

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