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…
-
-
Performance Tuning step by steps Series – SQL 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…
-
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. Note: It been moved to – http://whoisactive.com/ Run the stored procedure without any parameter parameters You can run the procedure without any parameter. It’s a default mode. Exec [sp_WhoIsActive] I used two table and method to load a data into a table – lightweight (minimal parameter) and heavyweight. Steps to load: Download the SP from website Create a SP in any of the DB…
-
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.…
-
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…