Can the transaction log be turned off?

I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?

ThanksNo

Why what the problem?

Waht's your recovery model set to?|||Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.|||Originally posted by grahamt
Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.

When you say timeouts, what do you mean by that?

From QA, from the application layer?

Are you blocking yourself (sp_lock)

How many connections are open (sp_who2)

How big is the database?

How often do you take backups?|||Setting recovery model is nothing to do with blocks or timeouts on the database. Ensure there are no issues with network, client connections and query conditions involved which may help causing timeouts.

Whatever the recovery model ensure to maintain & schedule full backup of the database.|||The SQL Server timeouts are reported by the ASP.
A bit of config info.

Hardware

SQL Server 2000
Dual Xeon 2.4GHz CPU
Ultra 320 SCSI controller
Two Ultra 320 18GB, 15000 RPM Hard Disks (C: and D:)
Two GB RAM (1.5GB assigned to SQL Server)
Windows 2000 Server

WEB Server connected to SQL server by Gigabit Ethernet.

Two databases involved. One has a single table of perhaps 2000 records (Session state info for WEB users) and is heavily used (Insert, Delete, and Select). It resides on drive C:.

Second DB residing on drive D: has one Master table and 26 others (A-Z). Master table has about 16000 6K records, Primary key on a varchar(50) and a char(2) column. Other tables run from 1000 to 50000 records 70 bytes/record (PK on a varchar(50) field). No updates are ever done (well, every few months maybe) and the Selects are done using stored procedures, one for the Master table (2 parameters) and 26 for the other tables (1 parameter).

Every few minutes (time varies as does the DB with the DB on drive D: getting the most) the WEB app logs an SQL Server timeout. With this horsepower driving these small DBs (and nothing else running) I wouldn't expect any timeouts at all.

Since I don't care about the data in one table and the data in the other never changes, I don't bother with backups.

Any thoughts?|||What are the timeout settings on SQL & ASP script?
IT may be worth if you enable DBCC DBREINDEX and other maint.plan checks on database which will addup performance.

And also consider network settings and take help of netadmin.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm - a useful guide to troubleshoot timeouts when using SQL & ASP.|||I've been monitoring network traffic with 3COMs Network Monitor and everything seems fine. The WEB server showed high FTP traffic so I've shut that service down but there's no perceptable load on SQL server. Performance monitor shows Disk Idle Time averaging 98% or better on both drives and the CPU load never exceeds 3-4%. I've run the SPs through QA and because they are so basic the execution cost is almost nil. The two SPs are

CREATE PROCEDURE [DBO].[prGetOrigins_A]
@.SomeVar Varchar(50)
AS

SET NOCOUNT ON

SELECT Code, Master FROM A Where SomeVar = @.Somevar
GO

and

CREATE PROCEDURE [DBO].[prGetMasterRec]
@.master varChar(20),
@.code varCHar(3)
AS
SET NOCOUNT ON

SELECT id, code,SomeData,
FROM Masters WHERE Master = @.master AND Code = @.Code
GO

and still I get random timeouts.

Personally I am not concerned. A timeout every 5 minutes or so with 250 users on the WEB server probably shouldn't happen but I can live with it. It's the boss who panics and worries that he may have lost a $5.00 sale. 8-)

Can SQL Lose Records?

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.

Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).

I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.

So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.

Thanks!

NeilOn Jun 20, 2:53 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.
>
Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).
>
I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.
>
So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.
>
Thanks!
>
Neil

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.|||I've never seen or heard of a row going missing either, and I spent
plenty of time using 7.0.

Along with what Alex suggested I would suggest doing a complete set of
DBCC integrity checks on the database.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 19:53:32 GMT, "Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>We are running SQL 7 with a front end that links to the tables through ODBC.
>In our main table, the user has no way to delete a record through the
>interface, though it is possible to delete it by opening the ODBC link.
>Users would have no reason to delete a record, but one of our records turned
>up missing.
>
>Now, it's possible that a user may have accidentally deleted the record.
>But, since users don't have any reason to delete records, and since they
>don't access the ODBC links, it seems unlikely (though possible).
>
>I was wondering if anyone had every heard of SQL Server ever "losing" a
>record that had previously been saved. I checked the nightly backup from the
>night after it was added, and the record was there. So either a user deleted
>it, or somehow it got lost in SQL Server. I have no code that deletes
>records in this table in any way, shape or form, so it couldn't have been
>malfunctioning code.
>
>So, while I have a hard time believing that SQL Server would just "lose" a
>record, I also know that anything's possible, so I thought I'd ask if anyone
>had ever heard of such a thing.
>
>Thanks!
>
>Neil

|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

So, while I have a hard time believing that SQL Server would just "lose"
a record, I also know that anything's possible, so I thought I'd ask if
anyone had ever heard of such a thing.

Well, I have lost rows, but that was a on a system where no one was looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.

As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.

But I'm prepared to place my bets that there was a human involved.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.
>

If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?

Thanks.|||I'm not familiar with DBCC. Can you point me in the right direction?

Thanks.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9955ED0DF77BAYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>So, while I have a hard time believing that SQL Server would just "lose"
>a record, I also know that anything's possible, so I thought I'd ask if
>anyone had ever heard of such a thing.

>
Well, I have lost rows, but that was a on a system where no one was
looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.
>
As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.
>
But I'm prepared to place my bets that there was a human involved.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jun 20, 5:01 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.

>
If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?
>
Thanks.

You can disable the trigger for the duration of your delete.
Alternatively you can have you trigger allow you to do whatever you
want, based on user_id() or suser_id(). Trigger can be bypassed using
nested triggers and or recursive trigger setting. There are other ways
best described in T-SQL Programming by Itzik Ben-Gan.

http://sqlserver-tips.blogspot.com/|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

I'm not familiar with DBCC. Can you point me in the right direction?

There are several DBCC commands, but the one of interest here is DBCC
CHECKDB which checks the database for consistency errors. If the database is
of any size, run it off-hours.

You should regularly run DBCC on your database, for instance as part of a
maintenance job, and make sure that you get alerted if it finds any errors.

If memory serves, you just say "DBCC CHECKDB" in the database you want to
examine. But check Books Online for details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

You can think of SQL Profiler as well if still you doubt on the SQL
Activities for some particular time.

Thanks
Ajay