circular transaction log

Hi,
I have two important questions :
Question 1:
I need to know what the is the importance of transaction log file in MSSQL.
If we shrink our transaction log regularly, then would there be any negative
effect or loss of any useful feature in MSSQL.
Question 2:
In our scenario, the transaction log grows very fast because of the heavy DB
operations, so we need to shrink the transaction file regularly. I need a
way to create a circular log file in MSSQL so that it will automatically
overwrite the transaction log file when ever it reaches the maximum limit. I
know in oracle and DB2 this feature is there, but I could not find any
satisfactory Microsoft resource which tells can inform me how to create a
circular log file. Please help me out.
Best Regrads,
Abdul-Rahman"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:%23AgZWGg3EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two important questions :
> Question 1:
>
> I need to know what the is the importance of transaction log file in
> MSSQL.
> If we shrink our transaction log regularly, then would there be any
> negative
> effect or loss of any useful feature in MSSQL.
>
The transaction log is used for recovery. Without a transaction log you
will only be able to restore your database with a full backup. For instance
if you take full backups every night,
Here's a description of Sql Server recovery models.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_60s9.asp
Under the Simple Recovery model your log file will be truncated at every
database checkpoint, and is only used to roll back failed transactions.
> Question 2:
>
> In our scenario, the transaction log grows very fast because of the heavy
> DB
> operations, so we need to shrink the transaction file regularly. I need a
> way to create a circular log file in MSSQL so that it will automatically
> overwrite the transaction log file when ever it reaches the maximum limit.
> I
> know in oracle and DB2 this feature is there, but I could not find any
> satisfactory Microsoft resource which tells can inform me how to create a
> circular log file. Please help me out.
>
The equivilent of Oracle's NOARCHIVELOG mode is Simple Recovery model. In
the full recovery model, with heavy use you will need to run a BACKUP LOG to
move the log somewhere safe and truncate it.
David|||"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:%23AgZWGg3EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two important questions :
> Question 1:
>
> I need to know what the is the importance of transaction log file in
MSSQL.
> If we shrink our transaction log regularly, then would there be any
negative
> effect or loss of any useful feature in MSSQL.
Shrinking the transaction log is generally a bad idea.
For one thing you risk getting disk level fragmentation. You're better off
creating a full size transaction log on a newly formatted disk and not
shrinking it.
In addition, if you keep shrinking it, you'll have to keep expanding it.
This takes time and can slow down your database.
Also, how are you clearing it out?
For production databases, you probably should be be doing backups as often
as necessary.
>
> Question 2:
>
> In our scenario, the transaction log grows very fast because of the heavy
DB
> operations, so we need to shrink the transaction file regularly. I need a
> way to create a circular log file in MSSQL so that it will automatically
> overwrite the transaction log file when ever it reaches the maximum limit.
I
> know in oracle and DB2 this feature is there, but I could not find any
> satisfactory Microsoft resource which tells can inform me how to create a
> circular log file. Please help me out.
>
Use Bulk Logged or Simple Recovery Mode. Books Online can go into more
detail.
However, keep in mind your decisions affect your recovery options.
>
>
> Best Regrads,
> Abdul-Rahman
>

circular transaction log

Hi,
I have two important questions :
Question 1:
I need to know what the is the importance of transaction log file in MSSQL.
If we shrink our transaction log regularly, then would there be any negative
effect or loss of any useful feature in MSSQL.
Question 2:
In our scenario, the transaction log grows very fast because of the heavy DB
operations, so we need to shrink the transaction file regularly. I need a
way to create a circular log file in MSSQL so that it will automatically
overwrite the transaction log file when ever it reaches the maximum limit. I
know in oracle and DB2 this feature is there, but I could not find any
satisfactory Microsoft resource which tells can inform me how to create a
circular log file. Please help me out.
Best Regrads,
Abdul-Rahman
"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:%23AgZWGg3EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two important questions :
> Question 1:
>
> I need to know what the is the importance of transaction log file in
> MSSQL.
> If we shrink our transaction log regularly, then would there be any
> negative
> effect or loss of any useful feature in MSSQL.
>
The transaction log is used for recovery. Without a transaction log you
will only be able to restore your database with a full backup. For instance
if you take full backups every night,
Here's a description of Sql Server recovery models.
http://msdn.microsoft.com/library/de...kprst_60s9.asp
Under the Simple Recovery model your log file will be truncated at every
database checkpoint, and is only used to roll back failed transactions.

> Question 2:
>
> In our scenario, the transaction log grows very fast because of the heavy
> DB
> operations, so we need to shrink the transaction file regularly. I need a
> way to create a circular log file in MSSQL so that it will automatically
> overwrite the transaction log file when ever it reaches the maximum limit.
> I
> know in oracle and DB2 this feature is there, but I could not find any
> satisfactory Microsoft resource which tells can inform me how to create a
> circular log file. Please help me out.
>
The equivilent of Oracle's NOARCHIVELOG mode is Simple Recovery model. In
the full recovery model, with heavy use you will need to run a BACKUP LOG to
move the log somewhere safe and truncate it.
David
|||"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:%23AgZWGg3EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two important questions :
> Question 1:
>
> I need to know what the is the importance of transaction log file in
MSSQL.
> If we shrink our transaction log regularly, then would there be any
negative
> effect or loss of any useful feature in MSSQL.
Shrinking the transaction log is generally a bad idea.
For one thing you risk getting disk level fragmentation. You're better off
creating a full size transaction log on a newly formatted disk and not
shrinking it.
In addition, if you keep shrinking it, you'll have to keep expanding it.
This takes time and can slow down your database.
Also, how are you clearing it out?
For production databases, you probably should be be doing backups as often
as necessary.

>
> Question 2:
>
> In our scenario, the transaction log grows very fast because of the heavy
DB
> operations, so we need to shrink the transaction file regularly. I need a
> way to create a circular log file in MSSQL so that it will automatically
> overwrite the transaction log file when ever it reaches the maximum limit.
I
> know in oracle and DB2 this feature is there, but I could not find any
> satisfactory Microsoft resource which tells can inform me how to create a
> circular log file. Please help me out.
>
Use Bulk Logged or Simple Recovery Mode. Books Online can go into more
detail.
However, keep in mind your decisions affect your recovery options.

>
>
> Best Regrads,
> Abdul-Rahman
>

Choosing drives, transfert rate or IO/sec?

Hi,
what is most important to focus on when we choose a drive setup to support a
small datawarehouse?
(10GB and less)
IO/Sec
or
MB/s
?
(same question regarding the tempdb database storage)
except the SQLIO and SQLIOStress, there is any testing tool which simulate
DW loading process & DW query process?
thanks.
Jerome."Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uPa1X68wFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> what is most important to focus on when we choose a drive setup to support
> a small datawarehouse?
> (10GB and less)
> IO/Sec
> or
> MB/s
> ?
> (same question regarding the tempdb database storage)
>
Write throughput.
For a small datawarehouse, your memory cache should be a large percentage of
your database size. So for physical IO, sequential write operations (like
the log file and checkpointing) will predominate.
David|||Hi Jerome,
for 10GB or less of data....design a reasonable model and throw a bit
more hardware at it if it is not going ok......the time and money it
will cost to think about how to tune it is more than the cost of the
HW/SW to run it....
Of course, this kind of advice does NOT apply to large DWs where we do
spend time considering the performance in some great detail..
Best Regards
Peter Nolan
www.peternolan.com

Choosing drives, transfert rate or IO/sec?

Hi,
what is most important to focus on when we choose a drive setup to support a
small datawarehouse?
(10GB and less)
IO/Sec
or
MB/s
?
(same question regarding the tempdb database storage)
except the SQLIO and SQLIOStress, there is any testing tool which simulate
DW loading process & DW query process?
thanks.
Jerome.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uPa1X68wFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> what is most important to focus on when we choose a drive setup to support
> a small datawarehouse?
> (10GB and less)
> IO/Sec
> or
> MB/s
> ?
> (same question regarding the tempdb database storage)
>
Write throughput.
For a small datawarehouse, your memory cache should be a large percentage of
your database size. So for physical IO, sequential write operations (like
the log file and checkpointing) will predominate.
David
|||Hi Jerome,
for 10GB or less of data....design a reasonable model and throw a bit
more hardware at it if it is not going ok......the time and money it
will cost to think about how to tune it is more than the cost of the
HW/SW to run it....
Of course, this kind of advice does NOT apply to large DWs where we do
spend time considering the performance in some great detail..
Best Regards
Peter Nolan
www.peternolan.com