Service packs are very critical and important. It is very important from product upgrade & bug fixing point of view.
Regular upgrades to your system with latest releases help you in :-
· Continued Support
· Resolves Problems
· New Features
Microsoft has recently released SQL Server 2008 R2 service packs. Please note SQL Server 2008 R2 and SQL Server 2008 are different versions & this release is for SQL Server 2008 R2. It will not work on SQL Server 2008 but will work only on SQL Server 2008 R2. If you are installing fresh SQL Server 2008 R2 or having SQL server 2008 R2 RTM or SQL server 2008 R2 SP1 or any other CUs installed , you just have to download and install SP2 and there is no need to install mediator releases. Service pack SP2 will contain all releases & upgrades before SP2 including SP1 upgrades.
List of Bug Fixes : http://support.microsoft.com/kb/2630458
You can download : http://www.microsoft.com/en-in/download/details.aspx?id=29848
Blocking & Deadlock
SQL Server blocking
SQL Server blocking occurs when one connection (user process or application process) places a lock on a table (or a number of rows) and a second connection attempts to read or modify the data under the lock. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.
The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.
SQL Server deadlocks
The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.
Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.
Blocks escalating to deadlocks
The following diagram shows the sequence of events leading to a deadlock. Consider two applications (A1, A2) accessing two different table (T1, T2):
Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements
Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements
Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the transaction) but has to wait for A2 to release its lock
At this point, a block is created since A2 is blocking A1
Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can finish its own transaction)
A deadlock is created since two connections have blocked one another. SQL Server automatically resolves the deadlock by choosing one of the connections as a deadlock victim and killing it.
In SQL Server 2005 and later, the information in the TextData column captured for the RPC:Starting and RPC:Completed events is also available in the BinaryData column. For performance reasons, it is more efficient to include the BinaryData column and omit the TextData column, when adding these events to the trace. If the BinaryData column is included, Profiler will extract the text from that column and display it in the lower event details pane, even if the TextData column was not included for the event.
However, a problem occurs when the trace does not contain ANY events with the TextData column. If such a trace is open in Profiler, the TextData column is not displayed in the grid, and the text information associated with the RPC:Starting and RPC:Completed events is not displayed in the lower event detail pane, even though the information is available in the BinaryData column.
While testing the performance of the following two scripts in query analyzer, interesting results are discovered.
While comparing their performance in loop SELECT statement gives better performance then SET. In other words, SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all three values in one execution.
SET is the ANSI standard for variable assignment, SELECT is not.
SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET.
If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned.
When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.
Application doesn’t use the dbo schema and I need to set the users coming into the database to use the correct schema automatically. We’re using Windows groups and whenever I go to set a default schema, SQL Server gives me an error. How can I set the default schema for my users?
The short answer is that if you’re using Windows groups, you can’t. While the best practice is to use Windows groups because that makes security management much easier on the DBA, one area that’s not true is when you need to specify a default schema.
As of SQL Server 2005, users in a database can be set to have a default schema except for the following cases:
- Windows groups
- Logins mapped to certificates
- Logins mapped to asymmetric keys
We’ll focus on Windows groups. If you try to specify a default schema, as in the following example:
You get this error:
This is not a bug; it is the expected behavior. While you can set other default settings, like default database and default language, these correspond to the server level of SQL Server and not an individual database. So why is there a difference? It has to do with the fact that a given Windows user is typically a member of multiple Windows groups. More than one of those Windows groups could have access to the SQL Server and to a specific database, especially in larger environments. For instance, as a DBA you might be a member of the following groups at the domain level (I’ve using generic names as each organization has different naming conventions):
- Domain User
- Full Time Employee
- Information Technology Member
- Database Administrator
And for a given SQL Server, the groups Database Administrator and Full Time Employee might both access. In the event that both have access, when we’re talking about default database and default language, it’s not a big deal if we have multiple values as long as they are all valid. After all, both can be overridden when connecting to the SQL Server, even if there is an issue. They also can be overridden after the connection is made. However, when it comes to default schema, we have a problem. For instance, imagine if we could do the following:
- Database Administrator -> Admin
- Full Time Employee -> Employee
When a DBA connects and tries to execute a stored procedure in the database, does SQL Server execute Admin.SomeStoredProcedure or Employee.SomeStoredProcedure? What if there are multiple groups? How do you know for a particular user which schema should be default? The only way to solve the confusion is to specify per user. And if you’re going to do that, there’s no point specifying the default schema for a Windows group. One could argue that if a particular user is a member of only one group, then a default schema should be specified. However, because of the fact that the association with groups occurs outside of SQL Server, this can lead to unexpected behavior. Also, if a second group is added to the database which the user is a member of, we no longer have the single group situation. Then how should SQL Server react? There’s no simple way to solve this issue. That’s why default schemas aren’t allowed for Windows groups.
SQL has long resolved similar issues for logins that are Windows groups. For instance, if a Windows user is a member of two Windows groups that are in turn created as logins on a SQL Server machine, SQL is able to resolve possible differences in default database, language, etc. So it is beyond any possible justification that Microsoft has yet to in some way resolve this for default schema. Defaulting to the dbo schema in such a situation would certainly be an acceptable solution so long as the user is not automatically granted alter on the dbo schema.
At present, if a user that does not have a default schema by virtue of accessing via a group creates an object without qualifying it (ie: “create table” rather than “create dbo.table”) will automatically cause a new schema to be created named after themselves. This user is then granted full ownership of this new schema. This happens even when the group login/user that they used to get to the database is EXPLICITLY DENIED create schema rights. This is totally unacceptable. A user should not be able to create a schema when they are denied that permission. Microsoft has tried to claim that the user is not actually creating the schema, that SQL Server actually is. While this may technically be true, it in fact always it technically true. Any user that creates a schema does so by submitting a request to SQL Server to create it, and SQL Server then creates is. In either case, the user caused SQL Server to create it so the attempt at justifying this by saying that the user didn’t violate the “deny create schema” fails the test of basic logic
Unfortunately, the only real workaround if users access SQL Server directly is to add users individually to SQL Server and to the database. This doesn’t work if you have many users, but because specifying default schema gets to a per user setting, it’s really the only way to go. There are ways, such as through PowerShell to script the members of a Windows group and then generate the T-SQL to create the logins, then add them to the database and set the default schema, but you’ll be running this regularly to keep everything up to date. However, if you have an application that has a schema other than dbo and users cannot specify the schema, you’re not left with any other choice.
If users are accessing through an application, such as a web service, then using a Windows user account for the web service is an option, which allows you to specify the default schema. This assumes, of course, that the web service or application is handling security outside of SQL Server. If it’s not, then this isn’t a viable option either, and you’re forced back to the workaround. In this situation, resorting to a SQL Server-based login doesn’t work either, because there is no way SQL Server can tell between Mary and John accessing the DB through the application. You’ll have to drop back to specifying the users to be passed through and create them in the database individually.
In SQL 2012 we can have default schema for windows groups as well.
SQL Server 2008 Enterprise gives you the added boost to verify your data quickly and efficiently with multiprocessor support. Database console commands such as DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE check the allocation and integrity of the database objects. In short, these commands are used to ensure that the database is free from corruption—pages are linked properly, the data is valid, and page offsets are correct. It is important to execute these checks against your healthy system to be sure that any internal database problems are corrected early, any hardware or database issues that cause corruption are detected and corrected, and that your database is responding in the appropriate manner to application requests. When you execute DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE against a large database or table stored in the Enterprise edition of SQL Server 2008, the Database Engine may check multiple objects in parallel if system resources are available and the Database Engine deems the load would benefit from parallel processing. The query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked. Parallel DBCC should typically be left enabled, but can be disabled by using trace flag 2528.
Checking database objects in parallel reduces maintenance and operational costs and improve database availability. Large databases may require a long maintenance window to complete validation, which can affect operations during normal business hours. By using multiple processors to complete maintenance operations, SQL Server 2008 Enterprise completes database validation more quickly, which will free up system resources and tempdb, and in some cases reduce locking during your vital production hours.
1) Recycle SQL Error log
2) Recycle SQL Agent Error log
3) Recycle(rollover) SQL Server Default trace
EXEC sp_configure ‘default trace’, 0 RECONFIGURE
EXEC sp_configure ‘default trace’, 1 RECONFIGURE
4) Recycle SQLFT#.Log
a. Before 2008 – restart the indexer
b. After 2008 – no way to cycle to full text log without restarting SQL server
5) Recycle FDLAUNCHERRORLOG
a. Restart FDLauncher service
SQL Server 2005 onwards includes the mirroring of backup media sets to provide redundancy of your critical database backups. Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. These malfunctions are very serious because backups are the last line of defense against data loss. SQL Server 2005 Standard Edition supports only a single backup copy during your backup operations. Depending on your requirements, SQL Server Enterprise allows you to create up to four mirrored media sets.
Mirrored backup media sets improve availability by minimizing downtime during restore operations. A damaged backup could result in a longer restore time, or a restore failure. As databases grow, the probability increases that the failure of a backup device or media will make a backup unrestorable. Restoring a database is time sensitive, and mirrored backup media sets give you added protection to get your application fully functional more quickly.
In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.
Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.
Example 1. Single File Backup to Multiple Locations using Mirror
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘d:AdventureWorksBackup1.bak’
MIRROR TO DISK = ‘d:AdventureWorksBackupCopy.bak’
If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMAT reinitializes the backup.
Example 2. Multiple File Backup to Multiple Locations using Mirror
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘d:AdventureWorksBackup1.bak’,
disk = ‘d:AdventureWorksBackup2.bak’
MIRROR TO DISK = ‘d:AdventureWorksBackupCopy.bak’,
DISK = ‘d:AdventureWorksBackupCopy1.bak’
Example 3. Only 4 Mirror are allowed (1 Backup file + 3 Mirror files)
Example 4. Different number of backup file & Mirror files – Error Occur
There is 2 ways of finding current / particular transaction level :-
SELECT CASE transaction_isolation_level
WHEN 0 THEN ‘Unspecified’
WHEN 1 THEN ‘ReadUncomitted’
WHEN 2 THEN ‘Readcomitted’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’ END AS TRANSACTION_ISOLATION_LEVEL
where session_id = @@SPID
DECLARE @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
DECLARE @IsolationLevel varchar(100)
EXEC(‘DBCC USEROPTIONS WITH NO_INFOMSGS’)
SELECT @IsolationLevel = Value
WHERE SetOption = ‘isolation level’
Script to get details of permissions on Database objects
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
WHEN ‘G’ THEN ‘Windows Group’
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc — Schema-contained objects
ELSE perm.[class_desc] — Higher-level objects
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) — General objects
WHEN 3 THEN schem.[name] — Schemas
WHEN 4 THEN imp.[name] — Impersonations
[ColumnName] = col.[name]
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
sys.objects obj ON perm.[major_id] = obj.[object_id]
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
princ.[type] IN (‘S’,'U’,'G’) AND
– No need for these system accounts
princ.[name] NOT IN (‘sys’, ‘INFORMATION_SCHEMA’)
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)