Solved

Deleting records but Transaction log full

Posted on 2010-08-26
32
409 Views
Last Modified: 2012-05-10
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
Comment
Question by:Dalexan
  • 15
  • 5
  • 5
  • +3
32 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33535276
first try to shrink your database and then try to delete records.
0
 

Author Comment

by:Dalexan
ID: 33535309
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33535348
ohh sorry, I missed. Have you tried after restarting your SQL Server.
0
 

Author Comment

by:Dalexan
ID: 33535375
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 33535439
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
 

Author Comment

by:Dalexan
ID: 33535510
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
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 100 total points
ID: 33535626
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33535637
pls change 100 to 1000 in the above
0
 

Author Comment

by:Dalexan
ID: 33535704
Incorrect syntax near keyword TOP

DELETE TOP (1000) R
FROM ReportingData R
                           WHERE     (OrdCallDate < CONVERT(DATETIME, '2007-02-10 02:00:00', 102))
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33535861
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
 

Author Comment

by:Dalexan
ID: 33535953
Here's a screenshot of the error
Syntax-error.bmp
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33536094
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
 

Author Comment

by:Dalexan
ID: 33536114
Same syntax error, running SQL2k server
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33536273
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 20 total points
ID: 33538442
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
 

Author Comment

by:Dalexan
ID: 33541452
Recovery model is simple
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33542593
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33542613
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
 

Author Comment

by:Dalexan
ID: 33542697
Yes, I do have a TB usb drive with plenty of space already attached to the server.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 180 total points
ID: 33542736
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
 

Author Comment

by:Dalexan
ID: 33542805
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
 

Author Comment

by:Dalexan
ID: 33543057
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33544297
>>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
 

Author Comment

by:Dalexan
ID: 33544479
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33544551
What does DBCC OPENTRAN report?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33544573
Never mind, I misread the error message.  It sounds like your Log file is now below 2GB.  Did you want it any smaller?
0
 

Author Comment

by:Dalexan
ID: 33544655
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33544762
So what size is the maximum size the Log file can be?
0
 

Author Comment

by:Dalexan
ID: 33544797
Since I'm running in Simple recovery model i dont need a transaction log.

It can be 0, null if thats possible.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 180 total points
ID: 33545327
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
 

Author Comment

by:Dalexan
ID: 33545967
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
 

Author Comment

by:Dalexan
ID: 33560189
Over the weekend we were forced to create a new database server and restore a backup creating a new database server.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now