Shrinking Databases

This is difficult to perform without downtime as shrinking a database requires exclusive use of it.
If a database is online, the portion of the file in use is likely to be at the end of it, making reducing it's size impossible. SQL may have finished with the file and may just 'forgotten' to release this space. If this is the case, the following is worth a try.

Try the shrinkdatabase and shrinkfile methods below. They may achieve from partial to total success, depending on current activity i.e. any running processes.

Method 1 : Use the TSQL command dbcc shrinkdatabase >

i. Kill all running processes
ii. run the following sql -

dbcc shrinkdatabase (databasename, 'target percent')

Method 2 : Use the TSQL command dbcc shrinkfile to shrink the data & log files separately >

i. Kill all running processes
ii. run the following sql -

use tempdb
go
-- this command shrinks the primary data file
dbcc shrinkfile (datafile, 'target size in MB')
go
-- this command shrinks the primary data file
dbcc shrinkfile (datafilelog, 'target size in MB')
go

* you can specify a target size of 0 and SQL will shrink the file as much as it can (although the first operation on that db will inevitably trigger automatic growth)

In SQL 2005+, these tasks can be done from Management Studio by Right clicking the database, selecting 'Tasks' then 'Shrink' followed by 'Database' or 'Files'.

If the files dont shrink, identify and stop any running processes you can afford to and repeat.
Running processes can be viewed in Activity Monitor (Server > Management > Activity Monitor in Management Studio).

If they have stalled and are stuck or you simply want to stop them, right click the process and select 'Kill Process'. Then click 'Yes' to confirm.


Shrinking TempDB

TempDB is a SQL Server System Database. It is a temporary database, used both as a work area for internal processing and to store temporary objects created by users.
It is recreated each time SQL Server starts.

Method 1 (Downtime allowed) -

Stop and Start the SQL Server service.
TempDB will be recreated at its initial size as set in the Files page of the Database Properties.


Method 2 (No Downtime) -

Kill as many processes as you can afford to, and use the TSQL commands e.g.

use tempdb
go
dbcc shrinkfile (tempdev, 'target size in MB')
go
dbcc shrinkfile (templog, 'target size in MB')
go

( In SQL 2005+, this can be done from Management Studio by Right clicking the database, selecting 'Tasks' then 'Shrink' followed by 'Database' or 'Files'. )