Results 1 to 8 of 8

Thread: code for re-build index and shrink db or file?

  1. #1
    Join Date
    Mar 2010
    Posts
    4

    code for re-build index and shrink db or file?

    We have issue to re-build index in large table. Will get insufficient disk space in filegroup 'PRIMARY'...error. So we have to manually shrink the database and re-build in the same time. How can we put the script together to do this automacally? And when I used shrinkdatabse, it only shrink the log file? And it seems we can't shrink unless we change the modle to simple. Do we use shrinkfile or shrinkdatabase? Is the syntax correct below? Thanks in advance!

    ALTER INDEX ALL ON RD_WorkorderMart.dbo.WO_Star_Facts REBUILD;
    ALTER DATABASE RD_WorkorderMart
    SET RECOVERY SIMPLE
    USE [RD_WorkorderMart]
    GO
    DBCC SHRINKDATABASE(N'RD_WorkorderMart', 10 )
    GO
    ALTER DATABASE RD_WorkorderMart
    SET RECOVERY FULL

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Use shrinkfile in this case. Better solution is moving db to bigger disk, shrinking causes double performance hit.

  3. #3
    Join Date
    Mar 2010
    Posts
    4

    why shrinkfile will be better?

    Hi, Rmia,
    Could you explan why shinkfile will be better in this case? Do I need to change to simple before the shrinkfile or shrinkdatabase?

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Because you know which file to shrink, saves time. Don't have to change recovery mode to shrink data file.

  5. #5
    Join Date
    Mar 2010
    Posts
    4

    how to?

    I found out I can't switch the database in the stored procedure. I read about I can create it in the master db, and calling from there. However, my procedure is doing something like...
    USE [master]
    GO
    /****** Object: StoredProcedure [dbo].[sp_ALTERINDEX_REBUILD] Script Date: 03/03/2010 10:30:18 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[sp_ALTERINDEX_REBUILD]
    AS

    ---------------------------------------
    --PURCHASING
    ---------------------------------------
    ALTER INDEX ALL ON RD_PurchaseMart.dbo.Purchase_Star_Facts REBUILD;
    DBCC SHRINKFILE ('RD_PurchaseMart_Log', 10)

    ---------------------------------------
    --SALES
    ---------------------------------------
    ALTER INDEX ALL ON RD_SalesMart.dbo.Sales_Star_Facts REBUILD;
    ALTER INDEX ALL ON RD_SalesMart.dbo.Sales_Star_FactForecastSummary REBUILD;
    DBCC SHRINKFILE ('RD_SalesMart _Log', 10)
    ...etc.

    I have 6 more database to rebuild the index.

    Do I need to modify anything? Not sure how do i run it in the agent, since it goes the whole thing again..Thanks!

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    You need add 'use db_name' in sp to switch to each db you work with.

  7. #7
    Join Date
    Mar 2010
    Posts
    4
    do you have a sample code to share? If I pass the db_name from the job agent, for example, PurchaseMart, but the procedure also contains other database index re-build. Is that mean if I have 6 database re-build, I have to create 6 jobs?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    You can create 1 job with 6 steps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •