How to check the Cluster Size for a drive or Allocation unit size for a drive?

As documented on BoL, the recommended cluster size (Allocation Unit Size) for Sql Server files is 64K. For Windows drive, the recommended (or the default) is 4K. Let’s say you log into a new server and all of this is already configured, how do you query or figure out what was the allocated unit size (or Cluster size) of each drive?

There are two ways:

Method 1: Go to my computer >> right click on a drive letter >> Format >>

In the resulting window, you can see the allocation unit size already used for a given drive.

Allocation Unit Size From MyComputer

Method 2: Using command like commands.

This MSDN article has more details about this command, but the one I use is this:

fsutil fsinfo ntfsinfo t:

This spits out a bunch of information about this drive and the one you are looking for is under the name ‘Bytes Per Cluster’.

Allocation Unit Size From Commands

Hope it helps,

_Sqltimes

Read Full Post »

Sql Server Aliases

There is a quick and easy way to set up Aliases for long and boring Sql Server Instance Names.

Let’s say, I have a Sql Server Instance (name:Sandbox) on a machine with some long name like HPWPSVR3009DELL. Now to connect to this Sql Instance, through SSMS, you use HPWPSVR3009DELL\Sandbox. This is too long and too hard to remember. Add to this, custom port numbers and this will get even longer.

There is an easy to get around remembering these names and port numbers (no, without saving the credentials on SSMS).

Creating Sql Server Aliases

Sql Server Configuration Manager (SCM) allows you to set small and easy to remember aliases to the server you need to connect more frequently. Open SCM, go to ‘SQL Native Client 11.0 Configuration (32bit)’  >> Aliases >> ‘Create a new alias’

Creating Aliases

NOTE: If you have Sql Server 2008 or 2005 installed on your laptop, look for something like ‘SQL Native Client 10.0 Configuration (32bit)’ or something similar.

Now you are ready to connect to the same server with new alias. Open SSMS and try it…

Connecting Using Alias

Hope it helps,

_SqlTimes

Read Full Post »

Sql Server Start up Steps

When you restart Sql Server, it goes through several steps to make sure that the instance is up and running including the user databases. It is important to make sure that the data that was committed before restart is still intact. Rollback and roll forward are some of the steps it goes through for each database but there are several other steps that happen before it even comes to user databases. It is important to understand this sequence to employ better  troubleshooting techniques when faced with fatal issues.

As Sql Server goes through the start up process, ERRORLOG file is updated with its progress. Open ERRORLOG and you get an insight into the start up sequence.

(The sequence below is a simplified version of the start up process for ease of understanding)

• Microsoft Corporation and other machine details
  • These may seem obvious, but they come handy sometimes. The details about the machine on which the instance runs will be helpful.
• Windows process ID
  • Shows the process ID that the Sql Server service is assigned. For OS, Sql is just another service and each service has (along with a lot of other parameters)  a process ID and security context (user account assigned)
• Authentication mode
  • Shows if the instance is enable for mixed mode or just Windows Authentication
• Start up parameters
  • Indicates the file location for ERRORLOG file and master database MDF & LDF files. If you need to move the system database (master), you set the new location here.
    • -dE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;
    • -lE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;
    • -eE:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Number of CPUs available for this instance
• Amount of memory allocated.
• NUMA configuration
  • If multiple nodes are available they are indicated (node 0, node 1, …) with its CPU Mask.
  • On large systems, you can leverage NUMA configuration to balance the traffic for better performance.
• Now starts master database
  • Master database would be located in the path indicated in the above ‘Start up’ parameter
• Starts Resource governor
• Makes entry about FILESTREAM options
• Starts the default Sql trace
  • By default, this is located at “.\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log”
• Starts mssqlsystemresource database
  • This is a hidden database not available for direct querying. But the objects from this database form the basis for many system catalog views (and other objects)  in user databases.
• Loads self-generated encruption certificate
• IP address and Ports Sql Server is listening on for incomming connections. If configured to accept connections on Named pipes, necessary related information is displayed.
• Starts msdb databases
• Opens up ports to listen for DAC connections.
• Attempts to register SPN (Service Principal Name)  for Sql Server service.
  • For windows Sql Server is a just another ‘service’ and each service runs under a user security context. On start up, Sql tries to register its SPN to be used for Kerberos authentication. If it is not available, it creates an entry that it attempted and failed. This is not a problem, unless your services depend on this. More on this on a separate post.
• Now Sql is ready for incoming client connection requests.
• Starts model and tempdb
  • If there is any incorrect configuration with these databases you can start Sql Server in a ‘minimal configuration’  mode and troubleshoot further.
• Database Mirroring and Service Broker are enabled (if configured)
• Starts user databases
  • Each user database is restored. Any committed transactions are rolled forward  and uncommitted transactions are rolled back.

As you can see, I started this out to give a simplified version, but it evolved into a long short-summary. There are more steps that Sql goes through, but I omitted some of them to keep it simple to get a good glimpse of the overall picture. If you need more detailed understanding, please refer to BoL.

Hope this helps,

_SqlTimes

Read Full Post »

Sql Server does not start

Recently, we had a scheduled down time for a production server to perform some maintenance tasks. One of them was to move the tempdb to a dedicated drive of its own. This is was the first step and then implement a bunch of optimization steps. But guess what !! There was a bug (mistake) in the code prepared for this maintenance window.

Note: Before running any non-SELECT query (ex: ALTER DATABASE, ALTER TABLE, UPDATE, etc) on Production, write-out the complete code ahead and test it thoroughly on a Development/Staging environment.  In this case, even after doing all of that , somehow, this code-bug was not caught.

So, I ran the first query (ALTER DATABASE) and restarted the instance. But Sql Server would not come up. Production instance of Sql Server could not start !!! After banging my head on my laptop for a while and replaying the recent steps I performed, I realized the bug in the ALTER DATABASE query for tempdb.

To be exact, the mistake was this: I gave the same file name extension for both the data and log file for tempdb in the new location.

--
--  Incorrect Query
--
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'tempdev', FILENAME = 'E:\Data\tempdb.MDF')
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'templog', FILENAME = 'E:\Data\tempdb.MDF')
GO

--
--  Correct
--
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'tempdev', FILENAME = 'E:\Data\tempdb.MDF')
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'templog', FILENAME = 'E:\Data\templog.LDF')
GO

As you can see, same path and file extention was given for both the data file and log files !!  (No, it was not a copy-paste query). So, once I realized this, I took a step back and re-assessed the complete situation.

When Sql Server starts, it writes out its progress in ERRORLOG file. So, reading the ERRORLOG file shows the extent to which Sql start up was able to progress. On start up, Sql Server goes through a lot of steps. I’ll cover those steps in detail in a separate post, but for now, let’s look at the high level steps.

1. Shows the Edition and Version information and windows process ID
2. Indicated the registry start up parameters for location for master database and errorlog file.
3. Starts master, mssqlsystemresource, model, msdb and tempdb databases
4. Starts database mirroring and server broker protocols
5. Start user databases
6. …
7. (This is an over simplification, but a good high level picture relevant for our discussion)

So after running my incorrect query, Sql Server could not allocate files for tempdb as it was competing to create the same file twice in the same location. So it kept failing. There are very limited options to go about undoing it now.

A few weeks ago, I wrote a post that talks about moving system databases. In that I mention about some start up parameters that allow to start Sql Server in minimal configuration by limiting recovery to just master database.

• NET START MSSQLSERVER /f /T3608 or
• NET START MSSQL$instancename /f /T3608

So, I started a batch file with these start up parameters and immediately followed it with with correct ALTER DATABASE query for tempdb. So as soon as master database is started, I ran

--
--  Correct
--
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'tempdev', FILENAME = 'E:\Data\tempdb.MDF')
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'templog', FILENAME = 'E:\Data\templog.LDF')
GO

After correctly assigning files for the tempdb, Sql Server came up correctly.

Lesson re-learnt: Test and re-test any major code before running on production and write technical blogs to help yourself (if not others).

Hope this helps,
_SqlTimes

Read Full Post »

Recycle Sql Server Error Logs (or create new sql server error logs)

When Sql Server is restarted, along with a lot of other things, a new Sql Server Error Log file is created in its default location (…\MSSQL10.MSSQLSERVER\MSSQL\Log\). When you open that location, you’ll see abunch of ERRORLOG files with names like:

• ERRORLOG
• ERRORLOG.1
• ERRORLOG.2
• …

A new file is created after every restart with the name ERRORLOG. The old ERRORLOG file will now be renamed as ERRORLOG.1 and (ERRORLOG.1 is renamed to ERRORLOG.2…so on and so forth). These ERRORLOGs are just plain text files in this location …\MSSQL10.MSSQLSERVER\MSSQL\Log\ where new line items are appended at the end of the file. But when you open it through SSMS, you see it upside down where you see the most recent entry at the top. Sql Server reads the entire file using EXEC master..sp_enumerrorlogs and loads into a temporary table then

create table #err_log_tmp
(
 ArchiveNo int,
 CreateDate nvarchar(24),
 Size int
)
insert #err_log_tmp
exec master.dbo.sp_enumerrorlogs

If you look at the details of the error log file after restart, you will see something like this:

Sql Server Error Log

Once in a while this error log gets too huge and could cause delays in loading the entire file into temp table and then open it for viewing. In such cases, you can enable Sql Server to open a new log file without restarting Sql Server.

USE [master]
GO

EXEC sp_cycle_errorlog
GO

A new file is created with ERRORLOG as the name and the old file is renamed as ERRORLOG.1.

Interestingly, the details in the first part of the error log are slightly different from a new error log when Sql Server is restarted. Since we are just recycling the log file and not restarting the entire instance, it does not have all the steps that Sql Server goes though when a restart happens. It makes a note early on that this is not a restat, but a reinitializing of the ERRORLOG file. See the image below (after recycling or reinitalizing)

SqlServer ErrorLog After Recycle

Hope this helps,
_SqlTimes

Read Full Post »