March 11, 2010 at 10:45 pm
A little late to the party on this one:
Have you tried something like this:
dbcc shrinkfile(DataFileName, 100)
That will try to shrink the file down to 100MB as best as it can. Obviously not recommended for production systems, but may get the job done for you.
Steve
March 11, 2010 at 11:32 pm
Even if this must be done in a prod environment, you may be able to use the shrinkfile and do it in chunks. Of course pay attention to performance and potential fragmentation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2010 at 12:58 am
S.K. (3/11/2010)
A little late to the party on this one
There are sometimes multiple pages of replies. Still catches me out occasionally.
Obviously not recommended for production systems, but may get the job done for you.
As Jason says, it is quite safe to do it in chunks, preferably in a maintenance window.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 10:38 pm
Alright, so I know have 3 seperate people telling me that dbcc shrinkfile will shrink the file lower than the inital file size.
However, if you read my first post, I have tried shrinkfile. I have also tried shrinkdatabase. Neither are working.
At this point, I have dropped the database, created a new database with a 20GB initial file size. Once that was done, i restored the production database to our testing server. DBCC Shrinkdatabase did nott do anything, even if I did the reorg of the data inside of the file before releasing it.
currently, as I write this post, I am running the following commands
dbcc shrinkfile ('data', 100, notruncate)
dbcc shrinkfile ('data, 100, truncateonly)
However, I a no very hopeful since the shrink database didn't work. Assuming this failed, what are my other options.....other than porting over 100GB of information to a new database?
Thanks,
Fraggle
March 13, 2010 at 11:22 pm
Fraggle-805517 (3/13/2010)
Alright, so I know have 3 seperate people telling me that dbcc shrinkfile will shrink the file lower than the inital file size.However, if you read my first post, I have tried shrinkfile. I have also tried shrinkdatabase. Neither are working.
At this point, I have dropped the database, created a new database with a 20GB initial file size. Once that was done, i restored the production database to our testing server. DBCC Shrinkdatabase did nott do anything, even if I did the reorg of the data inside of the file before releasing it.
currently, as I write this post, I am running the following commands
dbcc shrinkfile ('data', 100, notruncate)
dbcc shrinkfile ('data, 100, truncateonly)
However, I a no very hopeful since the shrink database didn't work. Assuming this failed, what are my other options.....other than porting over 100GB of information to a new database?
Thanks,
Fraggle
I really think you are wasting your time using those options.
NOTRUNCATE
Causes the freed file space to be retained in the files.
When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system.
TRUNCATEONLY
Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
Perform the shrinkfile without those options that you are specifying.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2010 at 12:08 am
CirquedeSQLeil (3/13/2010)
I really think you are wasting your time using those options.Perform the shrinkfile without those options that you are specifying.
Nice.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 7:42 am
Jason,
per you suggestions, I removed the options for NoTruncate and TruncateOnly.
Still does not shrink. Output is as follows.
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
125129565184128 1145888011458880
Also, BOL says to run the following command if there are problems shrinking the file.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
After doing it, I get the following output, which tells me there is more than enough room. for shinking the file.
nameAvailableSpaceInMB
Data141451.875000
Log3461.515625
Thoughts.
Fraggle
March 14, 2010 at 3:25 pm
Since your estimated pages and used pages are the same - that could be an indicator as to why.
Sometimes I update stats or rebuild indexes - and then the shrink works. How are your indexes and stats looking?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2010 at 9:45 pm
I had a 2005 database once, many years ago, that absolutely refused to shrink, even when in single-user mode, with all the user objects in the database dropped completely. Those that know me will guess that I had a pretty good go at shrinking it, using every trick I could think of. Nothing worked. I re-created the database in the end.
I have a feeling it might have been related to LOB storage, but life is way too short to spend weeks investigating.
I agree about the estimated pages thing: the file will only shrink if there is contiguous free space at the end of the file. If there is one page at the end of the file that cannot be moved for some strange reason, that's it, you are stuck.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply