• DBA,  VLDB

    TempDB database is Full and Optimization

    What is TempDB and best practice for TempDB TempDB is the system database and it is per instance. It is a common and shared by all other databases. All the temporary activities are done here and yes, definitely the TempDB will become full and occupy more space depends on the temporary tasks, which we are running. There are many activities can happen in tempDB. Best practice, create a TempDB in separate disk with the estimated initial file size, those are old days and now most of us using disk array. The spindles and HDDS/SSDs are striped through RAID and shared across LUNs and pools, check with your infra team about the…

  • DBA,  Performance

    Performance Tuning Series – Main Part

      Performance tuning is a tricky part. We need to analysis all points of view. Sometimes it is an easy fix and can be solved in a minute and sometimes it take a day or more to find and fix. I received many emails for performance tuning help. It has a variety of questions, that how can we tune a query and how to find out what is going on in the server, since my application team is does not give a slow running code. The query only slowdown in some time period. My server is banging all time above 95% of resources. Nowadays, every business has a tool that…

  • DBA,  Performance

    Whoisactive capturing a SQL server queries Performance Tuning

      Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier. It’s a great script with lots of parameter and I am going to show, which are all more important. Run the stored procedure without any parameter parameters You can run the procedure without any parameter. It’s a default mode. Exec [sp_WhoIsActive]   Lightweight parameters @get_plans = 1 – It will show the execution plans of the running query. @get_task_info = 2 – It will show the wait type of the query. Ex: Parallel CXPACKET waits. With the additional info like, 3 times…

  • DBA

    Lost Administrator SA password SQL server 2012

    There are many blog posts talking about, how to recover “SysAdmin” password in case if it’s lost OR the server is new to you and no idea that has own and access to it. In my case the second option J In my opinion, it’s very easy to work on GUI instead of command line CMD 🙂 I used a SSMS.   Steps: 1. Stop the SQL server agent 2. Take the SQL server in single user mode, earlier version, it’s a bit hard All programs –> SQL server 2012 –> Configuration Tools –> SQL server configuration manager Stop the agent and take the SQL server into single user mode.…

  • DBA

    An application error occurred on the server running on SQL server

    Recently one of my application website went down.   I checked all the basic connectivity troubleshooting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state. Error from the event viewer:     The quick solution is rebooting the SQL browser (Start –>All programs–>Microsoft SQL server 200X–>Configuration tools –>SQL server configuration Manager) without rebooting SQL service. I searched and found a couple of MS links (KB-2526552 And SQLBrowser Unable to start) but, I did not apply it. I used another way that is also a permanent fix.   Troubleshooting ways and a permanent fix: For me it’s…

  • DBA,  DR/HA

    Create and setup a SQL clustering on your Desktop or Laptop

    How to create and setup a SQL clustering on your Desktop or Laptop   I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get a chance to do or to learn SQL clustering. Also it needs bit knowledge from out of SQL (Windows AD, Network and Storage (SAN)). In this post, I will give you an idea how to configure SQL failover clustering to yourself. We are doing everything in virtual environment (Single machine) the same you can do physically but you need all in…