CODEWITHSUNDEEP
c#sqlsql-server
Brian Hvarregaard
Brian Hvarregaard

Reputation: 4209

Unable to delete from SQL Server

I have a database with a lot of products for a web shop. When I try to delete I get a

transaction log is full

exception. I went to my WebHotel provider and told them this, they replied that I could not delete 13444 rows in a single transaction, but had to delete them in increments of eg. 100.

Has anyone ever heard of this?

EDIT This is my table

CREATE TABLE [dbo].[PProduct](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](150) NOT NULL,
    [ProductUrl] [nvarchar](500) NOT NULL,
    [ImageUrl] [nvarchar](500) NOT NULL,
    [Description] [nvarchar](4000) NOT NULL,
    [Price] [money] NOT NULL,
    [BeforePrice] [money] NULL,
    [ShortDescription] [nvarchar](4000) NOT NULL,
    [ProductId] [int] NOT NULL,
    [PCategoryId] [nvarchar](100) NOT NULL,
    [PProgramName] [int] NOT NULL,
    [FrontPage] [bit] NULL,
    [FrontPageText] [ntext] NULL,
    [VersionNo] [nvarchar](50) NULL,
    [UserName] [nvarchar](255) NULL,
    [ShopName] [nvarchar](150) NULL,
    [FeedId] [int] NULL,
 CONSTRAINT [PK_PProduct] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

They say each of my deletes take up 256Kb, and i have 75Mb Log space. That is around 300 rows i can delete in a single transaction. How is my table design - inefficient?

Upvotes: 1

Views: 278

Answers (4)

TomTom
TomTom

Reputation: 62157

Yes, it means your host is run in a way that means you loose data on a server crash, without a way to take backups. Check your SLA about data loss and recovery scenarios.

It suggests you have:

  • VERY little space in the log (only enough to delete 100 items)
  • THe log space is reclaimed after every commit.

This means:

  • Yes, the host is right, as you run out of log space (pathetic amounts) you can not delete 14.000 rows in a run.
  • You possibly also take backups once per day. This is signicicant an issue if a server fails. A professional setup would: (a) keep the log until the next backup and (b) do daily backups of the databhase and like hourly backups of the logs, so you can go back to the "last hour". In your case you may loose 23.9 hours worth of changes.

Technicaly the 100 item limit is pathetically low. What do we talk here? 16mb log space?

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425753

Each data modification in SQL Server should be recorded in the transaction log and stored there until its not needed anymore (the transaction it made is over in one or another way, there is no active replication going on, the data had been written to the data files and several other conditions).

If there is no enough space in the transaction log to do this, the exception you see is raised.

The transaction log is truncated from time to time, and usually is set up to grow automatically if it's full and cannot be truncated.

In some cases, it can neither grow (because it's forbidden by the setup or there is no more free space on disk), neither truncated. Here is the list of possible causes that would prevent the log truncation:

http://msdn.microsoft.com/en-us/library/ms345414.aspx

The transaction log in any reasonably tuned database should be large enough to delete 10K records (unless they are really large, e. g. hold large images, binary data etc.).

Upvotes: 0

Farzin Zaker
Farzin Zaker

Reputation: 3606

this is because of your database log file max size witch is limited by your provider. you can delete them with a query like this:

suppose that you have a unique primary key in your table witch is named Id:

While(Exists(SELECT * FROM MyTable))
BEGIN
   DELETE FROM MyTable 
      WHERE Id IN (SELECT TOP(100) Id FROM MyTable)
END

Upvotes: 1

gbn
gbn

Reputation: 432639

Like this

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (100) MyTable WHERE ...

But 13k rows is peanuts. Lordy. What size are these rows...?

Upvotes: 2

Related Questions