September 22, 2008 at 4:25 am
hi ,we r having production server,in that a space issue is came, we had one db with datafile of 1 Gb and log file of 65Gb, what actions should be tacken to red{uce the log full,and how to release the space..{give the response for the db is in both simple and full recovery mode}
September 22, 2008 at 5:49 am
With the ratio of data to log file size I'm going to guess your database is in full mode and you haven't been backing the log up. This causes the log to grow until it fills the disk and then you get the much gnashing of teeth, wailing and weeping. This is known as "A Bad Thing".
For a test or development system, use the brutal but effective method of changing your database to simple mode, shrinking the log (use EM if needed) and marvel at the disk space reclaimed. You can then put your database back into full mode before configuring the various maintenance plans needed to keep things ticking over and not going back to this situation.
For production systems you need to know your backup is good. And that means you've tested the restore on another server, and the database has come up, and you can select stuff from the tables. Don't just assume that because Veritas (or whatever) says it's backed up that it is.
If (and it's probably a big if) there is space on your server, back up the log file. You should now be able to shrink the log file. Create maint plans as needed to prevent it happening again.
If you don't have enough space on the server then you can always use the method above for test systems (at your own risk). You should of course take and test a backup of the newly shrunk database immedately before proceding to create some maint plans to stop it happening all over again in a few days/weeks/months.
September 22, 2008 at 6:23 am
In simple recovery the log shouldn't grow large as old log records are automatically discarded when they are no longer necessary. If the log is growing in simple recovery, then you have either long-running transactions or transactional/merge replication that isn't working properly.
In full recovery mode, you need to back the log up on a regular basis otherwise it will grow without bound.
I would suggest, seeing the state of the log, run a backup log .. with truncate. Then shrink the log file down to a reasonable size. Run a full backup immediately after (to restart the log chain) and then implement log backups on that DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply