I've restored a database from production to QA, on SQL Server 2012. One of the databases has two log files:
logfile.ldf
- 6 GBlogfile2.ldf
- 200 GB
Due to space issues and data not being truncated through the years, there is data dating back from 10 years ago. However, I would like to only retain data from 2015 onwards.
I did the following:
- Set Recovery Mode to Simple;
Issued
DBCC SHRINKFILE
multiple times in this order:200 GB > 100 GB > 50 GB > 25 GB > 12 GB
DBCC SHRINKFILE(EMS_1_4_3_log, 12288); -- unit is set in MBs
Used a table for min and max dates and the dates remain the same.
Questions
Although the shrink was successful, I believe I can shrink it more but I'll stop here. How would I know that it shrunk efficiently?
I didn't use
TRUNCATEONLY
, should I have used this?TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size
is ignored if specified withTRUNCATEONLY
. TheTRUNCATEONLY
option does not move information in the log, but does remove inactive VLFs from the end of the log file. This option is not supported forFILESTREAM
filegroup containers.What does it mean that "it does not move information in the log"?
To get the data dates back to retention period, do I truncate data in the tables themselves? What's a better way of doing this? I would lose the LSN, which we don’t care right now, but I still would like to recover from the retention dates.
How do I consolidate multiple logfiles?