-
Database Permissions extraction script
Dear DBAs, Few days back one of my junior colleague reported me that while performing database refresh, he extracted the permissions of database before refresh and applied the permissions back on it after restore but still all permissions were not applied correctly and user is reporting access related issues. I checked and found that the…
-
March towards SQL Server : Day 13 – SQL DBA Interview Questions Answers – Database Backups and Restore-4
Here I am posting fourth and last series of Backup/Restore interview questions. I am trying to cover most of the known errors and some concepts too. You may take a look at the other posts on Backup/Restore here. Day 10, Day 11 & Day 13 1) What are Partial Backups in SQL Server? Partial backups…
-
SQL DBA Cheatcode Part 3 : Few Important Queries for any DBA
In continuation of our DBA Cheatcode series :- I started this series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I…
-
10 Interesting things that ‘CAN NOT’ be done in SQL Server
I was just thinking about few interesting facts about SQL Server properties which are good to be known for a SQL Server DBA. You can not add data and log files in master and model database. You can not add User Defined File Groups in master, model and tempdb database. You can not change the recovery…
-
How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?
How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?
-
How to create Drop and Recreate Script of all Indexes in a database?
I found this interesting script once so thought of sharing with everyone. Special thanks to the Unknown-Creator of this script. This script generates the ‘create’ & ‘drop’ index script for each and every index in a database. I have tried this number of times and it does what is expected from it. What all you need…
-
SQL DBA Cheatcode Part 2 : Few Important Queries for any DBA
Hi Friends, From last few days, I was thinking about writing a series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I…
-
Common Human Errors as DBA (Part 3)
In continuation of the series of “Common Human Errors as DBA” – here I am posting Third Part of it. You may check others here. Part 1 & Part 2 Poor naming standards & Lack of documentation: This is a common mistake (not human error) that frequently happens. From the very beginning, a DBA…
-
How to enable remote connection to SQL Instances from single SQL Server Management Studio?
Problem : All SQL Server Instances in the Environment are not able to connect from central SQL Server management Studio. Resolution: Below steps need to be followed to enable Remote Connection : 1) RDP to the SQL Server on which remote access needs to be enabled. 2) Open SQL Server Configuration manager (Start>All Programs>Microsoft SQL Server>Configuration Tools>Configuration manager) 3)…
-
SQL DBA Cheatcode Part 1 : Few Important Queries for any DBA
Hi Friends, From last few days, I was thinking about writing a series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I…
-
How to get access on SQL Server if you don’t have “sa” password or sysadmin access?
PROBLEM DBAs need to have sysadmin access on SQL Server to support Databases effectively. There may come situations where DBAs neither have sysadmin access nor [sa] password. In that case, DBA needs to perform below activity to get the access. RESOLUTION PREREQUISITES: 1) Administrative privileges needed on Host Server. 2) Downtime of approximately 10 minutes…
-
Is tempdb configured correctly?
Let’s learn about tempdb and some of the best practices about tempdb. Before doing this, let’s talk about tempdb and its usage. A Tempdb is a system database that is available to all users who are connected on an instance of SQL Server. Tempdb holds temporary user objects like global/local temporary tables, temporary stored procedures,…
-
Common Human Errors as DBA (part 2)
You must have read my previous post (Part-1) about Common Human Errors as DBA and how to avoid them. Here is another post of the same series. Third error – Data or Log file Shrinking – sometimes, one may not be checking how much free space is available in data or log files and shrinking databases…
-
MAXDOP – What is best approach to use it!!!
The Maximum Degree of Parallelism (MAXDOP) setting will determine the degree to which SQL server will attempt to parallel process transactions which are received from the scheduler. As a general rule of thumb, OLTP environments with high transaction volume and repetitive queries should normally have a MAXDOP value of 1, as any benefit gained from…
-
Common Human Errors as DBA (part 1)
“Human Error” is not a new word. Most of the time if there is screwed up done by any one – these golden words are used. For sure, these are the Human Errors and its hard to control. Here I am trying to consolidate some common human errors that are done by DBAs at different…