Sql-server – Table size increasing drastically in sql server

The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

This is fin as long as you are doing regular full and log backups to keep the log file size under control. There are plenty of articles and questions on here regarding log management.

In the beginning of this summer the database was about 5GB. Since then, the only thing we did beyond normal usage is some extensive deletions.

Large deletions are fine but understand that the space that data used was not released back to the operating system, unless you did a shrink of the data file.

This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

I hope you tested your code! T-SQL syntax changed a good bit from SQL 2000 to SQL 2008 R2. I have supported many databases that broke due to some of these changes. Also note that Express has a limitation on how large a database can grow, 10GB with SQL 2008 R2

The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size. I checked the size of the tables and they cannot reach the 9GB reported!

When you do a backup of the database, the backup is only going to grab the data itself along with some configuration information of the database. One of those configuration items is the initial size of the data and log file. I would expect if you checked your database properties you might find the initial size of your data file (MDF) is set to 9GB in size.

I did a shrink but the size did not change.

A database shrink is likely what you performed. Although ill-advised, if you need to regain disk space or just bring the data file (MDF) of your database under control you will need to perform a data file shrink. Again there are plenty of articles on MSDN and blog post that provide the way of doing this.

Is there a chance that the Full Recovery, can affect the size of the MDF files?

Nope.

I am thinking of settinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference?

With the size of your data file, nope.

The only thing we have done is replaced ScanImage on every row with a much smaller image (this is how so much unused space is there)

From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in).

Example code that achieved the best space reduction for me with the table structure in the question is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRAN

SELECT [ImageID],
       [ScanImage]
INTO   #Temp
FROM   [dbo].[MyTableName]

ALTER TABLE [dbo].[MyTableName]
  DROP COLUMN [ScanImage]

/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD

ALTER TABLE [dbo].[MyTableName]
  ADD [ScanImage] IMAGE NULL

UPDATE [dbo].[MyTableName]
SET    [ScanImage] = T.[ScanImage]
FROM   [dbo].[MyTableName] M
       JOIN #Temp T
         ON M.ImageID = T.[ImageID]

DROP TABLE #Temp

COMMIT 

Everything is in a transaction so if the machine crashes it will be rolled back. Could probably do with some error handling or at least SET XACT_ABORT ON. I used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; to prevent any concurrent modifications from happening during or after the copy and being lost.

The number of LOB pages reserved after reducing the size of an image in all rows was as follows:

+--------------------------------------------------+---------------------+-------------------------+
|                      Event                       | lob_used_page_count | lob_reserved_page_count |
+--------------------------------------------------+---------------------+-------------------------+
| Inserted 10,000 rows with 100,000 byte data each |              135005 |                  135017 |
| Updated all rows to 10,000 byte image data       |               31251 |                  135012 |
| Reorganize                                       |               23687 |                   25629 |
| Drop and re-add image data                       |               13485 |                   13489 |
+--------------------------------------------------+---------------------+-------------------------+