Blog Archives

SQL Tip: Changing Database names along with file names (logical and physical)

Sometimes you need to rename a databases. Although it seems pretty easy to rename it on the surface. However for long term maintainability it is crucial that the underlying logical files and physical files also reflect the changes. Without the

Tagged with:
Posted in security, sql, SQL Server, tsql

SQL Tip: How to find SQL Server installation date and time

Getting right down to business… heres the script SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000 And for Eval versions — Evaluation Version Expire Date SELECTcreate_date ASInstallationDate, DATEADD(DD, 180, create_date) AS’Expiry Date’ FROMsys.server_principals WHEREsid = 0x010100000000000512000000 GO Reference: Pinal Dave (http://blog.sqlauthority.com)

Tagged with:
Posted in sql, SQL Server, tsql, Uncategorized

SQL Server 2016 new features & common performance issue of Tempdb Contention

Tempdb the “shared resource” or the “scratch pad” or even the “waste bin” as described by many names in SQL Server has of course a lot many important things to do but by very nature of any shared resource there

Tagged with: , ,
Posted in log, sql, SQL Server, Uncategorized

SQL Server Restoring backups from UNC Network paths and Recovery Time Objectives

Might sound very basic but I still come across colleagues who are confused on this topic. SQL Server management studio when restoring backups does no let you browse to network drives. That is why if quick workarounds are not known people

Tagged with: , ,
Posted in backup, sql, SQL Server

Usage data for SSRS reports to decide on reports to consider for re-engineering & retiring

You have a SSRS instance on either live or production and you want to find out which reports and most / least used. Luckily SQL Server maintains by default SSRS usage info in audit logs it maintains. Although you can

Tagged with: , , ,
Posted in sql, SQL Server, ssrs, tsql

Encrypt SQL Server connections

SQL Server connections can and should be encrypted where possible but must when passing through public circuits. The overview of the process of setting up SQL Server connection encryption is as follows. Get a certificate issued by the CA in

Tagged with: , , , , ,
Posted in security, sql, SQL Server

SQL Server Index and Statistics maintenance: Importance and a good solution with explanation (Ola hallengren’s IndexOptimize)

INTRODUCTION Index and Statistics need to be maintained regularly for optimum performance of a SQL Server database engine. The SQL Server core engine within the Query Optimizer part of it, uses Indexes and statistics to create a query plan to

Tagged with: , ,
Posted in sql, SQL Server, tsql

Running Diagnostic TSQL scripts remotely within and without SCOM- 1

Requirement: To be able to run a generic diagnostic information gathering script remotely via System Center Operations Manager on a SQL Server instance without having to start a separate session of SSMS and / or RDP and executing script. There

Tagged with: , , , ,
Posted in Development, Monitoring, SCOM, sql, SQL Server, tsql

Add local windows Administrators group to SQL Logins

Adding Windows groups (local or domain) based security is best practice as it can help you in a situation where you loose credentials potentially. You want to add Local Windows Administrators group to have SA or any other permissions in

Tagged with: , , , ,
Posted in Active Directory, security, sql, SQL Server, tsql

SSRS reports & URL / query string parameters passing

You have a parametrised report and you want to run it with a simple URL with non default parameter values. Here’s how you do it.Get the default URL of the report in report viewer. http://<ssrs server>/ReportServer/Pages/ReportViewer.aspx?%2fFolder1%2fFolder2%2fReport+File+Name&Param1=Value1&Param1=Value2&Param2=Value3

Tagged with: , , , , ,
Posted in sql, SQL Server, tsql