Post reply

Shrink Master database

  • June 22, 2009 at 11:26 pm

    #207450

    Hi all,

    One of our DBA, wanted to copy some objects from user db (SQL 2000) to another sql server user database (SQL2005). But, he copied it master database (SQL 2005). so, the master db becomes 11 GB. we removed all the user objects, but system tables from sql 2000 still there. When we try to shrink data files, we get the following error:

    View 'dbo.sysprotects' is not updatable because the definition contains a UNION operator.

    DBCC SHRINKFILE: Heap page 1:1136138 could not be moved.

    DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

    ------ ----------- ----------- ----------- ----------- --------------

    1 1 1136144 512 7088 6896

    We need to get back the space, as the OS drive left with minimum space.

    What we have tried so far,

    DBCC SHRINKDATABASE, DBCC SHRINKFILE. Please guide me to get ride of this issue.

    Thanks in advance,

  • June 23, 2009 at 12:49 am

    #1013734

    You can try to rebuild your master db. In this tutorial you can see the steps how to do that:

    http://geekswithblogs.net/mskoolaid/archive/2005/12/17/63413.aspx

    Don't mind the fact that they talk about Windows XP because it works the same in WIndows Server

  • June 23, 2009 at 1:22 am

    #1013751

    You could restore a backup of the master database that was taken before the tables were added. You do have a backup of master?

    If you rebuild master you will lose all logins, server permissions, linked servers and you will have to reattach all user databases

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 23, 2009 at 10:17 pm

    #1014413

    Thank you for the reply. We are thinking to restore master database from old backup. Rebuilding master database is not the prefered option. Wonder, anything else can be done without these 2 options.

    Thanks again.

  • June 24, 2009 at 12:11 pm

    #1014834

    If he has just copied objects from user database to master database then delete those objects manually and after that make use of shrinkfile command.

    MJ

  • June 24, 2009 at 1:14 pm

    #1014874

    This is a WAG, but if a user database has any indexed views, the following options must be set before running DBREINDEX. Should not hurt to set the options and then try the SHRINKFILE.

    SET ARITHABORT , CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS ON

    SET NUMERIC_ROUNDABORT OFF

    SQL = Scarcely Qualifies as a Language

  • June 25, 2009 at 5:21 pm

    #1015697

    Dear MJ,

    Thanks for the post. We already have removed those objects. Got the error,

    View 'dbo.sysprotects' is not updatable because the definition contains a UNION operator.

    DBCC SHRINKFILE: Heap page 1:1136138 could not be moved.

    Thanks

    JYM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply