November 1, 2006 at 2:19 pm
Okay.. so I have been in the Oracle world for years and this SQL Server business is not as clear as I would like it to be so be gentle.
I have tried to setup a 'Maintenance Plan' to backup the databases (all, master, customer dbs, etc) and transaction logs. I noticed that some of our production databases were set for SIMPLE recovery mode (including the master). Our SharePoint database is growing fast and at one time the Transaction Log was 19 GB. Yup! The databases were setup in a vacuum and it wasn't until recently that I was asked to oversee the SQL Server world as well.
Anyway .. I am having trouble understanding whether or not I my Transaction Logs are getting truncated. I noticed this afternoon that my SharePoint Trans-Log file is back up to 600+ MB.
Can anyone help? I thought backing up the database and log file that SQL would automatically truncate (recapture the unused space). What do I have to do to get this stuff to work?
All of the databases that I care about are set to FULL recovery and at one I had the option 'auto shrink' enabled but that appears to only affect the database and not the log file (so it seems).
I would hate to take a maintenance window once a month to deal with this issue.
Thanks!
November 1, 2006 at 3:17 pm
issue this command in Query Analyzer.
back up <logfile_name> with truncate_only
dbcc shrinkfile <logfile_name>.
This will reduce the logfile.
Then set up the maintenance plan to take full backups every night and transaction log backups every one hour.
November 2, 2006 at 8:09 am
Thanks. This is what I have found as well, 'backup log .. with truncate_only'. Which process should I use?
If I use the maintenance plan it looks as though the transaction log file creates seperate log files with the option to create seperate directories. This feature I like. But the Maintenance command created looks like 'EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3B785293-44B4-4A76-A......'. Is it possible to include 'with truncate_only' on this command like? What would it look like?
Full statement: EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3B785293-44B4-4A76-ADAB-C5520E202949 -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL$SQLTEST\LOG\DB Maintenance Plan16.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -CrBkSubDir -BkExt "TRN"'
If I create a seperate backup job for a database (vs the maintenance plan - you can include all db's), I do as you suggested but it looks as though I get one TRN file vs a log file for every hour. The maintenance plan creates unique filenames.
Is there a best practice? Is it better to run a maintenance plan for the data and run seperate log backups for individual databases? What about the DBCC SHRINKFILE, I remember having to set my database in SIMPLE recovery mode before I could shrink the logfile. If 'with truncate_only' is doing the job of shrinking the logfile, why include DBCC SHRINKFILE?
Unless you were just referencing the command!? I should be able to use one step in backup job vs multiple steps.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply