0

I have a database with three files

  • Log
  • Primary Data File
  • Blob Data file

the database was becoming too big to manage (600GB) so we removed the blob data (500GB) into FileStorage outside the DB.

we then dropped and recreated the Tables into the Primary datafile, following that we ran

Alter Database (db) Remove_File (Blob_Data)

Then ran

DBCC ShrinkDatabase ('db',10)

. As well as a few other shrinks database properties is still reporting the database size as 600GB.

sys.database_files

still remembers the file but it is in the status of 'Offline',

sys.sysfiles

has no mention of the file.

we are currently rebuilding indexes following the shrink, and intend to test the backup and restore process to see if the speed of backup / restore is affected by this.

is this something we need to be worried about? is there any resolution to this?

  • Try quickly setting the database to offline and then back online again. Of course, if you've deleted the file on the file system and have tested backup / restore, you could also just ignore what sys.database_files has to say (and check again after the next restart / failover). Also don't use sys.sysfiles - while it is correct in this case, it is deprecated and only there for backward compatibility. – Aaron Bertrand Mar 8 '15 at 21:38
  • thanks Aaron whilst we did not try what you suggested above we did come across the following article which explains the exact same issue social.msdn.microsoft.com/Forums/sqlserver/en-US/… it would appear a log backup resolved the issue – RoughPlace Mar 9 '15 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.