February 8, 2011 at 5:43 am
Folks,
I need to transfer a backup of our SQL Server database to our supplier. I deleted the attachments (stored in an image field in the database) from two tables, dropped 2 tables with session info that's not needed, and tried to shrink the data files before the backup, so that the supplier doesn't need much space to restore. Problem is that the shrinking isn't working, and I recall a note from MSDN (unfortunately, I can't find that note anymore). that I can't shrink because the tables with the image fields is still holding the extents.
What do I need to do to shrink the data files ?
TIA,
Dave
February 8, 2011 at 7:06 am
Thanks for the question, I haven't encountered this before. Found a related link.
http://www.tek-tips.com/viewthread.cfm?qid=1186640&page=189
Hope this helps.
M&M
February 8, 2011 at 7:25 am
That's exactly why DBCC CLEANTABLE was created.
Look it up in BOL before using it.
February 8, 2011 at 8:16 am
Forgot to mention that I Already tried DBCC CLEANTABLE, also did a rebuild of all the indexes, etc, etc...
Shrink still doing anything :crying:
February 8, 2011 at 8:26 am
Ya I had problems to shrinking my logs in live environement. Maybe this can work for you too.
I'd change shrink log file to data file. Tho I'm not too optimistic that it will work for you.
USE [master]
GO
ALTER DATABASE [<db name here>] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [<db name here>] SET RECOVERY SIMPLE
GO
CHECKPOINT
GO
USE [<db name here>]
GO
DBCC SHRINKFILE (N'<db name here>_Log' , 7500)
GO
USE [master]
GO
ALTER DATABASE [<db name here>] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [<db name here>] SET RECOVERY FULL
GO
--restart backup chain to be able to restore.
EXEC msdb.dbo.sp_start_job @job_name = 'Backup <db name here> NEW'
February 8, 2011 at 8:31 am
This sounds very much like the issue SQL Server 2000 had with not reclaiming space for tables with text/image columns.
The only solution I found to that problem was to add another file to the database, move the data to the new file, move it back to the original file and then delete the new file. The action of moving the data frees up the wasted space for reuse.
ALTER DATABASE x ADD FILE(name=new data file...
DBCC SHRINKFILE ('old data file','EMPTYFILE')
DBCC SHRINKFILE ('new data file',EMPTYFILE)
ALTER DATABASE x REMOVE FILE new data file...
February 8, 2011 at 8:33 am
Ian Scarlett (2/8/2011)
This sounds very much like the issue SQL Server 2000 had with not reclaiming space for tables with text/image columns.The only solution I found to that problem was to add another file to the database, move the data to the new file, move it back to the original file and then delete the new file. The action of moving the data frees up the wasted space for reuse.
ALTER DATABASE x ADD FILE(name=new data file...
DBCC SHRINKFILE ('old data file','EMPTYFILE')
DBCC SHRINKFILE ('new data file',EMPTYFILE)
ALTER DATABASE x REMOVE FILE new data file...
I'll add my vote to this one. I've read a similar discussion before and AFAIK it solved it.
I like it better than my idea.
February 11, 2011 at 8:48 am
Ninja's_RGR'us (2/8/2011)
Ian Scarlett (2/8/2011)
This sounds very much like the issue SQL Server 2000 had with not reclaiming space for tables with text/image columns.The only solution I found to that problem was to add another file to the database, move the data to the new file, move it back to the original file and then delete the new file. The action of moving the data frees up the wasted space for reuse.
ALTER DATABASE x ADD FILE(name=new data file...
DBCC SHRINKFILE ('old data file','EMPTYFILE')
DBCC SHRINKFILE ('new data file',EMPTYFILE)
ALTER DATABASE x REMOVE FILE new data file...
I'll add my vote to this one. I've read a similar discussion before and AFAIK it solved it.
I like it better than my idea.
Well, count me in too! Adding a datafile and then shrinking with the EMPTYFILE option did the trick!
I had four datafiles in the filegroup, so I added a new datafile and did a DBCC SHRINKFILE ('old data file','EMPTYFILE') on all the existing datafiles. And in the end I could not only remove the new datafile but also three of the four existing datafiles (actually, I had to remove the files cause I got an error with shrinking : "File ID 3 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty"). The EMPTYFILE option probably moves all the data to the first datafile instead of round robin (didn't know that!).
Anyway, Thanks all for helping me on this one! 🙂
Dave
February 13, 2011 at 10:59 am
are you deleting all the data in the table or just image columns, truncate gets around this issue but does remove all data. This article may also explain a little more
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply