Monday, September 15, 2008

SQL Server 2005 Log Truncating

To set a database to automatically shrink when space is made available enter the following command in a query window:

ALTER DATABASE DB_NAME SET AUTO_SHRINK ON

To set the database to truncate the logs after a backup, enter this command into a query window:

EXEC sp_dboption 'DB_NAME ', 'trunc. LOG ON chkpt.', 'TRUE'

To manually shrink a database with free space:

DBCC SHRINKDATABASE (DB_NAME)

To manually truncate the logs, enter this command into the query window:

BACKUP LOG DB_NAME WITH TRUNCATE_ONLY

No comments: