June 4, 2008 at 3:46 am
Recently, I splitted my database into several multiple datafiles; I created 2 filegroups "data" and "index" and moved all the user tables en indexes to the according filegroups. The original datafile (primary) now only contains systemfiles (and 130GB free space :w00t: )
In order to reclaim this space, I temporary enabled "auto shrink", but that didn't work so I disabled this option.
If I do a manually shrink now, by using this statement:
DBCC SHRINKFILE (N'KERNEL_Data' , 0, TRUNCATEONLY)
GO
I get the error:
File ID 1 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty
But I see no autoshrink process. I also checked database properties and autoshrink is off. So maybe the file was empty (like the message said) and I created a dummy table with some data. But this error still pops up when trying to shrink this file.
Am I missing something? Is this a bug? SQL2K5 EE SP2, Hotfix 6, 64bit
Wilfred
The best things in life are the simple things
June 4, 2008 at 4:29 am
found this post on http://weblogs.sqlteam.com/tarad/category/95.aspx/rss
vrijdag 9 november 2007, 0:31:40 | Tara Kizer
Recently we purged millions of rows from a database as it was determined that we did not need to retain it. Since we were low on free disk space, we decided to shrink the primary data file (MDF). Usually I wouldn't recommend shrinking the database, but this was a situation where it met the exception to the rule.
Since the amount of space to be reclaimed was around 50GB, I decided to run the shrink in smaller batches (1 GB each pass). After about an hour of shrinking the database down, I started to receive the following error:
File ID 1 of database ID 12 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After much troubleshooting and googling, I threw in the towel and opened a case with Microsoft.
According to Microsoft, the problem was "DBCC SHRINKFILE was unable to run on the primary data file of the user database "DatabaseName" because a background operation which had obtained a latch for the required shrink operation previously was not released."
It was determined that a transaction log backup had run at the same time that the shrink was occurring which is what caused this latch problem.
There is no way to fix this issue except to restart the SQL Server service, since "such type of latches are in-memory structures" and "there are no DMVs in SQL Server 2005 which would allow us to view unreleased latches."
My recommendation to avoid this issue is to disable your transaction log backups while you are shrinking the database. Don't forget to re-enable it when you are done!
Wilfred
The best things in life are the simple things
June 4, 2008 at 7:10 am
Can you do something like this? I'm assuming you would know if "It was determined that a transaction log backup had run at the same time that the shrink was occurring which is what caused this latch problem." is occurring.
USE master
GO
ALTER DATABASE Test1 MODIFY FILE( NAME = ‘test1dat3’,SIZE = 20MB)
-- You can't be late until you show up.
June 4, 2008 at 7:33 am
Can you do something like this? I'm assuming you would know if "It was determined that a transaction log backup had run at the same time that the shrink was occurring which is what caused this latch problem." is occurring.
USE master
GO
ALTER DATABASE Test1 MODIFY FILE( NAME = ‘test1dat3’,SIZE = 20MB)
Regards,
Terry
NO:
ALTER DATABASE AXDB MODIFY FILE( NAME = 'KERNEL_Data', SIZE = 140GB)
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
Wilfred
The best things in life are the simple things
June 4, 2008 at 2:24 pm
I am curious about something. If I followed what you were trying to do, you were trying to move the 'indexes' over to the new filegroup and leave the data in the original filegroup.
If so, then why would the original filegroup have nothing in it? The original file group should have the 'data'. Most likely, you also moved the clustered indexes to the new filegroup - which actually moves the table.
As to the actual problem of shrinking - the suggestion has been made to disable transaction log backups during the shrink. I would also recommend either that - or switch to simple recovery model, perform the shrink, resize to the appropriate size, reindex whatever else is left, switch back to full recovery model and finish up by running a full backup.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2008 at 1:32 am
fyi,
I moved the nonclustered indexes from [primary] to [index]
I moved heap tables and clustered indexes from [primary] to [data]
[primary] now only contains systemobjects (which are not movable)
Simple recovery is not an option for this critical database (Axapta)
Wilfred
The best things in life are the simple things
June 5, 2008 at 2:54 pm
Oh - I missed that little detail 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2008 at 1:54 pm
SQL Server does NOT ALLOW you to remove a Primary Datafile.
Anne
June 7, 2008 at 5:07 am
I know. I'm not removing it, just shrinking (see my first post 😉 )
Wilfred
The best things in life are the simple things
June 7, 2008 at 7:04 am
In the SHRINKFILE statement, you have specified TRUNCATEONLY, which de-allocate starting from the end of the file to the begining of the file until the first extent with any data is found. The shrink needs to specify a target size. Try running with these commands:
1. Get the file sizes
select sysfilegroups.groupid
,Files.FileId
,sysfilegroups.groupname
,Files.FileName
,Files.AllocatedMb
,Files.SpaceUsedMb
,Files.AllocatedMb - Files.SpaceUsedMb as SpaceFreeMb
fromdbo.sysfilegroups
JOIN(
SELECTsysfiles.FileId
,sysfiles.name AS FileName
,sysfiles.groupid
, (sysfiles.size * 8) / 1024 AS AllocatedMb
,( (CAST(FILEPROPERTY(sysfiles.name, 'SpaceUsed' ) AS int) * 8 ) / 1024) AS SpaceUsedMb
FROM dbo.sysfiles
) as Files
on sysfilegroups.groupid = Files.groupid
order by sysfilegroups.groupid
,Files.FileId
2) Based on the SpaceUsedMb, shrink the file leaving enough space to re-organize the tables and indexes, which is 1.5 times the size of the largest table. This is necessary because shrinkfile cause significant fragementation.
DBCC SHRINKFILE(fileid?, targetsizeMb?)
3) Rebuild all of the indexes for tables and indexes within the shrunk file.
SQL = Scarcely Qualifies as a Language
June 8, 2008 at 2:14 am
It's doesn't matter which shrink option I use (truncate, full reorg, migrate data tot other datafiles in same filegroup), SQL won't allow me to use a shrink command (same error keeps hitting me).
Btw the primary filegroup now only contains systemtables/-indexes, so I assume these are at the beginning of the datafile.
Last night, I changed the recovery mode to simple, but that didn't work either. :crazy:
My plans so far: disable transaction logs, do a restart, shrink file, enable translogs. I'll keep you informed! :unsure:
Wilfred
The best things in life are the simple things
June 16, 2008 at 2:17 am
SOLUTION
It's seems the only way to resolve this issue, was to restart SQL. I created a shrinkjob, which ran after a restart and that solved this issue.
Wilfred
The best things in life are the simple things
October 3, 2008 at 12:15 pm
Hi,
We also faced same problem on one of our production servers and what we did was to take the database offline and then bring it back online.After bringing it online we immediately issued the Shrink command and it worked like charm.Hope it works for you too!!
Thanks,
Vikas Sharma
April 4, 2011 at 4:52 pm
for all others which will reading this article after two years.
Try to add some more space at the end of the data file (1MB should help)
After that, it is very likely that nothing holds any lock or latch ( or whatever 😉 ) so you should be able to shrink.
In very rare events there is a book with an unreleasable lock issue.
For this rare cases take db offline and online should help.
If this would not help either resetting the sql server service must solve the shrinking problem.
if not --> call PSS (and tell them, what you have already tried)
September 22, 2011 at 11:51 am
I ran into this same issue, as we have a server that performs Transaction Log backups every 15 minutes.
The solution proposed by Ralf Dietrich DOES work, and resolved the problem without downtime.
Commands I used were:
ALTER DATABASE [{databasename}] MODIFY FILE (NAME = '{filename}', SIZE = {current size + 5MB})
GO
DBCC SHRINKFILE ('{filename}', {desired size in MB})
GO
Thanks Ralf and Wilfred!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply