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.
2
-
First, you should ask a real question! What are you trying to do? 20MB (1MB log) is TINY! So the answer is: you should do nothing.Mitch Wheat– Mitch Wheat2012-01-24 00:35:00 +00:00Commented Jan 24, 2012 at 0:35
-
@MitchWheat I try to asking real question. My problem is my database file is 20 megabytes with 10 tables and 50 Stored procedures while there are no data in tables.Shahin– Shahin2012-01-24 00:38:49 +00:00Commented Jan 24, 2012 at 0:38
Add a comment
|
2 Answers
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.
Comments
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.
4 Comments
Shahin
Thank you. Why when i create back up from database and compress that to zip file the compressed file size is less than 1 MB ? There are twenty tables and each table has about 10 columns.
Mitch Wheat
...because that's what compress does! It makes things smaller!
Remus Rusanu
There are twenty tables and each table has about 10 columns. How about the hundreds of objects in sys.all_objects? And I bet your sys.syscomments is not empty either. How about sys.database_principals, or sys.database_role_members? And more... Your database comes with a lot of bootstrapping.Remus Rusanu
Keep in mind that allocation goes by extents. Backup does not need to transfer allocated but empty pages, and the backup data is highly zip compressible.