Blog Archives

SQL Tip: Empty space release from large databases. Small chunks at a time

Many a time as DBAs and developers we come across trying to handle large or very large databases. Large is a relative term depending on experience, tools and infrastructure resources you are dealing with. But it can be a pain

Tagged with: ,
Posted in backup, SQL Server, SQL Tips, tsql

SQL Server intelligent Database compression script with error handling

In absence of a good script for Database compression that includes, Error handlings of all and any unforeseen kind Avoidance of “Sparse columns” Not attempting on already compressed objects Attempt Row compression if Page  compression fails Proceeding in case of errors

Tagged with:
Posted in SQL Tips, tsql

SQL Tip: Change owner on all Agent Jobs – TSQL code

Quick note. After creation of maintenance plans and agent jobs to be careful always replace owner with SA or any other more permanent account. This will reduce the chances of jobs failing due to any issues with accounts. DECLARE @name_holder VARCHAR(1000) DECLARE

Tagged with:
Posted in SQL Tips, tsql

SQL Tip – Comma delimited list from a table column

If you want to extract query result of a column to a variable as Comma separated values list use the following example and modify accordingly. declare @Dblist varchar (max) set @Dblist = ” select @Dblist = case when @Dblist =

Tagged with:
Posted in sql, SQL Server, tsql

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: Read + Write (or any) role on databases, all existing and new ones

Trying to avoid the double hop issue which configuring linked servers, I opted for local users. After setting them up I asked the developer who requested this as to which databases he wanted to be read across the servers /

Tagged with:
Posted in SQL Server, tsql, Uncategorized

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

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

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