Home » Archives for May 2012

May 2012

sql server DBA 999

How to check Log shipping status in SQL Server 2008

How to check Log shipping status in SQL Server 2008:

SQL server recovery model has to be Full recovery mode for log shipping which allows log backups.

sql server database log backup is shiped and restore from one server or Primary to another server Secondary/destination server. Reports can be run from Secondary server for business needs. Using sql server nolock is recommended if dirty reads are allowed for reporting purposes.

This can be done by two methods:

1.By using the standard report on Instance level for Transaction log shipping status both on Primary and each secondary/destination servers individually.
On the Primary/source server, it shows when backup jobs succeeded.
On Secondary/Destination … Read the rest

sql server DBA 999

Unable to Shrink Temp DB Size in SQL Server 2008

It occurred in production environment when I was Unable to Shrink Temp DB Size SQL Server 2008 eventhough nothing was using it.

To check the free space you can use the query below:

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

and to get idea on files

sp_helpdb 'Tempdb'

When trying to shrink Tempdb it does not let me shrink with command below to 1000MB.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)
GO

I have tried setting intial sizes for tempdb which did not work either,But when we run below query to set the tempdb appropriately with fixed filegrowth then it works

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', … Read the rest

sql server DBA 999

Bringing Database back from Single User to Multi User mode with active connections

Bringing Database back from Single User mode to Multi User mode for Database with active connection:
Some times you will need to bring a database back from single user mode.
If we issue the below command,

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET multi_USER
GO

It errors out:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'AdventureWorks' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

For conditions like this:
Use the below query to find out the active connection to the database

select d.name, d.dbid, spid, login_time, nt_domain, … Read the rest

sql server DBA 999

When do we use SQL Server Database in Restricted User Mode?

Why do we need restricted user SQL server?

When performing some maintenance tasks, particularly in recovery situations, it is essential to limit access to databases by general users by restricting the user access by putting into restricted user sql server or for sql database in single user mode after restore. restricted user sql server access mode can be employed to limit connections to high-level users only.

How do we put restricted user SQL server?

In order to put SQL server restricted user mode we need to use the alter database set restricted_user below. This command can help with sql database in single user mode after restore.

ALTER DATABASE Command
The ALTER DATABASE command allows a database administrator to modify SQL … Read the rest

sql server DBA 999

Protecting your database using Transparent Data Encryption in SQL Server 2008

Encryption:
Encryption is the process of transforming information in plain text using a cipher, or algorithm, to make it unreadable to everyone other than the person who has the key. There are two types of keys; symmetric and asymmetric. When the same value is used to encrypt and decrypt, then it is known as a symmetric key. An asymmetric key has two parts: one is a private key and the other is a public key. The private key is used to encrypt the data and the public key is used to decrypt the data.

TDE: is a full database level encryption that protects the data files and log files.
As per Microsoft documentation for TDE

“Transparent data encryption (TDE) performs … Read the rest

sql server DBA 999

Memory configuration and using AWE /3GB /USERVA in SQL Servers 2005/2008 in 64 bit and 32 bit installations

In this post we will discuss various memory configurations in 64 bit and 32 bit installations of SQL Server:

In 32 bit OS systems, AWE was used for addressing the issue to access the memory above the 32 bit process limitation. It also enables SQL Server to lock pages in memory.
Servers that are x86 or 32 bit have a VAS region that is 2^32 in size or roughly speaking 4 GB. This is by default split 50/50 into kernel mode and user mode VAS regions. To work around this limitation, the /3GB and /USERVA boot.ini switches can be used to change the user/kernel mode proportions from 50/50 to 75/25 (/3GB) or a user defined proportion (/USERVA) to allow SQL … Read the rest

sql server DBA 999

Why Copy-Only Backups taken in SQL Server?

A copy-only backup in SQL Server(any version like 2012 or 2008 or 2005) is taken in cases you do not want effect the sequence of conventional SQL Server backups.
Usually in using a normal Backup,the backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. copy-only backups serve this purpose.

Note: The transaction log is never truncated after a copy-only backup.

The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

Copy-only log backups (full recovery model and bulk-logged recovery model only)

Note:A copy-only full backup cannot serve as a … Read the rest

sql server DBA 999

SQL Server 2005/2008 Lock Escalations

Please read my previous post on SQL Server Locks and concurrency control before this….
Lock Escalations are there for a reason. For every lock taking place on SQL Server it takes up around 100 bytes approximately in memory in RAM. So, if we are holding 500,000 locks then it would be 47.68372 MB of memory used. This includes all types of locks.

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:

•When locking rows or index key ranges, the Database Engine places an … Read the rest

sql server DBA 999

SQL Server 2008/2005 Concurrency control with locks

Today let us discuss more on SQL Server 20008/2005 Locks and concurrency control……

Locks are very essential to maintain concurrent data access without issues. Say you are trying to update some data row and someone else wants to read that data, to avoid phantom reads (inconsistent view of data or data which is not yet committed) a Exclusive lock is placed on the row. But it does not start nor end there. First thing when you issue a update statement is a lock is placed on database which is called intent exclusive (IX) then same IX on table with EX(Exclusive lock on table) once it available for you.
Some locks are compatible with each other like a simple select queries … Read the rest

sql server DBA 999

SQL Server 2008/2005 Storage Internals, GAM, SGAM, PFS, IAM

Today lets discuss some of the SQL Server Storage internals like what is GAM, SGAM, IAM etc
Basically in short words:
1.GAM(Global Allocation Map) tracks around a 64,000 extents(extent is 64KB which consists of 8 pages) which is around 4GB of space. GAM has bit indicating if the extent is currently available for allocation or not. GAM bit for a extent is 1 only if it’s not being used at the moment.
2.SGAM(Shared Global Allocation Map) tracks mixed extents with free space. A mixed extent is used by multiple objects like table, index etc. SGAM bit is 1 for a extent only if it has free space in it and it is mixed extent. Even SGAM tracks 64,000 extents same … Read the rest