SQL Server : To find data and log file size of all DBs

Query to find data and log file size of all DBs
================

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

SQL Server : Backup path, size

Query to check Backup path, size , time etc..

SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'db_name'
ORDER BY backup_finish_date DESC

SQL Server :: Clearing the plan cache for specific proc

It is very common to deal with situation where you have hundreds of procs in your database and you suspect  query plan is corrupted for one specific proc.

Following mentioned script will return plan handles and second script will help removing them one by one.

Please change database context accordingly.

**********************************************

1st, script to find all plan handles for one proc 

SELECT distinct cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%proc_name%';

2nd, script to clear cache just for this proc

SET NOCOUNT ON;
DECLARE @clear AS TABLE (id INT IDENTITY(1,1), p_handle VARBINARY(64))
DECLARE @minID INT = 0
DECLARE @p_handle VARBINARY(64) = NULL
DECLARE @SQL NVARCHAR(400) = '';

INSERT INTO @clear
SELECT distinct cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%proc_name%';

WHILE (SELECT COUNT(*) FROM @clear) > 0
BEGIN
SELECT @minID = MIN(id) FROM @clear;
SELECT @p_handle = p_handle FROM @clear WHERE id = @minID


SET @SQL = 'DBCC FREEPROCCACHE(0x' + CONVERT(VARCHAR(MAX), @p_handle, 2) +');'
EXECUTE sp_executesql @SQL
--PRINT @SQL

DELETE FROM @clear WHERE id = @minID

END;

SQL Server: Install the cluster management WMI objects on Non-clustered servers

There may be need to have cluster management WMI objects on standalone server like in my case , script needs these objects in order to collect clustered server details.

If server is not a cluster node, it doesn’t have cluster management WMI objects installed. In this case, we need to manually install the WMI objects. 

This is the procedure to install the clustering WMI objects:

On Windows 2012 Hosts:

- open a Powershell command or Powershell_ise and run the following command to install the clustering Automation Server only

  (msclus.dll only, does NOT install clustering support; just this management tool):

        install-windowsfeature RSAT-Clustering-AutomationServer

- additionally for managing via Powershell scripting, you may also want to install:

        install-windowsfeature RSAT-Clustering-PowerShell

Hope it will help

SQL Server :: T-SQL command that will determine / find the biggest tables in a database

It is very common to deal with situation where you have thousands of tables and want to find the biggest tables in your database. Following mentioned script will return row count, total consumed size per table so we can apply filters too to get only those table which satisfy our criteria ( for example , table which have more than 1 million rows) .

Please change database context accordingly.

**********************************************

SELECT

    t.NAME AS TableName,

    i.name as indexName,

    sum(p.rows) as RowCounts,

    sum(a.total_pages) as TotalPages,

    sum(a.used_pages) as UsedPages,

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND  

    i.index_id <= 1

GROUP BY

    t.NAME, i.object_id, i.index_id, i.name

ORDER BY

    (sum(a.data_pages) * 8) / 1024 desc

Hope it will help you .

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server :: Moving SSAS Database to a new drive on same server

As per business requirement, We wanted to move SSAS Cubes or databases to new location.

Here are steps.

1. Take backup of cubes 

2. Detach Cubes

3. Run set directory script -- Just replace values with actual values before running 

----------------------------------------------------------------------------

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object />

  <ObjectDefinition>

    <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">

      <ID>S123\##INSTANCE_NAME##</ID>

      <Name>S123\##INSTANCE_NAME##</Name>

      <ServerProperties>

        <ServerProperty>

          <Name>DataDir</Name>

          <Value>F:\mpdbs001\olapdb_##INSTANCE_NAME##\</Value>

        </ServerProperty>

        <ServerProperty>

          <Name>LogDir</Name>

          <Value>F:\mplog001\olaplog_##INSTANCE_NAME##\</Value>

        </ServerProperty>

        <ServerProperty>

          <Name>TempDir</Name>

          <Value>G:\mptmp001\olaptmp_##INSTANCE_NAME##\</Value>

        </ServerProperty>

      </ServerProperties>

    </Server>

  </ObjectDefinition>

</Alter>

------------

4. Run browse script 

---------------

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object />

  <ObjectDefinition>

    <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">

      <ID>S123\##INSTANCE_NAME##</ID>

      <Name>S123\##INSTANCE_NAME##</Name>

      <ServerProperties>

        <ServerProperty>

          <Name>AllowedBrowsingFolders</Name>

          <Value>G:\olapbackup_##INSTANCE_NAME##\|F:\olaplog_##INSTANCE_NAME##\|F:\mpdbs001\olapdb_##INSTANCE_NAME##\|F:\olaplog_##INSTANCE_NAME##_Encrypted\|F:\mpdbs001\olapdb_##INSTANCE_NAME##_Encrypted|G:\olaptmp_##INSTANCE_NAME##_Encrypted</Value>

        </ServerProperty>

      </ServerProperties>

    </Server>

  </ObjectDefinition>

</Alter>

--------------

5. Copy files to new location

6. Attach cubes

7. Verify all cubes are online

Now, Task completed and all cubes are moved to new location. Hope it will help.

Thanks

SQL Server : SQL Query takes too long to execute

Story start with a call as i was oncall that weekend. On Sunday evening , Application support team called DBA team and tell that one of the job which was working all fine till yesterday , today it is running since last 3 hours.  Normally, it takes approx 10 mins to complete. 

My reaction was, there may be blocking or any optimization job may be running (Usually maintenance job runs over weekend) . Let me have a look. 

I checked and found that indexes had been rebuilt on that instance and Resources utilization was also normal. There were no blocking at all. 

While i was analyzing , Application team told that Job got completed but we still need to analyse to avoid issues in weekdays. 

My second question was, whether there were any changes which were implemented recently. I got reply "Friday implementation added two new columns in a table which is part of query and job is an informatica Job". As it was informatica job, I asked for actual SQL code to get to know that whether problem is with SQL Server databases or with informatica server. 

I ran SQL code on prod DB to capture estimate and actual execution plans and get IO and CPU statistics. I concluded that stats are updated as estimate and actual execution plans are almost same. 

I decided to compare with non-prod DBs as Data volume is comparable in Dev and Prod both the environments . Query ran all fine on dev DB ( took less than 10 mins) however Execution plans are different.

While comparing both execution plans, i observed that key lookup is there on prod for same table on which 2 columns were added and that gave me clue.

I further checked the query and found that part where one column was mentioned in where clause but there was no index on it while other colomns which were part of lookup and index scan were being selected based upon colomn mentioned in where clause.

I added one covering indexes and added those 2 columns in include clause and that worked like a magic.

CREATE INDEX [IX_dsc] ON [dbo].[case_t] ([case_typ_dsc]) INCLUDE ([cd], [id]) 

While dealing with above mentioned scenario,I case across a awesome article written by Denny Cherry (One of my favorite)  and You should also read that. 

https://redmondmag.com/articles/2013/12/11/slow-running-sql-queries.aspx

Hope it will help.