You must Sign In to post a response.
  • Category: SQL Server

    How many times the rebuild and reorganize can be done?

    Hi,

    I want to know, how many times we can rebuild and reorganize the database, as our database size is around 25GB. Also, is it advisable to Shrink the database with these two processes all the time?

    Regards
    Vivek Sharma
  • #767715
    Hi
    you can try this Query for shrink Database



    DBCC SHRINKDATABASE (UserDB, 10);
    GO

    USE YourDB
    GO
    ALTER DATABASE YourDB SET RECOVERY SIMPLE
    DBCC SHRINKFILE(2,1) --Shrink the log file to 1MB
    ALTER DATABASE YourDB SET RECOVERY FULL
    GO

    ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

    GO

    DBCC SHRINKFILE (MyDatabase_Log, 5)

    GO

    ALTER DATABASE MyDatabase SET RECOVERY FULL

    GO




    you can go through Below link


    "help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs"

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767717
    Sir, kindly read the question.

  • #767718
    Re-build and shrink will not run at a time as shrink will re-build and identify un-used spaces.
    to shrink it do the following thing.
    First you need to identify OLD records and take its backup and delete them from database, and then A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
    Delete LDF file if it is heavy, it will automatically re-create it again, use 'DBCC SHRINKDATABASE ' command or login to SSMS (MS SQL Server Management Studio.) and Right-Click the DB -> TASKS -> SHRINK -> FILES -> select the LOG file
    I think take a Archive backup and Delete some OLD record will do the trick, cause shrink will only make a certain space for you not all.

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments