Database Backup in 0.5 B and Transaction log backup in GBs

SQL gurus,
Simple question:
Our database is 470MB and transaction log has become 18GB! (Clustered). I
see local Fixed drives in both the SQL boxes from 'System Information' and
disk manager where data is being stored.
We are backing up database once in 24 hours and transaction logs every two
hours during business hours and full transaction log at night.
Simple Question is if we are backing full transaction log at night then
isn't supposed to make the transaction log to "zero" bytes or say clear the
transaction log automatically? After all when transaction logs have been
backup then why the transaction log continue to grow'
If the database is crashed what would be the sequence to recover:
Restore database in restore mode then restore first transaction log backup
taken after full backup of database and then restore the next backup taken
of transaction log and so on. (to apply in sequence starting from the first
one backup of transaction log taken).
Now question is :
Situation #1
1) if database is crashed and we still have transaction logs then can we
restore transaction logs (not backup of transaction logs) at the end of
procedure to restore as given above'
Situation #2
2) database is crashed and we do not have transaction logs then we can
restore as given in the procedure above except that in the last stage we
keep "no restore mode"'
Thanks
MeiNarendra
All of these issues are described on BOL very well.
Please refer to BOL.
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically? After all when transaction logs have been
> backup then why the transaction log continue to grow'
> If the database is crashed what would be the sequence to recover:
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
> Thanks
> Mei
>|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically?
Backing up the log does truncate the log( making space available for
re-use), but it does not shrink the log. Long running transactions can cause
the log to grow larger than you intended. After backing up the log, you may
use dbcc shrinkfile on the log file to reduce its physical size.
After all when transaction logs have been
> backup then why the transaction log continue to grow'
The only explanation I can think of is long running transactions...
> If the database is crashed what would be the sequence to recover:
>
set the db to dbo use only and kick off all users
backup the log with truncate_only
Restore the last whole data backup
restore each transaction log in the proper sequence.
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
No, restore the log backups
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
Yes, you may restore the whole database backup and allow recovery to run.
The database will be available, but you will have lost all of the data which
was in the transaction logs which you did not restore.
> Thanks
> Mei
>

Database and transaction log question

New to SQL. I have 2 drives mirrored for os and sql installation files and a
raid 5 array for the database files for sql.
Should I create two partitions on the raid 5 array and put the database file
s on one and the transaction logs on another or is it not going to make a di
fference in performance since both partitions would still be on the same rai
d 5 array. Appreciate a qui
ck response to this question.
Also if it is recommended to place the transaction logs on a separate partit
ion on the same raid 5 array. Where do I set that in sql. Thanks a lot every
one.
OwenHaving multiple partitions on the same RAID5 array isn't going to buy you
anything. Put the logs on their own RAID1 volume. If you have the disks,
place the data files on a RAID10 volume. If you don't have the disks, then
put the data files on a RAID5. Never put the logs on the same array as the
data files.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"owen" <anonymous@.discussions.microsoft.com> wrote in message
news:5C7163A9-C709-4466-A823-3DCA707A5912@.microsoft.com...
New to SQL. I have 2 drives mirrored for os and sql installation files and a
raid 5 array for the database files for sql.
Should I create two partitions on the raid 5 array and put the database
files on one and the transaction logs on another or is it not going to make
a difference in performance since both partitions would still be on the same
raid 5 array. Appreciate a quick response to this question.
Also if it is recommended to place the transaction logs on a separate
partition on the same raid 5 array. Where do I set that in sql. Thanks a lot
everyone.
Owen|||"owen" <anonymous@.discussions.microsoft.com> wrote in message
news:5C7163A9-C709-4466-A823-3DCA707A5912@.microsoft.com...
> New to SQL. I have 2 drives mirrored for os and sql installation files and
a raid 5 array for the database files for sql.
> Should I create two partitions on the raid 5 array and put the database
files on one and the transaction logs on another or is it not going to make
a difference in performance since both partitions would still be on the same
raid 5 array. Appreciate a quick response to this question.
> Also if it is recommended to place the transaction logs on a separate
partition on the same raid 5 array. Where do I set that in sql. Thanks a lot
everyone.
> Owen
Creating 2 partitions on a RAID 5 is going to give you no performance
advantage at all. In fact, though log files will benefit from the fault
tolerance afforded by RAID 5, as they are only written to, rather than read
from, they will not benefit from read performance advantages that RAID 5
gives you.
If your main concern is fault tolerance then stick the logs on the mirror.
That way if your entire RAID 5 controller goes up the Swanny, you still have
your log files.
If you are going to reposition your fils, simply go into Enterprise Manager,
properties of the database and change it there
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004