Tibor Karaszi : Transaction log

Point-in-time restore of database backup?

TiborKaraszi — Tue, 28 Dec 2010 16:42:00 GMT

SQL Server 2005 added the STOPAT option for the RESTORE DATABASE command. This sounds great - we can stop at some point in time during the database backup process was running! Or? No, we can't. Here follows some tech stuff why not, and then what the option...(read more)

Why we never want to trick SQL Server into rebuilding a log file

TiborKaraszi — Thu, 14 Oct 2010 17:04:00 GMT

"Never" is a strong word, so let me say that we really really want to avoid it, if at all humanly possible. In short, we will have a (potentially) broken database, both at the physical level and at the logical level. Read on. Just to be clear, when I...(read more)

Restore database to the point of disaster

TiborKaraszi — Sat, 27 Mar 2010 09:50:00 GMT

This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster? Yes, of course...(read more)

Are log records removed from ldf file for rollbacks?

TiborKaraszi — Mon, 22 Mar 2010 09:34:00 GMT

Seems like a simple enough question, right? This question (but more targeted, read on) was raised in an MCT forum. While the discussion was on-going and and I tried to come up with answers, I realized that this question are really several questions. First,...(read more)

Do you perform log backup for the model database?

TiborKaraszi — Wed, 18 Nov 2009 12:05:00 GMT

Are you stupid, you might think... But stop and think for a while. Model is no different from other databases. And by default it is in full recovery model. So as soon as you do your first database backup (you do backup your system databases, right?) the...(read more)

Will EMPTYFILE on primary ldf "doom" it somehow?

TiborKaraszi — Tue, 29 Sep 2009 15:48:00 GMT

I just read a newgroup question whether doing SHRINKFILE with the EMPTYFILE option for the primary log file somehow cause ill effects. Shrinkfile for the ldf will not move any data (log records) or so. For an ldf file it is basically a preparation to...(read more)

Table restore and filegroups

TiborKaraszi — Thu, 25 Jun 2009 10:49:00 GMT

The story usually goes something like: Q - How can I restore only this table? A - Put it on its own filegroup and you can do filegroup level backup and restore. The problem with above answer is that it most likely misses the point. We need to ask ourselves:...(read more)

Remove transaction log files

TiborKaraszi — Wed, 17 Jun 2009 05:47:00 GMT

Say you happened to get too many transaction log (ldf) files. Can you remove log files from the database? Yes, but only if a file isn't in use, and you cannot remove the first ("primary") log file.

So, be prepared to investigate the virtual file layout, using DBCC LOGINFO, to see if a log file is in use or not. You can find information about how to investigate the virtual log file layout in my shrink article. The basic steps are a bit similar to shrinking a log file: Investigate virtual log file layout, backup log, possibly shrink file, try removing it. Do this again as many times as it takes (repeat, rinse and lather).

Below is a script that, if you take the time to study it and play with it, will prepare you to remove transaction log files from a database. As always, don't execute it if you don't understand what it does!

USE master 
IF DB_ID('rDb') IS NOT NULL DROP DATABASE rDb 
GO 

CREATE DATABASE rDb 
ON  
PRIMARY  
 ( NAME = N'rDb', FILENAME = N'C:\rDb.mdf' , SIZE = 50MB , FILEGROWTH = 1024KB ) 
LOG ON  
 (NAME = N'rDb_log2', FILENAME = N'C:\rDb_log2.ldf', SIZE = 3MB, FILEGROWTH = 2MB) 
,(NAME = N'rDb_log3', FILENAME = N'C:\rDb_log3.ldf', SIZE = 3MB, FILEGROWTH = 2MB) 
,(NAME = N'rDb_log4', FILENAME = N'C:\rDb_log4.ldf', SIZE = 3MB, FILEGROWTH = 2MB) 
GO 

ALTER DATABASE rDb SET RECOVERY FULL 
BACKUP DATABASE rDb TO DISK = 'C:\rDb.bak' WITH INIT 
CREATE TABLE rDb..t(c1 INT IDENTITY, c2 CHAR(100)) 

INSERT INTO rDb..t 
SELECT TOP(15000) 'hello' 
FROM syscolumns AS a 
  CROSS JOIN syscolumns AS b 

--Log is now about 46% full 
DBCC SQLPERF(logspace) 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 
--See that file 4 isn't used at all (Status = 0 for all 4's rows) 

--We can remove file 4, it isn't used 
ALTER DATABASE rDb REMOVE FILE rDb_log4 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 

--Can't remove 3 since it is in use 
ALTER DATABASE rDb REMOVE FILE rDb_log3 

--What if we backup log? 
BACKUP LOG rDb TO DISK = 'C:\rDb.bak' 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 
--3 is still in use (status = 2) 

--Can't remove 3 since it is in use 
ALTER DATABASE rDb REMOVE FILE rDb_log3 

--Shrink 3 
USE rDb 
DBCC SHRINKFILE(rDb_log3) 
USE master 

--... and backup log? 
BACKUP LOG rDb TO DISK = 'C:\rDb.bak' 

--Check virtual log file layout 
DBCC LOGINFO(rDb) 
--3 is no longer in use 

--Can now remove 3 since it is not in use 
ALTER DATABASE rDb REMOVE FILE rDb_log3 

--Check explorer, we're down to 1 log file 

--See what sys.database_files say? 
SELECT * FROM rDb.sys.database_files 
--Seems physical file is gone, but SQL Server consider the file offline 

--Backup log does it: 
BACKUP LOG rDb TO DISK = 'C:\rDb.bak' 
SELECT * FROM rDb.sys.database_files 

--Can never remove the first ("primary") log file 
ALTER DATABASE rDb REMOVE FILE rDb_log2 
--Note error message from above