• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Deleting records but Transaction log full

We have made the unfortunate mistake of not closely monitoring the disk space on our sql server. (The drive that contains the data)

We would like to delete some old reporting data older than 2009 but we cant now because when we run a query to delete these records we get "Transaction log full"

sp_helpfile comes back with the below:
KPDatamart_Data                                                                                                                       1      D:\Data\KPdatamart.mdf                                                                                                                                                                                                                                                    PRIMARY      44887104 KB      Unlimited      10%      data only
KPDatamart_Log                                                                                                                        2      D:\Transactions\KPdatamart_log.ldf                                                                                                                                                                                                                                        NULL      7488 KB      Unlimited      10%      log only

We have run the below commands but we still cannot delete data from the table in order to reduce the physical size of the .mdf file

DBCC SHRINKFILE(kpdatamart_log,2)
BACKUP LOG kpdatamart WITH TRUNCATE_ONLY
DBCC SHRINKFILE(kpdatamart_log,2)


What can we do to delete records from one of the big reporting tables in order to reduce the size of this database now that we are physically in a catch 22 situation?

I'm running the query:
DELETE FROM ReportingData
WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))


The data starts on 02/10/07 and there's only about 1000 records from that day but it will not delete
0
Dalexan
Asked:
Dalexan
  • 15
  • 5
  • 5
  • +3
5 Solutions
 
Mohit VijayCommented:
first try to shrink your database and then try to delete records.
0
 
DalexanAuthor Commented:
Thanks for quickly responding but your response doesnt help, In my question post I do say that I run the commands:
DBCC SHRINKFILE(kpdatamart_log,2)
BACKUP LOG kpdatamart WITH TRUNCATE_ONLY
DBCC SHRINKFILE(kpdatamart_log,2)

This doesnt help....I still cant delete the records even after the shrink.....
0
 
Mohit VijayCommented:
ohh sorry, I missed. Have you tried after restarting your SQL Server.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
DalexanAuthor Commented:
I'm trying to avoid that as its in production. Is there any other way? Is the syntax of the shrink command I am using correct? I am only trying to shrink the logfile, how do I run the shrinkfile command on the database?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the statements you have shown might need to be repeated before they take effect.can you add another transaction log file?can you delete 1000 rows at once (using SET ROWCOUNT 1000) before running the delete
0
 
DalexanAuthor Commented:
Whats the syntax for the SET ROWCOUNT? I tried the below but it doesnt recognize the syntax.
SET              ROWCOUNT 1000
                          DELETE FROM ReportingData
                           WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))

Also I have not tried the DBCC SHRINKDATABASE(kpdatamart,2) command, will this shrink the physical size of the database while its in production?
0
 
vdr1620Commented:
try this to delete top 1000 rows

DELETE TOP (100) R
FROM ReportingData R
                           WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
0
 
vdr1620Commented:
pls change 100 to 1000 in the above
0
 
DalexanAuthor Commented:
Incorrect syntax near keyword TOP

DELETE TOP (1000) R
FROM ReportingData R
                           WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
0
 
vdr1620Commented:
The syntax is correct.. check this sample code

DECLARE @Test TABLE
(
OrdCallDate DateTime
)

INSERT INTO @Test  VALUES ('2007-02-10 02:00:00')

DELETE TOP (1) A
FROM @Test A
WHERE  (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
0
 
DalexanAuthor Commented:
Here's a screenshot of the error
Syntax-error.bmp
0
 
vdr1620Commented:
It works in SQL 2008..Ok. try this without aliasing


DECLARE @Test TABLE
(
OrdCallDate DateTime
)

INSERT INTO @Test  VALUES ('2007-02-10 01:00:00')

DELETE TOP (1)
FROM @Test
WHERE  (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))

Untitled.jpg
0
 
DalexanAuthor Commented:
Same syntax error, running SQL2k server
0
 
vdr1620Commented:
Ok... the last way i can think of .. i have tried and its working fine for me..for your table use an Unique/identity column

DECLARE @Test TABLE
(
Id INT,
OrdCallDate DateTime
)

INSERT INTO @Test  VALUES (1,'2007-02-10 01:00:00')

DELETE @Test
WHERE ID =
(SELECT TOP 1000 ID FROM @Test
WHERE  (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102)))
0
 
Anthony PerkinsCommented:
The following is perfectly legal SQL Server 2000 syntax:

SET ROWCOUNT 1000
DELETE  FROM ReportingData
WHERE   (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))

However you first need to resolve your Transaction Log situation.  What is your Recovery Model?  If it is Full, when was the last time you did a Transaction Log Backup,  I suspect never.
0
 
DalexanAuthor Commented:
Recovery model is simple
0
 
Scott PletcherSenior DBACommented:
What about the other dbs on the server?  Can you recover any significant space from them?

>> Also I have not tried the DBCC SHRINKDATABASE(kpdatamart,2) command, will this shrink the physical size of the database while its in production? <<

I would NOT try that, unless you have a LOT of free space in the db itself.  It will take a very long time to shrink that size a db with almost no disk space free.


>> The data starts on 02/10/07 and there's only about 1000 records from that day but it will not delete <<

Ouch.  You may to start out deleting only 100 at a time.

If this has enough space:
    SET ROWCOUNT 100
    DELETE FROM ReportingData
    WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
    SET @rowcount = @@ROWCOUNT
    DBCC SHRINKFILE(kpdatamart_log,2)
    BACKUP LOG kpdatamart WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(kpdatamart_log,2)

Then you can set up a loop, like so:

DECLARE @rowcount int
SET @rowcount = 1

WHIILE @rowcount > 0
BEGIN
    SET ROWCOUNT 100
    DELETE FROM ReportingData
    WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
    SET @rowcount = @@ROWCOUNT
    DBCC SHRINKFILE(kpdatamart_log,2)
    BACKUP LOG kpdatamart WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(kpdatamart_log,2)
END --WHILE
0
 
Scott PletcherSenior DBACommented:
Naturally also check other *large* files on the server, to see if you can free some space that way.

You can use Windows file search to find only files larger than a certain size, say 500M.

Do you have a USB / removable drive you can connect to the server temporarily to hold files temporarily?
0
 
DalexanAuthor Commented:
Yes, I do have a TB usb drive with plenty of space already attached to the server.
0
 
Scott PletcherSenior DBACommented:
See if you have any large files you can move there.

Is there any large db you can do without for, say, an hour or two?

If so, you can FIRST run a CHECKDB on that db, then DETACH it, move it to the USB drive.  
****** NEVER EVER DETACH A DB YOU DID NOT CHECK FIRST !! *****
That will free up additional space for dealing with the bloated db.  Then, when done with those deletes, you can put ATTACH the db to put it back in place.

0
 
DalexanAuthor Commented:
Can I detach it, move it to the USB drive and attach from there?

I cant do without any of the databases, production software is using them all.
0
 
DalexanAuthor Commented:
I'm running the below but it seems I am not decreasing the physical size of the database any.

DECLARE @Counter Int
SET              @Counter = 1
WHILE @Counter < 2
BEGIN
      PRINT 'counter number %1!' + cast(@Counter as varchar(20))
      SET              ROWCOUNT 5000
                          DELETE FROM Harp_ReportingData
                           WHERE     (OrdCallDate < CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
      SET              @Counter = @Counter + 1
END
GO

DBCC SHRINKFILE(kpdatamart_log,2)
BACKUP LOG kpdatamart WITH TRUNCATE_ONLY
DBCC SHRINKFILE(kpdatamart_log,2)

Then I get this message:

Cannot shrink log file 2 (KPDatamart_Log) because total number of logical log files cannot be fewer than 2.
0
 
Anthony PerkinsCommented:
>>Cannot shrink log file 2 (KPDatamart_Log) because total number of logical log files cannot be fewer than 2<<
Since you are running in Simple Recovery model.  Try executing the following several times before doing a DBCC SHRINKFILE:
CHECKPOINT

You should not execute DBCC SHRINKFILE before doing a BACKUP LOG only after, other wise it will never shrink.  
Also, do you really want to srhink it down to only 2MB.  Don't you mean 2GB as in:

DBCC SHRINKFILE(kpdatamart_log, 2000)
0
 
DalexanAuthor Commented:
I did the CHECKPOINT command several times then did a BACKUP LOG kpdatamart WITH TRUNCATE_ONLY

Then I tried the DBCC SHRINKFILE(kpdatamart_log,2000) which returns the below:

Cannot shrink file '2' in database 'KPdatamart' to 256000 pages as it only contains 1136 pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
Anthony PerkinsCommented:
What does DBCC OPENTRAN report?
0
 
Anthony PerkinsCommented:
Never mind, I misread the error message.  It sounds like your Log file is now below 2GB.  Did you want it any smaller?
0
 
DalexanAuthor Commented:
I need to reduce the size of the database in order to free up physical space on the drive. Deleting the records out and truncating the log and then running the shrink doesn't seem to be reducing the physical size.
0
 
Anthony PerkinsCommented:
So what size is the maximum size the Log file can be?
0
 
DalexanAuthor Commented:
Since I'm running in Simple recovery model i dont need a transaction log.

It can be 0, null if thats possible.
0
 
Scott PletcherSenior DBACommented:
ac, the log was *never* that big.  It' always been the db.

I should have realized before, you will have to rebuild (or reorg?) the table to get the space back from the DELETEs on that table ... the problem is rebuilding the table typically takes approx. 1.5 times the size of the table to do the rebuild ... D'OH.  And it will increase the log size.

Is there a clustered index on the table?  **If so**, *after* doing the DELETEs, issue this command:

DBCC INDEXDEFRAG
    ( database_name , table_name, 1 )

INDEXDEFRAG does *not* require additional space, and will free space for removed pages, but takes a relatively long time to run.  But for one table it shouldn't be too bad unless the table is huge.


[With some trace flag settings, you can have a db on an external / remote drive.  Worst case, you might have to resort to that here to get it truly cleaned up.  With the extra space on that drive, you could issue your DELETEs *and* a full table rebuild with no problems.]
0
 
DalexanAuthor Commented:
Running it now, I will run the truncate log and shrink once its done. Hopefully this will free up enough space for the weekend.

DBCC INDEXDEFRAG( kpdatamart , reportingdata, 1 )

DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 4% complete.
DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 33% complete.
DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 34% complete.
DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 39% complete.
DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 41% complete.
DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 50% complete.
DBCC: Compaction phase of index 'KPdatamart.dbo.ReportingData' is 52% complete.
0
 
DalexanAuthor Commented:
Over the weekend we were forced to create a new database server and restore a backup creating a new database server.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 15
  • 5
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now