0

if i want to shrink one mdf file:

dbcc shrinkfile (N'xy');
go

i got error:

DBCC SHRINKFILE: Heap page 23:878847 could not be moved. The statement has been terminated. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 7105, Level 22, State 9, Line 1 The Database ID 20, Page (23:200140), slot 1 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

But if i run dbcc checkdb or checktable, it doesnt show me any error. Where can be problem? What should i do? Thank you.

Thom A
  • 88,727
  • 11
  • 45
  • 75
naTro
  • 1
  • 1
  • 2
    *"How can i shrink mdf file in SQL Server with error?"* Do you really mean you *want* to get an error when you shrink the file? Generally shrinking the mdf file is a bad idea anyway; it can massively fragment your indexes. – Thom A Nov 07 '19 at 10:41
  • [You shouldn't be messing around with `Shrink` anyway.](https://stackoverflow.com/questions/57178221/sql-windows-os-releasing-unused-space/57179040#57179040) Let the database do it's thing. – Zohar Peled Nov 07 '19 at 10:48
  • my customer wants to shrink it because datafile is 15gb large but used space is just 15%. i wanted to reduce size of it but i cant – naTro Nov 07 '19 at 10:49
  • 15GB is tiny... – Thom A Nov 07 '19 at 10:52
  • ye i fully understand but customer doesn't ... but im afraid because it shows me error in shrink but in checkdb doesnt – naTro Nov 07 '19 at 10:53
  • And just because a Customer says they want something doesn't mean they should, or it's a good idea. This is an example of this. Shrinking an already very small data base is going to achieve other but performance degradation. if they have storage problem, they'll be able to find 10GB of space from elsewhere (probably just my uninstalling an application, or migrating some data files). If the problem really is *that* bad, they should be upgrading their storage. – Thom A Nov 07 '19 at 10:54
  • ok but now dont think about shrink but error which was shown in execute of shrink and dbcc checkdb didnt show it. Do u have any ideas why it is like that? – naTro Nov 07 '19 at 11:33
  • What is your sql server version? SQL 2005 had such error (fixed by now). Can you set database to single user mode and try then again? – Arvo Nov 07 '19 at 14:19
  • I'm getting this error on 2016. Did anyone ever find a solution? – Andrew Lackenby Feb 18 '20 at 10:05

2 Answers2

1

your Must Put Your Database In simple Mode and After That shrink your database and Then put your database in Recovery Full Mode 

Alter Datatbase yourdatabaseName Set recovery simple 
Go
DBCC ShrinkFile (yourdatabaseName ,5)
GO
Alter Database yourdatabaseName  Set Recovery Full
mhd.cs
  • 711
  • 2
  • 10
  • 28
0

May This helps you

USE yourdatabaseName 
GO
DBCC SHRINKFILE (N 'yourdatabaseName', 3)
GO
Nits Patel
  • 380
  • 3
  • 15