Explore DMVs
You can explore DMV’s within SQL Server Management Studio. A sample picture is provided below.
Also you can right click on any DMV’s and then select top 1000 rows how you do like every other table in your local databases. Below image provides information on selecting DM_OS_BUFFER_DESCRIPTORS DMV.
Dynamic Management Views
Microsoft have released many DMV & DMF for every release of its new SQL Server release. This has helped every administrator and developers a VERY BIG HAND in sorting out many problems. The days in writing our own scripts are now over and these DMV’s also provide support for almost all areas no matter whether it is to do with sessions, tables, indexes, Operating system, I/O. All are now taken care. Below I am providing some of the DMV’s that might be helpful for you.
• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_cached_plans
• sys.dm_exec_query_plans
• sys.dm_exec_sql_text
• sys.dm_exec_query_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_columns
• sys.dm_os_performance_counters
• sys.dm_os_schedulers
• sys.dm_os_nodes
• sys.dm_os_waiting_tasks
• sys.dm_os_wait_stats
• sys.dm_io_virtual_file_stats
• sys.dm_io_pending_io_requests
• sys.dm_io_cluster_shared_drives
Change Database Recovery Model
In this post, I am going to provide a simple demo on how to change the Database Recovery Model. For this example I have chosen the AdventureWorkdDW2008R2 database and you can download it from here.
Once you have opened your SQL Server Management Studio, expand the databases and right click on our AdventureWorkdDW2008R2 database and choose properties. The below image will appear. In here go to the Options tab from the left and then you can see “Recovery Model” options which has Full, Bulk-logged, Simple. You can simply select the required recovery model and click on OK button at the bottom of the pop-up box. That’s it, your recovery model will be changed.
Differential Database Backup
This post is to cover differential database backup in Microsoft SQL Server. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.
In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…
Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and selected “Differential”. If you click on the drop down box, you will then see options for other types of backups.
In “Backup set”, I am giving a name for the differential backup. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the default location.
Also you can use below T-SQL scripts to take backups instead of using GUI interface.
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N’F:\\AdventureWorksSimpleRM.bak’ WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N’AdventureWorksDW2008R2-Differential Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
NOTE: Full Database backup should be taken before taking differential backup.
Give a try at your end.
Full Database Backup
This post is to cover some of the backup options that we have. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.
In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…
Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and you see “Full” is selected. If you click on the other options you will then see options for other types of backups to be chosen.
Then I have highlighted “Backup set”, which means I am giving a name for it. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the provided default location.
You can also use below T-SQL Scripts to do the same manually instead of using GUI screens.
–Syntax for Full Database Backup
BACKUP DATABASE databasename
TO backup_device [ ,…n ] –Device and Location
[ WITH with_options [ ,…o ] ] ;
–Simple Script to take Full Database Backup
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\AdventureBackup.bak’
GO
–Craete a Full database backup by using WITH FORMAT to overwrite any exisiting backups and Create a new media set
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\\AdventureWorksSimpleRM.bak’
WITH FORMAT;
GO
Happy going.
Data Awareness Programme @ Stamford College
Following the success of Data Awareness Programmes conducted in India, took a huge step in conducting the same program in Kuala Lumpur – Malaysia at Stamford College on 21/04/2014.
This included a mixed variety of Students from various degrees with in Information Technology. This programme provided the analytical solution based on the data collected within Malaysia. This helped better understand the students to learn and visualize based on their already known information.
This session also included students, teachers & non-teaching staff to enlighten data & bring awareness related to personal, individual, education & social and this was again successfully delivered, by Dr. Subramani Paramasivam and his Malaysian based team members.
The audience were unbelievably surprised with the level of information provided about data and how to learn more about data and analyse and help themselves and their surroundings including friends, family, relations, and local people in and around. The attendees were amazed with the level of information that was provided for about 8 hours with easy to understand slides & related videos.
This programme was specifically targetted to the attendees by providing more information about Microsoft SQL Server Products, Microsoft Releases, Microsoft SQL Server Editions, Database Development, Database Administration and other Microsoft related database & BI related products.
The demos were mainly based on Power Map, Power Pivot, Power BI and the visualization technology on power map really amazed every single attendee in the auditorium.
This was also a good opportunity for Your SQL Man UK Ltd to better explain about the Microsoft products, certifications and awards at student level like MTA (Microsoft Technology Associate) & MSP (Microsoft Student Partner).
Students were very much looking forward for further awareness programmes in Malaysia and thrilled.
Stamford College Attendees
Stamford College Attendees
Stamford College Attendees
Dr. Subramani Paramasivam and Dr.Janet Lourds Rani
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Introduction by Dr.Janet Lourds Rani
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Data Awareness Programme Session delivery by Dr.Subramani Paramasivam
Speech by Dr.Janet Lourds Rani
Speech by Dr.Janet Lourds Rani
A slide in Data Awareness Programme sponsored by Your SQL Man Ltd
Sponsor Gift
Interaction by Stamford College Students at Data Awareness Programme.
Interaction by Stamford College Students at Data Awareness Programme.
Interaction by Stamford College Students at Data Awareness Programme.
Interaction by Stamford College Students at Data Awareness Programme.
Interaction by Stamford College Students at Data Awareness Programme.
Interaction by Stamford College Students at Data Awareness Programme.
YSM Malaysia team in Stamford College
YSM Malaysia team in Stamford College
A Creativity session for one of the Students at Stamford College
Student doing HANDS-ON lab Session
Student doing HANDS-ON lab Session
Student doing HANDS-ON lab Session
Student doing HANDS-ON lab Session
Student doing HANDS-ON lab Session
Stamford College Students enroling for Data Awareness Programme
Stamford College Students enroling for Data Awareness Programme
Stamford College Students enroling for Data Awareness Programme
Stamford College Students enroling for Data Awareness Programme
Stamford College Students enroling for Data Awareness Programme
Stamford College Students enroling for Data Awareness Programme
Dr. Subramani Paramasivam and Dr.Janet Lourds Rani
Catchup with Dr.Janet Lourds Rani with Dr.Subramani Paramasivam and YSM Malaysia Team
Dr. Subramani Paramasivam and Dr.Janet Lourds Rani
Clarifications provided to Stamford College Student
Clarifications provided to Stamford College Student
Attendees
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Student by Dr.Subramani Paramasivam
Participation certificate issued to Teacher by Dr.Subramani Paramasivam
Volunteer
Being a DataAP volunteer, you will be proud in serving every community people, who really need your time and expertise. A BIG THANK YOU for all volunteers who volunteered in the past and for all current and future volunteers.
Get to know more about volunteering by sending an email to info@dataap.org
DataAP Fundraising Volunteers:
DataAP fundraising volunteers, are the key people, who make Data Awareness Programme a successful one.
“You don’t just raise money, but improve the quality of every individual on this earth”.
Volunteer your time:
Your esteemed time is the indestructible investment that any individual can do. Join your hands with us to help delivering the Data Awareness Programme a successful one.
Volunteer by giving a Speech:
Your can utilize this opportunity to start speaking in events and improve your communication & delivery skills. Also you receive a recognition of “DataAP Speaker”.
SQL Server Memory Issue
Every one running SQL Server has always had the same issue of memory. Whatever the extra memory is given to windows, it is all eaten by the SQL Server alone. No matter what the process is going on, the memory takes much resources for all related to SQL Server. How can we control the memory issue. Well there are some possibilities from SQL Server that we can restrict the server to occupy the server memory.
–To check for the existing memory usage
use master
EXEC sp_configure ‘max server memory (MB)’
By default the server won’t display the advance options to enable this service. So follow the steps below to view the memory usage.
–To display advance option
USE master
EXEC sp_configure ‘show advanced options’, 1 RECONFIGURE WITH OVERRIDE
After this you can run the below query to see the usage.
use master
EXEC sp_configure ‘max server memory (MB)’
Then use this to change the server memory.
–To change the maximum server memory
USE master
EXEC sp_configure ‘max server memory (MB)’, 6144 RECONFIGURE WITH OVERRIDE
Then again you can check the usage using the query which we have see earlier.