CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Shahin
Shahin

Reputation: 12843

SQL Server Reduce Database File Size

I have a SQL Server 2008 R2 Database with twenty tables and each table has about 10 columns. There are no data in it and all tables are empty but the database size is 20 megabytes. I tried to shrink and deleting log file but they didn't work. Log file size is 1 megabytes and database file size is 19 megabytes . What should I do ? I want to delete unused spaces.

Upvotes: 1

Views: 3694

Answers (2)

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Schema will take up some space, but 20MB is a lot. I've created a small test, added 50 tables with 10 columns...and nothing, data size is the same as it was when I created a database (2048 kB). Then, I took database with fewer number of tables (7), deleted all records and did Shrink->Database from managament studio, and data file shrinked to 2048kB. Are you sure that your database file is 20MB, is it 2MB maybe? Also, you said that you have 50 stored procedures. I would try deleting them and then shrinking the database. Also, I would double check all of my tables, because it might happen that there is still data in some of them. Anyway, you are right, if there is nothing in the tables, and you don't have that many stored procedures, your data file should go down to 2MB.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294197

How big is your model database? Your database will usually not shrink bellow model size. You also say that all tables are empty which implies that there are tables defined, so you must have some storage needed just to store the definitions. 20 MB is really nothing.

Upvotes: 2

Related Questions