locked
Cannot shrink DB no matter what RRS feed

  • Question

  • Thank you in advance for any help!! Greatly appreciated.

    Now, before anyone asks why: because. Yes, we do want to and need to shrink the database. It's a legacy database that ballooned out in size due to a bug in an application accessing it. The bug is fixed and we need the disk space back.

    Now on to the question/issue: We have this BusObj (Yes, Business Objects) database that is 90% empty (data file). I run  DBCC SHRINKDATABASE(N'BusObj' ); and I get the error "A severe error occurred on the current command. The results, if any, should be discarded."

    I ran DBCC checkdb('BusObj'), no errors.

    I brought the DB offline, then online, then attempted to shrink, same error.

    I expanded the DB by 5MB, then attempted to shrink, same error.

    I brought the DB into single-user mode, then attempted to shrink, same error.

    What gives?

    P.S. DB is in simple recovery mode. And yes, I did disable any backups before attempting to shrink.

    Again, help greatly appreciated. TIA,

    Raphael


    rferreira


    • Edited by rferreira.dba Monday, June 15, 2015 10:38 PM Added info.
    Monday, June 15, 2015 10:25 PM

Answers

  • So... Here's what actually solved the problem:

    1- Create a new "Secondary" filegroup.

    2- Create a new data file in the secondary filegroup.

    3- Script ALL DB objects to move them all into the new filegroup. Yes, that meant I had to move everything, clustered and non-clustered indexes, etc.

    4- Verify that there is nothing left in the primary filegroup.

    5- Attempt shrink file on the primary filegroup. This time it worked!!

    6- Move everything back to the primary filegroup.

    7- Delete secondary data file, delete secondary filegroup.

    8- DONE. Tks guys.

    PS. I still would like to know/understand what exactly caused the problem in the first place..


    rferreira


    • Marked as answer by rferreira.dba Thursday, June 18, 2015 6:56 PM
    • Edited by rferreira.dba Thursday, June 18, 2015 6:57 PM Corrected misspelling.
    Thursday, June 18, 2015 6:56 PM

All replies

  • Hi,

    Instead of dbcc shrinkdatabase, did you try using dbcc shrinkfile for the data file and then shrink it in increments.

    If that also didnt work check the fragmentation levels of indexes , if its too high first reindex then shrink and then you might have to reindex again.

    Make sure that you leave enough room for future growths.

    Another option is also mentioned in the below blog.

    http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Monday, June 15, 2015 11:16 PM
  • Can you show us the output of  DBCC LOGINFO (dbid)?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 16, 2015 3:14 AM
  • There is something happening inside database which is stopping shrink command. Can you try with dbcc shrinkfile , did you tried with GUI what was the result ?

    Are you trying to shrink log file or data file ?

    What is output of

    select @@Version


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 16, 2015 4:03 AM
    Answerer
  • Hi,

    Try specifying both filename and target size in mb as,

    DBCC SHRINKFILE('filename', 1024);


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Tuesday, June 16, 2015 5:06 AM
  • 1)  use the DBCC OPENTRAN Transact-SQL to verify if there is an active transaction in a database at a particular time. If yes kill it.

    2) check the value of log_reuse_wait_desc

    select name, database_id,recovery_model_desc,log_reuse_wait_desc from sys.databases where name LIKE 'yourDatabaseName'


    Cause
    In my case, column log_reuse_wait_desc returned REPLICATION (e.g. BOL Factors That Can Delay Log Truncation). So the log was not truncated because records at the beginning of the log are pending replication.

    Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. Typically the Log Reader agent will parse the entire log and then mark each log record as replicated by executing sp_repldone.


    Resolution
    When I tried doing the same manually, my issue was fixed:

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

    With sp_repldone, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.

    Refer


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue


    • Edited by AV111 Tuesday, June 16, 2015 7:40 AM
    Tuesday, June 16, 2015 7:38 AM
  • Yes, I just tried shrink file, attempting to shrink only 1MB (out of the 30GB) the data file has. Same results. Same error. All backups disabled, DB in single-user mode. Same issue. Indexes do not appear to be an issue. The DB itself is pretty simple, with only 7 tables and about 30 or so indexes. I ran a physical fragmentation report and everything looks ok. I also ran Ola's (https://ola.hallengren.com) index defrag maintenance script and there was basically nothing there that really needed defrag (nothing 30% or more fragmented). 

    I guess my next step will be to try and create a new file group and move everything.. This should not have to be this difficult. Insane!

    P.S. Tks for trying to help. Raphael


    rferreira

    Tuesday, June 16, 2015 3:56 PM
  • DBCC LOGINFO(BusObj) generates 300+ lines. This forum will not accept the results in this window (too many characters). DB is in simple recovery mode.. Log file size is fine as well. It's the data file that is 90% empty and needs shrinking. Thoughts? TIA, Raphael



    rferreira

    Tuesday, June 16, 2015 4:03 PM
  • Select @@version:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

    I tried GUI and SSMS script. Same results. I put the DB into single user mode. I can see in the activity monitor that my shrink query is the only one in there. Here's a picture while the shrink is trying to run:



    rferreira

    Tuesday, June 16, 2015 4:06 PM
  • I did. Same results. R.

    rferreira

    Tuesday, June 16, 2015 4:06 PM
  • I bring the DB into single-user mode. I can see in activity monitor that there is nothing accessing the DB. Here's the results:

    name database_id recovery_model_desc log_reuse_wait_desc
    BusObj 9 SIMPLE NOTHING


    rferreira

    Tuesday, June 16, 2015 4:09 PM
  • Select @@version:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

    I tried GUI and SSMS script. Same results. I put the DB into single user mode. I can see in the activity monitor that my shrink query is the only one in there. Here's a picture while the shrink is trying to run:



    rferreira

    I am not aware about any such bugs but this does not seems normal to me. Plus you have RTM version which is not supported at all. There are lot of bugs fixed after RTM release in SQL Server 208 R2 SP3 release please apply SP3 bot for getting at least extended support and seeing if this resolves the issue.

    Any other message in SQL Server errorlog ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 16, 2015 4:12 PM
    Answerer
  • Hello - You are running SQL Server 2008 R2 RTM (GDR Release) and it is quite possible that you are hitting a bug that is resolved in recent service packs. I suggest you first install SP3 which is the latest for this version of SQL Server and try shrink operation again.

    On the other hand, I would always say NO to shrink because it inherently induces massive fragmentation and you would end-up having other performance issues unless you build all your indexes again

    Indeed, creating another file-group and moving objects there is surely a good idea and works best in most scenarios.

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Tuesday, June 16, 2015 4:15 PM
  • Hi.

    The first thing I would do would be to do some database maintenance and then to the shrink.

    Rebuild your indexes, update your stats, backup the database and then shrink the files.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, June 16, 2015 4:16 PM
  • Instead of upgrading the server, which is a production server and this would require down time, I went ahead and used the latest backup of the database to restore it to my local machine. My local machine is running SQL 2012. Same exact problem. So, it's not the server. It's the database. There seems to be some corruption at the database level that SQL Server cannot detect with DBCC, but that prevents shrinking. This is insane! Nothing on the error logs either. R.

    rferreira

    Wednesday, June 17, 2015 2:06 PM
  • Instead of upgrading the server, which is a production server and this would require down time, I went ahead and used the latest backup of the database to restore it to my local machine. My local machine is running SQL 2012. Same exact problem. So, it's not the server. It's the database. There seems to be some corruption at the database level that SQL Server cannot detect with DBCC, but that prevents shrinking. See original post. Shrinking is needed in this case. It was a bug in the application that caused the DB to grow out of proportions. Indexes are fine, and after shrink we can reorg, rebuild, defrag if necessary. Tks. R.

    rferreira

    Wednesday, June 17, 2015 2:09 PM
  • Hello again; I refer to my post just above your posts here.

    Carry out some database maintenance by rebuilding your indexes and updating your stats.  Also shrink the individual files rather than the whole database and select the option to "Reorganize pages before releasing unused space".


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, June 17, 2015 3:11 PM
  • Thank you for trying to help Tony, but as I have written in my posts before, the indexes and stats are fine. I have already checked them. Nothing has more than 30% fragmentation. I have already tried shrinking individual files and I have already tried using the "Reorganize pages before releasing unused space". Same error.

    rferreira

    Wednesday, June 17, 2015 3:24 PM
  • Hmm.

    In that case something is reserving the space. What are the Padding values on the indexes? Are there any database snapshots for this Database?


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, June 17, 2015 3:39 PM
  • No database snapshots. For all indexes, the is_padded attribute reads 0. R.

    rferreira

    Wednesday, June 17, 2015 6:59 PM
  • Take a look at the status column ,do you see value =2 at the bottom of the list?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 18, 2015 5:13 AM
  • Uri, yes, there is one record out of the 304 records with a 2 for status. Not at the bottom. Record 298 out of the 304.But I have run full and transaction log backups with no issues. Also, DBCC OPENTRAN(BusObj) returns no active transactions. Remember, I am using single-user mode throughout this. Also, DB is in simple recovery, not full. What gives? TIA, Raphael

    P.S. It's the data file that needs shrinking, not the log file.


    rferreira


    • Edited by rferreira.dba Thursday, June 18, 2015 1:00 PM Added info.
    Thursday, June 18, 2015 1:00 PM
  • So... Here's what actually solved the problem:

    1- Create a new "Secondary" filegroup.

    2- Create a new data file in the secondary filegroup.

    3- Script ALL DB objects to move them all into the new filegroup. Yes, that meant I had to move everything, clustered and non-clustered indexes, etc.

    4- Verify that there is nothing left in the primary filegroup.

    5- Attempt shrink file on the primary filegroup. This time it worked!!

    6- Move everything back to the primary filegroup.

    7- Delete secondary data file, delete secondary filegroup.

    8- DONE. Tks guys.

    PS. I still would like to know/understand what exactly caused the problem in the first place..


    rferreira


    • Marked as answer by rferreira.dba Thursday, June 18, 2015 6:56 PM
    • Edited by rferreira.dba Thursday, June 18, 2015 6:57 PM Corrected misspelling.
    Thursday, June 18, 2015 6:56 PM