:::: MENU ::::

Posts Categorized / Storage

  • Dec 10 / 2011
  • 0
dbDigger, SQL Server Training, Storage, System Stored Procedures

Logical and physical file names of SQL Server data and log files

SQL Server files have two types of file names.

Logical File Name

Logical file name of SQL Server data or log file is used to refer the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

Physical File Name/ OS File Name

Physical file name is the name of the physical file including the directory path. It belongs to OS  hence must follow the rules for the operating system file names.

Here is a simple practical to exactly mark both the types.

Create a database

When a SQL Server database is created, by default same physical name is granted to the file in folder as that of its logical name. Following T-SQL script would create a database with different and meaningful logical and physical filenames.


CREATE DATABASE [LogiPhysiFiles]
ON PRIMARY
( NAME = N’LogiName_data’,
FILENAME = N’F:DBs2k5PhysiName_data.mdf’)
LOG ON
( NAME = N’LogiName_log’,
FILENAME = N’F:DBs2k5PhysiName_log.ldf’)
GO

A database has been created with following file names

Logical data file name      = LogiName_data
Physical data file name     = F:DBs2k5PhysiName_data.mdf
Logical log file name        = LogiName_log
Physical data file name     = F:DBs2k5PhysiName_log.ldf

We may get information about files of this database by using Files option in properties of this database in SSMS

Logical and physical file names of SQL Server data and log files

For T-SQL use following script to get files information of this database

USE [LogiPhysiFiles]
GO

SELECT Name AS LogicalName, filename AS PhysicalFile
FROM sys.sysfiles
GO

Following result shows both types of file names

Get SQL Server files info through T-SQL

Or use system stored procedure

USE [LogiPhysiFiles]
GO

EXEC SP_HELPFILE
GO
  • Dec 10 / 2011
  • 0
DBA Interview questions, dbDigger, SQL Server Training, Storage, Transaction log files

Major types of SQL Server files

SQL Server databases have three types of files:

Primary data files

Primary data file is the starting point of the database. It points towards other files in the database. Every database has one primary data file. Recommended file extension for primary data file is .mdf.

Secondary data files

All data files other than the primary data file in a database are secondary data files. These are optional and may be added for further requirements. Recommended extension for secondary data files is .ndf.

Log files

Transaction log files hold all the log information used to recover the database and individual transactions.  At least one log file is required for each database. Recovery models of the database affect the growth of log files. Recommended extension for log files is .ldf.
It is notable that SQL Server does not enforce the recommended extensions, but for proper and standard environment these should not be changed.

  • Dec 05 / 2011
  • 0
dbDigger, Storage, System Administration, Windows 7

Use DiskPart utility for disk operations in Windows

After running FDisk utility on my laptop. I created a primary partition for windows 7 installation with intention to configure extended partition later along with logical drives on it. After completing the Windows 7 installation, i opened disk management snapin.
It was a bit confusing when i found no option to create extended partition for unused disk space. I was only able to create simple volumes configured all in primary partition.

Solution

After some research i came to know that Windows 7 uses a command line utility ‘DiskPart’ for disk operations with full access. It has more operational capabilities than that of commonly used disk management snapin. It can format, create/delete partitions, etc. DiskPart is also available in Windows 2000 and XP. It should be considered that disk management snapin lacks some operations on disk to avoid any data loss. However DiskPart provides full access for any operation without any prevention from data loss. So while using DiskPart utility extreme care is required.

How to Open

Open cmd, write diskpart and press Enter. Else directly enter DiskPart in run box or search box of windows. DiskPart console would be opened.

Using DiskPart for disk operations

How to operate

DiskPart is a powerful tool and can be operated through few simple commands on console. First of all have a clear idea of what to do. Then get its commands by entering ? on diskpart console. Full list of commands would be available along with short description. Also consider online product help for any guide line. For operations select the target disk. Simple type select disk 0 (as in my case it was disk no 0) and press enter. Disk number may be get from disk management snapin.

Select disk in DiskPart utility

Creating extended partition through DiskPart

In my case i was only required to create logical partitions of all available disk space other than primary partition. I typed create partition extended and entered. Size parameter may be provided, here it converted all available space to a extended partition.

Going Back to Disk Management Snapin

Further operations of creating logical drives may be performed in disk management snapin. So to be on safe side i used disk management snapin for creating logical drives and my disk was ready to be used.

  • Oct 27 / 2008
  • 0
dbDigger, Hardware and Platform, Performance Tunning and Optimization, Storage

Windows Server 2003 for SQL Server 2005 with 32-bit and 64-bit architecture

Windows Server 2003 is available for X86, X64 and Itanium platforms. SQL Server 2005 is available for Windows Server 2003 for 32-bit architecture, Windows Server 2003 for 64-bit architecture and Windows Server 2003 for Itanium. Clustering is greatly developed in Windows Server 2003.

Windows Server 2003 comes in a variety of editions, including

* Standard Edition
* Enterprise edition
* Datacenter Edition

The edition of window server 2003 that you choose depends upon number of required CPUs and amount of memory required. It is possible to use SQL Server 2005 32-bit on windows server 2003 64-bit, but you can not use SQL Server 2005 64-bit on windows server 2003 32-bit obviously.

Click here to read all posts related to Hardware and OS related to SQL Server

  • Oct 27 / 2008
  • 0
dbDigger, Performance Tunning and Optimization, SQL Server IO, Storage

Sequential and Random I/O for Databases

Sequential I/O is operation in which adjacent data is accessed on a disk drive. Track to track seek is performed and thus greater throughput is provided.

And in case of random I/O, disk head reads data scattered on various parts of disk. Different tracks are read that are not adjacent, so random movement of head causes degrade in performance.

Keeping in view the major characteristic of sequential I/O and random I/O, it is important to design database disk storage in such a way that maximum sequential I/O may be performed. Transactional log is a major example of sequential I/O in SQL Server environment. You should place a heavily used log file on one disk. And it is good approach to use just only one default log file. If you place multiple log files on same disk the access type will be changed from sequential to random I/O. Practically it is not possible to achieve all I/O as sequential I/O in a server based environment. Clients request different operations for different pieces of data. So the only solution is to map your data on multiple disks so that random access should be minimized.


Click here
to read all posts related to SQL Server storage systems and I/O operations

Pages:123
Consult us to explore the Databases. Contact us