Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2000’

SQL Server – How to Calculate Length and Size of any field in a SQL Table

Posted in LOB, Performance, Queries, T-SQL, tagged , , , , , on June 4, 2015| 7 Comments »

Recently I came across a situation where queries are loading extremely slow from a table. After careful analysis we found the root cause being, a column with ntext datatype was getting inserted with huge amounts of text content/data. In our case DATALENGTH T-SQL function came real handy to know the actual size of the data in this column.

According to books online, DATALENGTH (expression) returns the length of the expression in bytes (or) the number of bytes SQL needed to store the expression which can be of any data type. From my experience this comes very handy to calculate length and size especially for LOB data type columns (varchar, varbinary, text, image, nvarchar, and ntext) as they can store variable length data. So, unlike LEN function which only returns the number of characters, the DATALENGTH function returns the actual bytes needed for the expression.

Here is a small example:

Use AdventureWorksLT2012
GO
Select ProductID, DATALENGTH(Name) AS SizeInBytes, LEN(Name) AS NumberOfCharacters
FROM [SalesLT].[Product]

–Results

DATALENGTH

If your column/expression size is too large like in my case, you can replace DATALENGTH(Name) with DATALENGTH(Name)/1024 to convert to KB or with DATALENGTH(Name)/1048576 to get the size in MB.

Advertisement

Read Full Post »

SQL Server – How to Add More Than One Column as a Primary Key

Posted in Constraints, SSMS, tagged , , , , on September 17, 2013| 2 Comments »

Sometimes you might want to add more than one column as primary key. For example, if you have three columns named Last Name, First Name and Address and  there can be duplicate Last Names  or duplicate First Names but can never have duplicates in Last Name, First Name  and Address combined together

Adding columns to a primary key constraint using T-SQL script:

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID 
PRIMARY KEY CLUSTERED (TransactionID,[ProductID]);
GO

In order to add columns to already existing primary key, you need to drop the primary key constraint first to add columns later.
Drop primary key script is provided below.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive 
DROP constraint [PK_TransactionHistoryArchive_TransactionID]

Adding columns to a primary key constraint using SSMS GUI:

Right click on the Table Name and click on ‘Design’. Hold Ctrl key and select the column names that you want to add as Primary key. Then click on the ‘Set Primary Key’ as shown below.

1

The below screen shot shows three columns as primary key.

2

As a quick fact, the maximum number of columns that you can add to the primary key is limited to 16.

Read Full Post »

SQL Server – How to Perform Striped Database Backup & Restore Using T-SQL Command

Posted in Backup, Disk Space, T-SQL, Transaction Log, tagged , , , , on September 3, 2013| 1 Comment »

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB).

As shown in the picture below, striping is nothing but splitting one backup file to multiple backup files (maximum 64 files). However, these files may or may not be the same size (depends on the storage disks IO).

Striped Backups

By Striping a backup we can:

  • Increase backup throughput and reduce the backup time window
  • Allow backups & restores to be written or to be read from all devices in parallel
  • Enable backup to different disks, thus distribute the space usage

Below are the simple T-SQL backup commands using AdventureWorks2012 sample database as an example.

T-SQL command for Striping a database backup

Note: In the below script, I used only Disk C to contain all the striped .bak files. However, we can  direct to multiple disks if required

-- Striped Backups -- Backup to multiple files - 4 files in this case
BACKUP DATABASE [AdventureWorks2012]
TO 
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL command to restore from Striped database backup 

--Restoring from striped backup -- from multiple files
RESTORE DATABASE [AdventureWorks2012] 
FROM  
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO

Also, we can apply the same striping concept on Log backups. Below is how we do it

T-SQL command for Striping transaction log backup

--Striped Log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn', 
DISK='C:\AdventureWorks2012_2.trn', 
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

Demo: Normal Backup Vs Striped Backup

Below are the results and screenshots from a live production environemnt. This once again proves striping backup files increase data transfer rate and reduce the time to backup

Results:

Backup Type Time to Backup Data Transfer Rate
Normal Backup (1 File) 537.6 Seconds 111.9 MB/Sec
Striped Backup (4 Files) 201.0 Seconds 299.3 MB/Sec

Screenshots:

StripedBackupDemo_1of2

StripedBackupDemo_2of2

However, the major downside of striping a backup is that if at-least one backup file is corrupt, restore operation cannot be performed using the other files.

Also, HERE is why I haven’t discussed striping backups using SSMS GUI.

Technical Reviewer(s): Hareesh Gottipati; Jaipal Vajrala

Read Full Post »

SQL Server – Why is the ‘sa’ Login Account Disabled & How to Enable ‘sa’ Login Account

Posted in Security, T-SQL, tagged , , , , on August 20, 2013| 11 Comments »

Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state?

SA Login

The reason is simple, sa login account is disabled out of the box (by default) in Windows Authentication mode. You have to enable manually to use it.

On the other hand, if you request Mixed Mode Authentication during installation, SQL Server Setup prompts you to set an sa login password. So sa login account is enabled in this case.

Enable/Disable sa login using SSMS GUI:

  • From the Object Explorer, expand “Security” and then expand “Logins” –> Right click on sa and select “Properties”

SA Login - 1

  • On the “General” tab, create a strong password

SA Login - 2

  • On the “Status” tab, click “Enabled”, and then click “ok” (If sa is already enabled, you have to chose “Disabled” to disable the same)

SA Login - 3

Enable sa login using T-SQL script:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '$trongPa$$w@rD';
GO

Disable sa login using T-SQL script:

ALTER LOGIN sa DISABLE ;
GO

Here’s some more info about “sa” account:

  • System administrator (sa) is a special login provided for backward compatibility
  • Usually there is no effect sa being in a disabled state though it pertains and owns the system databases
  • By default, sa login is assigned to the sysadmin fixed server role and cannot be changed
  • Microsoft do not recemmond using sa login in application (as it is often targeted by malicious users)
  • However, Microsoft recommonds using sa only when there is no other way to log in to an instance
  • The sa login cannot be removed/deleted

Technical Reviewer: Jaipal Vajrala

Read Full Post »

SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information

Posted in Disk Space, Files and FileGroups, tagged , , , , on June 28, 2013| 28 Comments »

From a recent conversation with a customer, I noticed even some of the experienced DBAs are not very clear about the terms “unallocated space” and “unused space” and by doing a quick Bing search, found there are many SQL friends out there trying to get a clear correlation between these SQL Server terms: unallocated space, unused space, and reserved.

Today, let us try to get a clear picture (infact, a picture!) of these terms using sp_spaceused system stored procedure and at the same time understanding results for database size information.

Execute the below query on AdventureWorks2012 sample database

USE [AdventureWorks2012]
GO
EXEC sp_spaceused
GO

Results:

sp_spaceused

Now, let us understand the above result sets, lets check by the column names

  • database_size: database size (data files + log files) = 205.75 MB
  • unallocated space: space that is not reserved for use by database objects (Space Available) = 14.95 MB
  • reserved: space that is reserved for use by database objects = 190.05 MB
  • data: total space used by data = 97016 KB/1024 = 94.74 MB
  • index_size: total space used by indexes = 88048 KB/1024 = 85.99 MB
  • unused: portion of the reserved space, which is not yet used = 9544 KB/1024 = 9.32 MB
  • used:  let us coin this word to simplify things. say used = data + index_size = 94.74 + 85.99 = 180.73 MB

We now know what these columns are and the numbers they are returning. But, here is the real confusion part: How are they correlated with each other?

To answer this, below is the simple formulae I came up with to better interpret the results.  I haven’t found this simple explanation clearly anywhere else on SQL space (trust me, you won’t)

(color coded to read easy)

used = data + index_size
reserved = used + unused
database_size = reserved + unallocated space + log space

To cross verify, we can substitute the result set we got from AdventureWorks2012 database in the above formula and confirm by checking the log file size. Alternatevely, we can calculate the log file size, which is not returned from the ‘sp_spaceused’ result set.

180.73 = 94.74 + 85.99 –>TRUE
190.05 = 180.43 + 9.32 –> TRUE
205.75 = 190.05 + 14.95 + log space

Hence log space = 205.75 – (190.05+14.95) = 0.75 MB (which is right!)

And, below is my small art in the same color coding as above to illustrate in a picture format. Of course, who doesn’t love pictures, and I am no exception -:)

Suresh Raavi - Copy Right

Read Full Post »

SQL Server – How to Insert Explicit Values Into An Identity Column

Posted in Identity, tagged , , , , on June 2, 2013| 1 Comment »

By default, SQL Server automatically assigns a value to the Identity Column for each new row inserted into the table. However, if desired, we can insert explicit values into the Identity column when IDENTITY_INSERT property is set to ON.

Let us take an example from AdventureWorks2012 database. Table [HumanResources].[Department] is having DepartmentID as Identity column.

Now, if we want to insert a value into the DepartmentID which is an Identity column, we can achieve this by:

--Turning ON the IDENTITY_INSERT property
SET IDENTITY_INSERT [HumanResources].[Department] ON
GO

--Now insert a sample row into table with DepartmentID = 1001
INSERT INTO [HumanResources].[Department] (DepartmentID, Name, GroupName, 
ModifiedDate) -- MUST to specify the Identity column name 
VALUES (1001, 'SureshRaavi', 'Operations', GetDate())
GO

--Don't forget to turn OFF the IDENTITY_INSERT
SET IDENTITY_INSERT [HumanResources].[Department] OFF
GO

Note:
If we try inserting values without turning ON the IDENTITY_INSERT, we will encounter the below error message:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘Department’ when IDENTITY_INSERT is set to OFF.

However, only one table in a session can have the IDENTITY_INSERT property set to ON at any time. And, user must own the table or have ALTER permission on the table in order to do this.

Read Full Post »

SQL Server – Can a Database Have More Than One .mdf File?

Posted in Files and FileGroups, tagged , , , , on November 23, 2011| 2 Comments »

Does a database allows to create multiple mdf files? Let’s check here

Until recently I am on the assumption that a database can only have one data file with the *.mdf extension.

While checking the tempDB configuration on one of our production servers, I noticed tempDB having multiple data files with .mdf extension as shown in the screenshot below.

Can a Database have more than one mdf file 1

However, I expected the tempDB to be having only one .mdf file and multiple .ndf files, which is the way I have been seeing all these years.

To test it by myself (which I always prefer) I tried creating more than one data file with *.mdf extension on a database, and ofcourse YES, it accepted without any issues.

Reason: The primary data file shouldn’t necessarily have to be a .mdf extension

  • A primary data file is the data file associated when first created the database, and YES this can only be one

Does the .mdf/.ndf/.ldf file extensions really matter?

  • NO, SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, these extensions help us identify the different kinds of files and their use.

Microsoft’s recommended file name extension for primary data files is .mdf. Similarly for secondary data files is .ndf and for transaction log file is .ldf. However, I can have any extension (something like .xyz) for any file (primary/secondary/log). Please refer to the screenshot below for an example of database created with different FileName extensions.

Can a Database have more than one mdf file 2

So, how does SQL Server know the file we are adding is a data file or log file ?

  • If using GUI, in the File Type we mention either “Rows Data” or “Log”
  • If using T-SQL, ALTER DATABASE DbName ADD FILE… or ALTER DATABASE DbName ADD LOG FILE…

In Conclusion: Yes, we can create multiple data files with .mdf extension for a database either on an already existing filegroup or by creating a new filegroup. Generally speaking secondary files have a .ndf extension (not necessarily though!), but you can give it whatever extension you want, and it will work correctly.

Read Full Post »