?
Solved

Question about SqlServer Transaction Log File

Posted on 2008-02-07
7
Medium Priority
?
1,100 Views
Last Modified: 2008-04-07
Hey Guys,

My Sql Server transaction Log is now 1.4 Gig and my Database size is 1.o GIG.
Is this common or can I clean up the Transaction Log?

How can I go about cleaning the Log?
I am looking to boost the performance of Sql Server

Thanks
0
Comment
Question by:sonu19
7 Comments
 
LVL 4

Accepted Solution

by:
almilyo earned 400 total points
ID: 20841720
The transaction log will grow each time you make a transaction against the database, so if you don't put something in place to clean the transaction  log it will keep growing. It isn't a problem that it is larger than the database.

Don't expect much of a performance improvement from cleaning it - the sql server will continue to write to the log each time a transactino occurs,and this writing is what worsens performance (by a very small amount).

You can set a maximum size on the transaction log by right clicking the database and selecting properties, then navigating to the 'transaction log' tab. You can also set up a database maintenance plan to remove old transaction logs.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 20841735
>Is this common or can I clean up the Transaction Log?
Seems like you are not taking the backups of your transaction log, which is really very risky unless you have a job offer in your hand; So make sure that you takes the regular backups of your Log
0
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 1200 total points
ID: 20841899
Dont let your transaction log reach its maximum size ever as this will halt action on the db, scheulde a regular transaction log back up as this will truncate the log.

Alternatively set your db to Auto_shrink mode as this will keep it under control
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:sonu19
ID: 20842156
I got this error on the Log file backup


Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
0
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 1200 total points
ID: 20842229
You shoudl be able to set the back up withinin ssms if you are using 2005? shoudlnt see this error?

Right click database select backup and then select transaction data log
0
 

Author Comment

by:sonu19
ID: 20842359
I dont see anything to select Transaction log all I see is the delete inactive activity from transaction log
i am on a sql server enterprise manager
0
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 1200 total points
ID: 20842440
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318

tells you how to use dbcc shrinkfile to shrink your transaction log, however this wont back it up, in em you shoudl have back up transaction log within the back up window

sorry its been a while since i used 2k
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question