I need to reclaim space from
tempdb database which has more than
50 GB unallocated space. I have tried DBCC SHRINKDATABASE but it didn't do anything. Please note, I am aware that it has performance implications but I have no choice. I run SQL Server Express on a laptop and really need this
50 GB space. Also I have tried restarting SQL Server/laptop and have ensured that no process is running right now.
I need to reclaim space from
First of all you know the
SQL Server Express database limitation.As you said
tempdb which has
50 GB. As per MSDN BOL Here SQL Sever Express supports Maximum database size is 10 GB. And also you can find more
SQL Server 2012 limitation details Here.
To shrink all data and log files for a specific database, execute the
DBCC SHRINKDATABASEcommand. To shrink one data or log file at a time for a specific database, execute the
Shrinks the size of the data and log files in the specified database
Shrinking a database and specifying a percentage of free space
The following example decreases the size of the data and log files in the
UserDB user database to allow for
10 percent free space in the database
DBCC SHRINKDATABASE (UserDB, 10); GO
Truncating a database
The following example shrinks the data and log files in the AdventureWorks sample database to the last allocated extent.
DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);
Shrinking a data file to a specified target size
The following example shrinks the size of a data file named
DataFile1 in the UserDB user database to
USE UserDB; GO DBCC SHRINKFILE (DataFile1, 7); GO
Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
USE AdventureWorks2012; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO
Truncating a data file
The following example truncates the primary data file in the AdventureWorks database. The sys.database_files catalog view is queried to obtain the file_id of the data file.
USE AdventureWorks2012; GO SELECT file_id, name FROM sys.database_files; GO DBCC SHRINKFILE (1, TRUNCATEONLY);
Note: You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.
Before execute the DBCC SHRINKFILE , As
Mike Walsh explained very well you must know Here that, is it really necessarily to run that command.
For further your ref Here