SQLTeam.com | Weblogs | Forums

SQL Database Size

I have a SQL Server database on a production server. The size of database is growing very fast and now going to reach 40 GB now in one and a half year (Above expectation). I am not database admin but have some relevant responsibilities. I checked the software and database. With my findings, the temp data is being removed and scripts will be modified accordingly to handle this size of data issue.

Now I want to reduce the database size. I tried shrink command to reduce the size but it reduces the database not more than 2 percent.

When I export its data using the Import-Export tool in SQL Server Management Studio, the target database filled up with complete data. And the size of target database reduced dramatically around below 50% (i.e. goes to 14 / 15 GB).

My process for this import export is as below:

Generate scripts for tables/functions/procedures creation from source database
Use those scripts on blank target database to create same tables (with constraints), functions and procedures
Then disable all constraint on target database
Use SQL Server’s Import/Export tool and import data from source to target database
After completion of importing of all data, enable all constraints on target database
Done

This process make the exact replica of source database. The target database however comes with very small in size when I compare it with the source database. After this import/export process, when I remove the temp data, and then shrink the database, the size of database reduced to below 10GB only. (I performed this process on test server)

My question is, can I fully trust this new target database? Can I replace the source database with this target database and use it on production server?

hope the below helps !! :slight_smile:

can I fully trust this new target database?

you will have to do some checks !! or at least your critical functionalities !!!
first check will be ... nothings broken

Can I replace the source database with this target database and use it on production server?
there are several approaches to do this !!
please google search .. LOTS LOTS LOTS LOTS of links

one link is

another link is

another 3rd link is
https://www.cybrary.it/blog/0p3n/best-way-copy-sql-server-database-one-server-another/

What is the initial size of the data files? You can't shrink it lower than that. 40GB is not a large db at all. What was your expectation? Is there any type of data purge. The other difference could be page splits on the prod db.

What is the shrink command that you used? If it used the truncateonly option - that will only release space at the end of the file if it can be released.

Note: after shrinking, all of your indexes will have been fragmented and you will need to schedule a rebuild of them all. This may cause the file to grow back out to the 40GB size it is at now.

Is there an issue or concern with the size - as 40GB is not considered to be a large database.