Rebuild and Restore Master Database in SQL Server

database - @SeniorDBA

The Master database is important to your SQL Server instance. This database records all the system-level information for your SQL Server system. This includes instance-wide metadata like logon accounts, endpoints, linked servers, system configuration settings, and records for the existence of all other databases and their file locations.

SQL Server cannot start if the Master database is unavailable. The core information of master database is recorded in a physical file called master.MDF files, and the transaction logs are stored on to the masterlog.LDF file. This means the all user and login details for the instance and the information about all the other databases on that instance are stored on the master database.

If anything happens to the master database you can’t start or use your SQL Server instance. As a Database or System Administrator, it is essential that you to know the symptoms that may indicate the corruption or damage in your Master database so you can troubleshoot those types of issues correctly.

Continue reading “Rebuild and Restore Master Database in SQL Server”

Advertisements

How to Create a SQL Server Logon Trigger

Login - @SeniorDBA

You may want to log the event each time a user logs into your database. This can be easily done by starting SQL Server Managements Studio (SSMS) and running the following code in Transact-SQL: Continue reading “How to Create a SQL Server Logon Trigger”

Transaction Log File Size and VLF in SQL Server

This article by Kimberly Tripp is very interesting. Simply put, she says you want the initial size of your transaction logs set to 8 GB, with auto growth set to 8 GB. This should help keep your Virtual Lof File (VLF) sizes below 512 MB, improve performance, and make maintenance during backups much faster.

VLF Image

The article, in part, reads:

First, here’s how the log is divided into VLFs. Each “chunk” that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

Continue reading “Transaction Log File Size and VLF in SQL Server”

Building Games Using SQL Server

You can build an interesting game with almost any computer language. Building a game using Transact-SQL seems to be the biggest challenge, but it has been done before. I wrote about this subject before, but there is now a new effort by Daniel Janik as described in this article.

Continue reading “Building Games Using SQL Server”

SQL Server Hall of Shame

Shame - @SeniorDBA

In a recent post by Adam Machanic, he asked his followers to send him the items they thought were the worst features of SQL Server. The list he developed is called the “SQL Hall of Shame”. He put together the following list:

  • In-Memory OLTP
  • English Query
  • Data Quality Services (DQS)
  • Master Data Services (MDS)
  • Auto-Shrink
  • Auto-Close
  • Auto-Grow
  • Notification Services (SSNS)
  • Query Notifications
  • Buffer Pool Extension (BPE)
  • Management Data Warehouse (MDW) / Data Collector
  • Lightweight Pooling / Fiber Mode
  • JSON
  • SQL Server Management Studio (SSMS)
  • Connect to SSIS from SQL Server Management Studio
  • DROP DATABASE IF EXISTS
  • Columnsets (and Sparse Columns in general)
  • SQLCLR
  • Utility Control Point (UCP)
  • Raw Partitions
  • Uninstall
  • Service Broker (SSB)
  • Not Freeing Allocated Memory Except Under Pressure
  • Database Engine Tuning Advisor (née Index Tuning Wizard)
  • DBCC PINTABLE
  • Virtual Interface Adaptor (VIA) Network Protocols
  • Mirrored Backups
  • SQL_VARIANT

If you read the article by Adam Machanic, you’ll get the detail for each item on the list.

Best Hacking Tools Of 2017: Nmap

Nmap, a simple Network Mapper, is a powerful port scanner tool. This free and open source hacking tool is the most popular port scanning tool around that allows you to easily perform network discovery and security auditing. Used for a wide range of services, Nmap uses raw IP packets to determine the hosts available on a network, their services along with details, operating systems used by hosts, the type of firewall used, and other information.

Nmap is available for all major platforms including Windows, Linux, and OS X.

We have written about how you can use this simple tool to find SQL Server instances on your network.

SQL Server Version Information

SQL Server - SeniorDBA

Microsoft’s SQL Server database engine has gone through various versions over the many years it has been one of the most popular solutions for database design. Some of the versions also support databases created under the older versions of the engine. This table helps you understand what support is available from those various SQL Server versions.

Product Engine Version Compatibility Level Supported Compatibility Levels
SQL Server 2016 13 130 130, 120, 110, 100
SQL Database 12 120 130, 120, 110, 100
SQL Server 2014 12 120 120, 110, 100
SQL Server 2012 11 110 110, 100, 90
SQL Server 2008 R2 10.5 105 100, 90, 80
SQL Server 2008 10 100 100, 90, 80
SQL Server 2005 9 90 90, 80
SQL Server 2000 8 80 80

You can get your current version information with this simple query:

SELECT databases.name, databases.compatibility_level from sys.databases 
GO

You can also get end-of-life information here.