Sql-server – Should I be regularly shrinking the DB or at least the log file

dbccmaintenancesql server

My question is, should I be running one or both of the shrink command regularly,

DBCC SHRINKDATABASE

DBCC SHRINKFILE

=============================

background

Sql Server: Database is 200 gigs, logs are 150 gigs.

running this command

SELECT name ,size/128.0 - 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0 
AS AvailableSpaceInMB FROM sys.database_files;`

produces this output..

MyDB: 159.812500 MB free

MyDB_Log: 149476.390625 MB free

So it seems there is some free space.

Our backup schedule is as follows:

Transaction Logs Once an hour
Full Backups twice a week
Differential Backups 5 times a week

Best Answer

I strongly recommend you read Paul Randal's article on why you should NOT shrink data files (log files yes, data files no).

I won't quote or try to summarise the article as I really wouldn't do it justice! Just something I think you should at the very least be aware of.

Related Solutions

SQL Server 2008 – How to Shrink Log File

I don't see any reason to repeat the command 6 times as you said. 1 within the command is the target size. DBCC ShrinkFile allows to specify a value < the original size when the file was created. Beware though of the line : Alter Database DatabaseName Set Recovery Simple You will have to backup your database first as if you use another recovery model (either full or bulk_logged) the log chain will be broken.

Backup your database
Swith the recovery model to simple.
Run DBCC ShrinkFile command (I recommend a reasonable size here , if you have a 5Gb log file you may need a bigger original size, and or bigger increments in MB for its growth.
Switch back to the original recovery model.

Sql-server – Shrinking the log file does not reduce size

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

background

Best Answer

Related Solutions

SQL Server 2008 – How to Shrink Log File

Sql-server – Shrinking the log file does not reduce size

Related Question