Truncate Log File
-
I have a database that has a 14 GB log file that I am going to attempt to truncate. The problem is that I have to back it up over the network since it requires at least 14 GB of space for the backup. Question is: What is the best method of reducing the physical size of the log after the backup? Is is DBCC SHRINKFILE? Is the file automatically truncated after a full backup using a Complete backup scheme? Any assistance is greatly appreciate since I inherited this beast.
-
For the first time
back up <logfile_name> with_truncate only.
After that, issue the dbcc shrinkfile command.
After that set the database maintenance plan to take back up of transaction logs every one hour. So that it will automatically truncate the inactive portion of the transaction logs
-
Hi,
Backup Log <database name> with truncate_only
DBCC shrinkfile(Logical file name of log fiel,size u want to shrink)
For future set a Database Optimization Plan which will shrink the file after the backup is done.
HTH
from
Killer
-
Does Shrinking of the files improve performance, I mean if i have enough of space is it still necessary to shrink a datafile or a log file ....
Would it help.
Can someone give the advantages & dis-advantages of shrinking the datafile.
Thanks
-
the question is if you need point in time restoration
if no, then just set the recovery model to simple so that you don't have to shrink the log file, it'll grow to a size that will handle your largest batch
if yes, frequent log backup will help you manage the log file size by removing entries that were already committed and backed up
ime, shrinking the file is not an efficient way of managing the size, because it'll grow to the same size if not larger eventually, that's not management
HTH
-
Thanks everyone for your advice. The dilemma is actually the fact that I don't have enough room on the filesystem to do the copy. I have to do it over the network but the domain account does not have network privileges. Which account should have these privs? The windows authenticated login account? Any help would be greatly appreciated.
-
the windows account that runs the sql services
-
The dilemma is actually the fact that I don't have enough room on the filesystem to do the copy.
This is the reason for the "with truncate only" on the backup command, it just truncates the log file without backing anything up.
Backup Log <database name> with truncate_only
Will set the log file as backed up, without anything actually being backed up. You can then:
DBCC shrinkfile(logFileName.ldf, New file size)
Which will shrink the log file. None of this will require any sort of drive space to do. After this is done, backup your database immediatly, as you are no longer going to be able to recover to the current time without it.
Once you are back to a normal log file size, either change to simple logging or setup a maintenance plan to backup your log file regularly. Which you choose will depend upon the acceptability of data loss. If you can afford to go all the way back to your last full back, you can just use simple logging. If you need to be able to recover within a shorter period of time, you'll need to do full logging and back the log up regularly.
As for which account to use to write to a network drive, this is going to depend heavily on how your network admin has things setup. Talk to the guys who handle that.
-
OK, I am almost there. I have the permissions to a network drive now..and I still don't see the drive come up via Enterprise Manager....just the local drives and I have the other drive mapped. Any suggestions? or reasons why the directory is not showing up? Again, I am attempting to backup to another machine and I have mapped the other machines drive and I don't see it in EM when I do a Xaction Log backup.
-
dear ,
If u want to backup the database to a network drive then u have to backup the database through code or from the database maintinance plan.
U should have domian logon permission on the system u are backing up the database as well on the folder u are going to store backup batabase.
SQL server show only the system drives not the map drives u have to work with code in that case.
in maintinance plan u can give the address of the drice like this
\\ip address or computername\foldername
the same through code.
from
Killer
-
dear ,
If u want to backup the database to a network drive then u have to backup the database through code or from the database maintinance plan.
U should have domian logon permission on the system u are backing up the database as well on the folder u are going to store backup batabase.
SQL server show only the system drives not the map drives u have to work with code in that case.
in maintinance plan u can give the address of the drice like this
\\ip address or computername\foldername
the same through code.
from
Killer
-
specify it in the path, do not browse coz you will not see it there...
-
In Enterprise Manager you will only be able to backup to either local or mapped drives. In order to map the drive, either go through Windows Explorer (Tools - Map Network Drive) or go to a command prompt and use:
net use n: \\server_name\share_name /user:domain\user_name
Which will map your share to the N:\ drive. You will then be able to use this mapped drive in the backup database wizard.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply