This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 23%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDK%3C/text%3E%3C/svg%3E)
Hi Experts,
we have built a database with ssis packages on Dev which sized 29 gb and recovery model in simple and moved to prod and scheduled to run once in every day. after couple of months we found db size is around 300 gb in PROD with same data as in DEV (300 gb vs 30 gb) with prod database recovery model in FULL and every hour logbackups been taken with 1 full backup per day. can anyone help me what is going on , the goal is get DB same sized as dev.
I am pretty sure the latency between DEV and PROD is one day. why the size vary that much. also noticed every day db size growing by around 5 to 16 GB. please help.
Thanks,
ADKR
What does
exec sp_spaceused;
return for that dev & prod database return?
Can you run this query use db_name go; SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files; in both the databases (prod and dev) and post the output here
Hi @dileep kumar ,
Run code as next and post the output here:
--1.Check the log current usage and database status.
DBCC SQLPERF(LOGSPACE)
GO
SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc
FROM sys.databases
GO
BR,
Mia
CIMDataMart FULL 0 NOTHING
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
Perhaps you have heap tables? SQL server is known for being bad at releasing space for heaps.
we have lot of heap tables( without indexs) both in dev and prod, but dev is fine where as prd db growing excepetnally.
Check out the size of the tables. You can for instance use my sp_tableinfo (https://karaszi.com/articlesandutilities), or some other means. If you find the heap tables seems unseasonable regarding size, then either don't have them as heaps or rebuild them (ALTER TABLE REBUILD) to reclaim space. You can have an insanely amount of free space for heaps. And don't expect your dev to behave same as prod. I've seen dev environments with reasonable sized heaps and prod environments where heaps has a ridiculous size.
Regarding data size, if it isn't heaps, then you just have to bite the bullet and monitor the larger table to see what is growing, in order to have a chance to determine why.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Is it the data file or the log file which is growing? Also, please post the results of SELECT @@VERSION.
Are you running replication or AlwaysOn on the database?
both files are growing,
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
yes , the databases is in Always on, we also have configured logbackup(oce in hour) and full backups(once in day)
It likely does not affect your problem, but you are running the original RTM version of SQL 2017. There have been many updates since that version was released and you should update. https://support.microsoft.com/en-us/help/4047329/sql-server-2017-build-versions
Hi @dileep kumar ,
Any updates?
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.