When using the SQL Server Migration Assistant for Oracle (OracleToSQL) to convert the tables in an Oracle database to tables in a SQL Server database; it automatically converts the Oracle NUMBER datatype to the SQL Server NUMERIC datatype. The Oracle tables used a column with the datatype and precision of NUMBER(10, 0) as the PRIMARY KEY. This column gets its […]
Find Your SQL Server Version
SQL Server usually comes in 4 versions: Standard Enterprise Express Developer To find the version of your SQL Server instance: Open SQL Server Configuration Manager Select SQL Server Services => right-click on the SQL Server instance service => select Properties In the Advanced tab, scroll to the Stock Keeping Unit Name to see the version
Powershell Script To Find Last Modified Files
Recently, I had a Windows Server whose E drive was close to filling up. The problem is that we couldn’t find which files were filling up the drive. It wasn’t the database or any other known program yet there were 80GB unaccounted for. In an attempt to determine which files have been filling up the drive, I created a powershell […]
SQL Server Corruption Checklist
You may already know how corruption happens and how to look for it. But what do you do when you actually find corruption? Well, firstly, you should not start with trying to repair the corruption because it may be that the storage system itself is corrupting the data. Any attempts to repair the database may cause further corruption. Your primary […]
Windows Server Shuts Down After 1 Hour
The physical server abruptly shutting down can be due to faulty hardware like a power supply or a failing hard drive. Or it can possibly be a response to overheating. The Systems Administrator is usually responsible for the hardware and will have monitoring tools in place to determine the problem. However, if Windows Server shuts itself down, this can sometimes […]
SQL Server Remote DAC (Dedicated Administrator Connection)
SQL Server reserves a single thread that can be used when the server is unresponsive or you cant remote desktop into the SQL Server machine. It is intended for EMERGENCY USE ONLY so that the DBA can run troubleshooting queries. This is called the Dedicated Administrator Connection or Remote DAC. You can enable remote DAC using SSMS or the sp_configure […]
Automating SQL Server Backups With Windows Task Scheduler
SQL Server Standard and Enterprise editions come with their own version of Task Scheduler called Maintenance Plans. However, this feature is not available for SQL Server Express Edition. If you want to automate the backups, you will need to use Windows Task Scheduler instead. Step 1 => Create the backup script You may write a simple backup script, such as: […]
Simple Backup Script
For production systems, use Ola Hallengren’s Maintenance Solution. However, if you want a simple solution that allows you to: Take all 3 types of backups: full, differential, and log backups Not have to deal with backup devices for each database The files are separated so they can be quickly copied to another drive using windows commands Specific backup files can […]
Dropping a User or Role with Objects
At some point, we get asked to delete a user or role for a particular database. For example, we want to drop the barney_user account from the company1 database. When we run the following script: We get the error: The database principal owns a schema in the database, and cannot be dropped. So, we decide to drop the schema as […]
Troubleshooting Missing Mail From Queue in SQL Server
When sending mail, SQL Server assigns a mail ID to the job e.g 1049: However, when viewing the Database Mail Log, you may not find it: In the image above, the last mail to be sent has the ID of 1039 which means there are 10 mails missing from the log. Use the following stored procedure to confirm that there […]