LincolnBlog.net – …my observations in the world of SQL Server, PowerShell, .Net etc…

Properly Taking a Cluster Offline for Disk Maintenance

I recently needed to replace the older, slower attached disks on our clustered SQL instance offline so as to copy each drive to…

Some database design guidelines and best practices

Below are some best practices known within the SQL Server database community use when designing tables. 1. All data columns should be properly…

SQL documentation tools and permissions required

Database documentation tools like Devarts’ dbDocumenter will require, at a minimum, VIEW DEFINITION rights to the login:

USE myDatabase
GO

GRANT VIEW DEFINITION TO [DOMAIN\MyADGroup]

USE myDatabase

GRANT VIEW DEFINITION TO [DOMAIN\MyADGroup]

Views – 437

Random SQL tips and best practices

Kevin Kline posted on wait states here with some valuable insight: TROUBLESHOOTING SQL SERVER WAIT STATS A number of these are from Adam…

PowerShell Robocopy GUI Tool – PowerCopy

Here is a very cool PowerShell script that creates a Windows form front-end GUI for RoboCopy: PowerShell Robocopy GUI Tool – PowerCopy Views…

What is a .TUF file in the DATA folder?

The TUF files you see in the ..\DATA folder is a “transaction undo file” which contains modifications to the database that were not…

Bulk-copy SQL Server Agent jobs to new server

The easiest way to bulk-copy all SQL Agent jobs to a new server is to follow these steps 1. Load SSMS and connect…

Configuring SQL Database Mail

Here is a sample script for configuring database mail in the event you want to automate your SQL installs.

EXEC sys.sp_configure 'Advanced options', 1;
RECONFIGURE

EXEC sys.sp_configure;

USE msdb
GO
--Step1: Varifying the new profile

SELECT *
FROM sysmail_profile
--Step2: Verifying accounts
SELECT *
FROM sysmail_account
--Step3: To check the accounts of a profile
SELECT *
FROM sysmail_profileaccount
WHERE profile_id = 3
--Step4: To display mail server details
SELECT *
FROM sysmail_server

select * FROM sysmail_allitems

EXEC sys.sp_configure 'Advanced options', 1;

RECONFIGURE

EXEC sys.sp_configure;

USE msdb

--Step1: Varifying the new profile

SELECT *

FROM sysmail_profile

--Step2: Verifying accounts

SELECT *

FROM sysmail_account

--Step3: To check the accounts of a profile

SELECT *

FROM sysmail_profileaccount

WHERE profile_id = 3

--Step4: To display mail server details

SELECT *

FROM sysmail_server

select * FROM sysmail_allitems

…

My Microsoft Code Extensions

Here are the current set of MS Code extensions I use on a daily basis. Views – 985

Removing TempDB data files from SQL Server

I had a situation where a SQL Server 2016 install did not install correctly. The install completed but when I tried to move…