Category: Scripts

  • Script : Disable all non sysadmin logins

    Script : Disable all non sysadmin logins

    Hello Reader, I am sharing a quick script to disable all non sysadmin logins for SQL Server. You can not disable windows groups so I tweaked this script to add Deny Connect for Windows Groups. This script is handy if you are also seeing the error below : Msg 15151, Level 16, State 1, Line…

  • Interesting Query

    Interesting Query

    Hi Guys! Today one of trainee in my team asked me one strange question, he shows a table and data – Then he asked me to get first 7th id row then 15th id row then 10th id row after this other rows- I tried few queries, Hope this will help you too- Solution1 : select * from…

  • Experiment With Nth Highest Or Lowest Salary Or Record in Sql

    Experiment With Nth Highest Or Lowest Salary Or Record in Sql

    I was reading about it so i searched it on so many blogs & websites. Each blog has described this for employees having unique salaries, but what if few employees have same salary. I am asking this because in an organization few people have same salary structure. Lets understand this with an example- –Create a table and fill…

  • SQL DBA Cheatcode : 15 Things about SQL Server Error logs

    SQL DBA Cheatcode : 15 Things about SQL Server Error logs

    Today I am writing about some good facts about SQL Server Error logs.   SQL Server maintains its own error logs that contain messages describing informational and error events. “Error logs” is just the name but it contains warnings, error, information messages. 1. How to View SQL Server Error Log file location EXEC xp_readerrorlog 0,1,”Logging…

  • SQL DBA Cheatcode : SQL Server properties

    SQL DBA Cheatcode : SQL Server properties

    In this article I am trying to outline about some important server properties which can help you out at the time of auditing and trace history of SQL Server. It has been checked in various SQL versions like SQL 2005, 2008, 2008 R2 and 2012. SELECT SERVERPROPERTY(‘BuildClrVersion’) AS [BuildClrVersion] , SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) AS ComputerNamePhysicalNetBIOS , SERVERPROPERTY(‘ServerName’)…

  • Backup of OLAP Database using Powershell

    Backup of OLAP Database using Powershell

    In this article I would like to explain another method to take the backup of OLAP databases via PowerShell. It will help out to take the backup of multiple OLAP databases with a specified retention period. In Tsql, XMLA function is applied however it is a bit puzzling to initiate the similar process for more than…

  • PowerShell : Copy a table from one server to another

    PowerShell : Copy a table from one server to another

    In general, a DBA like us gets requests to copy a table data from one SQL Server instance to another. In that case, a DBA first sees the amount of data present in the table and then he decides a method to transfer. Normally, a DBA selects Import Export method or SSIS and of course that is a suggested way to transfer data…

  • Script : Who deletes the databases?

    Script : Who deletes the databases?

    Hello Readers, Before starting, let me give you some background about this problem. I used to support few servers which had more than 1000 small databases. Worst part was that many users had access to create/delete databases. Many times, other users used to approach me asking “who has deleted my databases” It was a nightmare to…

  • SQL DBA Cheatcode Part 6 : Know your SQL Server

    SQL DBA Cheatcode Part 6 : Know your SQL Server

    Hello Readers, I am here adding few more feathers to our SQL DBA Cheatcode series hat. If you have not read our others blogs of this series, you may do it by clicking the links below or you may visit here again. SQL DBA Cheatcode Part 5 : Playing with indexes SQL DBA Cheatcode Part 4…

  • SQL DBA Cheatcode Part 5 : Playing with indexes

    SQL DBA Cheatcode Part 5 : Playing with indexes

    Hello Readers, Today, I am trying to put down few but very useful index related scripts. I have used these scripts many times with different versions of SQL Server. 1. Missing Index : Missing indexes are one reason why an SQL query takes longer (much longer) to complete. Here’s how to find out about them and…

  • Proactive Monitor of System Database Files

    Proactive Monitor of System Database Files

    Dear Readers, Today I am writing about a very common problem of Outages that are caused by system database data/log files growth. Problem: generally, we face this issue when system database file is filled up and it can impact to Business an unexpected downtime. This applies to those environments where monitoring/alerting mechanism is not configured properly or not…

  • SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA

    SQL DBA Cheatcode Part 4 : Few Important Queries for any DBA

    Dear Readers,   I am back with part 4 of Cheatcode series after gap of almost one month. In First three Parts of this series I covered, Few Generic Scripts, Replication Specific Scripts & Database Backup\Restore Specific Scripts.   In this Series, I try to cover those Scripts which we can use in our day…

  • Script : Who all have access to SQL Server via AD Groups?

    Script : Who all have access to SQL Server via AD Groups?

    Dear Reader, Few days ago, I was looking for a script to find out who all have access to SQL Server via AD Groups. There are a couple of good scripts available for this task. However I thought of re-inventing the wheel. You are free to modify and re-share it. This script works with SQL Server…