September 29, 2003 at 10:34 am
I am relatively new to sql server.
This morning I noticed that the filesize of one of my database's .LDF file was 3GB. Even though a backup file of the database is only 400mb. Sounds crazy to me. What could be going on?
ps i use sql server 2000. the corresponding .MDF file is around 500mb.
September 29, 2003 at 11:08 am
If you do not have recurring log backups your log will continue to grow as DB backups do not truncate the log file. If you do not require transactional recovery you can switch your recovery mode to simple which basically truncates the log on checkpoint. For the immediate need you can run a backup of the log (or truncate it) then do a dbcc shrinkfile on it to shrink the footprint.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 29, 2003 at 1:10 pm
I suggest you do a search of the topics, especially in the Backup forum. This subject has been covered quite a lot.
So, look through the forums, find what has already been discussed and if that doesn't answer your questions, then let us know what else you need answered.
-SQLBill
September 30, 2003 at 5:21 am
Thanks for your answers. All sorted now.
September 30, 2003 at 9:20 am
thanks yogi
September 30, 2003 at 11:37 am
Here is a script my company uses regularly to clean up large logs. The last statement is to ensure the size information is up to date. We had several instances where it's not.
-------------------------------
DECLARE @DB nVarchar(100),
@SQL nVarChar(1000);
SELECT @DB='MyDatabase'
-------------------------------
SELECT @SQL='BACKUP LOG '+@DB+' WITH TRUNCATE_ONLY'
EXEC sp_ExecuteSQL @SQL
SELECT @SQL='DBCC SHRINKDATABASE ('+@DB+')'
EXEC sp_ExecuteSQL @SQL
SELECT @SQL='DBCC UpdateUsage ('+@DB+')'
EXEC sp_ExecuteSQL @SQL
-------------------------------
-Regards
Edited by - dragon_1973 on 09/30/2003 11:37:11 AM
-Regards
September 30, 2003 at 11:49 am
thanks dragon_1973
September 30, 2003 at 1:06 pm
quote:
Here is a script my company uses regularly to clean up large logs. The last statement is to ensure the size information is up to date. We had several instances where it's not.-------------------------------
DECLARE @DB nVarchar(100),
@SQL nVarChar(1000);
SELECT @DB='MyDatabase'
-------------------------------
SELECT @SQL='BACKUP LOG '+@DB+' WITH TRUNCATE_ONLY'
EXEC sp_ExecuteSQL @SQL
SELECT @SQL='DBCC SHRINKDATABASE ('+@DB+')'
EXEC sp_ExecuteSQL @SQL
SELECT @SQL='DBCC UpdateUsage ('+@DB+')'
EXEC sp_ExecuteSQL @SQL
-------------------------------
If you truncate the logs "regularly," that's tantamount to just setting them to truncate on checkpoint (Simple recovery model in SQL Server 2000). This means that you've broken the chain of recoverable log backups and your database is not in a recoverable state until the next full or differential backup is performed.
Some basic points:
[*]b) it may make running out of disk space less likely.
The disadvantages are
[*]b) no point in time recoverability
[*]c) no recovery to time of failure
If you decide that recoverability to the last full or differential backup is "good enough" for your environment, then you are forsaking sophistication for convenience.
[*]2. If you do not back up the tran logs specifically, they will not truncate. Full or Differential backups do not truncate the transaction logs.
[*]3. Tran log backups are separate from other backups and can be restored independently. If you perform full backups every night and tran log backups twice a day, and you find that last night's backup is corrupt, you can restore the previous night's backup (with norecovery) and apply the log backups to reach a more current state.
[*]4. The first thing you should do if disaster strikes is attempt a tran log backup. DBAs that I instruct get this drilled into them, and it has saved several.
[*]5. If you change your model to Bulk-Logged ("select into/bulkcopy" set to true in previous versions), in order to, say, rapidly load from a text file, you should change the model back to Full and immediately back up first the tran log and then the database.
[*]6. Try and time tran log backups before a full (or differential) backup rather than after. If you back up the tran log after the database, you will need to restore both tran log backups when recovering after restoring that database backup.
[*]7. Do not shrink the tran log files as a matter of course. These files should be sized to contain the maximum amount of logged information that may occur between backups. If you insist on shrinking them, they will only grow as necessary, which will lower the performance of your system by sapping processing for autogrowth and creating file fragmentation. (Similarly, if you estimate that a database will grow to, say, 20GB, allocate that much space when you create it.)
[*]8. Prove that you can sucessfully restore and recover a database to its most recent state. Create a test database and maintain it exactly as you maintain your production databases. Then have a "fire drill" where you pretend that the hardware with the data files has failed. (What's the first thing you try? See 4 above.) Validate your tape backups and recovery plan this way at least once a year or whenever you change anything in the backup process. You don't know if your backups are working unless you try and restore, and you don't want to find out the hard way. Also practice a point in time recovery; I've been known to forget the where clause in a delete statement.
[*]9. Hard disks and other hardware will fail. It's a question of when, not if.
[*]10. Do not back up the database's files (mdf, mdf, ldf) unless you first detach the database or stop the SQL service. If you must use a third party backup utility without native SQL Server support, back up your databases and logs to disk (on a separate physical disk from the data and log files, please) and then back up those backup files.
[*]11. Do not delete or overwrite any backups until you have at least two independent restore choices (see 3 above).
[*]12. You cannot disable logging. It is an integral component of SQL Server and helps guarantee the system's ACID properties.
[*]13. If you have a write-caching disk controller, make sure that its memory is redundant and battery-backed. Understand how to recover its cache to the disks in a disaster. Disable write caching on the disks themselves.
[*]14. Ensure that the tran log files are on redundant physical disks, and not on the same array as the data. RAID 1 is ideal for tran logs because their typical I/O (unlike the data files) is sequential. If you have several databases being used concurrently, it can be beneficial to put each log file on a separate RAID 1 drive pair; otherwise the sequential writes start to look like random I/O.
Wow, once I started typing, I couldn't stop. I wonder what I forgot...
--Jonathan
--Jonathan
--Jonathan
October 1, 2003 at 3:52 am
thanks Jonathan. very informative.
October 1, 2003 at 7:16 am
Jonathan,
How about turning that into a FAQ (Resources>FAQ)?
-SQLBill
October 1, 2003 at 8:04 am
Perfect! Thanks a lot, Jonathan, and I second SQLBill that this should go into FAQ or some other place, more permanent than a forum thread.
October 1, 2003 at 12:04 pm
I agree, your post should be a FAQ. Don't get me wrong, we don't run this script because we are lazy or inept. It gets run only when we run into physical space problems, and then only on databases that have a recent reliable backup we can go back to if we need to. Also, a manual backup is done immediately after we run this script.
Unfortunately, our company generates millions upon millions of transactions a day, and we never have the space to store them all... We try, but those with the purse strings just don’t understand. We haven’t had a disaster we’ve not been able to recover from, so they are not worried enough yet.
-Regards
-Regards
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply