Solved

Disk is full

Posted on 2001-06-20
23
679 Views
Last Modified: 2006-11-17
MS SQL 7.0 is installed in my NT4.0 server, the data disk is almost full, and where I delete some table's records, a error message is displayed: "The log file for database MyDatabaseName is full. Back up the transaction log for the database to free up some log space.". How can I free the log space?
Thanks.
 
0
Comment
Question by:Yuanyu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 6
  • 2
  • +1
23 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6212599
try a backup command on the log with truuncate only.
Set the database option to truncate log on checkpoint (if you aren't relying on log backups which you probably aren't if you get this problem)

You should set a max size for the files to stop the disc filling up.

If you can't get anywhere try a sp_detach_db
copy the ldf to tape (if you don't have any room otherwise just rename it)
then sp_attach_single_file_db - this will create a 2MB ish log file
0
 

Author Comment

by:Yuanyu
ID: 6212634
I have truncated transaction log, but "the log file is full" is still displayed, and I deleted some records, but disk space is not returned, when the space can be returned after records deleted?
0
 

Author Comment

by:Yuanyu
ID: 6212651
My database is 6.8G, and free disk space is 4M, can I run CHECKPOINT?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:nigelrivett
ID: 6212720
Have you set the tran log to truncate on checkpoint?
this will happen automatically - or can just run checkpoint in a query window to force it.

Also set the tran log and database to increase in increments of 1 MB - it may need to write a log record to do anything.
If you can delete anything on this disc then do so.

also run

dbcc opentran('dbname')
where dbname is the database you are having problems with.
This will show any open transactions which may be stopping the log being cleared - it will only truncate up to the earliest uncommitted entry.
Try killing any spids that are holding transactions open.
0
 

Author Comment

by:Yuanyu
ID: 6212801
"Truncate on checkpoint" is enable.
I used dbcc opentran('dbname'), and no active open transactions is found.
My database is related two transaction logs(I don't know why), when I run checkpoint, the first one can be truncate, but the second one cannot. Is the second one eating my disk space. How can I delete the second log?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6212826
If you have no active transactions I would go for

Backup that database
sp_detach_db
delete the ldf files
then sp_attach_single_file_db
0
 

Author Comment

by:Yuanyu
ID: 6212835
"Truncate on checkpoint" is enable.
I used dbcc opentran('dbname'), and no active open transactions is found.
My database is related two transaction logs(I don't know why), when I run checkpoint, the first one can be truncate, but the second one cannot. Is the second one eating my disk space. How can I delete the second log?
0
 

Author Comment

by:Yuanyu
ID: 6212844
Delete the two log files?
0
 

Author Comment

by:Yuanyu
ID: 6212851
Is data safety?
0
 

Author Comment

by:Yuanyu
ID: 6212860
How to use 'sp_attach_single_file_db'?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6213824
Backup the database

sp_detach_db 'dbname'
delete the log files (you can always restore the backup if it goes wrong)
sp_attach_single_file_db 'dbname', 'c:\...\filename.mdf'

It basically reattaches the mdf and creates a new tran log.
0
 
LVL 3

Expert Comment

by:mgmanoj
ID: 6215149
Better way is add one more log file the drive on which you have space & than try to truncate as per niquels suggestion it will work as to run the command also you need space in log file.
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6215308
"dump tran databasename with truncate_only" cannot be executed since it is a logged operation. Since the log is already full, you can't run that command.

You can run "dump transaction dbname with no_log" to clear the completely.

In some cases, even the no_log will not clear the log. It will comeback very quick and will do nothing. The only possibility for clearing this is by increasing it by couple of MB (say 5 MB) and run the dump tran dbname with truncate_only right away.

0
 
LVL 3

Expert Comment

by:mathavra
ID: 6215329
There used to be a bug in SQLServer reporting the log space usage incorrectly. Do the following in that database and see what it shows:

1) Run the following command and find Used KB.

sp_spaceused syslogs

2) Run "select count(*) from syslogs" and see how many rows are in the syslogs table.

The UsedKB should show about 16KB at the most when syslog has less number of rows.

Can you post the results of the above commands.
0
 

Author Comment

by:Yuanyu
ID: 6216566
--sp_attach_single_file_db 'dbname', 'c:\...\filename.mdf'
--It basically reattaches the mdf and creates a new tran log.

When I rename the log files name and run the "sp_attach_single_file_db", the new log cannot be created, and the database cannot be attached.

I don't need the second log file, how to delete it?



0
 

Author Comment

by:Yuanyu
ID: 6216582
Which database should I select to run 'sp_spaceused syslogs' in SQl Analyzer?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6216783
You need to be in the database whose log you want to check for sp_spaceused.

To look at log space used try
DBCC SQLPERF(logspace)
This will give a log report for all databases

better is
dbcc loginfo (dbname)
this will give an entry with status = 2 for the used pages.
0
 

Author Comment

by:Yuanyu
ID: 6216947
nigelrivett,
   Could you answer the previous problem?
   And, after DBCC loginfo(dbname), What can I do?
   Can I remove the second log?
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6216970
dbcc loginfo will give info on used virtual files.
You can only shrink back to the last used virtual file - this is why the shrink doesn't always do anything.


Sorry I missed this post
When I rename the log files name and run the "sp_attach_single_file_db", the new log cannot be created,
and the database cannot be attached

What does it say in the error message? If it doesn't have space then you will have to delete some files frm the disc. Try deleting the log files rather than renaming them (keep a copy somewhere just in case though).
0
 

Author Comment

by:Yuanyu
ID: 6217017
nigelrivett,
  Can I remove the second log?
 
  I do this in my test server, and its space is enough.My database is TT, interrelated logs are TT_Log and TT0_Log, when I do
  sp_attach_single_file_db 'TT','G:\MSSQL7\Data\tt_data.mdf'

  messages displayed as below:
Server: Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name 'g:\MSSQL7\data\TT0_Log.LDF' may be incorrect.
Server: Msg 945, Level 14, State 1, Line 1
Database 'TT' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'TT'. CREATE DATABASE is aborted.

  After I rename the log files back, TT is returned.
0
 

Author Comment

by:Yuanyu
ID: 6217433
 It is OK in the case of one log file.
  How can I remove the second log?
0
 

Author Comment

by:Yuanyu
ID: 6217657
Use sp_attach_db can solve the problem.
Thank you very much!
0
 

Author Comment

by:Yuanyu
ID: 6220809
nigelrivett,
  sorry, sp_attach_db cannot solve the problem actually.
  I ask a new question named 'How to remove the second log', welcome to point out the answer.
  thanks.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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