Microsoft SQL Server 2008 R2 Service Pack 2

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 28-07-2012


Microsoft on july 26th announced that SQL server 2008 R2 service pack is now available to download. If you apply this service will upgrade SQL build version to 10.50.4000.0; Also visit this link to find what all bugs have been fixed in this service pack.



What Lenovo IdeaPad Y560 Laptop Can’t do?

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 03-01-2011


I rarely find time to play computer games, however when I do, I certainly go for Need for Speed racing game from EA. Considering high system requirements for now a days demanding games and with prime reason to run multiple virtual machines for SQL testing, I decided to go for Lenovo laptop for the second time. It was hard to choose but I was somehow confident with what I have chosen. Lenovo IdeaPad Y560 06465BU is a pretty decent size hardware to do most of stuff, Under the hood It has:

Welcome 2011

Posted by Hemantgiri S. Goswami | Posted in Uncategorized | Posted on 31-12-2010



Myself Hemantgiri S. Goswami, SQL Server MVP, I am extremely happy to join SQL Knowledge as an author again. In coming days I will be actively sharing an articles, scripts and tips here.

I would like to thank Deepak for inviting me to be a part of SQL Knowledge once again.


SQL Server Consolidation & Virtualization Practice..

Posted by Deepak Kumar | Posted in SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 22-12-2010



    image Consolidation by definition is the process of combining multiple SQL Server Databases & Servers running on different machines (could be geographically separated) onto a smaller number of more powerful machines in a central location. However in my opinion regarding SQL, its a process of getting organized, spending for what you need or use, and off-course saving at the same time. Lets see how consolidation and virtualization works together?

    Microsoft SQL Server since beginning is overall self maintaining, secure by default and self tuning DBMS that requires a very little or basic configuration during installation or later during usage, In a large Enterprise size organization its easier adaptability and usage can lead into uncoordinated installations, wasted hardware and licenses, lack of standards and security holes.

Think Again!  You should consider adopting Virtualization and Consolidation technique if you have similar questions:

  • Looking by your SQL Server inventory list, you realized that number of SQL servers are going up every month?

  • Looking by server utilization reports, you realized that your team or vendor over estimated database server hardware requirements and workload; leading into buying excessive hardware that your application is never going to use?

  • Every time it take months to plan & implement SQL patching, upgrades, installation etc, Applying SQL Server best practices and security updates/settings are complex and far of reach.

Better together Approach: You can consider three best possible scenario to implement, but are having its own benefits and concerns.

  • One OS/One SQLinstance- multiple databases
    Its the best that you can get, in simple words evaluate your application that uses a few database(s); move your databases to a shared database server. Concerns: shared cache, SA permission, similar name databases or objects and logins, maintenance window, remote desktop connection (rdp) etc

  • One OS/Multiple SQL Instance
    Build a server having multiple SQL Server instances for applications that require its own unique collation, SQL version & build, own dedicated memory/cache, tempdb etc. Concerns: SA permissions, remote desktop etc.

  • One OS/One SQL Instance (virtualization)
    For applications having extreme performance needs and unique set of configurations. build a server with multiple virtual Operating systems and each OS running SQL Instance.
    Software dependency: Microsoft Hyper-V, VMware, hp polyserve etc


      Consolidation & Virtualization benefits

      • Reduced Software & Operating System Licensing cost
      • Reduced Server Hardwar cost, fewer number of servers required
      • Datacenter space, Power consumption, Cooling Cost cut down (GreenIT)
      • Monitoring & Support cost; fewer resources needed to monitor/control/patch servers etc
      • Easier Server move-ability with scale up & scale out solutions
      • High availability option (depends on setup)


        • Single point of failure (But, you can implementing a good high availability solution to deal with this issue)
        • Takes time & efforts to consolidate (But, once setup year by year return in terms of savings)
        • Complex Service Charge Model. If your organization do the billing of services provided to various business units, then you may need to do complex calculation based on usage before billing to individual units.

          Milestones to Destination

          • Inventory: Prepare inventory of SQL Server hosted in your environment. You may consider using Microsoft Assessment and Planning tool (MAP)
          • Hardware Sizing: Document database server resource available on the server like CPU, Memory, Storage, DISKIO, etc
          • Hardware Usage: Identify database server usage/utilization over a period of time, prepare histogram of Peak, Low & average usage. You may use perfmon or 3rd party tools like VMware capacity planner.
          • Savings: Calculate server operational cost in current setup and compare with new consolidated & virtualized model.
          • Going ahead: You may want to ask some specific database related questions to application owners
            • Is it a vendor supported/provided SQL Instance with limitations or internal home grown application? and what is workload or capacity planning guidelines for future.
            • Any significant reason a physical server is required? or why physical to virtual (P2V) should not be done?
            • Can Databases’ from SQL Instance be clubbed/consolidated with other SQL Instance? what is frequency of database changes/deployment or downtime requirements.
            • Can SQL instance be upgraded to latest SQL Server version & build? as per virtualization standard in your environment?
            • What are high availability options implemented for the databases & SQL Service?
          • Best practices:

            • Its better to divide entire SQL Server inventory into multiple smaller sections. Example- Should look at creating 5 subset of 100 servers rather than going in for virtualization of all 500 servers in a single attempt. Apply learning, experiences and best practices in later subsets.
            • Use single machine with individual SQL Named Instance or VM for Production/Test/Development/Staging db requirements. (depends on application/environment)
            • Never ever, oversubscribe resources for your server on virtual platform
            • Calculate total server workload in virtualization model with real-time application and database workload in different scenario or timings.
            • Carefully choose virtualization technique and server hardware to implement

            Resources: Microsoft, VMware, MS PDF

            Microsoft SQL Server 2005 Service Pack 4 RTM

            Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 20-12-2010


            Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP4 . You can use these packages to upgrade any of the following SQL Server 2005 editions:

            Enterprise Evaluation

            SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.


            Get Notification as soon as SQL Services are restarted

            Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev | Posted on 17-09-2010


            Being production DBA and responsible for a dozen to 100+ SQL Servers running in your organization or datacenter, Sometimes, you must be wondering that how you can make sure you are fully aware what is going when you are not at work. There is an easy way to stay in touch with your SQL Server(s), you can create a SQL job on the server, relax and hope you never get such alarm. yup, as soon as SQL Services will restart, the first thing its going to do is send you an email for restart notification.


            DECLARE @UpTimeDays int
            DECLARE @SQLSvcStarted varchar(20)
            DECLARE @rc int
            DECLARE @msg varchar(1000)
            SET @UpTimeDays=(select DateDiff(D, CrDate, GetDate()) from master..sysdatabases where name='tempdb')
            IF @UpTimeDays = 0
            SET @SQLSvcStarted = (select convert(varchar(20), CrDate, 113) 
            from master..sysdatabases where name = 'tempdb')
            SET @msg='The SQL Services on <b>'+@@SERVERNAME+'</b> was restarted on <b>'+@SQLSvcStarted +'</b>'
            EXEC @rc = msdb.dbo.sp_send_dbmail
            @profile_name = 'SQLMailProfileName',
            @recipients = '',
            @importance = 'high',
            @subject = 'SQL Server Restart Notification!',
            @body_format = 'html',
            @body = @msg,
            @exclude_query_output = 1
            IF @rc = 1 RAISERROR('xp_smtp_sendmail Failed', 16, 1)

            List of Undocumented DBCC command..

            Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 10-03-2010


            List of Undocumented DBCC command..

            DBCC activecursors [(spid)]

            DBCC addextendedproc (function_name, dll_name)

            DBCC addinstance (objectname, instancename)

            DBCC adduserobject (name)

            DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)

            DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

            DBCC balancefactor (variance_percent)

            DBCC bufcount [(number_of_buffers)]

            DBCC buffer ( {‘dbname’ | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])

            DBCC bytes ( startaddress, length )

            DBCC cachestats

            DBCC callfulltext

            DBCC checkalloc [(‘database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

            DBCC checkcatalog [(‘database_name’)] [WITH NO_INFOMSGS]

            DBCC checkconstraints [( ‘tab_name’ | tab_id | ‘constraint_name’ | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]


            DBCC checkdbts (dbid, newTimestamp)]

            DBCC checkfilegroup [( [ {‘filegroup_name’ | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

            DBCC checkident (‘table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )

            DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])

            DBCC checktable (‘table_name'[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

            DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id, [batch_size])

            DBCC cacheprofile [( {actionid} [, bucketid])

            DBCC clearspacecaches (‘database_name’|database_id, ‘table_name’|table_id, ‘index_name’|index_id)

            DBCC collectstats (on | off)

            DBCC concurrencyviolation (reset | display | startlog | stoplog)

            DBCC config

            DBCC cursorstats ([spid [,’clear’]])

            DBCC dbinfo [(‘dbname’)]

            DBCC dbrecover (dbname [, IgnoreErrors])

            DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]

            DBCC dbreindexall (db_name/db_id, type_bitmap)

            DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

            DBCC dbtable [({‘dbname’ | dbid})]

            DBCC debugbreak

            DBCC deleteinstance (objectname, instancename)

            DBCC des [( {‘dbname’ | dbid} [, {‘objname’ | objid} ])]

            DBCC detachdb [( ‘dbname’ )]

            DBCC dropcleanbuffers

            DBCC dropextendedproc (function_name)

            DBCC dropuserobject (‘object_name’)

            DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number} | {‘CLEAR’, exception_number})

            DBCC errorlog

            DBCC extentinfo [({‘database_name’| dbid | 0} [, {‘table_name’ | table_id} [, {‘index_name’ | index_id | -1}]])]

            DBCC fileheader [( {‘dbname’ | dbid} [, fileid])

            DBCC fixallocation [({‘ADD’ | ‘REMOVE’}, {‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}, filenum, pagenum [, objectid, indid])

            DBCC flush (‘data’ | ‘log’, dbid)

            DBCC flushprocindb (database)

            DBCC free dll_name (FREE)

            DBCC freeproccache

            DBCC freeze_io (db)

            DBCC getvalue (name)

            DBCC help (‘dbcc_command’ | ‘?’)

            DBCC icecapquery (‘dbname’, stored_proc_name [, #_times_to_icecap  (-1 infinite, 0 turns off)]) Use ‘dbcc icecapquery (printlist)’ to see list of SP’s to profile. Use ‘dbcc icecapquery (icecapall)’ to profile all SP’s.

            DBCC incrementinstance (objectname, countername, instancename, value)

            DBCC ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )

            DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})

            DBCC inputbuffer (spid)

            DBCC invalidate_textptr (textptr)

            DBCC invalidate_textptr_objid (objid)

            DBCC iotrace ( { ‘dbname’ | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

            DBCC latch ( address [, ‘owners’] [, ‘stackdumps’])

            DBCC lock ([{‘DUMPTABLE’ | ‘DUMPSTATS’ | ‘RESETSTATS’ | ‘HASH’}]|[{‘STALLREPORTTHESHOLD’, stallthreshold}])

            DBCC lockobjectschema (‘object_name’)

            DBCC log ([dbid[,{0|1|2|3|4}[,[‘lsn’,'[0x]x:y:z’]|[‘numrecs’,num]|[‘xdesid’,’x:y’]|[‘extent’,’x:y’]|[‘pageid’,’x:y’]|[‘objid’,{x,’y’}]|[‘logrecs’,{‘lop’|op}…]|[‘output’,x,[‘filename’,’x’]]…]]])

            DBCC loginfo [({‘database_name’ | dbid})]

            DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})

            DBCC memobjlist [(memory object)]

            DBCC memorymap

            DBCC memorystatus

            DBCC memospy

            DBCC memusage ([IDS | NAMES], [Number of rows to output])

            DBCC monitorevents (‘sink’ [, ‘filter-expression’])

            DBCC newalloc – please use checkalloc instead

            DBCC no_textptr (table_id , max_inline)

            DBCC opentran [({‘dbname’| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

            DBCC outputbuffer (spid)

            DBCC page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])

            DBCC perflog

            DBCC perfmon

            DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})

            DBCC pintable (database_id, table_id)

            DBCC procbuf [({‘dbname’ | dbid}[, {‘objname’ | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]

            DBCC proccache

            DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])

            DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

            DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])

            DBCC rebuild_log (dbname [, filename])

            DBCC renamecolumn (object_name, old_name, new_name)

            DBCC resource

            DBCC row_lock (dbid, tableid, set) – Not Needed

            DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

            DBCC ruleon (  rulenum | rulestring } [, { rulenum | rulestring } ]+)

            DBCC setcpuweight (weight)

            DBCC setinstance (objectname, countername, instancename, value)

            DBCC setioweight (weight)

            DBCC show_statistics (‘table_name’, ‘target_name’)

            DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])

            DBCC showdbaffinity

            DBCC showfilestats [(file_num)]

            DBCC showoffrules

            DBCC showonrules

            DBCC showtableaffinity (table)

            DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid [,option]}})

            DBCC showweights

            DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])

            DBCC shrinkdb  is no longer supported. Please use shrinkdatabase instead

            DBCC shrinkfile ({fileid | ‘filename’}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

            DBCC sqlmgrstats


            DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]

            DBCC tab ( dbid, objid )

            DBCC tape_control {‘query’ | ‘release’}[,(‘\\.\tape<n>’)]

            DBCC tec [( uid[, spid[, ecid]] )]

            DBCC textall [({‘database_name’|database_id}[, ‘FULL’ | FAST] )]

            DBCC textalloc ({‘table_name’|table_id}[, ‘FULL’ | FAST])

            DBCC thaw_io (db)

            DBCC traceoff [( tracenum [, tracenum … ] )]

            DBCC traceon [( tracenum [, tracenum … ] )]

            DBCC tracestatus (trace# [, …trace#])

            DBCC unpintable (dbid, table_id)

            DBCC updateusage ({‘database_name’| 0} [, ‘table_name’ [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]

            DBCC upgradedb (db)

            DBCC usagegovernor (command, value)

            DBCC useplan [(number_of_plan)]

            DBCC useroptions

            DBCC wakeup (spid)

            DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)

            © 2010 Increase your website traffic with