How did you figure out the size of the database? What are the file sizes of MDF and LDF files?
What do
exec sp_spaceused
report?
Backup files can be compressed and so much smaler in size then the database.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi I have SQL DB whose size is 20 GB if i add tables sizes. But despite of shrinking of DB it says size on Disk allocated is 90GB approx. I see no reason why it will take that much space on Disk. When i backup it is of 12 GB only but while restoring it ask for more space something 90 GB. I am not sure what all causing this. I need some help urgently as i need to restore it on VPS which have less space but enough for DB if it show correct size based on Table Rows Sise.
How did you figure out the size of the database? What are the file sizes of MDF and LDF files?
What do
exec sp_spaceused
report?
Backup files can be compressed and so much smaler in size then the database.
So it is the log file that is big, not the data file. The log file has no relation to the size of the data in the tables. The size of the log file depends on what operations you run. Let's say that you do:
BEGIN TRANSACTION
UPDATE tbl SET ...
DELETE tbl ...
INSERT tbl ..
COMMIT TRANSACTION
And all these operations affect the better part of your 20 GB table, these rows has to be logged in the log file, and the log file can be truncated beyond the oldest active transaction. And for that matter, it cannot be truncated if you are in full recovery and you don't take transaction log backs.
It is a bit unusual, though, that the log file exceeds the size of the data file, and even more so with a factor of six. So you could try to shrink it with DBCC SHRINKFILE. But if you have not backed up your transaction log (and you are in full recovery), it will have no effect.
In any case, don't delete the LDF file - that is likely to render the database unsuable..
Hi i deleted LDF file which was 78 GB it self and attached new one. But i don't think this is right.
This is size i am getting
M1_F2 12951.56 MB 80.92 MB
But i dont want to do on production server as ifi detached DB it can cause issue.
But i don't think this is right.
You are right, it isn't right and can cause issues. It should be possible to shrink a database/log file with DBCC SHRINKFILE (Transact-SQL)