June 3, 2013 at 4:23 am
Hi ,
Today we discovered a database that has 2 log files (reason unknown; they're on the same disk). This database is in SIMPLE mode. I'm wondering which view will identify which of the two are active. I've looked at ones that seemed the most likely from the autocomplete but haven't been able to find it (sys.fn_dblog provided nothing while sys.dm_audit_actions knows that the action was logged but doesn't say where to).
sys.dm_io_virtual_file_stats says the number of reads/writes (duration unknown, assume since startup) but it doesn't have a date field.
The idea would be to delete the currently inactive log file before the database switches to it again.
Mike
June 3, 2013 at 4:59 am
You are not able to deleted the primary log file. Empty the second logfile with DBCC SHRINKFILE ('name' EMPTYFILE) and follow this statement with a delete file statement (alter database [...] remove file ...)
June 3, 2013 at 5:11 am
So you're saying file_id 2 (the first log file) always has to exist? e.g. if the second logfile was being written to now (file_id 7) then I couldn't remove the initial log file instead?
Dird
June 3, 2013 at 5:17 am
Dird (6/3/2013)
So you're saying file_id 2 (the first log file) always has to exist?
Correct, the initial file can not be removed.
But by executing the DBCC command with the EMPTYFILE option, you order the SQL engine to not use the other file anymore. When the file is in use (due to current transactions) you'll have to execute the statement multiple times until the file is released (transactions are finished). The DBCC command will inform you of pages that are currently locked.
June 3, 2013 at 5:37 am
run this DBCC LOGINFO(database_name) command , If status is coming 0 for any fileid then that fileid log file is not in use..
check log file name using this query
SELECT * FROM database_name.sys.database_files .
If all the file in use then
step 1.
use master
ALTER DATABASE database_name SET RECOVERY FULL
BACKUP DATABASE database_name TO DISK = 'C:\database_name.bak' WITH INIT
step 2.
USE database_Name
DBCC SHRINKFILE(second_logfile_name)
step 3.
USE master
BACKUP LOG database_name TO DISK = 'C:\database_name.bak'
step 4.
check status again using DBCC LOGINFO(database_name)
If and fileid status is coming zero then
step 5.
ALTER DATABASE database_name REMOVE FILE second_file_name
June 3, 2013 at 6:19 am
HanShi (6/3/2013)
Empty the second logfile with DBCC SHRINKFILE ('name' EMPTYFILE) and follow this statement with a delete file statement (alter database [...] remove file ...)
DBCC ShrinkFile with the emptyFile option is for emptying data files (as explained in BoL). It will not move log records from one log file to another, nothing does.
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
June 3, 2013 at 6:25 am
Unfortunately some bad info here.
No need to shrink files. ShrinkFile with the EmptyFile option is for when you're removing a data file from a filegroup that has multiple data files. It's not for log files.
There's no need to set the recovery model to full (though taking a full backup is always a good idea).
There's no need to take log backups.
Use DBCC LOGINFO to see which file has portions of the active log (status 2). If only the primary log file has active portions of the log, then you can just drop the second file. If portions of the active log are in the second file, then you need to wait until that's no longer the case. If the database is in use (data modifications) that will happen in time with no need for log backups (checkpoint truncates the log in simple recovery model).
If the database is idle, then you need to generate 'fake' transactions to move the active portion of the log. SELECT INTO and DROP TABLE will do the job.
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
June 3, 2013 at 6:33 am
GilaMonster (6/3/2013)
HanShi (6/3/2013)
Empty the second logfile with DBCC SHRINKFILE ('name' EMPTYFILE) and follow this statement with a delete file statement (alter database [...] remove file ...)DBCC ShrinkFile with the emptyFile option is for emptying data files (as explained in BoL). It will not move log records from one log file to another, nothing does.
I stand corrected. Thanks Gail...
June 3, 2013 at 7:20 am
After transaction log backup, If we rerun the DBCC LOGINFO command then we will see how the Status=2 has changed in the file. The last entry is still marked as in use, but the previous entries have been reset to 0.
June 3, 2013 at 7:43 am
writearjun53 (6/3/2013)
After transaction log backup, If we rerun the DBCC LOGINFO command then we will see how the Status=2 has changed in the file. The last entry is still marked as in use, but the previous entries have been reset to 0.
Yes, but you don't need to switch to full recovery for that. In simple recovery model the automatic checkpoints will truncate the log and set all VLFs that are no longer needed to status 0.
Switching to full recovery, taking a full backup and running log backups is a waste of time when an automatic checkpoint in Simple recovery has exactly the same effect.
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
June 3, 2013 at 7:52 am
GilaMonster (6/3/2013)
writearjun53 (6/3/2013)
After transaction log backup, If we rerun the DBCC LOGINFO command then we will see how the Status=2 has changed in the file. The last entry is still marked as in use, but the previous entries have been reset to 0.Yes, but you don't need to switch to full recovery for that. In simple recovery model the automatic checkpoints will truncate the log and set all VLFs that are no longer needed to status 0.
Switching to full recovery, taking a full backup and running log backups is a waste of time when an automatic checkpoint in Simple recovery has exactly the same effect.
If We don't take a full backup and try to take backup of transaction log file then sql server give a error "Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally."
that's why i have to take a backup of database.
June 3, 2013 at 7:55 am
If We don't take a full backup and try to take backup of transaction log file then sql server give a error "Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally."
that's why we have to take a backup of database.
June 3, 2013 at 8:01 am
writearjun53 (6/3/2013)
If We don't take a full backup and try to take backup of transaction log file then sql server give a error "Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally."
that's why we have to take a backup of database.
If you read, I said there's no need to switch to full recovery, take a full backup or take a log backup as the automatic checkpoint truncates the log in simple recovery. Hence you won't get those errors because you don't need to take a log backup.
Switching to full recovery, taking a full backup and then taking log backups are all a huge waste of time. In simple recovery model a checkpoint (which runs automatically) marks the log reusable.
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
June 3, 2013 at 8:07 am
Sorry Gila,Now I got it what you want to say.
Thanks for reply.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply