Below is the T-SQL script to change DB owner.
EXEC sp_changedbowner 'owner_name';
No comments
Tag: DBA
SQL Server – Enabling Service Broker
Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation”. Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.
--Enable Service Broker
ALTER DATABASE DB_Name SET ENABLE_BROKER;
--Verify status of Service Broker. A status of 1 means that it is enabled (0 means it is disabled)
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'DB_Name'
SQL Server – SQL Server Agent Job History
The other day, I was troubleshooting a database slowness issue when the lead application developer asks me whether or not there are any SQL Jobs running during that time. The T-SQL script below helped me to retrieve that info. This T-SQL script will check the SQL Agent Job history.
SELECT
j.name AS 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
WHERE j.enabled = 1 --Only Enabled Jobs
ORDER by JobName, RunDateTime DESC
PowerShell – Send SMTP Email and Add Message Body Sent from Script Using PowerShell
This is the first PowerShell script that I wrote. I was inspired to start using PowerShell after I met with one of our developers. He creates scripts to automate repetitive administrative tasks (e.g. daily system health check). I asked the developer if he learned writing PowerShell from a book or a training class. He said that he neither read any book nor attend a PowerShell training class. Instead, he told me that he just knows what he wanted to achieve and then he searched the internet on how to achieve it. In other words, he just Googled the codes.
I always wanted to learn PowerShell. As a DBA, I think it would be beneficial to use this powerful tool. I read books about PowerShell and did the exercises but I feel I never progressed and I’m still not able to create anything.
So I tried to do what the developer told me. First, I started to think what I want to achieve and that is to send disk space usage of our Database Servers to my email. This is so that I won’t have to login to our DB servers everytime.
Below is the PowerShell script.
#.SYNOPSIS
# Sends Daily Disk Space Usage Report
#
#EXAMPLE
#.\Send-DiskSpaceUsageReport.ps1
#
$smtpServer = "Fsmtp.our.property.com.sg"
$smtpFrom = "NXPOPARVRAPPLE@nhg.local"
$smtpTo = "valenciajec@ncs.com.sg"
$messageSubject = "OPAS TTSH Daily Disk Space Usage Report"
[string]$messagebody = ""
$logs = Get-Content D:\DBA\DB_Health_Checks_Node1\6.DiskUsage_Node1_/TTSH.txt, \\10.240.xx.xx\d$\DBA\DB_Health_Checks_Node2\2.DiskUsage_Node2_TTSH.txt
foreach ($log in $logs )
{
$messagebody = $messagebody + $log + "`r`n"
}
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($smtpFrom,$smtpTo,$messagesubject,$messagebody)
This script will add a message body to emails sent from script. I have a SQL Server Agent job that generates the text file that contains the disk space usage report. So basically, the PowerShell script will get the contents of the text file and add it to the message body of the email.
The next step is to create a Scheduled Task to run the PowerShell script.
Open Task Scheduler
Open Task Scheduler and Create a new task. Name it and set your security options. Check "Run with highest privileges" as our scripts need to run as admin. Choose “Run whether user is logged on or not.”
Set Triggers
Create your Action
Click on Actions tab and click New.
Action: Start a program
Program /script: PowerShell.exe
You don’t need to put a path as it should already be on your system
Set Argument
You first need to set the ExecutionPolicy. There are two options to do that either you set the ExecutionPolicy on the machine or on a per-script basis. I will only set it on a per-script basis but If you want to set the execution policy globally, you can issue this command from within PowerShell.
Set-ExecutionPolicy Unrestricted
To set the ExecutionPolicy on a per script basis, put the line below on the Add arguments (change the file name to your script’s file name.ps1)
-ExecutionPolicy Bypass -File D:\DBA\Scripts\PowerShell\Send-DiskSpaceUsageReport.ps1
Indicate the path of your script in the Start in(optional)
Change Settings
I encountered the error “Launch request ignored, instance already running (154257)”and the task failed to run. To resolve this issue, I changed the setting to “Stop the existing instance”
Save and Test
SQL Server – Get the Table Structure Using T-SQL
When you are writing a SELECT statement and you just want to limit the number of columns, you would use the sp_help stored procedure to research the underlying table structure as shown below.
The sp_help system stored procedure will return all the data about a particular table when you pass the table name into the stored procedure.
But If you don't want to sort through all that information, it is possible to get only the names of the columns by executing a query that selects all columns with a false WHERE clause as shown below.
SELECT *
FROM [HumanResources].[Employee]
WHERE 1=0
This query tells SQL Server to return all the columns and rows where one equals zero. Because one can never equal zero, SQL Server will return the column names from the table with no data.
SQL Server – Check Recent Backup of All Your SQL Server Databases
Do you have a recent backup of all your SQL Server databases? Use the script below to check if you have.
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name
SQL Server – Get Last Modified Function
SQL Server – Get Physical Memory of Database Server
Below is a script that you can use to retrieve your DB Server’s Physical Memory
CREATE TABLE #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
INSERT #SVer exec master.dbo.xp_msver
SELECT *
FROM #SVer
WHERE Name = 'PhysicalMemory'
GO
DROP TABLE #SVer
SQL Server – Get Transaction Log Size and Log Space Used
Use the following T-SQL script to get the transaction log size and log space used of your database.
SELECT DB_NAME(database_id) AS DatabaseName ,
CAST(( total_log_size_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS TotalLogSizeMB ,
CAST(( used_log_space_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS LogSpaceUsedMB ,
CAST(used_log_space_in_percent AS DECIMAL(10, 1)) AS LogSpaceUsedPercent
FROM sys.dm_db_log_space_usage;
SQL Server – Pending Disk IO Count
Below is a script to see the number of pending disk IO. The ideal value is 0. This value indicates the number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed.
SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os]
FROM sys.dm_os_schedulers
