Saturday, May 10, 2008

Shrink SQL Server transaction log files

Once I'm using database mirroring, all my databases has the recovery model set to Full.

When using this recovery model, you must have a good backup policy to regularly backup your transaction log, between full backups of your database. If not, the database transaction log file size may increase a lot.

To shrink the file size I use the following script:

exec sp_dboption DBName, 'trunc. log on chkpt.', true

checkpoint

DBCC SHRINKFILE (DBNameFileName, 500);

exec sp_dboption DBName, 'trunc. log on chkpt.', false

4 comments:

Chantal said...

Hi, we've split out data files and log files to 8 files each, would I need to run this script for all data and log files.

Alexis said...

For work with sql files I usually use-mssql recovery.Because tool is quite reliable and has free status as far as I remember.Moreover tool helped me many times and can also repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Alexis said...

My friend advised me-.mdf file recovery.As he said this tool recovers sql files and to my surprise tool solved like issues very easy.Moreover tool was free as far as I know and it could compatible with all supported versions of Microsoft Windows operating system, it opens Microsoft SQL databases.

Alex said...

For me sql server is a quite important tool,because I often work with it. But yesterday I had a big unplesant problem. For luck I fast found a next software - recovery mdf file. And it determined my issue quite fast and easy as far as I remember. Moreover it learnt me how repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).