SQL Server 2005/2008 : Add data or log file to log shipping primary database

Today, I had to add a new data file to the primary databases which was configured for Log shipping.
As soon as I edited the database properties and added a new data file, the log shipping started to fail with the following error.

*** Error: Directory lookup for the file "P:\Datafiles\Sample_archive3.mdf" failed with the operating system error 3(The system cannot find the path specified.).

File 'Sample_archive3' cannot be restored to 'P:\DataFiles\Sample_archive3.mdf'. Use WITH MOVE to identify a valid location for the file.

The problem was that the new data file that was added on primary needs to be added to the secondary database too. But this cannot be done with the regular RESTORE command that is used by the Log shipping restore job. So as suggested in the ERROR I had to use the WITH MOVE option in the RESTORE command. I checked what was the next log backup file that had to be restored and I manually executed the following RESTORE command to fix the problem.

RESTORE LOG [SAMPLE]

FROM  DISK = N'E:\TranlogBkups\SAMPLE_20101101162500.trn'

with move 'SAMPLE_archive3' to 'E:\MSSQL\Data\SAMPLE_DATA22.mdf',

  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

While making this change I also discovered the RESTORE FILELISTONLY command which lists the logical file name of all the data and log files that are part of the database backup. So in case you had lost the database that was backed up and now wanted to restore it to a different location then you can use the command to find out all the data and log files.
You could then proceed to restore the database and move all the old logical files to the new physical paths.

You could run the command as follows

RESTORE FILELISTONLY FROM DISK = 'E:\TranlogBkups\SAMPLE_20101101162500.trn' WITH FILE = 1

GO

Shrinking the log file of secondary database in Log Shipping setup

SQL Server 2005/2008: Shrinking the log file of secondary database in Log Shipping setup


Platform : SQL Server 2005 and 2008

I had set up log shipping between two servers and most days the size of the log file of the primary database remains within acceptable limits. But today there was some bulk load and that resulted in a huge log file. Consequently the log file size of the secondary database (Database to which the logs were being shipped and restored) also got big. So big that the disk drive where the log file was located was almost full.

So I had to figure out a way to shrink the log file of the secondary database but unfortunately it was in READ ONLY mode so there was no way I can shrink that database. Until today I had not reached this situation where I have to shrink the log file of secondary database. Just to try I ran the DBCC SHRINKFILE  command against the primary database and that did the trick. This shrank the log file of the secondary server too. But before this happened, I had to run the LSBackp, LSCopy and the LSRestore job to make sure that the transaction was copied over to the secondary database.

Conclusion: In a Log Shipping set up in SQL Server 2005/2008, DBCC SHRINKFILE on primary database does get propagated to the secondary database.

SQL Server 2005/2008 : Error: 9002, Severity: 17, State: 2

Error: 9002, Severity: 17, State: 2

The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The transaction log file will get full in one of the following two situations.

1) If the log file has been configured with preset max size limit then the file is full.
2) If the log file has been configured with unlimited size then perhaps the disk is full.

If it is the second situation then first free up some space in the disk by moving some files or deleting some files.

Now lets look why the file got full. First thing that you need to check is the log_reuse_wait_desc column in the sys.databases.

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

There are several reasons that could come up in this column and some of them are noted here.
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

If the database in question is TEMPDB then the process to resolve it would be different and also the reasons for which TEMPDB gets full are different. But let me discuss the most common reason why a user DBs log file gets full. 

LOG_BACKUP
In most cases you will see the reason noted in 'log_reuse_wait_desc' is given as 'LOG_BACKUP'. This means that the database is in FULL recovery model and is waiting for a log backup to be taken.

If you have scheduled a regular log backup job then check its status and wait for it to finish before you shrink the log file. If you check the free space in the log file then you will indeed see a lot of unused space but you can not shrink it. Once the log backup completes you can shrink the file.

But if the data file is not as big as the log file then instead of doing a log backup, I will do the following.

1) Change the recovery model to SIMPLE
2) Shrink the log file.
3) Change the recovery model to FULL
4) Take a full backup and subsequently schedule log backups.

Sometimes the above steps take a lot less time to complete than taking a log backup and then shrinking the file. But please keep in mind that when you do this you have essentially broken the log chain and will have to resync the database if it is configured for log shipping. 

The question of whether to truncate the log or not is dependent on the DB size. If it is not too big then truncate it and take a full backup. Otherwise it is best to take log backups.

ACTIVE_TRANSACTION
Other prominent reason that I have seen is 'ACTIVE_TRANSACTION'. In this case, it would be best if you first add a new log file to the database or extend it. Then run the DBCC OPENTRAN on that database and check the open transactions. This should give you more information about the transaction that is consuming most of the log space and has not yet completed. 

If the reason given is ACTIVE_BACKUP_OR_RESTORE then refer to my earlier post to find what is the expected time to finish the current backup or restore.

http://saveadba.blogspot.com/2011/10/backup-and-restore-progress.html
If the reason is related to either replication or mirroring then first check the status of replication or mirroring to ensure that they are upto speed and don't have any latency. This should help in reducing the log reuse wait time.

Log shipping 'Could not find a log backup file that could be applied to secondary database'

Today the log shipping that had been set up for a database started to fail. Found the following errors in the job history for LSRestore


***Error: The file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBname_201112081234.trn is too recent to apply to the secondary database 'DBname'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: The log in this backup set begins at LSN 842977000024386700001, which is too recent to apply to the database. An earlier log backup that includes LSN 841347000010148300001 can be restored.
Searching for an older log backup file. Secondary Database: 'DBname'
*** Error: Could not find a log backup file that could be applied to secondary database 'Dbname'.(Microsoft.SqlServer.Management.LogShipping) *** 


This means that the current log backup file that is being restored is too recent. A backup from a prior time needs to be applied first. 

So lets check what was the log file copied. The following query will give you the last log backup file that was copied

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]


Then check what was the last log backup file that was restored.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Then check the transaction backup file that was immediately copied after the last restored file. Sometimes one file is so big that the subsequent backup files get copied quickly. (sort by name and not by time). The file that is still being copied will show up in the correct order when you sort by name. But if you sort by date modified then it might show up at the end of the list.

So on the next restore attempt it will try to restore those files that have been successfully copied. And it will skip the file that is still being copied. So wait for that big file to be copied. 

It might also be the case that one of the log backup files did not get copied because it was deleted on the primary server. Or it got deleted from the secondary server before it was restored. The second case is unlikely so check the retention time on backup job that runs on primary server.

If you see file on primary that was not yet copied then copy that over and log shipping to start working again.

SQL Server 2005/2008: Shrinking the log file of secondary database in Log Shipping setup

Hi,
      I have been working as Database Administrator for a few years now and everyday I come across questions for which I don't have any answers. So I rely on the Internet to provide me the answers. Google has to be the best colleague of mine, without whom I would not have survived in this profession for this long. So today I thought it was time that I give it back to the community and help my fellow Database Administrators with problems that we all face everyday. Starting today, I will start blogging one new technique, one new details, one new trick within SQL Server. Hopefully this will help others too.


Platform : SQL Server 2005 and 2008

I had set up log shipping between two servers and most days the size of the log file of the primary database remains within acceptable limits. But today there was some bulk load and that resulted in a huge log file. Consequently the log file size of the secondary database (Database to which the logs were being shipped and restored) also got big. So big that the disk drive where the log file was located was almost full.

So I had to figure out a way to shrink the log file of the secondary database but unfortunately it was in READ ONLY mode so there was no way I can shrink that database. Until today I had not reached this situation where I have to shrink the log file of secondary database. Just to try I ran the DBCC SHRINKFILE  command against the primary database and that did the trick. This shrank the log file of the secondary server too. But before this happened, I had to run the LSBackp, LSCopy and the LSRestore job to make sure that the transaction was copied over to the secondary database.

Conclusion: In a Log Shipping set up in SQL Server 2005/2008, DBCC SHRINKFILE on primary database does get propagated to the secondary database.