SQL Server Database - showing and understanding properties

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Published:
Hi all,

It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database properties”. I am writing here a very brief explanation for each and every property of SQL Database and will discuss the details in future articles.

To show properties, you use SSMS and right click on the database name and select properties. A pop up window will be displayed with options (or pages) on the left and relevant information on the right. So, once you have it displayed, let’s start discussing the properties one by one.


1.  General Properties
 General PropertiesIn the above image you can see different properties as below:

Last Database Backup: It shows the Timestamp of last backup.
Last Database Log Backup: It shows the Timestamp of last log backup
Name : It shows name of Database
Status : It shows status of Database like (Online, Offline etc)
Owner : It shows name of owner of Database like (sa, dbo or any user)
Date Created : It shows creation date of Database
Size : It shows allocated space to Database.
Space Available : It shows available space to Database (Allocated space – Used space).
No of Users: It shows no of users those who have permission to Database.
Collation : It shows the current collation for the database. It is a big thing to understand. We will see in future series.


2.  Files
FilesIn the above image you can see different files (mdf, ldf, ndf) as below:

Main file (.MDF): It shows details of .MDF file like name of file with path, size of file and it’s growing options. The file holds the data for database like (Tables, SPs, Functions etc)
Log file (.LDF): It shows details of .LDF file like name of file with path, size of file and it’s growing options. The file holds the data of transaction occurred on database and its tables. i.e log of inserted data, deleted data and updated data.
Partition file (.NDF) : when you scattered you data to multiple files then SQL server created .NDF files.  The file also holds the data for database like (Tables, SPs, Functions etc)


3.  File groups
File groupsIn the above image you can see different file groups as below:

Primary : When you create a database it automatically creates primary file group for Data file and log file.
Secondary : You can create secondary file groups to help distribute and manage disk allocation for your database. When you partition your database in different file (.MDF and .NDF) then you can allocate some data to go to Secondary group.


4.  Options
OptionsIn the above image you can see different properties as below:

Auto Shrink : It shows the Flag of Auto shrink of data and log files.
Close Cursor on Commit Enabled : It shows the flag of cursor automatically close when commit transaction.
Default Cursor : Scope of cursor Global or Process level (In same SP, Function etc)
Database Read-only : It shows the Flag that database is read-only or in write mode.
Restrict Access : It shows that database has set multiuser access or single user.


5.  Change Tracking
 Change TrackingIn the above image you can see different properties as below:

Change tracking : It shows the database is enabled to track the changes occurred on database, table (SQL objects).
Retention period : It shows the cleanup period in numbers (1,2, …) to change history.
Retention period unit : It shows the cleanup period unit (days, hours, minutes) to change history.
Auto cleanup : It shows the database is enabled to clear the change history automatically.
So what is change tracking? If you enabled the database change track enabled then SQL server manage the history of each and every change occurred on data.


6.  Permissions
Permissions
In the above image you can see different properties as below:

It shows different grant options (Facets). Granter have to just select the user and check the check box for permissions which they want to assign.
I recommend you read up more about permissions using Books Online. It is a huge topic area.


7.  Extended properties
 Extended properties
In the above image you can see different properties as below:

Extended property is useful to right comment related to database.


8.  Mirroring
Mirroring
When the data is too sensitive (ie to lose) and you want high availability of your data then you have some option to achieve this. Mirroring is one of them.

In that data will be transferred from one server to another for each and every execution of query excepts (reading of data) like insert, update, delete as well as sql object creation script.

When ever user execute insert/update/delete data will effect the same thing on mirrored server. means you will get same data on 2 different server at a time.


9.  Log shipping
 Log shippingWhen the data is too sensitive (ie to lose) and you want high availability of your data then you have some option to achieve this. Log shipping is one of them.

It will not affect the another server at a time but will create image of data and ship that to remote server in time interval and update the remote database.

Means that both server databases are in sync but periodically. There may be a change you will not get updated data when you executing query but you get after execution of schedule of log shipping.



Hope this helps you understand some aspects of the different properties for your database.

Thanks,

Alpesh Patel
3
6,424 Views
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Comments (2)

Good one
CERTIFIED EXPERT

Commented:
Nice information shared dude !!!

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.