CODEWITHSUNDEEP
c#databaselinq-to-sql
Asem
Asem

Reputation: 55

Delete from SQL Server database

I'm using a SQL Server database in my program with Linq-to-SQL

I'm using this code to empty the database

 db.Tb1.DeleteAllOnSubmit(db.Tb1.Select(x => x));
 db.SubmitChanges();

After executing this code, everything in the database will be cleared out. But the problem is the size of database didn't decrease when the rows deleted.

I mean the size of database is still the same after I use that code when it has no data

Sorry for bad English

Upvotes: 2

Views: 280

Answers (2)

Dirk Trilsbeek
Dirk Trilsbeek

Reputation: 6023

The database doesn't shrink its data files whenever rows are deleted. You have to ask the server to shrink its files, for instance with the T-SQL command DBCC SHRINKDATABASE (see MSDN Documentation for DBCC SHRINKDATABASE). Main reason for this behavior is probably the performance impact of a shrink. In addition, the server will reuse the now free pages. For databases that regularly delete large sets of rows or truncate complete tables you can schedule the shrinking as a maintenance task. You should then in addition schedule an update of your database statistics (T-SQL command UPDATE STATISTICS) for that table.

Upvotes: 2

Joel C
Joel C

Reputation: 5567

This is how SQL Server works. The space isn't returned unless you manually run a "Free unused space" operation on the database, which re-organizes how the records are physically stored in your database file. Otherwise the unused space is kept and reused as new records are added.

Upvotes: 3

Related Questions