SQLYARD

Microsoft SQL Server data virtualization with PolyBase

Microsoft SQL Server data virtualization with PolyBase enables you to access data stored in Hadoop Distributed File System (HDFS) or Azure blob storage without moving or copying the data. With PolyBase, you can query and join data from Microsoft SQL Server and Hadoop by using the Microsoft SQL Server Transact-SQL language. You can also use

Microsoft SQL Server data virtualization with PolyBase Read More »

SSIS Catalog Object parameter information script

SSIS Catalog Object parameter information script The first query is filtered out for information and the second query is a select * gets you all and you can change to your needs USE SSISDB SELECT pr.name AS [ProjectName], pr.description AS [ProjectDescription], pr.last_deployed_time AS [ProjectLastValidated], pr.validation_status AS [ProjectValidationStatus], op.object_name AS [PackageName], op.design_default_value AS [DefaultConnectionString],pr.deployed_by_nameFROM [internal].[object_parameters] opINNER

SSIS Catalog Object parameter information script Read More »

Steps to rename a database in SQL Server

Steps to rename a database ( Few ways to do this, this is how I achieve this) Step 1 get the logical and physical name SELECT file_id, name as [logical_file_name], physical_nameFROM sys.database_files  Step 2–Disconnect all existing sessions.USE [master];GOALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGO –Change database into OFFLINE mode.ALTER DATABASE SET OFFLINE –Step 3–Log in

Steps to rename a database in SQL Server Read More »

Create a trigger on SQL Agent job status change.

USE [msdb]GO/*Create a trigger to send a notification on a status change */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO Create TRIGGER [dbo].[sqlagentjobstatuschange]ON [dbo].[sysjobs]FOR UPDATE AS SET NOCOUNT ON; –** Declare variablesDECLARE @username VARCHAR(50),@hostName VARCHAR(50),@jobName VARCHAR(100),@newEnabled INT,@oldEnabled INT,@jobCreated INT,@bodyText VARCHAR(200),@subjectText VARCHAR(200),@servername VARCHAR(50),@profileName VARCHAR(50) = ‘Add Profile’,@recipients VARCHAR(500) = ‘Add email Address’ –** Set Variables SELECT @username = SYSTEM_USERSELECT

Create a trigger on SQL Agent job status change. Read More »

Find Missing indexes

/*Returns a list of missing indexes that SQL Server believes (if added)would result in the highest anticipated cumulative improvementbased on query execution since the last restart*/SELECTCAST((migs.avg_total_user_cost * migs.avg_user_impact) *(migs.user_seeks + migs.user_scans) AS DECIMAL(20,3)) “ImpactFactor”,statement “Table”,N’CREATE NONCLUSTERED INDEX ix_’ +sys.objects.name COLLATE DATABASE_DEFAULT + ” + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”) + ISNULL(mid.inequality_columns,”), ‘[‘, ”), ‘]’,”), ‘, ‘,”) +‘ ON ‘

Find Missing indexes Read More »

Get drive letter (Device ID), Drive size, drive free space and drive free space %

Get-WmiObject Win32_logicaldisk -ComputerName ADD Your Server Name ` | Format-Table DeviceID, ` @{Name=”Drive Size(GB)”;Expression={[decimal](“{0:N0}” -f($_.size/1gb))}}, ` @{Name=”Drive Free Space(GB)”;Expression={[decimal](“{0:N0}” -f($_.freespace/1gb))}}, ` @{Name=”Drive Free pct”;Expression={“{0,6:P0}” -f(($_.freespace/1gb) / ($_.size/1gb))}} ` -AutoSize 

Get drive letter (Device ID), Drive size, drive free space and drive free space % Read More »

What is SQL Server Dedicated Admin Connection also known as the (DAC)

The SQL Server (DAC) was created if you are having critical performance issues. The (DAC) can help you research the issue. The (DAC) connects to a separate port by default 1434. The (DAC) port is assigned dynamically by the SQL Server at startup. The SQL Server Browser service also needs to be started. Once the

What is SQL Server Dedicated Admin Connection also known as the (DAC) Read More »

How SQL Server Transaction Log Backup, Truncate and Shrink Functions Operate

SQL server has become one of the most powerful database tools to use in the industry. Many of its functions are exceptional in terms of protecting your data, including the SQL Server Transaction Log backups, truncating, and shrink operations. This article looks at this operation, providing some examples to further explain the processes. To get

How SQL Server Transaction Log Backup, Truncate and Shrink Functions Operate Read More »