Most SQL Server articles talk about how to execute a specific process or concept. I wanted to change things around a bit and discuss some common – yet tricky – issues that DBAs will run into. This is part two in my series on covering SQL Server from the trenches, where I present you with real solutions to real problems that occur while working on the leading edge of SQL Server.
1. Database mirror errors: Establishing a synchronous mirror with no witness.
Symptoms: While setting up the mirror you receive the error "The mirroring connection to 'TCP://IMPORTANTMACHINE.TEKMIRAGE.COM:7022' has timed out for database 'ImportantDB' after 10 seconds without a response. Check the service and network connections." Then, in the SQL Server logs you see "Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)' for 'TCP://IMPORTANTMACHINE.TEKMIRAGE.COM:7022."
Problem: The transactions that need to be restored to the mirror location to achieve synchronization are too large and take more than 10 seconds to restore/sync to the mirror location. That causes SQL Server to time-out the establishment of the mirror and there is no way to change this time-out value. This problem will occur if a witness is not present and the mirror is synchronous, not asynchronous.
Resolution: First, you must reset the status of the databases. Run "Alter database ImportantDB set partner off." At this point, you can apply transaction logs to the mirror and try again to establish the mirror, which might work if the transaction log is small enough. Disabling any application functions or picking a really slow time would help. You can also upgrade SQL Server to resolve this. CU6/CU7 would fix this issue -- they are builds 3228/3239, respectively. You would need to contact Microsoft directly to get access to these cumulative updates.
2. Primary filegroup is out of space.
Symptom: You receive this error message: "Primary Filegroup is out of space."
Problem: Database initial size is set to 3 MB, growth limit is set to 6 GB, growth rate is 6 GB. Vendor is complaining that they keep running around 3 MB.
Resolution: If the initial size is 3 MB and the growth rate is 6 GB, any attempt to grow would put the database past the growth limit. I recommend a smaller growth rate of around 200 MB to 1,000 MB, depending on the application's required transaction times/load. You could also increase the growth limit.
3. SQL Server login or AD login cannot log into the server even if their user has been mapped to the appropriate databases with appropriate permissions (login failed).
Symptom: Server logins exist, but users are unable to log in.
Problems and resolutions:
- The default database the user was mapped to was deleted, thus changing the default database to blank, which effectively disables the user's login.
- SQL Server logins are case sensitive. Any variation in case will cause login failure.
- AD logins are denied with errors such as "trusted account" has been denied access. If you do not see a login listed in the error, it means that the AD account has been locked out.
- ODBC configuration errors: Users are configuring ODBC accounts to use SQL Server authentication and then typing in "Domain\UserName." On the SQL Server side, it appears that an AD login is failing. In reality, though, the AD login name is just being passed as a SQL Server login that probably doesn't have access. Ensure that users are configuring ODBC to use NT authentication if you want to use AD accounts.
- User is denied access to the default database. The default database listed for the login does not have access rights for that login, thus causing the login to be denied access to its default database – effectively locking the login out of the server.
- For SQL Server logins, "Enforce password policy" checkbox is turned on, or the SQL Server password could be set to expire. SQL Server will then enforce general corporation policies on the password. This option can deny a user's access, since it applies a corporate policy to it, that is, after x number of days require a password change, certain number of characters, certain number of numbers, certain number of symbols, a given length, etc. I haven't seen one application that will handle SQL Server notification of those policies, so the applications just report a login failure.
4. SQL Server starts up, but is unable to bring a database online.
Symptom: Database does not come back online after a service restart, and SQL Server logs say SQL Server is unable to find a given path.
Problem/Resolution: Run "select * from sys.master_files where database_id = db_id('reportserver')" to determine if the file paths for the databases really exist. Assuming you can find the files, issue the command "ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name')" to reset the file path and restart the SQL Server service, which will recover the database. If you can't find the database files, you will be forced to restore.
5. Taking a database offline fails.
Symptom: Attempting to take a database offline fails.
Problem: When users are still logged into the system, attempting to take the database offline fails.
Resolution: Run sp_who2 and identify all users in the database, killing their spids. You will also want to disable their logins so they can no longer access the system. Once that is complete and you verify all users are out by running sp_who2, then you can reliably take the database offline.
6. Database diagram objects can't be installed.
Symptom: You receive an error such as "Database diagram support objects cannot be installed because this database does not have a valid owner."
Problem: The database owner is not set to sa, but is likely set to some other login.
Resolution: All databases should be owned by sa to prevent issues. Run the command "ALTER AUTHORIZATION ON DATABASE::dbname TO sa".
Note: Invalid database ownership, such as where a disabled/deleted AD account is the owner, will prevent database properties from appearing.
7. Changing object ownership
Symptom/problem: Application installed a proc called SchemaName.ProcName under a schema other than dbo, and now needs to be changed.
Resolution: Run "ALTER SCHEMA dbo TRANSFER SchemaName.ProcName". Basically, this will transfer the object "SchemaName.ProcName" from its current schema to the dbo schema.
Note: sp_changeobjectowner will still work, but it's depreciated, so don't expect it to be around for long.
Summary
I covered a lot of issues that administrators often find by accident as part of their daily tasks. After reading the solutions offered here, I hope you'll be able to fix errors more quickly and spend less time researching them.
| ABOUT THE AUTHOR: |
|
Matthew Schroeder is a senior software engineer working on SQL Server database systems, ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. Matt currently works for the gaming vendor, IGT, providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and non-profit. Matt specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.
|
|
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
I really appreciate that Matthew Schroeder takes the time to write his detailed articles. They are real nuts and bolts, real-world-problem-solving-types of articles. I find them very useful and am accumulating a library of his and Denny Cherry's articles. In my opinion, they are the two best writers that write on DBA subjects.
—Lee C.
******************************************
