Where can I shrink database directly from Central admin (SharePoint2010)

Where can I shrink database direct开发者_开发百科ly from Central admin (SharePoint2010)

Go to "Review problems and solutions" in central admin

http://yourcentraladmin/Lists/HealthReports/AllItems.aspx

If some databases have too much free space, you should see a warning under the "Availability" heading.

"Database has large amounts of unused space."

If you click that, there should be a "Repair Automatically" button.

If you change the rule definition, sharepoint should be able to fix it for you always.

You can't shrink your database directly from the central administration UI.

You need to use SSMS (SQL Server Management Studio) to shrink your database.

I found a nice automated solution (SQL script) to shrink all your Sharepoint related DBs: Keeping your SHarepoint 2010 Development Databases small

USE [master]
GO

DECLARE @dbname SYSNAME
DECLARE @altercmd NVARCHAR(1000)
DECLARE @shrinkcmd NVARCHAR(1000)

DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases

OPEN [dbcursor]
FETCH NEXT FROM [dbcursor] INTO @dbname

WHILE
    @@FETCH_STATUS = 0
BEGIN
    IF
        (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE'
        AND
        @dbname != 'tempdb'
    BEGIN
        SET @altercmd = 'ALTER DATABASE "' + @dbname
                                               + '" SET RECOVERY SIMPLE'
        EXEC (@altercmd)

        SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")'
        EXEC (@shrinkcmd)

        PRINT @dbname
    END

    FETCH NEXT FROM [dbcursor] INTO @dbname
END

CLOSE [dbcursor]
DEALLOCATE [dbcursor]

A different shorter method is explained here: Sharepoint: shrink database on a fast way

USE MyDatabase

GO

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (MyDatabase_log, 1)

GO

You cannot do it through Central Admin. The only option is to use SQL script or Management Studio.

http://blogs.technet.com/b/patrick_heyde/archive/2009/04/24/sharepoint-shrink-database.aspx

You should not shrink a database. Try this approach instead:

1. Remove documents/sites etc. from the website.
2. make a sharepoint backup of the site
3. Create a new web application
4. Run a sharepoint restore to the new web application (5. you can detach and attach the databases to get the smaler database back on the first web application if you like to).

For more info, see:

http://sharepointkaos.wordpress.com/2013/08/15/reduce-the-size-of-the-sharepoint-content-database-without-shrinking-it/