How to Reset Full Log File

[CoolGlenn]

I am having a problem right now.. It is my first to use Microsoft SQL. How do i reset logfile if it is full..

Please have it answered immediately..

Thanks

 

[sharkette11]

You can use

backup log [database name] with truncate_only

but if you don't want to use the log for any reason (i.e. point in time backups) then I suggest you change the database so that it doesn't keep a record of the transactions at all. Go to Properties of the database and under the option tab, change Model from Full to Simple.

 

[mrdenny]

After truncating the log you need to do a full backup then shrink the log file to a more resonable size. The shrink is done via the DBCC SHRINKFILE command.

You will want to shrink it to about twice the size of a single says logs. Then setup regular transaction log backups. Hourly or daily or whatever depending on your recovery needs.

When you backup the log on a regular basis the log will not grow out of control.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com