SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Broken Logfile fileid in sys.database_files
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 05/18/2010 :  16:40:14  Show Profile  Visit paulrandal's Homepage  Reply with Quote
Ah - well spotted Jack. I guess the masses of pointless and unwarranted vitriol and attitude made me miss his mistake.

:-)

Paul S. Randal,
CEO, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server Magazines
Author of SQL 2005 DBCC CHECKDB/repair code
Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 05/18/2010 :  18:08:08  Show Profile  Reply with Quote
I will attempt to deal with all of these at once. Were I able to post a jpeg of the error popup I would.

There are multiple log files for historical reasons, created long before I arrived on this particular scene. The primary logfile is just a few hundred blocks. The second logfile is the "actual" main one. The 3rd file was recently added to solve a logfile full error condition. (Please do not create static by instructing me on log file backups, the issue involved inability to flush the files and a subsequent inability to checkpoint, you will be aware of the possible causes of this scenario)

In the OP. The original DBCC error was generated directly from SSMS from the Files dialogue. It is therefore not technically possible to have referenced the incorrect database. My direct examples were simply to demonstrate that the error is present no matter whether the commands are executed from SSMS, or if I simply generate them from SSMS and run them directly in a pane. I was also trying to subtly (a) kill the big-dick DBAs use real commands static; (b) rebut the notion that SSMS uses alter database for the shrinkfile operation, and finally (c) to indicate that both DBCC and ALTER DATABASE have the same problem. In my haste to reach a dinner appointment, I did indeed post the incorrect message returned from DBCC (you correctly not the example has the incorrect context), it should have read "Msg 8985, Level 16, State 2, Line 1
Could not locate file '9' for database 'MyDB' in sys.database_files. The file either does not exist, or was dropped."

This is the message that leads me to the missing File_id 9, see the OP, where I indicate that it indeed does not exist - but from where is DBCC getting '9' as the File_id - it is obviously not entered in the command, and it certainly does not exist in sys.database_files, where the filename used is associated with File_id 36?

As previously noted. I have tried from a different instance of SSMS on a different physical machine with no difference observed. I guess there is a possibility that the SSMS instance running on the server itself is the same one used to create the file and disconnecting it might have an effect, though one wonders why a new instance running on another machine would exhibit the same symptoms.

To put this point to bed, the ALTER DATABASE command previously posted, generated from SSMS, provokes the error exactly as shown. The example I gave was simulated, however I bit the bullet (confident that it would indeed fail) and ran the command directly against the live database from a new pane in a new SSMS instance on a different physical machine.


My confident guess (from the OP and expanded) is that the SQL Server instance has cached data internally for the rows in sys.database_files and others, and continues to return an incorrect translation of the filname submitted to ALTER DATABASE and DBCC because the SQL Server engine incorrectly believes that the data is unchanged, and therefore has no need to read the actual data which in fact has changed. This is a pretty typical isolation level, buffer management or code optimisation style of bug. Restarting the SQLServer service will by definition create new internal memory structures for the contents of sys.database_files etc. for the instance. My OP prediction still stands, though my original supposition that SSMS might be at fault based on the fact that it constantly loses the plot in this manner seems now invalid - my subsequent use of a different SSMS instance would seem to exonorate SSMS.


Finally, my view of SQL Server is not specifically coloured by the quality of SSMS specifically. SSMS is a vast improvement on its predecessor. SSMS does however have some outstanding issues, several of them caused by lack of functionality exposed in the T-SQL implementation, which SSMS attempts to evade - not always with complete success. I have many technical issues with how T-SQL is implemented, the primitive optimizer and many other database engine level items. These reservations are not relevant here.

I will be scheduling a restart of the SQL Service as soon as is convenient.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 05/18/2010 :  18:52:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here's the solution to my PSS case (condensed):

-- add a dummy file
USE master
go
ALTER DATABASE <database name> ADD LOG FILE ( NAME = N'remove_log', FILENAME =
N'c:\remove_log.ldf', SIZE = 1MB, MAXSIZE = 1MB)
ALTER DATABASE <database name> REMOVE FILE [remove_log]
go

Run DBCC SHRINKFILE with EMPTYFILE option.
Run ALTER DATABASE to remove the file.

I don't have time to read over these lengthy posts and am not interested in the complaints. If you want the entire thread (modified to remove email addresses from Microsoft), please send me an email through SQLTeam.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 05/18/2010 18:53:25
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 05/18/2010 :  23:41:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by PhilipLewis

I will be scheduling a restart of the SQL Service as soon as is convenient.



What is the point of this? It will not correct your issue. This is a bug and has a workaround as outlined in my PSS case.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 05/19/2010 :  01:31:54  Show Profile  Reply with Quote
My last post was written before I saw your post on Page 2.
Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 06/28/2010 :  11:33:57  Show Profile  Reply with Quote
This is posted for the benefit of any individual who might have experienced this problem, and who might be interested in posting an intelligible solution


First we demonstrate the issue. The log file [Customer_Log2] does physically exist, and the database uses it. The internal references for T-SQL and utility manipulation are broken.


Use [Master]
GO
ALTER DATABASE Customer REMOVE FILE [Customer_Log2]
GO

Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.


USE [Customer]
GO
DBCC SHRINKFILE (N'Customer_Log2' , EMPTYFILE)
GO

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


Following the previous poster’s solution …

Use [Master]
go
ALTER DATABASE CUSTOMER ADD LOG FILE ( NAME = N'Customer_Log3', FILENAME =
N'M:\ Customer_Log3.ldf', SIZE = 1MB, MAXSIZE = 1MB)
ALTER DATABASE Customer REMOVE FILE [Customer_Log3]
go

The commands succeed, however we now have a Customer_Log3 log file which exhibits the same broken characteristics as Customer_Log2.


USE [Customer]
GO
DBCC SHRINKFILE (N'Customer_Log2' , EMPTYFILE)
GO

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


DBCC SHRINKFILE (N'Customer_Log3' , EMPTYFILE)
GO

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


Now, despite not being able to perform the DBCC tasks specified by the poster, we attempt to remove the broken files.

Use [Master]
GO
ALTER DATABASE Customer REMOVE FILE [Customer_Log3]
GO

Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.

Use [Master]
GO
ALTER DATABASE Customer REMOVE FILE [Customer_Log2]
GO

Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.


For laughs, we attempt a SHRINKFILE which will actually complete

USE [Customer]
GO
DBCC SHRINKFILE (N'Customer_Log' , 0, TRUNCATEONLY)
GO

Cannot shrink log file 2 (Customer_Log) because the logical log file located at the end of the file is in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Followed by an attempt to delete the offending log files.
ALTER DATABASE Customer REMOVE FILE [Customer_Log3]
Go

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.

ALTER DATABASE Customer REMOVE FILE [Customer_Log2]
go

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.

Subscribe to my blog
[/quote]
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 06/28/2010 :  13:01:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
I think you need my entire thread from my MS PSS case. Send me your email address, and I'll forward the info to you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

LeeGreene
Starting Member

South Africa
6 Posts

Posted - 07/02/2010 :  04:12:36  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

I think you need my entire thread from my MS PSS case. Send me your email address, and I'll forward the info to you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



I am having the exact same problem. Can you email me your solution? Thanks.
Go to Top of Page

LeeGreene
Starting Member

South Africa
6 Posts

Posted - 07/02/2010 :  09:22:33  Show Profile  Reply with Quote
I have had the exact same problem. The following is interesting:

SQL Server 2008 10.0.2723

sys.master_files - log fileid does NOT exist
sys.database_files - log fileid does NOT exist
sys.sysfiles - log fileid does NOT exist

sys.sysaltfiles - log fileid DOES exist AND another log fileid with the SAME logical name EXISTS

It looks like there is a bug in SQL 2008 that doesn't clean up sysaltfiles.

It seems the Work-around that has worked is to rename the log2 logical name and then to rename it a 2nd time back to its original name.

Thoughts are appreciated. Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 07/02/2010 :  12:35:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by LeeGreene

quote:
Originally posted by tkizer

I think you need my entire thread from my MS PSS case. Send me your email address, and I'll forward the info to you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



I am having the exact same problem. Can you email me your solution? Thanks.



I sure can, just PM your email address to me. I still haven't had a chance to get the data to Philip though. I'm working crazy hours right now and barely have enough time to sleep.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 07/03/2010 :  13:53:15  Show Profile  Reply with Quote
Unfortunately, my rogue logfile has become the logfile of choice for this SQLServer database, and it has now grown to almost 500GB (due to serious issues with Replication Cleanup - another disaster on wheels).

This logfile is also on the busiest disk array, and is operating with a latency of 2.5 orders of magnitude slower than the normal logfile disk - which pretty obviously slows everything down quite a bit.

I am anticipating catastrophic database breakdown shortly.

As usual, multiple "things" have broken, with fairly dire results.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 07/03/2010 :  18:01:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
So why haven't you opened a case with Microsoft?

In order to get the case notes to you, I've got to spend a bit of time stripping out some details. So I've got to kind some time to do it. If things are so bad for you right now, then I'd highly recommend opening a case with Microsoft for immediate help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

LeeGreene
Starting Member

South Africa
6 Posts

Posted - 07/05/2010 :  10:43:51  Show Profile  Reply with Quote
quote:
Originally posted by PhilipLewis

Unfortunately, my rogue logfile has become the logfile of choice for this SQLServer database, and it has now grown to almost 500GB (due to serious issues with Replication Cleanup - another disaster on wheels).

This logfile is also on the busiest disk array, and is operating with a latency of 2.5 orders of magnitude slower than the normal logfile disk - which pretty obviously slows everything down quite a bit.

I am anticipating catastrophic database breakdown shortly.

As usual, multiple "things" have broken, with fairly dire results.



This might help you:

Take a look at sys.sysaltfiles.
Do you see the logical file name listed twice?

If so, do they have different groupIDs?

If so, this is where Tara's PSS workaround comes into play:

Create a dummy log file with a unique name.

Remove this dummy log file
(at this point look into sysaltfiles and you should see that the fileID was re-used and you should now see the logical names for dummy file and your file)

Next, you must back up the log file as opposed to DBCC Shrinkfile EmptyFile. EmptyFile does not apply to log files.

Now, Remove your log file using Alter Database...

Let us know if it works for you as well.

Best of Luck!

Lee
Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 07/06/2010 :  08:37:28  Show Profile  Reply with Quote
Firstly, I apologise for the formatting.
Results appear as commented lines.

Customer_Log3 is a broken logfile we are trying to remove.


use master
go
/* This is the new "dummy" log file */
ALTER DATABASE [Customer] ADD LOG FILE ( NAME = N'Customer_Log4', FILENAME = N'M:\Customer_Log4.ldf' , SIZE = 2048KB , FILEGROWTH = 0)
go
select * from sys.sysaltfiles where filename like '%customer_Log%' order by 9
go
--fileid groupid size maxsize growth status perf dbid name filename
--39 0 128 128 10 1048642 0 5 Customer_Log3 M:\Customer_Log3.ldf
--37 1 3200000 -1 1280000 268435458 0 5 Customer_Log3 M:\Customer_Log3.ndf
--40 0 256 268435456 0 66 0 5 Customer_Log4 M:\Customer_Log4.ldf
USE [Customer]
GO
ALTER DATABASE [Customer] REMOVE FILE [Customer_Log4]
GO
--The file 'Customer_Log4' has been removed.
select * from sys.sysaltfiles where filename like '%customer_Log%' order by 9
go
--fileid groupid size maxsize growth status perf dbid name filename
--39 0 128 128 10 1048642 0 5 Customer_Log3 M:\Customer_Log3.ldf
--37 1 3200000 -1 1280000 268435458 0 5 Customer_Log3 M:\Customer_Log3.ndf
--40 0 1 268435456 0 268435522 0 5 Customer_Log4 M:\Customer_Log4.ldf

As you can see this procedure failed dismally - at least as an attempt to solve my problem. Customer_Log4 correctly appears as OFFLINE in the other systables, and the physical file does not exist either.

So it would appear that the net result is Customer_Log4 got created correctly with no Fileid reuse and was subsequently removed physically and logically from the database, but the reference in sys.sysaltfiles still remains (Fileid = 40) - which would also appear to be incorrect at this point.

I am curious to know why this might conceivably have worked, as I see no evidence that Fileids are anything other than sequentially assigned.

Lee, perhaps the description was less precise than it needed to be and/or I have misinterpreted what you wrote. Either way, I do not appear to be any better off than previously


Philip
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 07/06/2010 :  15:01:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
I emailed both Philip and Lee with my PSS case. Hopefully it helps them, otherwise a new PSS case should be opened by them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

LeeGreene
Starting Member

South Africa
6 Posts

Posted - 07/09/2010 :  03:55:00  Show Profile  Reply with Quote
quote:
Originally posted by PhilipLewis

Firstly, I apologise for the formatting.
Results appear as commented lines.

Customer_Log3 is a broken logfile we are trying to remove.


use master
go
/* This is the new "dummy" log file */
ALTER DATABASE [Customer] ADD LOG FILE ( NAME = N'Customer_Log4', FILENAME = N'M:\Customer_Log4.ldf' , SIZE = 2048KB , FILEGROWTH = 0)
go
select * from sys.sysaltfiles where filename like '%customer_Log%' order by 9
go
--fileid groupid size maxsize growth status perf dbid name filename
--39 0 128 128 10 1048642 0 5 Customer_Log3 M:\Customer_Log3.ldf
--37 1 3200000 -1 1280000 268435458 0 5 Customer_Log3 M:\Customer_Log3.ndf
--40 0 256 268435456 0 66 0 5 Customer_Log4 M:\Customer_Log4.ldf
USE [Customer]
GO
ALTER DATABASE [Customer] REMOVE FILE [Customer_Log4]
GO
--The file 'Customer_Log4' has been removed.
select * from sys.sysaltfiles where filename like '%customer_Log%' order by 9
go
--fileid groupid size maxsize growth status perf dbid name filename
--39 0 128 128 10 1048642 0 5 Customer_Log3 M:\Customer_Log3.ldf
--37 1 3200000 -1 1280000 268435458 0 5 Customer_Log3 M:\Customer_Log3.ndf
--40 0 1 268435456 0 268435522 0 5 Customer_Log4 M:\Customer_Log4.ldf

As you can see this procedure failed dismally - at least as an attempt to solve my problem. Customer_Log4 correctly appears as OFFLINE in the other systables, and the physical file does not exist either.

So it would appear that the net result is Customer_Log4 got created correctly with no Fileid reuse and was subsequently removed physically and logically from the database, but the reference in sys.sysaltfiles still remains (Fileid = 40) - which would also appear to be incorrect at this point.

I am curious to know why this might conceivably have worked, as I see no evidence that Fileids are anything other than sequentially assigned.

Lee, perhaps the description was less precise than it needed to be and/or I have misinterpreted what you wrote. Either way, I do not appear to be any better off than previously


Philip




Hi Philip,

The goal is to get SQL to reuse the "phantom" fileID. In your case, the "phantom" file is actually a data file in the Primary filegroup as noted by the "1" in the GroupID col.

I would recommend considering the following - if it's possible in your environment:

1. Add a data file to primary filegroup
2. Check sys.sysaltfiles and check to see if the FileID has been re-used.
3. Remove data file
4. Backup Log
5. Then, try removing your log file

Hope this one helps.

Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 07/09/2010 :  05:37:14  Show Profile  Reply with Quote
This is not going to work, and it's not going to work because it is a hack which relies on some unconfirmed and most likely incorrect notion of how SQLServer assigns the FileId.

Sure, if you only have a couple of files and you never deleted any previously, then you might "luck out", but on any database that has loads of files and which has been restructured multiple times, the probability of success rapidly approaches zero - and it was low to start with because of the aforesaid unverifiable FileId allocation assumption.

Is this kludge really the best one can expect from Microsoft SQLServer support (the apparent source of the hack)? I have instigated a call with MS, and I am expecting resolution sometime around 2018 - if ever.

http://connect.microsoft.com/SQLServer/feedback/details/482820/orphaned-log-file-can-not-delete-log-file-sysfiles1-duplicate-names

I direct your attention to the "Resolution".

p.
Go to Top of Page

LeeGreene
Starting Member

South Africa
6 Posts

Posted - 07/09/2010 :  09:14:41  Show Profile  Reply with Quote
quote:
Originally posted by PhilipLewis

This is not going to work, and it's not going to work because it is a hack which relies on some unconfirmed and most likely incorrect notion of how SQLServer assigns the FileId.

Sure, if you only have a couple of files and you never deleted any previously, then you might "luck out", but on any database that has loads of files and which has been restructured multiple times, the probability of success rapidly approaches zero - and it was low to start with because of the aforesaid unverifiable FileId allocation assumption.

Is this kludge really the best one can expect from Microsoft SQLServer support (the apparent source of the hack)? I have instigated a call with MS, and I am expecting resolution sometime around 2018 - if ever.

http://connect.microsoft.com/SQLServer/feedback/details/482820/orphaned-log-file-can-not-delete-log-file-sysfiles1-duplicate-names

I direct your attention to the "Resolution".

p.



The Workaround I outlined above worked for me. We had the exact same scenerio as you. It's a very large, very busy server with a 2+TB database with many data files.

Go to Top of Page

PhilipLewis
Starting Member

21 Posts

Posted - 07/09/2010 :  09:36:17  Show Profile  Reply with Quote
Well Lee, you "lucked out" and from your posts it would appear that you actually do not know "why" it worked, and from Tara's mail-trail I conclude that neither does she.


I just tried it, and to my utter astonishment, the command behaved exactly as I predicted. The FileId allocated was some unused value != to the value I was looking for. The allocated FileId was 4.

So I deleted the file and it eventually disappeared from all but sys.sysaltfiles, where it remains along with FileId=40 and various other broken file names.

select * from sys.sysaltfiles where filename like '%phant%'

fileid groupid size maxsize growth status perf dbid name filename
4 1 384 -1 0 268435458 0 5 Phantom M:\Phantom.ndf


This exercise does however provide a strong indication that the FileId allocation algorithm is in fact "first unused value > 0 in same database with same type" (4 meets this criteria). So if I add enough "phantoms" to fill every unused entry, and my inferred algorithm is in fact correct, it might also work for me - eventually. There seems altogether too many "unallocated" values in sys.master_files for me to want to test this hypothesis at this juncture.

Check back in about 2018, for a real solution from Microsoft - rotfl.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 07/09/2010 :  11:59:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
I give up PL. Your attitude is astonishing. I can't believe I even offered to send you my case notes.

If you opened a case with PSS, you would get a resolution. Using Connect is not the same thing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000