May 30, 2008 at 2:24 am
Hi all,
In my application, there are lot of entries and manipulation involved in DB.
So the transaction log of my database is growing very enormously and often. So the performance is decreased.
Can any one tell me how to restrict the transaction log entry?
Thanks a lot in advance.
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
May 30, 2008 at 2:49 am
What recovery mode is your database in?
Are you running regular log backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2008 at 3:42 am
Hi Gila,
Actually, I don't know how to view the recovery mode as I am new to this application.
How and I view whether the log backup is done or not?
Actually I don't want the log also as this will be managed in the front end code.
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
May 30, 2008 at 3:49 am
The transaction log is not optional. It's used by SQL to keep a permanent log (for recovery purposes) or all data modifications that occur. It's what allows SQL to always recover the DB to a transactionally consistent state.
Are you responsible for the admin of the database, or is there someone else there?
For the recovery mode, from management studio, go to the object explorer. Right click the database in question and select properties
At the top of the initial tab you will see entries that show date of last database backup and last log backup.
Select options on the left. You will see the recovery model listed at the top.
Is this a development system or a production system?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2008 at 4:10 am
Hi Gila,
I have checked the DB and ti was in "Full" recovery modal.
Actually this happens in the production DB in the client place.
Usually we will run the DBCC SHRINKDATABASE script.
Can we automate the same in the scheduler or else I have seen an option "Auto Shrink " in the settings, can I enable it.
If I enable it what will happen.
Or else is there any built in scheduler which will delete the transaction log.
Or can I have a script which will delete the transaction once it it is committed.
Please help me to resolve this. I am in a critical position now as the DB is growing enormously in the client place.
Note: I have given the option "Automatically grow" and "Unrestricted file size" option. Will I specify a limitation over there what will happen?
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
May 30, 2008 at 4:34 am
Shrinking database = really bad idea. The DB will just grow again and you will probably have messed the performance up doing the shrink.
Do you have transaction log backups running?
In the case of a server crash, do you need to be able to restore to the point of failure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply