August 23, 2012 at 8:42 am
Hi,
I am trying to run SHRINKFILE on one of our data files but it is failing with error "Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file"[/b] on every execution. I have checked disk space and that is OK and the file in question has a 1/3 free space which I am trying to trim back:
FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName
500.00340.50 159.50 EVVSGUKEVVSG01_1_1_data_002
Can anyone help me regarding what I should try next as it just keeps on failing. The database is in Full recovery mode with 34 file groups - PRIMARY and FingerprintCatalogue_000 thru 032. The file above belongs to file group FingerprintCatalogue_002.
Thanks for looking,
Greg
August 23, 2012 at 2:36 pm
The obligatory response: are you sure you want to shrink your data file? Stop Shrinking Your Database Files. Seriously. Now. by Brent Ozar[/url]
Now that we have that out of the way...
Are there any other files in the FingerprintCatalogue_002 filegroup?
Does the filegroup house tables with LOB data?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 24, 2012 at 5:57 am
Are there any other files in the FingerprintCatalogue_002 filegroup?
No. All file groups have been created with a single data file.
Does the filegroup house tables with LOB data?
No I don't think so. As this is all new to me and it is a 3rd party application/database installation I used the following query, which I got from the internet, to check for LOB data, feel free to correct it or suggest an alternative method:
select SO.Name as [Table], COL.name as [ColumnName], COL.system_type_id, TYP.name as [Type Name], SFG.groupname as 'Filegroup'
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId
join sys.columns as COL
on COL.object_id = SO.id
join sys.types as TYP
on COL.system_type_id = TYP.system_type_id
where sfg.groupname like 'FingerprintCatalogue%'
and TYP.system_type_id in (35,34,241,99)
order by SO.Name , SFG.GroupName
August 24, 2012 at 9:58 am
Can you please check again. Yuor query is not checking for any of the MAX types. Try this one instead:
SELECT t.name AS
,
c.name AS [column],
c.system_type_id,
typ.name AS [type],
c.max_length,
fg.name AS [filegroup]
FROM sys.columns c
JOIN sys.types typ ON c.system_type_id = typ.user_type_id
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.data_spaces ds ON t.lob_data_space_id = ds.data_space_id
JOIN sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE fg.name LIKE 'Primary%'
AND (
(
-- nvarchar, varbinary, varchar
typ.system_type_id IN (231, 165, 167)
-- (MAX)
AND c.max_length = -1
)
-- text, image, xml, ntext
OR typ.system_type_id IN (35, 34, 241, 99)
)
ORDER BY t.name,
fg.name;
As an aside, views like sysobjects have been maintained in the product since SQL 2005, but only for backward compatibility with code written for SQL 2000 and before. Please start using the current catalog views instead: Mapping System Tables to System Views (SQL Server 2008 R2)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 28, 2012 at 5:28 am
Thank you for info I'm rather new to this so it's nice to be pointed in the right direction.
I've ran your query and no rows were returned for any of the file groups.
Cheers,
August 28, 2012 at 3:26 pm
You're welcome. I try to pass along the latest info regarding the System and Catalog Views to keep the ball moving forward so to speak. The newer Views have more information and there are many more of them, so it's good to get to know them.
Try DBCC SHRINKFILE with the NOTRUNCATE option, then try shrinking it with a target size.
USE YourDatabase;
GO
DBCC SHRINKFILE (FingerprintCatalogue_002, NOTRUNCATE);
GO
DBCC SHRINKFILE (FingerprintCatalogue_002, target_size in MB);
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 29, 2012 at 7:16 am
The first SHRINKFILE with NOTRUNCATE executes OK, the second fails with the original message of:
Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file 'EVVSGUKEVVSG01_1_1_data_002'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
August 29, 2012 at 8:51 am
Try shrinking by a very small amount. Can you restore the DB to a test area so you can drop into SINGLE_USER mode and try the shrink, or can you do that during a maintenance window?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 4:32 am
Cheers opc.three will try this out when our system admin is back in the office. After reading the blogs you pointed me to I don't think it's worth doing the shrink anyway as the files are quite small.
Thanks,
Greg
May 1, 2018 at 3:15 am
Orlando Colamatteo - Friday, August 24, 2012 9:58 AMCan you please check again. Yuor query is not checking for any of the MAX types. Try this one instead:SELECT t.name ASAs an aside, views like sysobjects have been maintained in the product since SQL 2005, but only for backward compatibility with code written for SQL 2000 and before. Please start using the current catalog views instead: Mapping System Tables to System Views (SQL Server 2008 R2), c.name AS [column], c.system_type_id, typ.name AS [type], c.max_length, fg.name AS [filegroup]FROM sys.columns c JOIN sys.types typ ON c.system_type_id = typ.user_type_id JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.data_spaces ds ON t.lob_data_space_id = ds.data_space_id JOIN sys.filegroups fg ON ds.data_space_id = fg.data_space_idWHERE fg.name LIKE 'Primary%' AND ( ( -- nvarchar, varbinary, varchar typ.system_type_id IN (231, 165, 167) -- (MAX) AND c.max_length = -1 ) -- text, image, xml, ntext OR typ.system_type_id IN (35, 34, 241, 99) )ORDER BY t.name, fg.name;
Hi,
If I were to get results from this query, can you offer explanation?
December 16, 2021 at 1:56 am
I have seen this error when a backup is in progress for the database on which the shrink operation is being attempted. Wait for the backup to complete and try again.
November 10, 2024 at 6:16 pm
This was removed by the editor as SPAM
November 10, 2024 at 6:16 pm
This was removed by the editor as SPAM
November 10, 2024 at 6:16 pm
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply