SQL Server Missing Log File

We recently had one of our SQL Server instances experience a very large volume of change. Not suprisingly, the transaction log grew really big and was very close to filling up the disk. I had our SysAdmins add more space to the transaction log disk. The process that generated the large volume of transactions is a one-off process and I wanted to shrink the transaction log and let the SysAdmin take his space back to the storage pool. So I did as any SQL Server DBA might do. I took a backup of the transaction log. After that, I attempted the shrink operation.

Note: I normally don’t shrink the transaction logs. I only do this when I now a non-regular process grew it too big and I want to get back to something more normal.

 

When I attempted the shrink operation on the transaction log file, I received this error:

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'xxx_Log' for database 'master' in sys.database_files. 
The file either does not exist, or was dropped.

Hmm…the only thing I did was to backup the transaction log. So this is odd. I check the file system and sure enough, the file was there. So why doesn’t SQL Server see it? I discovered my answer when I queried sys.master_files. This view had no knowledge of any transaction log for that database. The database is in FULL recovery mode so my first check was to make sure that transactions could occur in the database. They could so my end users don’t notice the issue. The application appears to be fine. So how do I get the logfile back?

But in cases, where a couple is going through sexual dysfunction, then you can consult the best sexologist doctor in delhi buy levitra online https://pdxcommercial.com/wp-content/uploads/2019/03/15223-S-Henrici-Rd.-Flyer.pdf is a decision which lets you tackle the situation. Sometimes due to some infection of the joint, pain occurs which cialis 10 mg is called septic arthritis. Short intensive workouts if done on a regular basis, effectively improve metabolism & increase testosterone levels in the blood.Now that you are familiar with these natural ways to gain higher testosterone levels, let us remind you of powerful natural herbal product, natural cure for ED, that will no doubt cialis soft 20mg be your best option for boosting your “male” hormone, staying fit & strong – Power Khan!Power. This substance is responsible for allowing the penis to fill with price of viagra pills blood for a longer period of time. The first thing I did was take an backup of the instance. That way, if my method to fix screws it all up, I can at least go back to this known state. I then detached the problematic database. I then use the sp_attach_single_file_db procedure to re-attach the database as follows:

USE [master]
GO
EXEC sp_attach_single_file_db @dbname='xxx',
@physname=N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xxx.mdf'
GO
File activation failure. The physical file name "V:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Tfs_NAUCollection_Log.ldf" may be incorrect.
New log file 'V:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xxx_log.ldf' was created.

 

SQL Server knew that the transaction log was missing so it created it for me. I was back in business! The last thing was to take another backup that contained my working, well-functioning database.