Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Broken Logfile fileid in sys.database_files

Author  Topic 

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 05:50:53
SQLServer2008 10.0.2531/WindowsServer 2008 R2 Enterprise

For reasons known only to deities and SQL Server engineering, it is apparently possible to break the integrity of the SSMS view of internal storage file pointers in sys.database_files using simple SSMS actions.

This does not surprise me in the least, given the appalling engineering quality of SQLServer and SSMS in general.

In SSMS I see a primary lgfile and 2 secondary log files. These are correctly named and the locations correct.

However, any attempt, using SSMS, to alter the most recently created file results in an error. An attempted shrink gives "Input string was not in a correct format (mscorlib)", this is spurious. Attempting to change the file block allocation via the properties screen returns an error indicating that the file (file_id 9) cannot be located. It is indeed missing from sys.database_files.

The error is thus that the file_id (9) that SSMS is looking for is no longer in sys.database_files. There is however another file_id (36) which points to this file, and I assume that it is actively used by SQLServer.

This is a 24*7 system. This is a TB+ sized database, and I have a requirement to remove this logfile. I do not have the option of taking the database down to experiment, nor can the business tolerate a backup/restore scenario.


Q: What is an absolutely safe mechanism for removing this 100GB logfile without downtime.


My gut feeling is that it is just SSMS that has lost the plot (which happens constantly in so many scenarios), and that restarting the SQLServer service will result in the necessary data structures that SSMS uses being reinitialised - because clearly they are now out of synch. However, I cannot perform the experiment to validate this theory!

Kristen
Test

22859 Posts

Posted - 2010-05-18 : 07:39:29
Have you run DBCC CHECKDB recently?

If not restore a backup to another machine and do that. There may be some goofiness in the database.

Is your version of SSMS patched to SP1 (as per the database version)?

SSMS being junk is your opinion, I read vast numbers of posts here and don't even remember the last time someone reported SSMS as being the source of their problems. But even if you have found a bug so what? You would be much better off using SQL Syntax direct which, for a TB+ sized database I would have thought was a far more optimal way of working than using a GUI interface
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-18 : 09:04:46
There is no way, from simple queries to damage SQL's internal structures. Management studio converts your GUI actions to T-SQL statements that it sends to SQL Server, just as if you had typed them in a query window. What is probably wrong is a simple scenario of out of date caches in SSMS (it remembers DB structure so that it doesn't have to keep querying SQL). I assume you're working from Object Explorer. Try refreshing it, or disconnecting from the server and reconnecting.

If you query sys.database_files, do things look correct? Can you use ALTER DATABASE to remove that log file? (test on a DEV machine first to make sure that you have the syntax correct)

--
Gail Shaw
SQL Server MVP
Go to Top of Page

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 09:49:39
Gail,

1: I did not use the word "query", I used the word "action", and actions within SSMS clearly include changes to phyical database structure and metadata within databases - this is after all its raison d'être. It is certainly possible to end in the described situation through SSMS instigated actions - proof by existence. That this "shouldn't" happen is incontrovertible, but hardly surprising.

2: My OP was rather specific as to the details, so yes, things look correct in sys.database_files for file_id 36 and the fact that file_id 9 does not exist.

3: As my specific awareness of SSMSs "out of date" behaviour scenarios was indicated in the OP, it will not surprise you to learn that a new instance of SSMS exposes the same error condition, which inexorably leads to the possibility of restarting the service as a mechanism for re-aligning SSMS and SQLServer realities (as per the OP).

4: Your suggestion to try a direct ALTER DATABASE would imply that (a) the truth statement from you, of my point 1: is in fact not true and/or (b) SSMS does not use ALTER DATABASE to perform log file resizing specified from the properties dialogue.

So, your suggestion is to execute an ALTER DATABASE statement to manipulate physical storage characteristics on a live database where the physical storage characteristics are incorrectly reported to Microsoft's own tool, to wit SSMS?

I will wait and see if anyone actually has an answer I think.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-18 : 10:16:39
1) No, you did not use the word query, I did. I also stated that anything you do within SSMS (an action) is sent to SQL Server as a T-SQL statement (a query). Hence, there is nothing you can do within SSMS that will damage the SQL Server database structures. I did not say it cannot cause the described situation. I said it cannot damage a SQL database's internal structures.

2) If sys.database files looks fine, then you've potentially run into a bug in SSMS. Not something wrong in the SQL Server database. It happens. Feel free to report it on Connect (http://connect.microsoft.com) so that it can be investigated, addressed and included in future patches. Make sure that SSMS is patched to the latest level first. I know people often don't apply service packs to the client machines, so often SQL Server is at SP1, but SSMS is still on RTM. Help->About will give you the version that SSMS is.

3) Restarting SQL is unlikely to do anything, if the system views are correct. To populate the dialog that you mention, SSMS does a query against sys.databases and sys.master_files. (I can dig up the actual query if you like), so if those are showing correct data then the SQL Server database is OK and a restart will not help.

It may be worth restoring a backup to a dev/test machine for an integrity check (DBCC CheckDB) to be 100% sure. Do you run checkDB on a regular basis?

4) Neither. SSMS does use Alter database (again, I can dig up the exact statement that it runs if you like), however most DBAs that I know (especially those working on large databases) prefer using T-SQL directly as it's quicker and you know exactly what it's doing.

Yes, I suggest that you use ALTER DATABASE, after testing out the commands on a DEV server to make sure they do exactly what you want, because SSMS may be incorrect, it may be doing something that you don't want, and because it's generally quicker to use T-SQL statements than to navigate through the GUI and because you can do exactly, precisely and only what you want.

As for your last statement, I get the impression you don't want an answer, you want validation. If I'm wrong, I apologise, but that's what the tone is implying for me. If you want help, I'm happy to try, but a slightly less antagonistic tone may help. Otherwise, I would suggest contacting Microsoft's support people (CSS) and opening a case with them. If you're a premier customer, you get a certain number of free cases a year, if it is a bug, they don't charge for the call.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-05-18 : 10:17:25
" the possibility of restarting the service as a mechanism for re-aligning SSMS and SQLServer realities "

Restart what service? Just close SSMS and reopen it. If that cures the problem then SSMS had a stale copy of the metadata about the database.

" So, your suggestion is to execute an ALTER DATABASE statement to manipulate physical storage characteristics on a live database"

What Gail said was to try it on a DEV copy. As I said similarly with regard to checking the database for corruption. Has DBCC CHECKDB run recently? (running it on a recently restored copy of the database on DEV is equivalent)

I would not make database changes such as you describe from the GUI but rather script them (using the GUI to prepare the script if you like), then run the script on DEV copy, if it works as expected then the exact same script can be run on PRODUCTION system. Running such things direct from the GUI opens up the possibility of performing the steps in a different manner.
Go to Top of Page

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 10:24:54
Kristin - you really need to learn to read
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-18 : 10:35:17
Out of curiosity, if you query sys.master_files, is the information correct?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 10:43:12
Apologies, I probably should have mentioned that before. In sys.master_files, File_id 36 is there. My assumption mentioned in th OP that the file was indeed in use was gleaned from the ONLINE attribute here. The row looks normal in all respects - as do the other logfiles.

Anywhere else I might find a reference to the mysterious file_id 9?

I make no claims to know the complete datastructure of sys... but SSMS is be getting this faulty information from somewhere.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-05-18 : 10:46:45
quote:
Originally posted by PhilipLewis

Kristin - you really need to learn to read



If you could read you would have spelt my name correctly..
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-18 : 11:26:20
So sys.master_files and sys.database_files are not in agreement as to the files in the database?

Has this DB been recently upgraded from SQL 2000?
Has anyone does any direct updates to the system tables? (check the SQL error log, there will be an entry in there, at the point where SQL brings the DB online) if someone has.

Please run a CheckDB of master, ASAP (as in immediately). Master is small enough that it'll be very quick and should have minimal to no effect on the performance of the server.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 11:41:15
If SSMS generates and executes intrinsic commands, then it logically follows that the execution of the same commands either directly in a query window or from SSMS will result in the same error, unless the GUI itself generates an intermediate error or incorectly translates a returned error. Getting worked up over whether I choose to use one or the other interface is a pointless exercise in posturing.

It is therefore uncontentious to report that DBCC SHRINKFILE (N'MyDB_Log2' , 9999) (the "real command") does indeed return an identical error - "Msg 8985, Level 16, State 1, Line 1
Could not locate file 'MyDB_Log2' for database 'master' in sys.database_files. The file either does not exist, or was dropped."

I predict with a very high confidence level that "ALTER DATABASE [MyDB] REMOVE FILE [MyDb_Log2]" executed from a query pane will in fact return "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."
Go to Top of Page

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 11:45:43
Gail,

1: No, sys.master_files and sys.database_files agree. The log file with File_id 36 is there, it has the correct name and it is as far as can be determined ONLINE.

2: The was SQLServer2000, but that was quite some time (read: years) ago.

3: No one has been dicking with the database internals directly.
Go to Top of Page

PhilipLewis
Starting Member

30 Posts

Posted - 2010-05-18 : 11:50:34
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
Nothing in the output appeared untoward.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2010-05-18 : 14:43:06
I'm glad you have a such a high opinion of the code I spent 10 years building. SSMS is awful. The Storage Engine is not. The unfortunate fact is the many people's view of SQL Server are colored from using SSMS.

Please run the following:

select * from sys.master_files
where database_id = DB_ID ('yourdbname');
GO

select * from yourdbname.sys.database_files;
GO


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

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2010-05-18 : 14:46:42
Btw - restarting SQL Server will not do anything. Have you tried disconnecting from the instance from SSMS, and reconnecting? The cache of data in SSMS frequently becomes out-of-date, but shouldn't be in this case unless you very recently added the log file.

On a separate note, why do you have 3 log files in the first place?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-18 : 14:51:55
This issue sounded very familiar, so I scanned my email and found that I had opened a PSS case with Microsoft regarding it. It was found to be a bug that had not yet been fixed in the CUs.

Paul, not sure if you have access to PSS cases anymore, but my PSS case number is 109081760834637. Do you know if I'm allowed to post how we fixed this?

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

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-18 : 14:53:19
I should mention that we are on SQL Server 2005, but perhaps the bug is in 2008 too.

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

Subscribe to my blog
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2010-05-18 : 15:03:10
Hey Tara - send me an email with the what you did and I'll let you know whether it can be made public or not.

Cheers

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-18 : 15:18:06
Email sent (to the account you use in the MVP list).

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

Subscribe to my blog
Go to Top of Page

unclebiguns
Starting Member

1 Post

Posted - 2010-05-18 : 15:41:37
quote:
Originally posted by PhilipLewis


It is therefore uncontentious to report that DBCC SHRINKFILE (N'MyDB_Log2' , 9999) (the "real command") does indeed return an identical error - "Msg 8985, Level 16, State 1, Line 1
Could not locate file 'MyDB_Log2' for database 'master' in sys.database_files. The file either does not exist, or was dropped."

I predict with a very high confidence level that "ALTER DATABASE [MyDB] REMOVE FILE [MyDb_Log2]" executed from a query pane will in fact return "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."




Well, I think part of your issue is that you are trying to run shrinkfile in the context of the master database (noted in the error returned from DBCC ShrinkFile) and in that case the log file WILL NOT exist unless you added it to the master database.

From BOL:
quote:

DBCC SHRINKFILE applies to the files in the current database. For more information about how to change the current database, see USE (Transact-SQL).


So I predict with an even higher confidence level that "ALTER DATABASE [MyDB] REMOVE FILE [MyDb_Log2]" will NOT return the same error.

Jack Corbett
MCITP: Database Administration SQL Server 2008
Go to Top of Page
    Next Page

- Advertisement -