May 28, 2018 at 2:47 am
Hello,
I have a weird situation with one production database. I found it very large when I started working for my company, can't identify when it grew so large and by which operation. It is consisted of three data files, one primary and two secondary data files. The primary data file has around 9 GBs of reserved space out of which 8.6 are used, which seems completely ok. But one of the secondary data files has up to 156 GBs of reserved, I don't know the reason of that - I was not the DBA at that time, and out of those 156 GBs there are just about 20 GBs used.
I need to move this database to another production instance, and I have to make it normal in size in that process. I was looking for a solution to release the reserved space to the OS while making the migration, but still can't find he most proper one. I want to avoid shrinking at any price, but no other operations I tried releases the space to the OS.
Can you please share some best practices or experiences for such situations?
Thanks in advance
June 5, 2018 at 2:34 pm
tttcoding - Monday, May 28, 2018 2:47 AMHello,I have a weird situation with one production database. I found it very large when I started working for my company, can't identify when it grew so large and by which operation. It is consisted of three data files, one primary and two secondary data files. The primary data file has around 9 GBs of reserved space out of which 8.6 are used, which seems completely ok. But one of the secondary data files has up to 156 GBs of reserved, I don't know the reason of that - I was not the DBA at that time, and out of those 156 GBs there are just about 20 GBs used.
I need to move this database to another production instance, and I have to make it normal in size in that process. I was looking for a solution to release the reserved space to the OS while making the migration, but still can't find he most proper one. I want to avoid shrinking at any price, but no other operations I tried releases the space to the OS.
Can you please share some best practices or experiences for such situations?
Thanks in advance
Hello.
Maybe the other DBA wanted to reserve the space because the Server department wouldn't let him have the space if he wasn't using it so he just did that (Happens where i work), reserved the space to it wouldn't be taken away.
The only way to put it back to normal is deallocating the space with shrink file itself, i don't think there should be any problem since whats allocating the space are just written zeroes..
Before doing it i would recommend you to make a full backup with compression before the shrinking (AND MAKE SURE IT WORKS).
Shrink the data-file and run DBCC CHECKDB in the database to check for any error.
Backup full again and restore database in the new instance and re-run a DBCC CHECKDB to check for any errors.
Greetings.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply