D: drive is full and transaction log is full

Question

D: drive is full and transaction log is full

Viewing 15 posts - 16 through 30 (of 86 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

Carl Federl (9/15/2010)
The simpliest solution is to add a additional log file on another drive, backup the log, shrink the first log file and then remove the additional log file. Here is a full example:
USE [master]
GO
CREATE DATABASE [LogFileFull]
ON PRIMARY
( NAME = N'LogFileFull_Data'
, FILENAME = N'F:\MSSQL10.MSSQLSERVER\MSSQL\DATA\LogFileFull_Data.mdf'
, SIZE = 2MB , MAXSIZE = 2MB
)
LOG ON
( NAME = N'LogFileFull_Log'
, FILENAME = N'F:\MSSQL10.MSSQLSERVER\MSSQL\DATA\LogFileFull_Log.ldf'
, SIZE = 1MB , MAXSIZE = 1MB
)
GO
ALTER DATABASE [LogFileFull] SET recovery full
GO
-- Simple recovery mode in effect until a full backup is completed.
BACKUP DATABASE [LogFileFull]
to disk = N'F:\MSSQL10.MSSQLSERVER\MSSQL\Backup\LogFileFull.BAK'
GO
USE LogFileFull
GO
-- Fill the transaction log
CREATE TABLE dbo.Foo
(foo_txtnchar(4000)
)
go
declare @Cnt smallint;
set@Cnt = 0
while @cnt < 100
begin
insert intodbo.Foo (foo_txt ) values ( N' ');
set @cnt = @cnt + 1 ;
end;
go
USE [master]
GO
-- Rollback all transactions that are open and disconnect users from database
ALTER DATABASE [LogFileFull] SET OFFLINE WITH ROLLBACK IMMEDIATE
go
-- Only allow database owners to access database
ALTER DATABASE [LogFileFull] SET ONLINE, RESTRICTED_USER;
go
ALTER DATABASE [LogFileFull]
ADD LOG FILE
( NAME = N'LogFileFull_Log_2'
, FILENAME = N'F:\MSSQL10.MSSQLSERVER\MSSQL\Data\LogFileFull_Log_2.ldf'
, SIZE = 1MB , MAXSIZE = 1MB
)
GO
BACKUP LOG [LogFileFull]
to disk = N'F:\MSSQL10.MSSQLSERVER\MSSQL\Backup\LogFileFull.TRN'
go
USE [LogFileFull]
GO
DBCC SHRINKFILE (N'LogFileFull_Log' , 2)
GO
ALTER DATABASE [LogFileFull] MODIFY FILE ( NAME = N'LogFileFull_Log', MAXSIZE = 20480KB )
GO
ALTER DATABASE [LogFileFull] REMOVE FILE [LogFileFull_Log_2]
GO
ALTER DATABASE [LogFileFull] SET MULTI_USER;
go

Carl... Will that work if the current drives are 100% full?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Ok, before going on I'd really like an answer to what I asked last night.

What is that actual state of the database? (query sys.databases)?

Also please check the error log and see if there are any entries relating to this DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

if you are using same drive for all database ( tempdb also ) then you can do some shrinking on other DBs (like tempdb), for the time being it will provide you liberty to shrinking of concerned DB.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

hallhome SSC Veteran Points: 265 More actions · Answer 4

hallhome

SSC Veteran

Points: 265

September 16, 2010 at 7:12 am

#1222153

From sys.databases, state_desc = RECOVERY_PENDING

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ok, that is never going to recover by itself and you won't be able to add log files in that state.

Please read through the SQL error log and see what messages are in the log relating to this database.

What else is on the drive that contains the log file? Anything that can be shrunk/moved temporarily?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallhome SSC Veteran Points: 265 More actions · Answer 6

The messages in the errorlog indicated that there was no space left on the disk, followed by the transaction log full message. This occurred two weeks ago. Additionally the log is now reporting that the transaction logs for model and msdb are also full. Unfortunately there is nothing on the D: drive that can be deleted. This database is taking up 95% of the entire drive.This database was set up as LOG_BACKUP but from what I can see, they executed full backups each day, not log backups, which is why the log continued to grow.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please look in the error log, since the last restart, and post the exact errors that are there. There should be some errors relating to recovering the database (unable to write checkpoint or similar)

What's the other 5% of this drive?

Is there another drive on the server with some free space? If not, is it possible to put a flash drive into the server?

What's the latest backup you have of this database and is losing data back to that backup an option?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallhome SSC Veteran Points: 265 More actions · Answer 8

The SQL server errorlogs do not go back as far as the last restart of the server. There is a message indicating that the process has been active since 3/23 but I only have logs going back to June. I am waiting to hear if more space can be made available. The rest of the drive is taken up by the system databases and two very small user databases, there is nothing that can be deleted. The last full backup was taken just before the disk drive filled up. Yes, I can go back to that backup but how do I know that everything in the log has been committed to disk?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

hallhome (9/16/2010)
I am waiting to hear if more space can be made available. The rest of the drive is taken up by the system databases and two very small user databases, there is nothing that can be deleted.

Can the user databases be moved? Even temporarily?

The last full backup was taken just before the disk drive filled up. Yes, I can go back to that backup but how do I know that everything in the log has been committed to disk?

The backup will be consistent as of the point that backup was taken. You'll lose everything past that backup.

If that's an option, I think it'll be the quickest and easiest solution. Is there another server that you can test the backup on? I'd rather not recommend a drop and restore without the backup being tested first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Carl Federl One Orange Chip Points: 25384 More actions · Answer 10

Will that work if the current drives are 100% full?

There are some constraints with the solution:

1. The second transaction log needs to be on a different drive that has at least 1MB free.

2. The database primary file group needs to have enough space to add the definition of the second transaction log. So if the primary file is full and cannot grow, adding the second log file will fail.

SQL = Scarcely Qualifies as a Language

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Carl Federl (9/16/2010)
Will that work if the current drives are 100% full?
There are some constraints with the solution:
1. The second transaction log needs to be on a different drive that has at least 1MB free.
2. The database primary file group needs to have enough space to add the definition of the second transaction log. So if the primary file is full and cannot grow, adding the second log file will fail.

3. The database needs to be Online

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallhome SSC Veteran Points: 265 More actions · Answer 12

I can't connect to the database to issue the alter database command to add another file.

Does anyone think that recycling the SQL Server will help?

WayneS SSC Guru Points: 95460 More actions · Answer 13

No, it won't help. (At least, not yet.)

Gail is one of the best at this (I can only think of one better)... please follow her advice to the letter.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 14

All the solutions are really good but please consider the fact that the db is in recovering state...

I think you have these options,

1. Free up some space on D drive and see that if the DB recovers. This may include shrinking other databases to free up some space.

2. Wait for the db to recover. But it has been already 2 weeks that it's in the recovering state.

3. Restart SQL Server.

Thank You,

Best Regards,

SQLBuddy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

How will restarting the server help if the recovery fail because of missing space??