How to Shrink Your MS SQL Database Log File / Truncate Transaction Log. This article requires the use of Microsoft SQL Server Management Studio. You do not have to run the instance of SQL Server in single-user mode to shrink the. Size for each log file. DBCC SHRINKFILE tries to shrink each physical. Avanti home gym manual. Protel. How can the answer be improved? Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. Now, depending on the recovery goals of your database, these are the actions you should take. First, take a full backup Never make any changes to your database without ensuring you can restore it should something go wrong. If you care about point-in-time recovery (And by point-in-time recovery, I mean you care about being able to restore to anything other than a full or differential backup.) Presumably your database is in FULL recovery mode. If not, then make sure it is: ALTER DATABASE testdb SET RECOVERY FULL; Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup - this is for your protection, not to needlessly eat away at your disk space. You should be performing these log backups quite frequently, according to your recovery objectives. How ToFor example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don't choose any of the shrink options in maintenance plans, they're awful). DECLARE @path NVARCHAR(255) = N' backup_share log testdb_' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':',') + '.trn'; BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION; Note that backup_share should be on a different machine that represents a different underlying storage device. Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that's on the same physical host) does not really help you, since if the machine blows up, you've lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |