Hello,

  I have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.

Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?

Regards,

Is it best practice to shrink the datafiles?

Database shrinking is a BAD practice. Please take a look at this URL:

Stop Shrinking Your Database Files. Seriously. Now.

No, it is not best practice to shrink the database.

you said - everyday before loading table gets truncate - 

that mean after truncation and data is being inserted again  - which requires room for the data - so, why are you shrinking the database in first place??

How much space are going to reclaim by shrinking. There are tons of articles of web - that advice not to do shrink.

If this is a process you are planning to set up on every day basis - do not worry on it- please do not do this set up

even if it is one time deal, since are loadin the data backup again, it is not worth for you to do shrink 

if you know it will grow again better to put it in a dedicated lun and leave it, if you decided to shrink you can schedule a job and shrink it in batches.

USE DB-A;
GO
DBCC SHRINKFILE (DB-A_Data, 469999);
GO
DECLARE @value INT = 100, --100 GB
@filesize INT = 469999 --469 GB

WHILE @value<>0
BEGIN
SET @filesize=@filesize-5000;
DBCC SHRINKFILE (DB-A_Data, @filesize);
WAITFOR DELAY '00:02:00'; -- wait for 2 minutes 
SET @value =@value-5
END

• Proposed as answer by ashwin08 12 hours 51 minutes ago

SELECT [log_reuse_wait_desc] 	FROM [master].[sys].[databases] 	WHERE [name] = N'dbname';
what is result shows

Hello,

Never shrink the Data files, this will lead problems with index fragmentation. Either please add the secondary data files on the new drive or while loading bulk data keep the recovery model in bulk logged.

As per the description the DB is already in simple recovery mode so there is no room for log file to grow heavily, the data file as per your lines its truncating and loading back.

Is the amount of data truncated and loading is same?

If this is growing day by day please plan for data archival. Or need to add storage and make the file groups.

Hello,

  I have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.

Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?

Regards,

I cannot understand your motive of shrinking you want to shrink database after truncate command is complete ? Why you want to shrink actually are you facing space issue.

As already mentioned shrinking creates massive logical fragmentation this would affect query performance are you willing to take this risk