September 15, 2010 at 3:18 pm
Experienced DBA but new to SQL Server. Inherited a big mess where users complained they can't access the database. I discovered that the transaction log was created without a maxsize so it grew and grew until the D: drive filled up. Now I can't use the database because it is in recovery mode, so I can't run alter database, dbcc shrinkfile, etc. There is nothing else on the D: drive that can be removed. I'm not sure whether more disk can be added to expand the D: drive. I'm afraid to recycle the SQL Server as a means to resolve this. Is there anything I can do? This log has grown to 263 GB and the data is only 96GB. The recovery model was set to FULL and log_reuse_wait_desc is LOG_BACKUP.
September 15, 2010 at 3:36 pm
Usually when this happens on an active database you force truncate the log immediately, and then proceed to an immediate full or differential backup, because you broke the log-chain.
If you're stuck in recovery mode, it sounds like someone cycled the server. On a log THAT big, it can take a while. Around how long as this been going?
In the meantime, you might want to double check the server jobs and make sure backups are occurring on a regular basis... meaning at the least each hour if you're getting that large.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 3:39 pm
Gail (or someone else) should be around shortly, and will be able to provide much better answers to help you get out of this than I ever could. While waiting, DO NOT restart the server, attempt to take the database offline, detach the database, etc.
I can, however, tell you how you got in this mess in the first place. As you stated, your database is in the full recovery model. And, you're not performing transaction log backups. Transaction log backups is the only mechanism that will free up the transactions in the log file that have already been written to the data file, and allow them to be overwritten again. Since this wasn't being done, your log file grew (and grew.....).
To prevent this, you have basically two solutions:
1. Take regular transaction log backups. Depending on the volume of activity in the database, this can be from daily to every few minutes.
2. Change the database recovery model to simple.
To make this decision, you need to know things like how much data can you afford to lose? (Last 10 minutes only? Since the last full backup, last night, where all of the days work can be lost? etc.) This will guide you towards what you need to do.
Edit: the size of the log file will depend on the activity going on, but I don't see any way that it should be that big. 2-5gb, probably. 10-20 possibly. As large as the database - not likely at at. 3 times the size - no way.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 4:14 pm
What's the state of the database? (state_desc in sys.databases)
If RECOVERING, wait. It should finish eventually.
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
September 15, 2010 at 4:16 pm
Craig Farrell (9/15/2010)
If you're stuck in recovery mode, it sounds like someone cycled the server. On a log THAT big, it can take a while. Around how long as this been going?
Recovery time is affected by the size of the active portion of the log, not the total size.
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
September 15, 2010 at 4:35 pm
GilaMonster (9/15/2010)
Craig Farrell (9/15/2010)
If you're stuck in recovery mode, it sounds like someone cycled the server. On a log THAT big, it can take a while. Around how long as this been going?Recovery time is affected by the size of the active portion of the log, not the total size.
Can't argue that, but anytime I hear they filled the drive with their log, I usually assume the entire thing was active, and rolling back that monster they tacked onto the end and never got a checkpoint for. 🙂 I kinda wonder if it was bulk-logged, too, and hit that size... *twitch*
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 4:47 pm
Craig Farrell (9/15/2010)
Can't argue that, but anytime I hear they filled the drive with their log, I usually assume the entire thing was active, and rolling back that monster they tacked onto the end and never got a checkpoint for. 🙂
Depends whether it filled the drive because of an old active transaction or because of no log backups in full recovery. The first would leave the entire log active (from the beginning of the oldest open transaction), but the second is (fortunately) more common.
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
September 15, 2010 at 5:14 pm
GilaMonster (9/15/2010)
Craig Farrell (9/15/2010)
Can't argue that, but anytime I hear they filled the drive with their log, I usually assume the entire thing was active, and rolling back that monster they tacked onto the end and never got a checkpoint for. 🙂Depends whether it filled the drive because of an old active transaction or because of no log backups in full recovery. The first would leave the entire log active (from the beginning of the oldest open transaction), but the second is (fortunately) more common.
While we wait for a response from the OP, question on that then, because my understanding was it would recheck even checkpointed logging in case of severe failure. I know the log checkpoints once memory pages are written and implicit/explicit transactions complete, but is there a second checkpoint regarding memory page writes down to drive to confirm storage in case of crash? Do you have a document link handy that you could point me in the direction of in case it's a long complicated answer?
I know I've dealt with data corruption on drive buffers failing on me against checkpoints in the log (catastrophic failure, I know, and little we can do about that one), but until lazy writer gets around to sending it to disk, how do you not lose the change?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 5:36 pm
Thanks everyone. Whoever set up this database made some big mistakes. From the older logs that I have seen, it appears that they had set up full db backups for each database nightly. There is a daily process that inserts rows into two tables in the database daily. The only other activity is a handful of users running adhoc reports against it. The disk filled up over 2 weeks ago, so I highly doubt that the server is trying to recover from the log. There are no active transactions.
I know the "right" things to do regarding the logs and backups going forward....what I need to know now is do I have any way out of the Catch-22 I am in? Even if I were able to expand the D: drive or free up some space, I don't imagine SQL Server waking up and using the new space on its own.
I was thinking of shrinking the size of tempdb to free up some space, then shrink this database, and expand tempdb back. Could this work?
September 15, 2010 at 5:52 pm
I know *A* way, and it's not pretty. But to determine its validity before giving one of the gurus round here a chance come up with a better way, you'll want to answer this as well, from Wayne above:
To make this decision, you need to know things like how much data can you afford to lose? (Last 10 minutes only? Since the last full backup, last night, where all of the days work can be lost? etc.) This will guide you towards what you need to do.
Have you been successfully backing up, but not successfully putting in data, for two weeks? Or has the drive been full (because logs expand and re-use space unless shrunk) but have been successfully including data?
The reason I ask is because the command (Do NOT drop this in without recoginizing it's going to cause problems, think nuclear level repairs) BACKUP LOG <dbname> WITH TRUNCATE_ONLY will clear this, at least from the log perspective, allowing for a log shrink and for things to happen again. But this is only good if you're out of recovery.
Another nuclear option is to kill the recovery, destroy the .mdf and .ldf files, and restore it from backup.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 5:59 pm
Since the drive filled up two weeks ago, no backups have happened and no additional data has been loaded.
My background is in Sybase where I was rarely scared by "dump tran with truncate_only" in a situation such as this, but not so brave with SQL Server.
September 15, 2010 at 6:15 pm
If TempDB or any other logs are on the same drive I would shrink those before I did anything else. SQL is pretty smart and if there is space on the disk and the DB log file is set to AUTO GROW it will grow.
One of the problems you have is you may not be able to even do a "backup log ...with truncate" because to do this SQL needs to write a marker to the log file but if the file is full and can't grow, even this will fail.
Basically make some space on the disk, somehow, then let the DB recover, then backup the DB or put into SIMPLE mode, shrink log, back into FULL mode, backup DB, backup log.
If you need to stop other users from accessing the environment so there are no other actions that can write to the disk.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 15, 2010 at 6:21 pm
Leo,
I agree that backup log with truncate will likely not work for the reasons you stated. Works that way in Sybase, too. Autogrow is on for this log with no max size set. If I free up some space from tempdb and get back on track, how do I grow tempdb back to its previous size?
Kathryn
September 15, 2010 at 6:48 pm
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_txt nchar(4000)
)
go
declare @Cnt smallint;
set @Cnt = 0
while @cnt < 100
begin
insert into dbo.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
SQL = Scarcely Qualifies as a Language
September 15, 2010 at 7:27 pm
You don't need to worry about TempDB, it will grow as it needs to.
Carl's solution is also a good one if you have space on another disk, AND if this action doesn't also try write to the currently full log.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 15 posts - 1 through 15 (of 86 total)
You must be logged in to reply to this topic. Login to reply