Database Simplified Headline Animator

Database Simplified

Saturday 26 November 2011

Memory Utilization In SQL Server (Database Level)

Its common question for DBAs to find memory consumption by each database. With SQL Server 2000 its was quite difficult to find but with SQL Server 2005 + we have DMV (Dynamic Management Views)  “sys.dm_os_buffer_descriptors” which provides memory utilization by each database or table in database.

So let’s use the DMV to find current memory utilization by each database.

Before we start this memory test you need to create a test table and a database with following script.



Create Database DBMemoryTest
Go
Use DBMemoryTest
Go
Create Table TblMemoryTest(Id Int Default 0,NameValue Char(8000) Default 'Mem Test')
Go
Insert Into TblMemoryTest Default Values
Go 20000

Now you have 20000 records in TblMemoryTest Table. Lets check the data space of the table.



exec sp_spaceused 'TblMemoryTest'
Go
Output :

image

Data : = 160000 KB Means 160000/1024 = 156 MB Around. So it means that when I use select * on this table without any where condition then it must eat up memory upto 156 MB. Lets Test It.

Clear Buffer Cache and Procedure Cache



DBCC FREEPROCCACHE
Go
DBCC DROPCLEANBUFFERS
Go

Now run following query to find memory utilization by each database.



SELECT DB_NAME(database_id) AS DatabaseName, (COUNT(*) * 8)/1024.0 AS [Size(MB)]
FROM sys.dm_os_buffer_descriptors
Where Database_Id<>32767
GROUP BY DB_NAME(database_id)

Output :

image

Now run select statement in TblMemorycheck table.



Use DBMemoryTest
Go
Select * FRom TblMemory Test
Go

And now again run query to find memory utilization.



SELECT DB_NAME(database_id) AS DatabaseName, (COUNT(*) * 8)/1024.0 AS [Size(MB)]
FROM sys.dm_os_buffer_descriptors
Where Database_Id<>32767
GROUP BY DB_NAME(database_id)

Output :

image

You can clearly see that database DBMemoryTest is showing 157 MB of memory utilization which was not there in previous run of query.

Thursday 24 November 2011

Move Database In SQL Server With Minimum DownTime

When working with very large database and there is need to move database from one server to another, then downtime is always an issue in such cases. There are several ways to move database from one location to another. Here I am describing an efficient way to move database from one location to another location. Following option only works if database recovery modal is Full or Bulk Logged. Let’s see how to move database in following scenario.

Suppose database needs to be moved from Server-1 To Server-2, Total size of database is 50 GB or More. Database is online at Server-2.

Step 1 : Take Full Backup Of Database

Step 2 : Move Full Backup Of Database to Server – 2

Step 3 : Restore Database At Server – 2 With No-Recovery Option

---Down Time Starts Here

Step 4 : Disconnect All The User From Server – 1 Database. Make sure no user is accessing the database at Server – 1.

Step 5 : Take Transaction Log Backup of Database

Step 6 : Move Transaction Log Backup To Server – 2

Step 7 : Restore Transaction Log Backup At Server 3 With Recovery Option

Step 8 : Finish and Your Database is Up and Running at Server – 2.

So as you can see that down time start only when there is only a log backup pending to be restored, Since log backups are small in size thus can be moved and restore with small amount of time.

Thursday 10 November 2011

Database Backup At Multiple Location Using Mirror In SQL Server

SQL Server 2005+

At times we come across issue where we need multiple copies of database at different places. Many people end up taking single backup of database and making required copy of backup and moving them to required location using some cmd or script. But now this can be achieved using MIRROR with backup command. Mirror option allows you to create maximum 4 copies of database backup at a time.

Create Database with Following Script.



Create Database MakeMultipleCopies
Go

Take Backup With Mirror Option.



Backup Database MakeMultipleCopies To Disk='D:\Copy1.Bak'
Mirror TO Disk ='E:\Copy2.Bak'
Mirror to disk='C:\Copy3.Bak'
Mirror to disk='D:\Copy4.Bak'
With Format

Output :

image

As I mentioned earlier we have max limit with mirror we cannot use more then 4 backup mirror. Let’s try it and see what happens when we use 5th mirror. See Following Screen.

image

So, You can see that the error message clearly says only 4 are allowed. So in all its really cool to have multiple backup with single backup statement without putting effort of copy and pasting it manually or using scipt or command.

Wednesday 9 November 2011

How To Get Comma Separated Value In SQL Server.

In the earlier post we have seen use of xml path to get comma separated result. This post describes how to get comma separated value from a result set or a table data.

Create Table With Following Script.



Create Table CSData
(
Id Int Identity(1,1),
DepartmentName Varchar(40)
)
Go

Insert Into CSData(DepartmentName) Values('IT'),('FINANCE'),('MEDICAL'),('TRANSPORT'),('TALENT TRANSFORMATION'),('IMG')
Go

You have following data.

image

Now we need comma separated departmentnames.

image

So its quite easy to have comma separated list. I have used stuff to removed first comma from the result.

The media family on device is incorrectly formed. SQL Server cannot process this media family

This blog post deals with the problem of restoring SQL Server Database from higher version to lower version. We generally encounter error following error.

Error while restoring database from SQL server 2008 to SQL server 2005

Msg 3241, Level 16, State 7, Line 1

The media family on device ‘<backup path>’ is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

So, There is option where you can restore database from higher version to lower version.

  • Generating Script With Database : This feature enables you to create version compatible script of database. you can also generate script of database along with structure. See following screen shot step by step.

On Object Explere :  Right Click Database –> Tasks —> Click Generate Script

image

It opens a wizard for you which begins with introduction page which provides quick brief info about script wizard.

image

Click Next to go to next page.

image

In this page you can choose specific object of database for those you want to generate script. since we need complete data. So will choose option “Script entire database and all database objects”

image

This page is scripting option page. In this page you can provide file name and path where script will be saved. You have multiple option as you can save script to a file or copy the script to a clipboard or directly move it to query window. In Order to generate script with data and lower version compatible script you need to set some option in advance option. See following screen.

image

In advance scripting option you can define “Type of data to script” I have chosen “Schema and Data”  and Script for Server Version : you have Option from SQL Server 2000 to SQL Server 2008 R2. So You just need to select required version and click ok and press next on parent screen.

image

On clicking next you get summary screen which tell you about the options you have selected for generating script it just kind of verification and cross checking and now click ok and you are done with scripting.

image

Green symbols are indication that script has been generated without any warning or error. Now you can click finish and run you script in your target server.

 

Note : If you have VLDB ( Large Size Database) then you can use BCP. In order to move data.

Monday 7 November 2011

How to find list of stored procedure with content info in SQL Server

This post describes the how can you find list of stored procedure with specific text content or how to answer following question.

  • List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement.
  • List All Stored Procedure, Function, Views, Triggers which uses temporary table.
  • List All Stored Procedure, Function, Views, Triggers which uses a particular table.
  • and Many More.

There is a system table which contains information about stored procedure,view and trigger contents “syscomments

List All Stored Procedure, Functions, Views, Triggers which uses “Update, Insert or Delete Statement



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text Like '%update %' or text like '%insert %' or text like '%delete %'

List All Stored Procedure, Functions, Views, Triggers which uses temporary table.



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where Replace(Text,' ','') Like '%Table#%'

List All Stored Procedure, Functions, Views, Triggers which uses a particular table.



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Sys.Objects%'

List All Stored Procedure , Functions, Views, Triggers which contains a specific text



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Data%'

So “syscomments” is really helpful.

NOTE: Searching cannot be done when stored procedure function or View Are Encrypted.

Find Tables With Column Name in SQL Server Databases

This post describes how one can find list of all the tables where a column with a particular name exists in a databases. Many people come across this issue and searches for query. So here is the query with example.

I am using following query to find out all the table having name “Status” in master database.



Use master
Go

Select B.name as TableName, A.name As ColumnName,C.name as DataType
from Sys.columns A
Join Sys.objects B On A.object_id=B.object_id
Join Sys.types C On A.system_type_id=C.system_type_id
Where A.name='Status'--ColumnName
Go

Output :

image

So the above query lists all the tables having column with name “status” and their datatype.

Wednesday 2 November 2011

Is Data Always Sorted On Primary Key (Clustered Index) In SQL Server ?

Whenever we create a primary key on a table and we generally get data sorted on primary key defined. but that’s not always the case. Let’s examine it.

Create Table and Populate Data With Following Script



If Exists(Select * From sys.Objects where object_id=object_id('Records'))
Drop Table Records
go


CREATE TABLE [dbo].[Records](
    [Id] [int] IDENTITY(1,1) Primary Key  NOT NULL,
    [EmpName] [varchar](20) NULL,
    [AccountNo] [varchar](12) NULL,
    [IsActive] [tinyint] NULL,
    [Salary] [decimal](22, 2) NULL,
    [Department] [varchar](50) NULL
)
GO

INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department])
VALUES
('Kuldeep Bisht', N'012398760987', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Vishal Verma', N'987026749876', 3, CAST(6700.00 AS Decimal(22, 2)), N'BPO'),
('Rakesh Kumar', N'876894326754', 1, CAST(4500.00 AS Decimal(22, 2)), N'TESTING'),
('Sandeep Verma', N'786798564329', 1, CAST(9800.00 AS Decimal(22, 2)), N'IT'),
('Revinder Singh', N'999867543987', 1, CAST(7850.00 AS Decimal(22, 2)), N'TESTING'),
('Seema Singh', N'876549879898', 2, CAST(4560.00 AS Decimal(22, 2)), N'BPO'),
('Ashutosh', N'785640987567', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Devesh Gupta', N'776623458123', 2, CAST(6600.00 AS Decimal(22, 2)), N'TESTING'),
('Satinder Negi', N'768098723456', 1, CAST(3500.00 AS Decimal(22, 2)), N'BPO'),
('Meenakshi', N'009812346785', 1, CAST(9800.00 AS Decimal(22, 2)), N'BPO'),
('Manoj Pandey', N'767689900145', 3, CAST(7800.00 AS Decimal(22, 2)), N'IT'),
('Sanjay Rana', N'980765430974', 1, CAST(8800.00 AS Decimal(22, 2)), N'SALE')
Go

Since we have primary key on Id so data is sorted on Id columns when we pass select query.You can see in screen shot below.

image

Now Let’s Create a Non Clustered Index on Department Column



Create NonClustered Index NCI_Records_Id On Records(Department)
Go

Now pass select query on Table Again. You Get the Same Result.

image

 

But Now Let’s just select 2 columns Id and Department and see the resule.

image

Interestingly you can see that even after having Primary Key (clustered index) on Id  columns data is being sorted on non-clustered index. You must take care of this with using proper order by clause whenever sorting of data is required. Do not just rely on Primary Key.