How the unallocated space in SQL Server affects copying to another SQL Server-CodePudding

I am a newbie in SQL Server, I have a task to move the whole SQL Server to another.

I am trying to estimate how much space I need in the new SQL Server.

I ran EXEC sp_spaceused

And the following came up:

When I look into the output, it seems that the Database is using ~122GB (reserved), but when looking in the total database size (mdf ldf) it is ~1.8 TB.

Does that mean when I copy the Database from the existing SQL Server to a new one I will need ~1.8 TBs into the new?

I am thinking about creating a back-up and copy the back-up to the new Server. How does the back-up takes into consideration the unallocated space? Does the back gets closer to the reserved or the database_size? I understand that this is without taking into consideration the uncompressed in the back-up, which will improve the file size.

Thx for the help.

CodePudding user response：

The backup file will be much smaller than 1.8TB, since unallocated pages are not backed up. But the log and data files themselves will be restored to an identical size, so you will need 1.8TB on the target in order to restore the database in its current state.

Did you check to see if your log file is large due to some uncontrolled growth that happened at some point and is maybe no longer necessary? If this is where all your size is, it's quite possible you can fix this before the move. Make sure you have a full backup and take at least one log backup, then use DBCC SHRINKFILE to bring the log file back into the right stratosphere, especially if it was caused by either a one-time abnormal event or a prolonged log backup neglect that has now been addressed.

I really don't recommend copying/moving the mdf/ldf files (background) or using the SSMS UI to "do a copy" since you can have much greater control over what you're actually doing by using proper BACKUP DATABASE and RESTORE DATABASE commands.

How do I verify how much of the log data is being used?

If you're taking regular log backups (or are in simple recovery), it should usually be a very small % of the file. DBCC SQLPERF(LogSpace); will tell you % in use for all log files.

To minimize the size that the log will require in the backup file itself, then:

if full recovery, back up the log first.
if simple recovery, run a couple of CHECKPOINT; commands first.