The Young DBA

Source : Technet

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.

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

 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..

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 . 

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 .    

Contained Database Partially Contained Database User Defined Server Level Role Support for 15,000 Partitions Columnstore Index Online Index Create, Rebuild, and Drop Option for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns IntelliSence Feature Enhancements BI Semantic Model Sequence Object Distributed Replay Power View SQL Azure Support Big data support Reference :- http://www.mytechmantra.com/LearnSQLServer/New-Features-SQL-Server-2012-DBA.html http://mcpmag.com/articles/2012/03/14/top-12-features-of-sql-server-2012.aspx

It was very tricky question asked to me during one of DBA session and that time my answer was no Idea, but we can do this very easily with select command. TSQL select * from into ZFI_TAB_4_DAYBUK2 SAP .. MYAC . ZFI_TAB_4_DAYBUK where 1 = 2 ZFI_TAB_4_DAYBUK2 is new table ZFI_TAB_4_DAYBUK  is a source table We can also use SET FMTONLY to view metdata of table or query returns. SET FMTONLY ON ; GO SELECT      BUKRS , BELNR , BUZEI , GJAHR , BLART , LTEXT , CONVERT ( datetime ,LEFT( BUDAT , 4 )+ SUBSTRING ( BUDAT , 5 , 2 )+RIGHT( BUDAT , 2 )) as BUDAT , CONVERT ( datetime ,LEFT( BLDAT , 4 )+ SUBSTRING ( BLDAT , 5 , 2 )+RIGHT( BLDAT , 2 )) as BLDAT , BSCHL , GSBER , GTEXT , UMSKZ , DEB_CRE , DMBTR , WAERS , ACC_TYPE , SGTXT , SAKNR , TXT50 , KUNNR , CUST , LIFNR , VEND , XBLNR , TXT60 , BUSINESS_AREA , CONVERT ( datetime ,LEFT( CPUDT , 4 )+ SUBSTRING ( CPUDT , 5 , 2 )+RIGHT( CPUDT , 2 )) as CPUDT , VEND_CU

How to delete Records from a Very Large Tables ? If we have to delete a large records based on some conditions like year wise - month wise from very large table and our SLA is without degrading system performance then we will come into problem .We can't use TRUNCATE TABLE and we may run out of disk space and then DELETE query fails and nothing is deleted. The Real-Problem (from SQL Bible) A DELETE executes as a transaction where it will not commit until the last record is deleted. DELETE physically removes rows one at a time and records each deleted row in the transaction log. If the number of records in a table is small, a straight out DELETE is fine, however, with a large table the DELETE will cause the transaction log to grow. When this happens the system IO performance is degraded. Also, the table will be locked which will affect the application. The person executing the DELETE query will usually panic because what seems to be a simple task takes a long time an

What is the differences between Connection and Sessions in SQL-Server? The CONNECTION is the physical communication channel between SQL Server and the applications: the TCP socket, the named pipe, the shared memory . The connections show only external connections and each connection has an spid. select   a . spid   ,   a . nt_username   ,   a . loginame   ,   a . program_name   from   sys . sysprocesses   a   ,   sys . dm_exec_connections   b   where   a . spid = b . session_id A   SESSION   is a semi-permanent interactive information interchange, between two or more communicating devices  or between an application and users. We must open a connection in order to create a session. Normally there is one session on each connection, but there could be multiple session on a single connection. eg. If we connect to sql server using management studio then its one connection. once we connected we can  open 3 query windows then these are 3 sessions. Microsoft   SQL-

Dear Friends, Use of DMVs instead of SSMS GUI is always good sign of DBAs i understood and also trying to remove my bad habits by remembering all  DMVs,Sys Table,sys Functions & sys procedure etc..  On my daily activity every morning i check all database,there status and physical location using below TSQL/DMV.  use master SELECT     db.name AS 'DB_NAME', mf.name AS 'FILE_NAME', mf.type_desc AS 'FILE_TYPE', mf.physical_name AS 'FILE_PATH', db.state_desc,                       db.recovery_model_desc, mf.size, mf.growth FROM         sys.databases AS db INNER JOIN                       sys.master_files AS mf ON db.database_id = mf.database_id Happy Reading SumanJha_Accidental_Dba

How many Indexes we can create in a Table Friends, This is becoming a Frequently Asked Question in SQL Server Interviews now a days.  How many Indexes we can create in a Table ?  Ob ject SQL Server 7 SQL Server  2000 SQL Server  2005 SQL Server  2008 SQL Server  2012 Clustered Index 1 1 1 1 1 Non-Clustered Index 249 249 249 999 999 T otal 250 250 250 1000 1000 @SumanJha

Database Consistency Checker (DBCC) commands gives us valuable insight into what's going on inside SQL Server system. There are below DBCC Commands we use day to day Activity . D B C C INFORMATIONAL MAINTENANCE INTERNAL MEMROY DBCC HELP DBCC CHECKALLOC DBCC AUDITEVENT DBCC MEMORYSTATUS DBCC CONCURRENCYVIOLATION DBCC CHECKCATALOG DBCC LOCKOBJECTSCHEMA DBCC FREEPROCCACHE DBCC DES DBCC CHECKCONSTRAINTS DBCC CALLFULLTEXT DBCC FREESESSIONCACHE DBCC MEMUSAGE DBCC CHECKDB DBCC ADDINSTANCE DBCC FREESYSTEMCACH DBCC MEMORYSTATUS DBCC CHECKFILEGROUP DBCC SETINSTANCE DBCC PROCCACHE DBCC CHECKTABLE DBCC INCREMENTINSTANCE TROUBLESHOOTING DBCC UPDATEUSAGE DBCC CLEANTABLE DBCC DELETEINSTANCE DBCC CHECKIDENT DBCC BUFCOUNT(6) DBCC DBREINDEX TUNNING