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
>

Chose MSSQL collation

hi,

I want to migrate from MYSQL to MSSQL 2005. My website Contains Multilangugal contents "Frace & Arabic". I 'm using "UTF_8 generalCI" Collation in MYSQL . What Collation I should use in MSSQL 2005. I only want to have One DB.

thanks,

Inorder to store multi-lingual text, you need to set the field datatype to NVARCHAR. This should work fine. If you need more information, let me know. I am currently working on an arabic web portal.

Thanks

|||

thanks for your Rply,

I've set field types as nvarchar. But If I set collation as "latin1swedish" and field as nvarchar then I can't find Arabic words.. would you please tell me, what is the role of collation in DB. suppose

would you please tell me what method are you using for Arabic website developement.Do you use 2 Seprate DB? hae you create arabic section as a real subweb or you use methods like profile.

best regards,

|||

Hi,

The collation is used when sorting and querying. When sorting, SQL Server will sort according to the selected collation.

You can alter a column's collation by using the COLLATE clause. Please check the following link for more information.

http://msdn2.microsoft.com/en-us/library/ms184391.aspx

|||thanks Kevin for your reply,If I've understood right, I could enter multi languagual contents to NVARCHAR fields, and Collation doesn't matter while save data.We could change the DB Colation later if needed.But what is the usual Collation. ex. what www.asp.net uses for it's collation. regards,

Chinese garbled in MSSQL2000 - by ASP

Hi experts,

Here I got some problems with my application. (ASP & English Version SQL Server 2000)

As we are using English MSSQL Server 2000, we got some new functions and we have to facilitate support of Chinese characters in the DB. I have set the collation for those Chinese fields already and those queries or Stored Procs for Chinese are working fine, ONLY if I execute them in Enterprise Manager. Chinese characters can be displayed in the relevant tables.

However here comes the big problem and I got really frustrated. As we will provide user interface in ASP pages, we 'll let users to insert the information which will be sent to the DB. If there's Chinese characters in the query string, the Chinese characters added in the DB would be garbled.

e.g. EXECUTE proc_TestChinese 'XYZ', 'test123' (assume XYZ be those Chinese words)

I am wondering if there's any way I can solve this problem. Should I add special handling for these Chinese words? I have set the ASP pages in UTF-8 or Big5 encoding but it doesn't help. Hope you experts can show me the way out of the mess. Thanks in advance!

Manfred

Is your input variabl for the stored procedure a NVARCHAR or any other unicode data type, if not the data will be converted, leading to the problems you described. if you are fine on the database side you should consider posting some code in here which is used for getting the data in the database using ASP Code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

For that particular field for storing Chinese characters, I have set it as nvarchar already with collation settting as Chinese_Taiwan_Stroke_BIN. Here below is the Stored Procedure for testing.

Thanks in advance!

Manfred

to insert the Chinese characters for Germany: 德國 (&#24503;&#22283;)

use N'xxxx' for nvarchar

EXECUTE proc_TestChinese N'德國', 'Germany'

CREATE Procedure dbo.proc_TestChinese

@.chineseCharacters nvarchar(20),
@.Description varchar(50)

AS
BEGIN

INSERT INTO dbo.tblTestChinese
VALUES(@.chineseCharacters, @.Description)

END
GO

After the SP is executed, the table would shown as ?w°ê for those Chinese characters.

If I have a html file, see http://www.neocollage.com/de.htm . If you change the encoding to Western European then you 'll see the garbled text as in the DB.