InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:29:46 GMT20http://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxCheers 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,GregWed, 19 Sep 2012 04:32:52 GMTgreg.huxleyhttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxTry 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?Wed, 29 Aug 2012 08:51:28 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxThe first SHRINKFILE with NOTRUNCATE executes OK, the second fails with the original message of:Msg 3140, Level 16, State 5, Line 1Could not adjust the space allocation for file 'EVVSGUKEVVSG01_1_1_data_002'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Wed, 29 Aug 2012 07:16:51 GMTgreg.huxleyhttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxYou'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.[code="sql"]USE YourDatabase;GODBCC SHRINKFILE (FingerprintCatalogue_002, NOTRUNCATE);GODBCC SHRINKFILE (FingerprintCatalogue_002, [i]target_size in MB[/i]);GO[/code]Tue, 28 Aug 2012 15:26:11 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxThank 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,Tue, 28 Aug 2012 05:28:52 GMTgreg.huxleyhttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxCan you please check again. Yuor query is not checking for any of the MAX types. Try this one instead:[code="sql"]SELECT t.name AS [table], 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;[/code]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: [u][url=http://msdn.microsoft.com/en-us/library/ms187997(v=sql.105).aspx]Mapping System Tables to System Views (SQL Server 2008 R2)[/url][/u]Fri, 24 Aug 2012 09:58:31 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspx[quote]Are there any other files in the FingerprintCatalogue_002 filegroup?[/quote]No. All file groups have been created with a single data file.[quote]Does the filegroup house tables with LOB data?[/quote]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 SOjoin sysindexes as SIon SO.Id = SI.idjoin sysfilegroups as SFGon SI.GroupId = SFG.GroupIdjoin sys.columns as COLon COL.object_id = SO.idjoin sys.types as TYPon COL.system_type_id = TYP.system_type_idwhere sfg.groupname like 'FingerprintCatalogue%'and TYP.system_type_id in (35,34,241,99)order by SO.Name , SFG.GroupNameFri, 24 Aug 2012 05:57:41 GMTgreg.huxleyhttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxThe obligatory response: are you sure you want to shrink your data file? [u][url=http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/]Stop Shrinking Your Database Files. Seriously. Now. by Brent Ozar[/url][/u]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?Thu, 23 Aug 2012 14:36:11 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1349158-1292-1.aspxHi,I am trying to run SHRINKFILE on one of our data files but it is failing with error [i][b]"Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file[/i]"[/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:FileSizeMB UsedSpaceMB UnusedSpaceMB DBFileName500.00 340.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,GregThu, 23 Aug 2012 08:42:36 GMTgreg.huxley