October 20, 2011 at 6:03 am
I'm having problems with modify the settings on a secondary transaction log file in SSMS, so I thought I'd try with T-SQL. Turns out no matter what method I use, SQL doesn't think the secondary log file exists, but it lists it in a "Select * from sysfiles" command and in the GUI. I'm really confused here.
We're in the middle of rolling out SP2 (it hit the Dev environment, but not Production yet). Here's what I'm running:
Production: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Dev: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
I right click the database (GUI), go to Shrink Files. Pick Log2 and get the following error: "Input string was not in a correct format. (mscorlib)." It lets me click OK and get back to the Shrink Files screen, make my choices, and click OK, but nothing happens no matter which of the three options I choose.
Tried going into the database properties and disable Autogrowth. Got the following error when I clicked the final OK (to close the window): "Alter failed for Database <DB Name>. An exception occurred with executing a Transact-SQL statement or batch (Microsoft.Sqlserver.ConnectionInfo). MODIFY FILE failed. File 'DBName_Log2' does not exist. (Microsoft SQL Server, Error: 5041)".
Say what?
So I tried (in Dev) the same thing and got the same errors. Then I tried using the following T-SQL code in Dev:
ALTER DATABASE DBName
MODIFY FILE (NAME = DBName_Log2, FILEGROWTH = 0 );
Tried this with both the property in the "name" of the sysfiles response and then switched it to the physical name at the end of the path. Both attempts came up with the "File does not exist" error.
The file is there. It's just not allowing me to touch it. I've backed it up, truncated it. I need to remove it, but I can't even modify it, let alone remove it.
Any thoughts?
EDIT: Found this thread in which many smart people posted responses (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144747). Selecting from sys.database_files and sys.master_files shows all log files with the same id numbers and the same names as selecting from sysfiles. So, SQL agrees with me that the log file is there, until I try to do something with it.
October 20, 2011 at 6:25 am
Have you tried removing and re-creating the file?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2011 at 6:34 am
I don't want to recreate the file, actually. I just want to remove it.
Changing my T-SQL statement to REMOVE FILE I get:
Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.
That's my problem. It's there. I just can't touch it at all.
October 20, 2011 at 6:43 am
October 20, 2011 at 6:49 am
calvo (10/20/2011)
Just throwing this out there, would running checkdb provide any clues?
Just finished running it, MOF. It didn't find any problems with the database. Also ran it against MASTER (because of a comment on that thread I found earlier), no problems with MASTER either.
Found another link to a bug that said use sp_helpfile. I run it against DBName_Log2 and get:
Msg 15325, Level 16, State 1, Procedure sp_helpfile, Line 28
The current database does not contain a file named 'DBName_Log2'.
But when I just run sp_helpfile, without telling it what file to look for, it lists all three files (the data file and both log files).
Select file_id('DBName_Log2') AS FileID, file_name(4) as FileNm
The above code gives me the following results:
FileIDFileNm
NULLDBName_Log2
October 20, 2011 at 6:56 am
Can you take the database offline at all?
If so, take it offline, move the log files, and run Create Database For Attach_Rebuild_Log, and see what that does.
Edit: Don't have to mention this for you, but anyone finding this thread via a search or whatever: Please remember to do a backup and a test restore before doing what I just outlined.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2011 at 7:31 am
If I am not mistaken this is a bug in SQL Server 2008. It has not been fixed in SQL 2008 R2 either. I think there is also a pss case regarding this. I read about this long time back so I am not sure where the details are. There was a hack for it. But not sure. Sorry
-Roy
October 20, 2011 at 7:44 am
I would NOT take a troublesome db offline !!!! ( unless pss tells me to )
I must have overread it, but anyway here's my primitive Q:
- Does your service account have the needed auth to get to the pointed secondary file?
- Did someone change auth ( folder ownership, ...)
- can you post the servers errorlog file ( last succesfull startup of this db and the current one )
BTW secondary log files are only advised for DRP (CPR) situations to enable one to create a new log-backup and free the log-file. Then an emptyfile for the secondary log table and then take it out of the db.
LDF doesn't work as with regular data files.
ref: Paul Randal db myths session sqlpass 2011
ps: double check the folder holding your secondary ldf isn't compressed ! ( had issues with that when my sanadmin/winadmin just switched it on to save space )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 20, 2011 at 7:59 am
whats the state_desc value in sys.master_files for this offending log file?
if it is 'offline' and you have issued an emptyfile and remove file a transaction log backup could remove this entry.
If you have a full backup I would be interested to see which files are returned by restore filelistonly.
---------------------------------------------------------------------
October 20, 2011 at 8:13 am
george sibbald (10/20/2011)
whats the state_desc value in sys.master_files for this offending log file?
ONLINE
george sibbald (10/20/2011)
If you have a full backup I would be interested to see which files are returned by restore filelistonly.
I'll check into that.
GSquared (10/20/2011)
Can you take the database offline at all?
Only in Dev. This is one of our primary production dbs and taking it offline for any length of time would be bad. Even though our customers aren't 24x7, our jobs are.
Roy Ernest (10/20/2011)
If I am not mistaken this is a bug in SQL Server 2008. It has not been fixed in SQL 2008 R2 either. I think there is also a pss case regarding this. I read about this long time back so I am not sure where the details are. There was a hack for it. But not sure. Sorry
Roy, is this what you're talking about? I found this thread on MS Connect: http://connect.microsoft.com/SQLServer/feedback/details/482820/orphaned-log-file-can-not-delete-log-file-sysfiles1-duplicate-names. I'm not sure this hack would work since, so far as I know, we don't have duplicate file names. On the other hand, I can't find the sysfiles1 table to double check the information listed in that Connect item.
October 20, 2011 at 8:17 am
Brandie, I think there is another one cause I remember distinctly that you have to do a EMPTYFILE before you try to delete the secondary file once again after creating a dummy file.
-Roy
October 20, 2011 at 8:32 am
Roy Ernest (10/20/2011)
Brandie, I think there is another one cause I remember distinctly that you have to do a EMPTYFILE before you try to delete the secondary file once again after creating a dummy file.
I have yet to come across that one. Please let me know if you find it before I do.
October 20, 2011 at 8:38 am
I found another thread that deals with the same issue. Check the last page here. You will see the work around.
-Roy
October 20, 2011 at 8:44 am
So I tried the "hack" in the MS Connect item (see my earlier post for the link) and it doesn't work for me. As soon as I try to add the secondary log file in the new filegroup, I get an error:
Msg 1834, Level 16, State 1, Line 2
The file 'G:\MSSQL9Data\DU03\MSSQL.3\MSSQL\DATA\DBNameLog2.LDF' cannot be overwritten. It is being used by database 'DBName'.
So much for that work around.
October 20, 2011 at 8:48 am
If you backup the transaction log and change the Recovery Mode to Simple you should be able to delete the second file. Then you can change it back to Full.
Or have you tried that already? That's just how I was able to get rid of my secondary transaction log recently. It helped that we were switching to Simple mode anyway.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply