|
|
By Adam Bean, on October 7th, 2009
Lots of work to do to create additional forums, but for now it’s a start. For those of you who find us and want to ask questions about the admin database, DBACentral or general administration, this is a great place to do it. Give us time and the forum will evolve, for now though, it’s a good start.
Thanks
By Adam Bean, on October 7th, 2009
The official release to the public is now available on the admin database page.
We are working to have documentation ready as soon as possible. In the mean time, feel free to download and review/use the objects.
Thanks
By Matt Stanford, on October 7th, 2009
I can’t believe this. I’ve got a large (2+ TB) database that I want to do daily integrity checks on. Because it is so big, I was using PHYSICAL_ONLY. Here’s the command:
DBCC CHECKDB(BigDB) WITH PHYSICAL_ONLY, TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS
I very quickly get this error:
Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
After a quick google search I found this KB article that explains the issue. Evidently DBCC CHECKDB doesn’t work against databases that contain filegroups that are flagged as read-only. Wow.
The recommend work around is to take a snapshot of the database, check the snapshot, and then drop the snapshot. This is what I thought DBCC CHECKDB does internally anyways! Oh well, this isn’t the strangest thing I’ve ever heard of coming from Microsoft.
Here’s what I came up with as a workaround:
DECLARE
@SnapName NVARCHAR(128)
,@SQL NVARCHAR(4000)
EXEC admin.dbo.SnapshotDB 'BigDB', @SnapName OUTPUT
BEGIN TRY
SET @SQL = 'DBCC CHECKDB(' + @SnapName + ') WITH PHYSICAL_ONLY, TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS'
EXEC(@SQL)
SET @SQL = 'DROP DATABASE [' + @SnapName + ']'
EXEC(@SQL)
END TRY
BEGIN CATCH
SET @SQL = 'DROP DATABASE [' + @SnapName + ']'
EXEC(@SQL)
RAISERROR('DBCC CHECKDB failed on BigDB. Panic.',16,2)
END CATCH
Strange stuff.
By Jeff Mlakar, on October 6th, 2009
In working with SQL 2005 Maintenance Plan backup cleanup tasks, a frustrating bug was found. It seems that when the ability to select “hours” as a time criterion was added in SP2, the door was opened for misinterpretation of time units. For example, you can, in your local Management Studio, set the Plan to delete files greater that 4 days old, and when run on the server the Plan will delete files only greater than 4 WEEKS old. This was the full mapping I found:
SET ACTUAL
Hours -> Days
Days -> Weeks
Weeks -> Months
Months -> Years
Years -> Years
I don’t know yet if this is fixed in 2008. For 2005 at least, I suggest refraining from using the built-in “Maintenance Cleanup Tasks” in Maintenance Plans, and instead use a T-SQL Statement task with the snippet below. This will truly delete greater than 4 days. Set your time criteria however you like.
DECLARE @DateOlderThan datetime
SET @DateOlderThan = DATEADD(day,-4,GETDATE())
EXEC master.dbo.xp_delete_file
0 -- delete files
,N'H:\SQLBackup\INST3' --full path to the main directory
,N'bak'-- file extension
,@DateOlderThan-- delete files created before this timestamp
,1-- Including first-level sub
By Jeff Mlakar, on October 6th, 2009
Management Studio 2008 has a default that prevents saving table changes from the designer when the table will need to be rebuilt. I’m constantly being asked about this error by developers:
”Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.”
To be able to make table changes go to Tools->Options->Designers and uncheck “Prevent saving changes that require table re-creation”.
It’s a good idea to do this as soon as you install 2008. Tell everyone around you. I’ve been asked about it at least half a dozen times, as recent as a few days ago.
By Adam Bean, on October 5th, 2009
Locally I am running 2008 R2 and on my DBA management server I run 2008 RTM. Today I went to restore a database from my local to my server and received the following awesome error:
The database was backed up on a server running version 10.50.1092. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
My initial thoughts were that (hopefully), it was simply that I was running ‘08 RTM (was only running on RTM to support a small side project that has somewhat died). So I figured it was time to install SP1. Installed and tried again …
The database was backed up on a server running version 10.50.1092. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Awesome.
Granted, it’s the same scenario when going to a newer version with 2005/2008 from a previous version, but from R1 to R2 there is no compatibility (at least of today anyways). This is unfortunate to hear to say the least. It makes me wonder what else will not be supported.
By Adam Bean, on October 1st, 2009
So this was an interesting one, never seen such a thing before.
I wrote a quick audit procedure to basically monitor connections to every instance in my environment by simply querying sysperfinfo. Well, for the most part the data is spot on. Except for two instances. The cntr_value in sysprocesses for counter_name = ‘user connections’ is showing about 2,000 for two instances yet sysprocesses shows no more than 30-40 at a time for these instances. I have verified that using perfmon on the server, does indeed report the same number that sysperfinfo does.
I am polling data from over 200 instances, and only these two are showing inaccurate numbers. To add to the mystery, these servers are pretty much the same. One is a virtual and one is a physical, with the exact same layout, databases, etc. The virtual was to be a proof of concept that the physical could be turned into a virtual. The physical is in use whereas the virtual is not, yet they both have this same problem in regards to sysperfinfo not being anywhere near the actual number of connections, and the number for both is extremely high.
Wasn’t able to find much of anything relevant online, so I restarted the instance and the counters in sysperfinfo went back to normal. They now match sysprocesses. I’m going to keep an eye on this to see if the problem returns, but for now, all is well.
By Adam Bean, on September 24th, 2009
Matt and I will be giving our first public presentation at the SQL SIG next month: http://www.bennettadelson.com/sqlsig.aspx. We are hoping this is the first of many presentations. This presentation will focus around the admin database from its history to its purpose. The admin database should be fully available for download prior to the presentation.
Hope to see you all there!
By Adam Bean, on September 15th, 2009
I have been a DBA for about 5 years and unfortunately have not had any experience with clustering. That was up until I was hired by my current employer back in January and since have brought up several 2005 clusters and now manage over 12 clusters. I have installed, setup and configured many 2008 instances and even R2. Never have I had any problems I was not able to resolve on my own and/or researching online. This was all true up until I brought up my first 2008 cluster. I am rather confident that I experienced almost every problem possible with bringing this environment up. Hopefully this information will prove useful to others as I am now rather confident on how to install a 2008 cluster post these problems.
Summary: Advanced cluster preparation install failed.
Details:
- Successfully installed advanced cluster preparation on first node.
- Copied configuration file to secondary node and attempted “install based on configuration file”.
- Install failed: “The setting ‘AGTDOMAINGROUP’ is not allowed when the value of setting ‘ACTION’ is Install.”
- Tried several times, always resulting in the same error. Unable to find anything relevant online.
- At this point, decided to simply uninstall and start over.
-
- Attempted uninstall on first node. Failed: Object reference not set to an instance of an object.
- Ok great, I can’t uninstall … what to do. Figured I’d try to do the advanced cluster preparation on secondary node without the configuration files. It worked.
-
- Alright, let’s try to complete the cluster “advanced cluster completion” option.
- Failed: The instance prepared for clustering that was installed on computer XXX failed during installation and cannot be clustered. To continue, remove the failed cluster-prepared instance and run the cluster-prepare Setup operation again.
- Fantastic. I now have two preparded nodes, but can’t complete the cluster.
- Well, let’s try the uninstall again, tried on both nodes. Failed: Object reference not set to an instance of an object.
- This just keeps getting better. Tried to do the repair installation on either node. Failed The SQL Sever feature is not in a supported state for repair, as it was installed in preparation to be clustered. Cluster prepared features cannot be repaired. To continue, uninstall the specified SQL Server feature.
- At this point, I am at a loss. Unable to find anything relevant online. Decided to open a Microsoft premier support case.
- They connect to my work computer via Easy Assist and spend a good hour or so recreating exactly what I had been experiencing. They were at a loss as well, and decided the next best route at this point was to attempt to uninstall via registry.
- Almost 2 hours pass and no progress has been made. After manually searching and removing every entry related to SQL on this node, they were unable to even launch the install as it was erroring out with a new error (did not record, but it was basically the same error you get when you attempt to uninstall an app from add/remove programs (appwiz.cpl) when the program doesn’t exist.
- At this point, Microsoft asked if they could continue to work on the problem. I said that we had already spent to much time and even had they got this working, I had little faith that I’d have a clean install moving forward. I made the decision to have my IT guys simply reformat the servers.
- A few days later, Microsoft deemed the problem a bug and credited our hours back. While it’s not neccessarily a bug, the feature to install from config file in advanced cluster prepare should not be available. The error is expected if the config file is provided through the GUI. The correct way to prepare the remote nodes [in case we use the config file] is to provide the config file at the command prompt.
What did I learn?
In looking back, yes, this was my fault. I should have followed the instructions, but honestly … why would they give you the option to fail? Why if you’re not supposed to use the GUI, is it an option!? AHH, oh well, done and over with. Moving on …
Summary: Validate SCSC-3 Persistent Reservation failure
Details:
- So at this point, we formatted the servers and started over, hooray.
- Worked out well because at this point we now have 4 nodes of the 6 available to install on.
- The pre install validation checks failed due to the rule “Validate SCSC-3 Persistent Reservation” failing.
- This is a windows cluster validation check and if windows fails it, so does SQL. Found the log file in C:\windows\cluster\reports in the Validation Report 2009.08.19 At 07.42.56 – Validate SCSI-3 Persistent Reservation
- Found a decent article on the matter, but again, this was above me http://www.servercare.nl/Lists/Posts/Post.aspx?ID=71
- Sent it over to my systems guys and they worked on the issue for a week or so with HP and this was their response to the matter:
Somehow, one of the machines created a persistent reservation on a disk. Magically, the HP tool (hpprutil) couldn’t see the reservation. HP support couldn’t remote control the machine, as the install apparently wasn’t completed. Once they had that set up, they got in and saw that the array said there was still a reservation on the disk. They had to create a single-use password to force the machine to drop the paths that it wasn’t letting go of. It hasn’t been explained to me farther how in the world it would allow me to remove 6 paths, but the remaining 2 it held on to.
In the middle of it all, machines were swapped out. The WWN should have stayed the same on all the machines because we pay an exorbitant amount of money for virtual connect, but of course VC didn’t work right and all the WWN’s changed. Then once the machines were firmwared-yet-again, they all changed again. Everything ended up being rezoned about 20 times each, along with all the time wasted trying to figure out just what the WWN’s should and shouldn’t be, why the array wouldn’t let the damn LUN go (but had the previous 6 times), etc.
What did I learn?
This one was out of my league. I didn’t understand it then, and unfortunately I still don’t understand it.
Summary: The credentials you provided for the sql server service are invalid & The current SKU is invalid
Details:
- Ok, disk problems resolved, resuming the install (not doing advanced prepare this time!)
- Successfully installed first single node cluster, hoooooray!!!!
- When attempting to add node to the cluster, it failed stating “the credentials you provided for the sql server service are invalid”. It Failed on SQL Server / SQL Server Agent. I attempted to change the password to no avail, failed every time.
- Found this article: http://support.microsoft.com/default.aspx/kb/955948/ which states “To work around this issue, type the domain account manually in the Account Name text box.” This is not true, as the boxes are grayed out and do not allow you to change the account.
- Unsure to as where I got the information from, but I had read somewhere that CU1 would resolve this specific issue (trying to find link).
- Ok, installed SP1 on the node which has the single node cluster setup.
- Attempted to add node, “Failed: SQL 2008 Cluster – The current SKU is invalid.”
- Found these articles: http://support.microsoft.com/kb/957459/ & https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036&wa=wsignin1.0M
- Install SP1 on a node that doesn’t have SQL installed huh? Interesting. The 2008 cluster install doesn’t use the task scheduler any more, and the nodes have to be manually installed, so why do I need to patch a server that doesn’t have any trace of SQL on it yet?
- Low and behold, when I went to install SP1 on each of the other nodes, it patched the “shared features” successfully.
- Tried to add node to cluster, successful on each node!
- Had to install SP1 again on each node after I completed the install.
What did I learn?
You need to install SP1 (or at least CU1) after installing your first single cluster node. After that, you have to install SP1 (or CU1) prior and post to adding any other nodes on the cluster. Crazy, but at least I know now.
Recap
1) Don’t slack. When you’re installing a new technology for the first time, do your homework and read the documentation.
2) Install SP1 before and after adding a new node for SQL 2008
3) Did Microsoft test a 2008 cluster install? To many oddities here that seem like some further testing would have easily resolved. I have not heard one way or the other if they will remove the feature to perform an advanced cluster installation from a configuration file or not. If I hear any more on the matter, I will post about it.
By Adam Bean, on August 22nd, 2009
To do:
- Map out pages
- Map out forum
- Create banners
- Modify CSS to lower font size of side bars and add “Recent” page into posts/comments
- Find a better file uploader plugin and/or determine a better way to manage script files
- Implement proper anti-spam plugins
- Test child pages with top menu
- Work on “about us”
- Create policies for blogs
Done:
- Created forum
- Tested wp-syntax … it’s decent, not great for SQL though
- Tested file attachments
|
|
Comments