Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1530
  • Last Modified:

best way to shrink database file sizes

What is the best and safest way to shrink the mdf and ldf files of my databse? I use the following command:

DBCC SHRINKFILE(sfs_dat, truncateonly)
DBCC SHRINKFILE(sfs_log, truncateonly)

I have also used this command, but I think truncateonly is healthier. Is that correct?

DBCC SHRINKFILE(sfs_dat, 1)
DBCC SHRINKFILE(sfs_log, 1)

I see some people recommend this command. Why the backup log command does that force sqlserver to do some cleanup internally or something?

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

and final question: Is the only difference between DBCC SHRINKFILE and DBCC SHRINKDATABSE, that DBCC SHRINKDATABASE does all files?
0
brokeMyLegBiking
Asked:
brokeMyLegBiking
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
From BOL

EMPTYFILE

Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.

NOTRUNCATE

Causes the freed file space to be retained in the files.

When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system.

TRUNCATEONLY

Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.



brokeMyLegBiking,
> BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(<TransactionLogName>, 1)
The above commands are used to reduce the database size.Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file
0
 
Eugene ZCommented:
if it is sqls server 2000 and up
check db options - recovery mode: Full; Simple; BL (read BOL)
if it is 'FULL' make sure you have regular trans log backup job - it will prevent
the DB trans log bee too big and reduce shrinking time if you will need ever
<Is the only difference between DBCC SHRINKFILE and DBCC SHRINKDATABSE, that DBCC SHRINKDATABASE does all files?


DBCC SHRINKFILE -- does file by file
Shrinks the size of the specified data file or log file for the related database.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
----------
DBCC SHRINKDATABASE
Shrinks the size of the data files in the specified database.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
--
<that DBCC SHRINKDATABASE does all files?
yes, it does (read above)
0
 
brokeMyLegBikingAuthor Commented:
ok, thx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now