The Young DBA

When we execute query select * from sys . dm_exec_query_stats it will show aggregate performance statistics of cached query plans of SQL Server. The details including one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. Details have two column SQL Handle and Plan Handle.

Impersonation is when ASP.NET executes code in the context of an authenticated and authorized client. By default, ASP.NET does not use impersonation and instead executes all code using the same user account as the ASP.NET process, which is typically the ASPNET account. There are 5 below steps by which we can establish Impersonation configuration in our secured application environment. 1.)    Creation of Application/Proxy user where Application is hosted. 2.)    Give appropriate access to the user. 3.)    Create Database Login user on database. 4.)    Authenticate User and provide credential on IIS. 5.)    Then Configure web.config on Application.

Shortcut is always 2 nd important things to learn any software if we started working and using in daily DBA or Development activity. Today I am sharing 3 Shortcuts of MySQL Workbench which I use always during work and last 2 query to find tables with primary and Procedures list.

Hello friend,  I have been drunk last week end ,, yeah 5 liter vodka.. Dont surprise this drunk was IT enable  drunk in Microsoft Delhi UG Meet . Still my eye not opening and thinking about meets. Like Bangalore Monthly UG Meet microsoft planned for Delhi UG also. Thease are all about through MVPs of NCR ( Deubug_mod , Sarabpreet sir , ahmed sir , brij sir and Aman sir ).. 

Hi friend,   These days I become little bit Lazy. The reason was my long leave from work. I visited my Hometown in Durga Puja. It was really very cheerful moment for me to meeting with parents and cousins. Please  don’t ask about my last 7 Days I was suffered with Eye-Flue. It isolated me becouse I was not  unable to use TV/Computer/Phone/Book, social media and SQL. After 6 days  now i become OK. Session of Diwali festival also comes up.  So I should wake up and write some Diwali special Query.  I started my SQL Server, opened SSMS and New Query Editor new problem ….. How to generate Running Average of Last two days?

We should not expect that we required to use my backups but we should always ready for worst cases scenario either DB or IIS Server’s Configuration… In previous version IIS 6.0   we were taking Meta base (IIS Configuration) backup from GUI but later on in new version (7.0 > ) this is removed and now we are using AppCmd utility of IIS manager Service. Today i want to share about AppCmd.

When we install any SQL Server Instance, all installation log we can study through its Boot Strap folder which we can find from the PATH C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log .  Like Below

Below are two generic errors in every version of IIS. If we deploy applications without few configuration or setting in Application Pool & ISPAI Extension these errors will comes up. There are few changes in IIS 7/ IIS 8 but process are same.  After New installation of IIS and before deploying any Application I do below configuration in IIS 8/7 to remove these error.   The requested content appears to be script and will not be served by the static file handler.  or An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

I am a big fan of SOL Server since start of my career and will be long life may be god knows..  My SOL added in my resume in 2014. No doubt SOL Server is reach in feature and capabilities than MySQL. But today I am sharing you about MySQL's great feature that impressed me related to MySQL Excel Integration. MySQL is really very simple and light application, easy to manage and implement. Yesterday I was working on Excel and I seen an extra add ins option in Excel for MySQL Workbench. I followed the option. Really it impressed me.. Just see below…

MySQL became part of my skill set in 2014 . After installation of any instance of MySQL Server in Production or Test environment 2 nd most task for any DBA is to connect the Database from Remote. While i was new with MySQL it played with me a lot to do this task from remote machine. I hang over for 3 days and ask many ppl and search many site. Did telnet, ip-config, firewall et………….. but resolve my problem in easy through below process… 1 st Grant Remote Permission on Database Server 2 nd Configure Connection at client workbench to server

Error Code: 1175  You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. above error came in MySQL Workbench 5.2. During executing update command on table using MySQL Workbench 5.2. while updating rows. update bl_user set user_email='smnjha989@gmail.com' where user_name='suman';

This summary is not available. Please click here to view the post.

Actually I should write this post before my last post Network shared Backup , which I shared yesterday. I have decided I will share this week on network database activity. Show this is my 2 nd post on the same. While I was started to backup, restore and creating a database on network shares I faced below errors. And most of the cases issues are related to Start-up services. Generally when we install SQL server the service will be Local or Network in this situation it will not work until we will not change start-up services or we will give full access to the network shared folder.  

Hi friends, This is my post on Backup-Restore on Network Shared drive. After 2 days holidays coming back on job is difficult task and today I am not well due to cold. I did some wrong with myself eaten sweets more and after that cold water. SO my throat is not working properly. Actually Every Enterprise is facing with Crunch of resources for running application and keeping backups. Sometime we require to restore database and we have no more space on local server drive. We all facing in our life as a SQL-DBA. If you have space on Shared drive then we can use it for our backup-restore task test activity.  I am sharing you how to do on Network shared drive below tasks but be sure SQL have proper access on such drive or shares :-

If we have a set-up of Transactional Replication for Data Distribution running and wanting to add new object to replication on other server we can follow below process. To add an article In Transaction replication with PUSH Subscription 

Error:  It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS." I faced with above error while implementing VB.net+MySQL application yesterday in the company where I am working. Actually this error will come when Virtual Directory is not being configured as an ASP.NET application. To do so we have to follow below steps :- 

In Short for Daniel of Service Attack ( DoS ) is a type of attack attempt to make a machine or network resource unavailable to its intended users .  Risks Associated with Denial of Service Attacks :- Network bandwidth Server memory Application exception handling mechanism CPU usage Hard disk space Database space Database connection pool

Source : Technet

Hi Folk, This is my post for beginner who are learning Computer Language or those who is having confusion about various kinds of Language. Basically a computer language is what computer understand and we use it to do application. Now-a-days there a many language developed which allows to develop application in according to our business requirement but most useful platform is web applications. So I have differentiating the LANGUAGE in 3 categories: 1.)    Programing Language 2.)    Mark-up Language 3.)    Scripting Language

In short Page File is simply a file on the hard drive that handles situations where the system wants to move or page out sections of memory .  There are several situations that cause this, but the one you're most concerned about is when the system is out of physical memory. Tech net  Article is best to configure & monitor Paging File.

NodeXL resolve our problem to creating Many to Many Relationship in according to data.  Last month our team got requirement from IT Director: How we can create many-to-many relationship graph based on data, automatically ? We search and tested many tools some online and some desktop installed but  no one worked fi9 and complete as per requirement. Few doesn't responded and few suggested to use paper and pen . But  Our Team Lead resolve this  requirement  through a simple tool that was NodeXL.

An 'Inner Join' is used to join operation in an applications, which creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of A and B are combined into a result row. We can create inner Join through various method in application. I want to share 3 ways which I used in various application. Implicit Join Method Explicit Join Method Cross Apply Method

Keeping updated production environment is an important task for any DBA. Updated Database Server allows to Secured and bug free environment and it helps to keep growing organization and there business process. To updated our production environment we should know few terms related to Database server maintenance activity.   

Q:- What are the Default Services in SQL Server 2012 ? If this questions is related for Interview then this can be tricky because as per configuration manager the default services related to SQL server are 4 (DB Engine, Full text, Browser and Agent Service). To check run   compmgmt . msc  from CMD Prompt and select SQL Server Configuration Property. Then List of Services will show like below

MS Excel is one of most popular package for every kind of professional in 21st century. I am using this application since 2005 on different ways. I have been worked on excel with Simple tool, Advance tool, macros and VBA applications features. Every Data and Database Professional should know about use of Excel and their features.

Friend Developer’s Day is one of important day for me which I never miss and C#sharpcorner bring again in NCR with legends of IT Gurus, who will teach us new technologies on 28 th June 2014. I am going to learn mongoDB with Asp.net.

SyncBackPro ,  Edit Plus ,  Dreamweaver and S MSgee PC SMS Gateway Server 4.2 are my older tools which i used between 2010 to 2012 in previous organization. Sometimes i am missing all those.

By default the no count setting will be OFF in sql server for any procedure execution. What is happening when we execute any procedure , Sql server connect and send back information to client and client to server. This allows client to show the record set along with message in different tab but this message transferring activity creates an extra over-head on network. We can reduce this overhead by turning   ON to no count so the procedure will get much better performance in execution.

 It was Monday 9 th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server. “MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..

Cleanup History and Maintenance Clean-up History related to Maintenance Plan. It is a bit confusing but they are totally different . If we are designing maintenance plan then we should also create cleanup history and maintenance clean-up history.   

Dear God,                 1st of all thank you so much, You have made me the part of your creature. You have stolen few things early from my life but no issue I Born B+. You are a great architect and designer, you have designed a beautiful world, human beings and planet. You have given me opportunity to look everything in database point of view. I am sharing with you what I observed. Our  fingerprint   is working as Unique Identifier Primary Ky . Atmosphere and sessions are working interdependent like referential Integrity . All the things you have been relate to each other and effecting to each other just like done proper Normalization . You have given two Authentication mode of life Good/Bad and Truth/False. Configuration and Resources of our planet is with Fixed mode, it’s not dynamic. Sometimes earthquake and disaster turning life into RTO. But god there is no any planning for Backup and DR of our limited Resourced Earth. We are using our resources quickly and

Now I am habituated to use CTRL+R to see the code in full screen because it allows me to hide the result or message pane from windows.  Working on SSMS for Development or testing any TSQL procedures I like too much and I am using this tool since 4 years.

After New Installation of SQL Server database will be in Full Recovery Mode by default. When our Application Goes Live and Transaction started processing we should have a proper Backup-DR plan otherwise We can face below error in SQL server in future. Because it is necessary to Truncate the TLOG in Full Recovery Mode.

Error - provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe "A mountain in labour and a mouse be result" .Sometimes it happens in finding Root Couse. I faced the same when I was new in organization. Web application was throwing error something like below …

Every organization have different work environment, culture and network infrastructure. After getting place in new organization I am learning new things and comparing all those about past windows, db , application & processes . 

When we are going to design an Entity Relationship Diagram 1st time using SSMS in SQL Server, we will face an error of authorization like below.

Last month i have been faced with a great question that was, how to give permission to any user for manage SQL Server Agent, create,edit and monitor jobs only? This permission or role is relevant to server and service level, So we have to give permission at System Database Level and for SQL Server Agent only one Database is relating that is msdb.   So i follow the steps to give permission…   1.) Create a new user sql or domain :  "SqlAgentUser"   2.) Now map the msdb database.   3.) Then give below permission form same box and finally click on ok a.        Db_owner b.       Db_ssisadmin c.        Db_ssisltduser d.       Db_ssisoperator e.       Dc_proxy f.       Dc_Operator g.        Dc_Admin h.       SQLAgentOperatorRole i.       SQLAgentReaderRole j.       SQLAgentUserRole     Now Connect to the Instance with the New User SqlAgentUser and credential. We will we have access to manage agent only non-other than db .    

Hi friends if you are going to collect data or information in excel sheet and you want to restrict users to some part of fields she/he does not change or edit than this article is important for you . as i always forgetting after . This is very interesting. 1.) 1 st of All Select entire sheet by left-top-corner of sheet or by select all option and then open format cell window by ctrl+1 or by right click on sheet. Then Check mark yes to Locked option. 2.) Now  Select those cells by ctrl or one by one and again go to format cell and then unchecked the Locked option like below. 3.) Now got to Protect workbook and give the password and re-enter password.  And now check by editing the sheet, you will not edit...

We can use following code. Sub Button64_Click() ActiveWorkbook.RefreshAll End Sub

Backup-Restore msdb..sp_delete_backuphistory '1-Jan-2005' msdb..logmarkhistory msdb..backupset msdb..backupfile msdb..backupmediaset msdb..backupmediafamily msdb..restorefile msdb..restorefilegroup msdb..restorehistory Index Related sys.indexes sys.dm_db_index_usag sys.Indexes sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.index_columns SQL Server Operating System sys.dm_os_sys_info sys.dm_os_performance_counters sys.dm_exec_requests sys.dm_exec_sessions Master..sysprocesses sys.dm_exec_connections sys.dm_os_waiting_tasks sys.dm_os_wait_stats sys.dm_os_nodes       SQL Server Agent Tables dbo.sysjobactivity dbo.sysjobhistory dbo.sysjobs dbo.sysjobschedules dbo.sysjobservers dbo.sysjobsteps dbo.sysjobste