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 filegro
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
up 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:
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.
[TABLE]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.
******************************************
