Newbie: Truncating transaction logs

A 3rd party company told us that we need to truncate our SQL Server
Transaction logs because the server's hard disk is getting full. The hard
disk is losing around 10 GB a day. They also told us to change the SQL
Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
BACKUP LOG <DBName> with NO_LOG
ALTER DATABASE <DBName> SET RECOVERY SIMPLE
You should be careful doing this though because it will mean no
transactions are getting logged. So you can only do database backups
(not log backups) on that DB and if you lose your DB (eg. corruption)
between database backups, you'll have to restore from your last full
backup (so you will lose all data that had be written to the database
since then). What is a more normal thing to do is to keep your database
in FULL recovery mode and schedule regular transaction log backups
(which is easiest to do with the Database Maintenance Wizard that comes
as part of SQL Enterprise Manager).
If you're going to trash your transaction log and are not interested in
keeping it then you may as well shrink your log file down for your
database as well. To do that you need to know the logical name of the
log file, which you can find with
exec sp_helpdb <DBName>
and then shrink the file with
USE <DBName>
go
DBCC SHRINKFILE('<LogicalFileName>')
Hope that helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:

>A 3rd party company told us that we need to truncate our SQL Server
>Transaction logs because the server's hard disk is getting full. The hard
>disk is losing around 10 GB a day. They also told us to change the SQL
>Server Backup type from Complex to Simple.
>How do I truncate the log files and how do I change the backup type?
>Regards
>Thief_
>
>
|||Hi,
For me the best practice i do is
backup log epinav with TRUNCATE_ONLY
dbcc shrinkfile(logical name,size)
this will shrink the size of logfile on disk.
hope this help u
from
Doller
|||Hi,
This method is not recommended on the production system or any system which
has critical data.
If you make the recovery model SIMPLE then you can not perform a transaction
log backup. In this case
if you have any database crash you can recover the database only until the
last fulll backup.
So instead of changing the recovery mode; have the mode as "FULL" and
schedule a transaction log backup
using Maintenance plan and put a house keeping mechanism to purge the old
files.
If you data is not critical then follow as dolar pointed out:-
backup log epinav with TRUNCATE_ONLY
go
dbcc shrinkfile(logical name,size)
go
ALTER DATABASE <dbname> set recovery SIMPLE
Thanks
Hari
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1127292013.451860.127540@.o13g2000cwo.googlegr oups.com...
> Hi,
> For me the best practice i do is
> backup log epinav with TRUNCATE_ONLY
> dbcc shrinkfile(logical name,size)
> this will shrink the size of logfile on disk.
> hope this help u
> from
> Doller
>
|||Hari,
[vbcol=seagreen]
How specifically do I do that?
Regards
Thief_
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ecyPfHqvFHA.2792@.tk2msftngp13.phx.gbl...
> Hi,
> This method is not recommended on the production system or any system
> which has critical data.
> If you make the recovery model SIMPLE then you can not perform a
> transaction log backup. In this case
> if you have any database crash you can recover the database only until the
> last fulll backup.
> So instead of changing the recovery mode; have the mode as "FULL" and
> schedule a transaction log backup
> using Maintenance plan and put a house keeping mechanism to purge the old
> files.
> If you data is not critical then follow as dolar pointed out:-
> backup log epinav with TRUNCATE_ONLY
> go
> dbcc shrinkfile(logical name,size)
> go
> ALTER DATABASE <dbname> set recovery SIMPLE
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "doller" <sufianarif@.gmail.com> wrote in message
> news:1127292013.451860.127540@.o13g2000cwo.googlegr oups.com...
>
|||Why are the server hard disks losing space so quickly? Is it the fact that
the log files are not being deleted and everyday new ones are created adding
to the congestion?
How do I delete the existing log files? Are they simply files on the
server's hard disk?
Regards
Thief_
"Thief_" <thief_@.hotmail.com> wrote in message
news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>A 3rd party company told us that we need to truncate our SQL Server
>Transaction logs because the server's hard disk is getting full. The hard
>disk is losing around 10 GB a day. They also told us to change the SQL
>Server Backup type from Complex to Simple.
> How do I truncate the log files and how do I change the backup type?
> Regards
> Thief_
>
|||Use the Database Maintenance Wizard.
That is, open SQL Enterprise Manager, expand the server in the tree view
in the left pane, expand "management", right click on "Database
Maintenance Plans", click "New Maintenance Plan...". Then just go
through the wizard pages paying particular attention to the "Specify the
Transaction Log Backup Plan" page (which is about 6 pages into the
wizard). On the next page ("Specify Transaction Log Backup Disk
Directory") you define how long to keep old backup files for.
Typically you keep backup files on disk at least long enough to get them
on tape with some other general backup software (like Veritas
BackupExec, etc.), but preferably a few days because most restores are
from backups sometime in the last few days (like a business unit says
they need the database put back to how it was at 3pm yesterday for
example). The backup file retention period is normally dependant on how
much disk space you can spare for the backup files.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:

>Hari,
>
>
>How specifically do I do that?
>Regards
>Thief_
>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>news:ecyPfHqvFHA.2792@.tk2msftngp13.phx.gbl...
>
>
>
|||Every modification transaction against your database not only changes
the data but is appended to a transaction log. There is 1 transaction
log per database. The transaction log is not a physical file per se,
but is materialised on disk as one or more physical files (but you
cannot just delete the files without corrupting the database).
As I said in my first reply to this thread, you can see where your
physical transaction log file(s) is by using:
exec sp_helpdb <DBName>
This is a Transaction-SQL command that you run from within Query
Analyzer. This stored procedure will return a bunch of data that is
split into 2 sections: 1) summary info about the database and 2) the
names of all the physical files associated with your database. In the
2nd section, one or more rows will have "log only" in the last column of
the resultset. These are the transaction log files.
The way you reduce the size of your transaction logs is by backing them
up (hence all the talk about backing up your log via the Database
Maintenance Wizard). Typically you back up your transaction logs at
least one a day (I backup my production DB logs every 15 minutes).
Immediately after the backup log command is finished (the database
maintenance wizard runs these commands itself once you've set up a plan,
there's no need to do anything yourself other than set up the plan) the
log records automatically get deleted. This does not make the physical
files smaller, it just creates a whole lot of empty space in the
existing physical files that can then be filled up with more transaction
log records. This is by design and for good reason (but I won't go into
that in this thread as it is a tangent and this post is getting long
enough as it is).
If you've let the transaction log of one of your databases grow for a
lengthy period of time, the physical file(s) backing the log has
probably grown to many times the size needed to hold the transactions
you expect to accumulate between your scheduled automated (database
maintenance plan) log backups. So, in that case, it may be worthwhile
shrinking the physical file(s) associated with the log (this is not
something you would normally do in production databases as it pauses all
disk I/O to the file being shrunk while the shrink operation is
happening (and again if the file autogrows)). You shrink physical
files, as I said in my first reply to this thread, by using the DBCC
SHRINKFILE() command. That command requires the name of the logical
file (which sp_helpdb returns). Just make a note of that logical
filename (the first column, "name", in the "files" section of the
sp_helpdb resultset) and use it in the DBCC SHRINKFILE command as follows:
DBCC SHRINKFILE('<LogicalFileName>')
This command has to be run in the database in question with Query Analyzer.
Does this all make sense?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:

>Why are the server hard disks losing space so quickly? Is it the fact that
>the log files are not being deleted and everyday new ones are created adding
>to the congestion?
>How do I delete the existing log files? Are they simply files on the
>server's hard disk?
>Regards
>Thief_
>"Thief_" <thief_@.hotmail.com> wrote in message
>news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>
>
>
|||Thanks Mike,
Your help has been super-human and much appreciated. This is the Maintenance
Plan I have created:
------
DATABASES
JLRXApp
SERVERS
(local)
OPTIMIZATIONS
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
3:00:00 AM.
Perform the following actions:
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 10000 MB. Leave 10 percent of data
space as free space.
INTEGRITY CHECKS
Occurs every 1 week(s) on Sunday, at 2:00:00 AM.
Perform the following actions:
Check database
COMPLETE BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
4:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 3 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
TRANSACTION LOG BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
5:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
Reports will be generated and stored in directory: d:\d:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\Log
Operator 'SQL_Backup_Notify' will be e-mailed.
------
Here's my DB info:
JLTESTAPP_Data 1 d:\Program Files\Microsoft
SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB
Unlimited 10% data only
JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL
Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB
Unlimited 10% log only
I hope I have setup the MP properly. I have set it up to shrink the DB
automatically- I hope this is OK?!
Regards
Thief_
"Thief_" <thief_@.hotmail.com> wrote in message
news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>A 3rd party company told us that we need to truncate our SQL Server
>Transaction logs because the server's hard disk is getting full. The hard
>disk is losing around 10 GB a day. They also told us to change the SQL
>Server Backup type from Complex to Simple.
> How do I truncate the log files and how do I change the backup type?
> Regards
> Thief_
>
|||OK, a number of points...but firstly a couple questions. Is this a
production DB or a test/dev DB? Is it a super-active DB or just a DB
that has relatively normal activity against it but just hasn't had its
log backed up for ages? I'll assume it is a production DB and it's just
a normal OLTP database that hasn't had a log backup for a long time.
Given those assumptions, if this was my DB, I would make these changes:
* Do the optimisations once per week (this essentially rebuilds all
your indexes, which is a fairly intense activity). I would
schedule it for Saturday or Sunday night. And *don't
automatically shrink your files.* Shrinking the data file will
cause a large amount of log activity (because it's shuffling data
pages all over the place, hence it needs to log all that change
activity).
* Integrity checks once a week on the weekend is good. Make sure
you check the SQL errorlog each Monday (minimum) to see if the
DBCC CHECKDB command (that's the command that SQL Server runs in
this Integrity Check stage) logged any errors or warnings.
* Full database backup once a day is good. Sometime out of peak
hours (like 4am) is good. However, I would make the target of all
DB backups a different physical disk to where the data & logs
live. For example, if your data & logs live on the D: drive, then
I would add another local HDD for the backup files (call it E:
drive) and backup to that disk. This is because, if you have a
HDD failure and lose your D: drive completely, unless it's part of
a fault tolerant RAID set (like RAID 1 or RAID 5) then not only
have you lost your data & logs but you've lost your backup of
those DBs too! Better yet would be a backup disk volume that
didn't live inside the server at all (to safe-guard against the
whole server catching fire, etc.), like a disk on a SAN or NAS
device, or even a DAS (like an external RAID cage). However, this
can add considerable initial cost so it can often be hard to justify.
* A transaction log backup once a day that occurs 1 hour after the
last full DB backup will be next to useless. How much activity
occurs in the 1 hour, from 4am to 5am, between the full DB backup
and the transaction log backup? Remember, the transaction log
records *changes to the database*, so to restore you'd restore the
last full DB backup and any subsequent transaction log backups
that occurred after the DB backup. If I were you I'd start with
backing up the transaction log once per hour, eg. every hour
starting at quarter to one [00:45am] (offset from 0:00 as often
jobs get scheduled on the hour so this can be a busy time to do a
backup), to the same directory you put the DB backup files. I'd
also keep them for the same amount of time as the full DB
backups. If you keep 3 days of DB backups, then keep 3 days of
transaction log backups (you can't use any transaction log backups
that were taken before your oldest DB backup because you have to
restore them AFTER you restore the appropriate DB backup).
The points about auto-shrinking files & transaction log backups are
probably the most important points here. Also, I'd adjust the settings
of the DB itself a little. This is how I would set the data file & the
log file:
* I'd set your 8.4GB data file to be 9GB and autogrow by 1GB at a
time (nice large, round numbers - neat). This is the command that
would do that:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME='JLTESTAPP_Data', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB)
* I'd set the 42.8GB log file to be 1GB and autogrow by 512MB at a
time. This is the command that would do that:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME='JLTESTAPP_Log', SIZE=1024MB, MAXSIZE=UNLIMITED,
FILEGROWTH=512MB)
However, you'll have to shrink your log before you can set it to the new
size. There's not much point in backing up all 44GB - you'd be better
off truncating the log and then doing a full DB backup:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXApp TO
DISK='D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\JLRXApp_FixUp.bak' WITH INIT
The database should now be backed up and the log shrunk, so now you can
set the size of the transaction log file with the 2nd ALTER DATABASE
statement above. After a couple weeks, once you're sure things are
humming along nicely, you'll be able to delete that JLRXApp_FixUp.bak
file (which was just a precaution really).
All this should leave you with:
- a 9GB database
- a 1GB log
- that get reindexed & checked on a regular basis (once a week on the
weekends)
- and get backed up on a nice regular schedule that shouldn't overtax
your disk space.
Good DB setup, good maintenance plan. Just make sure you test your
backup files regularly (try restoring the backup files (both database
backups *and* log backups) to another server (or at least another DB on
the server) once a month or so) because if you can't restore from your
backup files, then they are useless and you may as well not be doing
them (always test your recovery strategy - recovery is the whole reason
you do the backups in the first place).
Remember, this is just a starting place. I'd expect you to monitor how
things are going (disk space, DB size, index performance, etc.) and
tweak things as appropriate (like more/less regular tlog backups or more
regular optimisations or something like that). Don't forget to
regularly test your backup files!
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:

>Thanks Mike,
>Your help has been super-human and much appreciated. This is the Maintenance
>Plan I have created:
>------
>DATABASES
> JLRXApp
>SERVERS
> (local)
>OPTIMIZATIONS
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
>3:00:00 AM.
> Perform the following actions:
> Reorganize data and index pages, changing the free space to 10 percent of
>the original space.
> Shrink database when it grows beyound 10000 MB. Leave 10 percent of data
>space as free space.
>INTEGRITY CHECKS
> Occurs every 1 week(s) on Sunday, at 2:00:00 AM.
> Perform the following actions:
> Check database
>COMPLETE BACKUP
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
>4:00:00 AM.
> Backup media: Disk
> Store backup files in the default SQL Server Backup directory.
> Delete backup files which are older than 3 Day(s).
> Verify the backup after completion.
> Create a subdirectory for each database, to store the backup files.
>TRANSACTION LOG BACKUP
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
>5:00:00 AM.
> Backup media: Disk
> Store backup files in the default SQL Server Backup directory.
> Delete backup files which are older than 4 Day(s).
> Verify the backup after completion.
> Create a subdirectory for each database, to store the backup files.
>Reports will be generated and stored in directory: d:\d:\Program
>Files\Microsoft SQL Server\MSSQL\BACKUP\Log
>Operator 'SQL_Backup_Notify' will be e-mailed.
>------
>Here's my DB info:
>JLTESTAPP_Data 1 d:\Program Files\Microsoft
>SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB
>Unlimited 10% data only
>JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL
>Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB
>Unlimited 10% log only
>
>I hope I have setup the MP properly. I have set it up to shrink the DB
>automatically- I hope this is OK?!
>Regards
>Thief_
>
>"Thief_" <thief_@.hotmail.com> wrote in message
>news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>
>
>