February 9, 2009 at 6:19 am
I recieved the following error on a when attempting to shrink a database after removing a column of datatype Image:
Location: tmpilb.cpp:2565
Expression: fFalse
SPID: 111
Process ID: 4216
Description: Attempt to access expired blob handle (3)
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
The system I was running this on is MS Server 2008, SQL Server 2005 Enterprise Edition 64 bit.
The code worked fine in a test env (and on another database where we did not drop the column with type Image). The script is
DECLARE @DataFileFreeSpaceGB INT
DECLARE @LogFileFreeSpaceGB INT
SELECT @DataFileFreeSpaceGB = 10
SELECT @LogFileFreeSpaceGB = 1
DECLARE @DBName VARCHAR(100) --Database Name
DECLARE @filenameD VARCHAR(500) -- DataFile
DECLARE @filenameL VARCHAR(500) -- Logfile
DECLARE @DSpaceUsed DECIMAL(10) --Spaceused in Meg
DECLARE @LSpaceUsed DECIMAL(10) --Spaceused in Meng
DECLARE @sStr VARCHAR(200) -- A string SQL statement to EXECute
SELECT @DBName = DB_NAME()
SET @sStr = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY Simple'
EXEC (@sStr)
--DataFile
SELECT @fileNameD = name From sys.database_files Where type = 0
SELECT @DSpaceUsed = (FILEPROPERTY (@filenameD,'SpaceUsed ') * 8192.0)/1024/1024
SET @DSpaceUsed = @DSpaceUsed + (@DataFileFreeSpaceGB * 1000)
SET @sStr = 'DBCC ShrinkFile (' + @fileNameD + ',' + Convert(VARCHAR(50),@DSpaceUsed) + ')'
EXEC (@sStr)
--Logfile
SELECT @fileNameL = name From sys.database_files Where type = 1
SELECT @LSpaceUsed = (FILEPROPERTY (@filenameL,'SpaceUsed ') * 8192.0)/1024/1024
SET @LSpaceUsed = @LSpaceUsed + (@LogFileFreeSpaceGB * 1000)
SET @sStr = 'DBCC ShrinkFile (' + @fileNameL + ',' + Convert(VARCHAR(50),@LSpaceUsed) + ')'
EXEC (@sStr)
SET @sStr = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL'
EXEC (@sStr)
I have run a DBCC CheckDB after the error and came back with no errors listed for the database this failed on.
February 9, 2009 at 9:01 am
Hi Gary,
You may want to try these items.
1. Run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to clean out the cached query plans.
2. Run a checkpoint command, or backup the database.
3. Run the shrinkfile command in single user mode.
Hope this helps.
Jim
Regards, Jim C
February 9, 2009 at 9:12 am
DBCC CLEANTABLE is your friend. Run it first then Apply your script.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply