Maintenance Tips

Introduction

This topic gives useful commands that can be used to manage SQL Server instance.

They can be executed as a new query via SQL Server Management Studio.

You have to log on with the “sa” user (or with an other account who have sufficient privilege).

To Get the Transaction Log Size

DBCC SQLPERF(LOGSPACE)

To Show Information Header of Backup

RESTORE HEADERONLY

FROM DISK = 'PATH_TO_BACKUP_FILE'

To List the Backup Files

RESTORE FILELISTONLY 

FROM DISK = 'PATH_TO_BACKUP_FILE'

To Manually Truncate Transaction Log File

If the transaction log file grows unexpectedly it can be truncated manually. Usually this operation is automatically performed by the maintenance plan.

Backup manually the database.

Execute this command by replacing the database name:

USE [Database_Name]; 

GO 

ALTER DATABASE [Database_Name] SET RECOVERY SIMPLE; 

GO 

DBCC SHRINKDATABASE(N'Database_Name')

GO 

ALTER DATABASE [Database_Name] SET RECOVERY FULL; 

GO