Metadata Services: 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_This is a multi-part message in MIME format.
--030007050705010603010200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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_
>
>
--030007050705010603010200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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 
</tt>
<blockquote><tt>exec sp_helpdb <DBName></tt> 
</blockquote>
<tt>and then shrink the file with 
</tt>
<blockquote><tt>USE <DBName></tt> 
<tt>go</tt> 
<tt>DBCC SHRINKFILE('<LogicalFileName>') 
</tt></blockquote>
<tt>Hope that helps.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midOviKzNnvFHA.3688@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">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_
</pre>
</blockquote>
</body>
</html>
--030007050705010603010200--|||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.googlegroups.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,
>>schedule a transaction log backup
>>using Maintenance plan and put a house keeping mechanism to purge the old
>>files.
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.googlegroups.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
>|||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_
>|||This is a multi-part message in MIME format.
--040606020606020807010101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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,
>
>>schedule a transaction log backup
>>using Maintenance plan and put a house keeping mechanism to purge the old
>>files.
>>
>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.googlegroups.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
>>
>>
>
>
--040606020606020807010101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midu4Lu0tzvFHA.3864@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">Hari,
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">schedule a transaction log backup
using Maintenance plan and put a house keeping mechanism to purge the old
files.
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--040606020606020807010101--|||This is a multi-part message in MIME format.
--060209080001050000020506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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...
>
>>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_
>>
>
>
--060209080001050000020506
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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: 
</tt>
<blockquote><tt>exec sp_helpdb <DBName> 
</tt></blockquote>
<tt>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: 
</tt>
<blockquote><tt>DBCC SHRINKFILE('<LogicalFileName>') 
</tt></blockquote>
<tt>This command has to be run in the database in question with Query
Analyzer. 
 
Does this all make sense? 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midOoCW$tzvFHA.2956@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">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_" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:thief_@.hotmail.com"><thief_@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl">news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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_
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--030107030007020107030305--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C5C294.05010B30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
WOW! The time you've spent is recorded in the back of my mind- always. =The info you have supplied is super and will become part of my =knowledge-base for ever. As for your questions, the DBs JLRXAPP & =JLRXSYS are our production DBs (although the difference b/w the APP & =SYS DBs is unknown to me at the moment but I've asked our Solomon =support company for details on each) and our test DBs are JLRXTestApp & =JLRXTestSys.
I feel that the shrinking of the DBs will be a time-crunching effort so =I will perform it later tonight when everyone is off the system.
Regards
Thief_
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl...
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:
a.. 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). b.. 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. c.. 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. d.. 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:
a.. 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=3D'JLTESTAPP_Data', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED, =FILEGROWTH=3D1024MB)
a.. 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=3D'JLTESTAPP_Log', SIZE=3D1024MB, MAXSIZE=3DUNLIMITED, =FILEGROWTH=3D512MB)
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=3D'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=20
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...
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_

--=_NextPart_000_000F_01C5C294.05010B30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Mike,
WOW! The time you've spent is recorded =in the back of my mind- always. The info you have supplied is super and will become =part of my knowledge-base for ever. As for your questions, the DBs JLRXAPP & =JLRXSYS are our production DBs (although the difference b/w the APP & SYS =DBs is unknown to me at the moment but I've asked our Solomon support company =for details on each) and our test DBs are JLRXTestApp & JLRXTestSys.
I feel that the shrinking of the DBs =will be a time-crunching effort so I will perform it later tonight when everyone =is off the system.
Regards
Thief_
"Mike Hodgson" wrote in message news:urrw9VBwFHA.1148=@.TK2MSFTNGP11.phx.gbl...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=3D'JLTESTAPP_Data', SIZE=3D9216MB, =MAXSIZE=3DUNLIMITED, FILEGROWTH=3D1024MB)
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=3D'JLTESTAPP_Log', SIZE=3D1024MB, MAXSIZE=3DUNLIMITED, FILEGROWTH=3D512MB)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_LOGCHECKPOINTDBCC SHRINKFILE('JLTESTAPP_Log')BACKUP DATABASE JLRXApp TO DISK=3D'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\JLRXApp_FixUp.bak' WITH =INITThe 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 =hodgsonblog: 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_" 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_
--=_NextPart_000_000F_01C5C294.05010B30--|||This is a multi-part message in MIME format.
--050801080804090309010007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Shrinking the *data file* is time consuming and I/O intensive because
SQL server will actually be shuffling pages around (hence the need to
log those activities in the transaction log). But shrinking
*transaction log files* is very quick & easy because it's just
truncating the inactive portion of the log (not actually moving pages
around). So it's fairly safe to shrink transaction log files in
business hours (given the normal caveats around shrinking files &
autogrowing files).
However, if the active portion of the log is at the end of the file,
then a DBCC SHRINKFILE operation won't achieve much because it can only
truncate back to the end of the active portion (so the log file may
consist of gigabytes of empty space at the beginning of the file
followed by a few megabytes of real transactions at the end of the
file). The reason behind this is that transaction logs are divided up
into virtual log files, which are cyclical - when you reach the end of
the physical file, SQL Server will start writing records again from the
beginning of the physical file until it comes full circle back to where
it started (at that point it must increase the size of the physical file
again). So when you truncate a transaction log, usually by doing a
BACKUP LOG, all the transactions in the log are backed up and deleted,
leaving a largely empty log file. SQL Server doesn't start writing
again from the beginning of the file, it just keeps going from where it
left off - hence the circular/cyclical aspect of the log. This means
that sometimes you can get huge amounts of empty space at the start of
the file and just a few transactions at the end of the file and that
means SQL Server can only truncate the very end of the physical file (if
anything). So your 44GB log file may only shrink down to 43GB for example.
This was the reason for doing a BACKUP LOG and a CHECKPOINT, in my
previous post, before actually doing the DBCC SHRINKFILE, just to make
sure the end of the physical log file has been cleared of active
transactions and the DBCC SHRINKFILE will actually achieve something.
The structure/behaviour of the transaction log is actually fairly
complicated - this explanation is really just an executive
summary/paraphrase. If you want to read up on it, SQL Books Online has
some excellent info on the transaction log (like the Transaction Log
Architecture
<http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_876t.asp>
section). For even more I'd recommend Inside SQL Server 2000 by Kalen
Delaney
<http://www.amazon.com/exec/obidos/tg/detail/-/0735609985/qid=1127709307/sr=8-1/ref=pd_bbs_1/104-8387021-1711117?v=glance&s=books&n=507846>,
chapter 5 (Databases & Database Files) - particularly pp.182-186
(dealing with the transaction log structure & changing the log size).
An essential tome for every professional DBA's bookshelf.
So, you should be able to shrink the transaction log very quickly &
easily with very little impact to the end users, but you should ditch
any transactions currently in there (with BACKUP LOG & CHECKPOINT) just
so the shrink operation shrinks right back to the near the beginning of
the file (so you may want to do that after hours so you can do a full
database backup immediately after the shrink without impacting your
users). Of course, you can do the whole thing during business hours,
users will still be able to work, but the performance of the SQL server
will be sluggish while you are doing the BACKUP DATABASE operation
(depending on your hardware).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
> Mike,
> WOW! The time you've spent is recorded in the back of my mind- always.
> The info you have supplied is super and will become part of my
> knowledge-base for ever. As for your questions, the DBs JLRXAPP &
> JLRXSYS are our production DBs (although the difference b/w the APP &
> SYS DBs is unknown to me at the moment but I've asked our Solomon
> support company for details on each) and our test DBs are JLRXTestApp
> & JLRXTestSys.
> I feel that the shrinking of the DBs will be a time-crunching effort
> so I will perform it later tonight when everyone is off the system.
> Regards
> Thief_
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl...
> 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...
>>
>>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_
>>
>>
>>
--050801080804090309010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Shrinking the data file is time consuming and I/O intensive
because SQL server will actually be shuffling pages around (hence the
need to log those activities in the transaction log). But shrinking transaction
log files is very quick & easy because it's just truncating the
inactive portion of the log (not actually moving pages around). So
it's fairly safe to shrink transaction log files in business hours
(given the normal caveats around shrinking files & autogrowing
files). 
 
However, if the active portion of the log is at the end of the file,
then a DBCC SHRINKFILE operation won't achieve much because it can only
truncate back to the end of the active portion (so the log file may
consist of gigabytes of empty space at the beginning of the file
followed by a few megabytes of real transactions at the end of the
file). The reason behind this is that transaction logs are divided up
into virtual log files, which are cyclical - when you reach the end of
the physical file, SQL Server will start writing records again from the
beginning of the physical file until it comes full circle back to where
it started (at that point it must increase the size of the physical
file again). So when you truncate a transaction log, usually by doing
a BACKUP LOG, all the transactions in the log are backed up and
deleted, leaving a largely empty log file. SQL Server doesn't start
writing again from the beginning of the file, it just keeps going from
where it left off - hence the circular/cyclical aspect of the log.
This means that sometimes you can get huge amounts of empty space at
the start of the file and just a few transactions at the end of the
file and that means SQL Server can only truncate the very end of the
physical file (if anything). So your 44GB log file may only shrink
down to 43GB for example. 
 
This was the reason for doing a BACKUP LOG and a CHECKPOINT, in my
previous post, before actually doing the DBCC SHRINKFILE, just to make
sure the end of the physical log file has been cleared of active
transactions and the DBCC SHRINKFILE will actually achieve something. 
 
The structure/behaviour of the transaction log is actually fairly
complicated - this explanation is really just an executive
summary/paraphrase. If you want to read up on it, SQL Books Online has
some excellent info on the transaction log (like the <a
href="http://links.10026.com/?link=Transaction">http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_876t.asp">Transaction
Log Architecture</a> section). For even more I'd recommend <a
href="http://links.10026.com/?link=Inside">http://www.amazon.com/exec/obidos/tg/detail/-/0735609985/qid=1127709307/sr=8-1/ref=pd_bbs_1/104-8387021-1711117?v=glance&s=books&n=507846">Inside
SQL Server 2000 by Kalen Delaney</a>, chapter 5 (Databases &
Database Files) - particularly pp.182-186 (dealing with the transaction
log structure & changing the log size). An essential tome for
every professional DBA's bookshelf. 
 
So, you should be able to shrink the transaction log very quickly &
easily with very little impact to the end users, but you should ditch
any transactions currently in there (with BACKUP LOG & CHECKPOINT)
just so the shrink operation shrinks right back to the near the
beginning of the file (so you may want to do that after hours so you
can do a full database backup immediately after the shrink without
impacting your users). Of course, you can do the whole thing during
business hours, users will still be able to work, but the performance
of the SQL server will be sluggish while you are doing the BACKUP
DATABASE operation (depending on your hardware). 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="mid%23Xby0AkwFHA.1504@.TK2MSFTNGP10.phx.gbl"
type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2722" name="GENERATOR">
<style></style>
<div>Mike,</div>
<div> </div>
<div>WOW! The time you've spent is
recorded in the back of my mind- always. The info you have supplied is
super and will become part of my knowledge-base for ever. As for your
questions, the DBs JLRXAPP & JLRXSYS are our production DBs
(although the difference b/w the APP & SYS DBs is unknown to me at
the moment but I've asked our Solomon support company for details on
each) and our test DBs are JLRXTestApp & JLRXTestSys.</div>
<div> </div>
<div>I feel that the shrinking of the DBs
will be a time-crunching effort so I will perform it later tonight when
everyone is off the system.</div>
<div> </div>
<div>Regards</div>
<div> </div>
<div>Thief_</div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl">news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl</a>...</div>
<tt>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: 
</tt>
<ul>
<li><tt>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).</tt>
</li>
<li><tt>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.</tt> </li>
<li><tt>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.</tt> </li>
<li><tt>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).</tt>
</li>
</ul>
<tt>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: 
</tt>
<ul>
<li><tt>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:</tt> </li>
</ul>
<blockquote><tt>ALTER DATABASE JLRXApp MODIFY FILE 
(NAME='JLTESTAPP_Data</tt><tt>', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB) 
</tt></blockquote>
<ul>
<li><tt>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:</tt> </li>
</ul>
<blockquote><tt>ALTER DATABASE JLRXApp MODIFY FILE 
(NAME='JLTESTAPP_Log', SIZE=1024MB, MAXSIZE=UNLIMITED,
FILEGROWTH=512MB) 
</tt></blockquote>
<tt>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: 
</tt>
<blockquote><tt>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 
</tt></blockquote>
<tt>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 </tt><tt>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. 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="mid%23KJJ4S$vFHA.2008@.TK2MSFTNGP10.phx.gbl"
type="cite">
<pre wrap="">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_" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:thief_@.hotmail.com"><thief_@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl">news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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_
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--000200050202090204030303--|||This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C5C3B5.C4081C90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
I found that the logical names for the JLRXApp are actually:
a.. JLRXApp_Data
b.. JLRXApp_Log
For some reason, the logical names returned by the "exec sp_helpdb =<DBName>" command, and thus the data in the sysfiles table, is =incorrect! I've noticed a few people on forums around the net have had =the same problem and sorted it out by renaming the logical filenames! I =feel this might be a bug in SQL Server 2000.
Anyhow, with your amazing help, me DB is now:
a.. JLRXApp_Data was 8GB and is now 9GB with Filegrowth of 1GB
b.. JLRXApp_Log was 50GB and is now 1GB with Filegrowth of 512MB
Thanks for your patience and mentoring- I'm now 50% more intuitive in =SQL Server!
Regards
Thief_
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl...
Something is amiss then because these results do not marry up with the =DB info you posted before:
> 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 onlyAccording to this info, the logical filename =of the data file is "JLTESTAPP_Data" so the ALTER DATABASE for the data =file should have worked fine. Somewhere between you posting the above =info and then posting the below info the logical name of the data file =has changed from JLTESTAPP_Data to JLRXApp. Anyway, you figured that =one out.
With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name of =the log file changed? and 2) are you executing the statement from within =the JLRXApp database? DBCC SHRINKFILE looks for the appropriate entry =in the dbo.sysfiles table in the current database. If the current =database is not JLRXApp then just run "USE JLRXApp" before doing the =DBCC SHRINKFILE. Also, make sure JLTESTAPP_Log is still the logical =name of the log file and that it hasn't changed (like the data file =did).
The dbo.sysfiles table you've dumped below matches what you posted =above but is not consistent with your initial ALTER DATABASE statement =that you used to change the size of the data file. You're not telling =us the whole story here.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
Thief_ wrote: Mike,
The SQLs to alter the databases failed.
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLTESTAPP_Data', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED, FILEGROWTH=3D1024MB)
I get this error:
MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
I changed it to:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLRXApp', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED,
FILEGROWTH=3D1024MB);
And it seems to have worked. However, the SQL to shrink the log would =not work no matter what I tried:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXAPP TO
DISK=3D'E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
I get the error:
Could not locate file 'JLTESTAPP_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The backup worked file (I now have a file called "JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the =SYSFILES table:
1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
?
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_

--=_NextPart_000_000A_01C5C3B5.C4081C90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Mike,
I found that the logical names for the =JLRXApp are actually:
JLRXApp_Data
JLRXApp_Log
For some reason, the logical names =returned by the "exec sp_helpdb " command, =and thus the data in the sysfiles table, is incorrect! I've noticed a few people =on forums around the net have had the same problem and sorted it out by =renaming the logical filenames! I feel this might be a bug in SQL Server 2000.
Anyhow, with your amazing help, =me DB is now:
JLRXApp_Data was 8GB and is now =9GB with Filegrowth of 1GB
JLRXApp_Log was 50GB and is now 1GB =with Filegrowth of 512MB
Thanks for your patience and mentoring- I'm now 50% more intuitive =in SQL Server!
Regards
Thief_
"Mike Hodgson" wrote in message news:%236fvIRywFHA.=1412@.TK2MSFTNGP09.phx.gbl...Something is amiss then because these results do not marry up with the DB info =you posted before:
> 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 onlyAccording to this =info, the logical filename of the data file is "JLTESTAPP_Data" so =the ALTER DATABASE for the data file should have worked fine. =Somewhere between you posting the above info and then posting the below info the =logical name of the data file has changed from JLTESTAPP_Data to =JLRXApp. Anyway, you figured that one out.With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name of the log file =changed? and 2) are you executing the statement from within the JLRXApp =database? DBCC SHRINKFILE looks for the appropriate entry in the dbo.sysfiles =table in the current database. If the =current database is not JLRXApp then just run "USE =JLRXApp" before doing the DBCC SHRINKFILE. Also, make sure JLTESTAPP_Log =is still the logical name of the log file and that it hasn't changed (like the =data file did).The dbo.sysfiles table you've dumped below matches =what you posted above but is not consistent with your initial ALTER DATABASE =statement that you used to change the size of the data file. You're not =telling us the whole story here.
--mike =hodgsonblog: http://sqlnerd.blogspot.com Thief_ wrote: Mike,
The SQLs to alter the databases failed.
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLTESTAPP_Data', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED, FILEGROWTH=3D1024MB)
I get this error:
MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
I changed it to:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLRXApp', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED,
FILEGROWTH=3D1024MB);
And it seems to have worked. However, the SQL to shrink the log would =not work no matter what I tried:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXAPP TO
DISK=3D'E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
I get the error:
Could not locate file 'JLTESTAPP_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The backup worked file (I now have a file called "JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the =SYSFILES table:
1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
?
Regards
Thief_
"Thief_" 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_
--=_NextPart_000_000A_01C5C3B5.C4081C90--|||This is a multi-part message in MIME format.
--040000030307020406040900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No prob, glad I could help.
Although the logical filename issue you're seeing I think is more likely
a confusion over DB names, logical filenames & physical filenames, all
of which can be different for any given database, than a bug in SQL
Server. The confusion may have arisen from the name changes that
implicitly occur when you restore a database from another DB backup
(like restoring JLRXApp from a backup of JLTESTAPP for example).
Anyway, I don't want to flog a dead horse - I'm glad you're all sorted.
(Just remember to do test restores from your backup files every month or
two to make sure 1) that they are valid backups and 2) that you're well
practised at recovery in case of a crisis - makes for a cool head when
the real thing happens...and it will, eventually.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
> Mike,
> I found that the logical names for the JLRXApp are actually:
> * JLRXApp_Data
> * JLRXApp_Log
> For some reason, the logical names returned by the "exec sp_helpdb
> <DBName>" command, and thus the data in the sysfiles table, is
> incorrect! I've noticed a few people on forums around the net have had
> the same problem and sorted it out by renaming the logical filenames!
> I feel this might be a bug in SQL Server 2000.
> Anyhow, with your amazing help, me DB is now:
> * JLRXApp_Data was 8GB and is now 9GB with Filegrowth of 1GB
> * JLRXApp_Log was 50GB and is now 1GB with Filegrowth of 512MB
> Thanks for your patience and mentoring- I'm now 50% more intuitive in
> SQL Server!
> Regards
> Thief_
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Something is amiss then because these results do not marry up with
> the DB info you posted before:
>> 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
> According to this info, the logical filename of the data file is
> "JLTESTAPP_Data" so the ALTER DATABASE for the data file should
> have worked fine. Somewhere between you posting the above info
> and then posting the below info the logical name of the data file
> has changed from JLTESTAPP_Data to JLRXApp. Anyway, you figured
> that one out.
> With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name
> of the log file changed? and 2) are you executing the statement
> from within the JLRXApp database? DBCC SHRINKFILE looks for the
> appropriate entry in the dbo.sysfiles table in the *current
> database*. If the current database is not JLRXApp then just run
> "USE JLRXApp" before doing the DBCC SHRINKFILE. Also, make sure
> JLTESTAPP_Log is still the logical name of the log file and that
> it hasn't changed (like the data file did).
> The dbo.sysfiles table you've dumped below matches what you posted
> above but is not consistent with your initial ALTER DATABASE
> statement that you used to change the size of the data file.
> You're not telling us the whole story here.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Thief_ wrote:
>>Mike,
>>The SQLs to alter the databases failed.
>>ALTER DATABASE JLRXApp MODIFY FILE
>> (NAME='JLTESTAPP_Data', SIZE=9216MB, MAXSIZE=UNLIMITED,
>>FILEGROWTH=1024MB)
>>I get this error:
>>MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
>>I changed it to:
>>ALTER DATABASE JLRXApp MODIFY FILE
>>(NAME='JLRXApp', SIZE=9216MB, MAXSIZE=UNLIMITED,
>>FILEGROWTH=1024MB);
>>And it seems to have worked. However, the SQL to shrink the log would not
>>work no matter what I tried:
>>BACKUP LOG JLRXApp WITH NO_LOG
>>CHECKPOINT
>>DBCC SHRINKFILE('JLTESTAPP_Log')
>>BACKUP DATABASE JLRXAPP TO
>>DISK='E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
>>I get the error:
>>Could not locate file 'JLTESTAPP_Log' in sysfiles.
>>DBCC execution completed. If DBCC printed error messages, contact your
>>system administrator.
>>The backup worked file (I now have a file called
>>"JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the SYSFILES
>>table:
>> 1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data
>>d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
>> 2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log
>>d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
>>?
>>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_
>>
>>
>>
--040000030307020406040900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No prob, glad I could help. 
 
Although the logical filename issue you're seeing I think is more
likely a confusion over DB names, logical filenames & physical
filenames, all of which can be different for any given database, than a
bug in SQL Server. The confusion may have arisen from the name changes
that implicitly occur when you restore a database from another DB
backup (like restoring JLRXApp from a backup of JLTESTAPP for example). 
 
Anyway, I don't want to flog a dead horse - I'm glad you're all
sorted. (Just remember to do test restores from your backup files
every month or two to make sure 1) that they are valid backups and 2)
that you're well practised at recovery in case of a crisis - makes for
a cool head when the real thing happens...and it will, eventually.) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="miduH210H2wFHA.3000@.TK2MSFTNGP12.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2722" name="GENERATOR">
<style></style>
<div>Mike,</div>
<div> </div>
<div>I found that the logical names for
the JLRXApp are actually:</div>
<ul>
<li>JLRXApp_Data</li>
<li>JLRXApp_Log</li>
</ul>
<div>For some reason, the logical names
returned by the "exec sp_helpdb
<DBName>"
command, and thus the data in the sysfiles table, is incorrect! I've
noticed a few people on forums around the net have had the same problem
and sorted it out by renaming the logical filenames! I feel this might
be a bug in SQL Server 2000.</div>
<div> </div>
<div>Anyhow, with your amazing
help, me DB is now:</div>
<ul>
<li>JLRXApp_Data was 8GB and is
now 9GB with Filegrowth of 1GB</li>
 <li>JLRXApp_Log
was 50GB and is now 1GB with Filegrowth of 512MB</li>

</ul>
<div>Thanks for your patience and
mentoring- I'm now 50% more intuitive in SQL Server!</div>
<div> </div>
<div>Regards</div>
<div> </div>
<div>Thief_</div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl">news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl</a>...</div>
<tt>Something is amiss then because these results do not marry up
with the DB info you posted before: 
</tt>
<blockquote>
<pre wrap="">> 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</pre>
</blockquote>
<tt>According to this info, the logical filename of the data file
is "JLTESTAPP_Data</tt><tt>" so the ALTER DATABASE for the data file
should have worked fine. Somewhere between you posting the above info
and then posting the below info the logical name of the data file has
changed from JLTESTAPP_Data to JLRXApp. Anyway, you figured that one
out. 
 
With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name of
the log file changed? and 2) are you executing the statement from
within the JLRXApp database? DBCC SHRINKFILE looks for the appropriate
entry in the dbo.sysfiles table in the current database. If </tt><tt>the
current database </tt><tt>is not </tt><tt>JLRXApp then </tt><tt>just
run "USE JLRXApp" before doing the DBCC SHRINKFILE. Also, make sure
JLTESTAPP_Log is still the logical name of the log file and that it
hasn't changed (like the data file did). 
 
The dbo.sysfiles table you've dumped below matches what you posted
above but is not consistent with your initial ALTER DATABASE statement
that you used to change the size of the data file. You're not telling
us the whole story here. 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midOk06OhpwFHA.3800@.TK2MSFTNGP11.phx.gbl"
type="cite">
<pre wrap="">Mike,
The SQLs to alter the databases failed.
ALTER DATABASE JLRXApp MODIFY FILE
(NAME='JLTESTAPP_Data', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB)
I get this error:
MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
I changed it to:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME='JLRXApp', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB);
And it seems to have worked. However, the SQL to shrink the log would not
work no matter what I tried:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXAPP TO
DISK='E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
I get the error:
Could not locate file 'JLTESTAPP_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The backup worked file (I now have a file called
"JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the SYSFILES
table:
1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data
d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log
d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
?
Regards
Thief_
"Thief_" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:thief_@.hotmail.com"><thief_@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl">news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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_
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--040000030307020406040900--

Saturday, February 25, 2012

Newbie: Truncating transaction logs

No comments:

Post a Comment

Metadata Services

Blog Archive

About Me