Author |
Topic |
PhilipLewis
Starting Member
30 Posts |
Posted - 2010-05-18 : 05:50:53
|
SQLServer2008 10.0.2531/WindowsServer 2008 R2 EnterpriseFor 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 |
|
|
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 ShawSQL Server MVP |
|
|
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. |
|
|
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 ShawSQL Server MVP |
|
|
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. |
|
|
PhilipLewis
Starting Member
30 Posts |
Posted - 2010-05-18 : 10:24:54
|
Kristin - you really need to learn to read |
|
|
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 ShawSQL Server MVP |
|
|
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. |
|
|
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.. |
|
|
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 ShawSQL Server MVP |
|
|
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 1Could 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 1One or more files listed in the statement could not be found or could not be initialized." |
|
|
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. |
|
|
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. |
|
|
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_fileswhere database_id = DB_ID ('yourdbname');GOselect * from yourdbname.sys.database_files;GOPaul S. Randal,CEO, SQLskills.com Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server MagazinesAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
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/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server MagazinesAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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.CheersPaul S. Randal,CEO, SQLskills.com Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server MagazinesAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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 1Could 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 1One 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 CorbettMCITP: Database Administration SQL Server 2008 |
|
|
Previous Page&nsp;
Next Page
|