Home » 2012 » July (Page 2)

Monthly Archives: July 2012

SQL Server 2008 || Parallel DBCC (Database Console Commands)

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.

Benefits

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.

How to Recycle SQL Server error logs – 5 Types of logs

1) Recycle SQL Error log

exec msdb..sp_cycle_errorlog

2) Recycle SQL Agent Error log

exec msdb..sp_cycle_agent_errorlog

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 – Mirrored Backup || Cool Feature

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.

Benefits
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’

with format

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’

with format

Example 3. Only 4 Mirror are allowed (1 Backup file + 3 Mirror files)

Example 4. Different number of backup file & Mirror files – Error Occur

 

 

 

 

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to find current/particular transaction level?

There is 2 ways of finding current / particular transaction level :-

1)

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

FROM sys.dm_exec_sessions

where session_id = @@SPID

2)

DECLARE @UserOptions TABLE(SetOption varchar(100), Value varchar(100))

DECLARE @IsolationLevel varchar(100)

INSERT @UserOptions

EXEC(‘DBCC USEROPTIONS WITH NO_INFOMSGS’)

SELECT @IsolationLevel = Value

FROM @UserOptions

WHERE SetOption = ‘isolation level’

 

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Script to get details of permissions on Database objects

Script to get details of permissions on Database objects

Script to get details of permissions on Database objects

SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
WHEN ‘G’ THEN ‘Windows Group’
END,
[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
END,
[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
END,
[ColumnName] = col.[name]
FROM
–database user
sys.database_principals princ
LEFT JOIN
–Login accounts
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
–Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
–Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
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/)

%d bloggers like this: