SQL Server 2008 Certification Details

Useful information about Sql Server 2008 Certification Exams : (Click on the image to read)

Log file Maintenance in SQL SERVER 2000/2005 & 2008 :

In earlier version of SQL Server if the database log is full then by truncating the Log file, we can free up the database log file space. For an example you have allocated 5 GB for log file and it crosses the limit after certain period. As a result it doesn't allows any database transactions and receives continuous errors. In SQL server 2005 and 2000 we can free up the space by using following commands.

USE DatabaseName
* Database will be your user database.

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
* it will truncate all the contents from log file

DBCC SHRINKFILE (Log File)
* It will shrink the file into its intial size.

But the above process doesn't work in SQL server 2008. These features are discontinued from the latest version and for achieving this requirement you need to work on following method.

First Alter the database from Full recovery mode to Simple recovery mode, then Shrink the database and again change the mode into full.

ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
* This will change the database mode into simple

DBCC SHRINKFILE (Log File)
* It will shrink the file into its intial size.

ALTER DATABASE DatabaseName SET RECOVERY FULL
* This will change the database mode into full

The above process can be done using SQL Server Management studio also.

To check up the database and its file details you can use system procs sp_helpdb, sp_helpdb databasename.