Hi, In my server, data file is in D drive and Log file is in F Drive. Now my log file used all the disk space. When I try to take database backup with no_log option i'm getting the below given error.BACKUP DATABASE dbname TO DISK='D:mydb.bak' with no_log Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.Msg 9002, Level 17, State 2, Line 1 The transaction log for database 'dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases When I check log_reuse_wait_desc in sys.databases it shows LOG_BACKUP. How to clear my log file to gain space without losing data? Thanks in advance. Regards, Mani
Switch the database to SIMPLE mode and then try the backup. From Books Online: "The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model."
Hi Mani, I assume that you have a full-database backup of your DB in question (this, as you are well aware, is mandatory for any kind of recovery processes that might be!) that is closest to your SLA needs! As a first step, you can first evaluate, or have an idea of your logspace of all your databases in the instance by using the command: DBCC SQLPERF(LOGSPACE); GO Once you have an idea as to the amount of space available vs. used, you can take appropriate actions that suits your environment.1. Since you are working with SQL Server 2008 (where future versions of the WITH NO_LOG may be banned/made dysfunctional with the usual backup commands...), you can start by: USE <dbname>; -- to set the context of currency of database of interest GO ALTER DATABASE <dbname> SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (<LogFileName>, <TargetSize>); GO /* Reset the recovery model to FULL as before */ ALTER DATABASE <dbname> SET RECOVERY FULL; GO Please remember the following points: The shrinking process for a transaction log addresses only the inactive virtual log files. So the parameter cannot be less than the overall size defined initially. More help is amply provided in the BOL for the corresponding version. One of the tips given in the MSDN is to check the size of the file in question after the shrinking process with the following command: SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMBFROM sys.database_files; GO Hope these small pointers help you combat your problem. Please share your experiences with the rest of the crowd. With best wishes, Venky
... and next step is to add new disks ,to avoid facing this problem again.[] Plan for storage capacity , not to exceed 80% of disk usage
HI Mani,I have one suggestion too. You can set T-Log backups for this database if you observe the hug transactions and Log Growth. Like this way, you can limit the Log file growth up to some extend.Assume your backup files are in different drive.