sqlserver 2000 database access is slow

[Always OpenTo Suggestions]

I'm working with Access 2003 Front end
I'm working with SQL Server 2000 Back End

Data was transfered from MDB to SQL Server simply by moving tables and
adding some indexes and relationships. Stored procedures were not used for
efficiency.

I've got 2 issues.

First: SQL SErver transaction logs get HUGE. I guess because transactions
are never really committed. I've got a 4 gig database and the transaction
log often exceeds 10 gig.
QUESTION -- When do transactions from queries and from data being updated
via forms get committed to remove the transactions from the transaction log?

Also, screens seem sluggish.
QUESTION - In general, other than taking advantage of stored procedures,
where can I find info on best practices for using SQLServer database with
Access Front end?

Thanx in advance!

Angelo

 

[Armen Stein]

First: SQL SErver transaction logs get HUGE. I guess because transactions
are never really committed. I've got a 4 gig database and the transaction
log often exceeds 10 gig.
QUESTION -- When do transactions from queries and from data being updated
via forms get committed to remove the transactions from the transaction log?

Transactions aren't just removed when updates are committed they're
kept for backup purposes. It depends on your SQL Server settings.
Look at the recovery model and backup settings to see if you are
holding too many transactions and not truncating your logs. You may
want to post detailed SQL Server questions on a SQL Server newsgroup.

Also, screens seem sluggish.
QUESTION - In general, other than taking advantage of stored procedures,
where can I find info on best practices for using SQLServer database with
Access Front end?

I've collected our best practices and posted them in a slide deck
called The Best of Both Worlds. It's at:
http://jstreettech.com/cartgenie/pg_developerDownloads.asp

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Always OpenTo Suggestions]

Ah - This is going to definately give me some stuff to look at.

 

[Tony Toews [MVP]]

Transactions aren't just removed when updates are committed they're
kept for backup purposes. It depends on your SQL Server settings.
Look at the recovery model and backup settings to see if you are
holding too many transactions and not truncating your logs.

My nightly backup on one minor server I run does the SQL Server backup
and truncating the logs right then and there at 4 am in the morning.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Armen Stein]

My nightly backup on one minor server I run does the SQL Server backup
and truncating the logs right then and there at 4 am in the morning.

Yes, most databases can run perfectly well that way. That's how we
run most of ours too. We also keep several days worth of the
date-stamped BAK files, and rotate those files offsite weekly on
encrypted media.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com