Hey Sjoerd,

Thanks for your reply. Unfortunately our applications are all heavily reliant on LINQ-To-SQL and thus on the SqlConnection .NET class which does not accept the Provider or Driver connection...

I'm currently experiencing exactly the same problem with a mirrored and witnessed SQL Server 2008 (non R2) mirror. As the principal disappears, either the box (via a network cable pull...

Bump for the possibility of at least 1 response. Sorry!

Thanks Chaps. The moving the head forward to flush then back to use up the preallocated, but now unused, space makes sense. 🙂

Thank Perry. I have read that. But it still doesn't explain why 2 x backups are required, if anything that article insinuates that 1 backup should be enough! "When the...

TRUNCATEONLY will never increase fragmentation as doesn't move any pages. Even so, you should only really do this if you're sure you're not going to fill the space you reclaim...

Trying to replay the SET OFFLINE -> SET ONLINE didn't work second time round! aarrggh!

Back to getting the following from SHRINKFILE

DbIdFileIdCurrentSizeMinimumSize UsedPages EstimatedPages

8.......1......17363872....384............2888816....2885160

132gb of current size vs 22gb of actual...

Secondaries don't have to have the .ndf extension btw. It's just convention 🙂

I suggest you read this:

http://msdn.microsoft.com/en-us/library/ms179316.aspx

Me too Stefan, although I'm still not 100% sure what it was! Guess I'll restore a local backup tomorrow and replay the SET OFFLINE -> SET ONLINE.

Thanks CirquedeSQLeil, I already...

Just thinking about it. I did offline then online the database. Maybe this caused some lock preventing sql server from shrinking the file? I recall reading something about that somewhere...

Hey Stefan. Yes DBCC SHRINKFILE'ing. It just won't drop that space.

I was thinking of creating a new secondary file, adding it to the same filegroup as the primary data file...

Just to add running DBCC CHECKALLOC ends up with:

The total number of extents = 6244877, used pages = 49737236, and reserved pages = 49956438 in this database.

(number of mixed...

1. backup the log

2. DBCC SHRINKFILE([log name here], TRUNCATEONLY)

3. If that didn't affect the log size then back it up again and run the shrink file command again.

4. DBCC SHRINKFILE([data...