Category Archives: Sql Server

Difference between SMALLDATETIME and DATETIME Data Types in Sql Server

Differences, Sql Server, , , , , ,

Both SMALLDATETIME and DATETIME Data Types in Sql Server are used for storing Date and Time values in Sql Server. Below table summarizes some of the major difference between these two Data Types.

[ALSO READ] DateTime vs DateTime2

SMALLDATETIME

DATETIME
FORMAT YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss.nnn
MIN Value 1900-01-01 00:00:00 1753-01-01 00:00:00
MAX Value 2079-06-06 23:59:00 9999-12-31 23:59:59.997
Storage Size 4 bytes 8 bytes
Usage SmallDateTime Example DateTime Example
Accuracy 1 Minute

Second’s values that are 29.998 seconds or less are rounded down to the nearest minute. And second’s values of 29.999 seconds or more are rounded up to the nearest minute. So seconds part value is always 00.

Example 1:
sql-smalldatetime-seconds-accuracy-1
Example 2:
sql-smalldatetime-seconds-accuracy-2

Rounded to increments of .000, .003, or .007 second
It means:

If time part in the date is 23:59:58.990 or 23:59:58.991, it will be stored as 23:59:58.990.

Example
sql-datetime-accuracy-1

If time part in the date is 23:59:58.992 or 23:59:58.993 or 23:59:58.994, it will rounded and stored as 23:59:58.993

Example 2:
sql-datetime-accuracy-2

If time part in the date is 23:59:58.995 or 23:59:58.996 or 23:59:58.997 or 23:59:58.998, it will be rounded and stored as 23:59:58.997

If time part in the date is 23:59:58.999, it will be rounded and stored as 23:59:59.000

ALSO READ

Error Logs Maintenance in Sql Server

Sql Server, , , , , , , , , , , , ,

Sql Server Error Log files contain informational messages, warnings, critical server events, auditing information etc. And this information in the log file is very critical for analyzing any Sql Server issues.

If proper steps towards the maintenance of the Sql Server Error Logs is not take then the error log file size may grow very large making it time consuming and difficult to analyze the issues. And also if proper maintenance is not taken care we lose the history of the error logs. This article explains the various maintenance strategies for the Error Logs.

In Sql Server by default there will be one current error log file i.e. ERRORLOG and six archived log files ERRORLOG.1 (i.e. Archive #1), ERRORLOG.2 (i.e. Archive #2), …. & ERRORLOG.6 (Archive #6). Each time the Sql Server restarts, the last Archive #6 (i.e. ERRORLOG.6) is deleted, Archive #5 is moved as Archive #6 (i.e. ERRORLOG.5 -> ERRORLOG.6), Archive #4 is moved as Archive #5 (i.e. ERRORLOG.4 -> ERRORLOG.5) and So on. Current Error Log (i.e. ERRORLOG) is moved as Archive #1 (i.e. ERRORLOG -> ERRORLOG.1). And a new current error log file (i.e. ERRORLOG) is created.

1. Recycling or Re-Initializing the Error Log file.

In Sql Server by default the Error Log files are recycled only when the Sql Server Service is restarted. Otherwise, it will keep appending the errors, warnings, information etc to the same current error log file i.e. ERRORLOG. If Sql Server service restart is a rare event then the current log file will be of very huge size.

Sql Server provides a mechanism where we don’t need to restart the service to recycle the error logs, instead we can execute the system stored procedure SP_CYCLE_ERRORLOG to reinitialize the error logs.

EXECUTE SP_CYCLE_ERRORLOG

Below image shows the Sql Server Error Logs folder view with the Error log files before and after recycling the error logs by executing the stored procedure SP_CYCLE_ERRORLOG

recycle-error-log-file-comparision

Below image shows the SSMS Sql Server Logs view before and after recycling the error logs by executing the stored procedure SP_CYCLE_ERRORLOG

recycle-error-log

So, it is a good strategy to create a Schedule Job which executes daily or at some frequency to recycle the error logs by executing the system stored procedure SP_CYCLE_ERRORLOG.

2. Increasing the number of Sql Server Error Logs files

By default as explained above Sql Server will have one Current Sql Server Log file and 6 Archived error log files. If we are recycling the error logs daily, then at any given point in time we will have the history of the error logs of the last 6 days excluding the current day.

Sql Server provides a mechanism where we can increase the number of archived error log files from the default 6 to any number between 6 to 99.

Follow the following steps to change the number of archived error log files.

Step 1: In the SSMS object explorer expand the Management folder and then right click on the Sql Server Logs folder and from the context menu click on the Configure option.

sql-server-error-logs-configuration-change

Step 2: In the Sql Server Error Logs configuration window as shown in the below image change the Maximum number of error log files from the default 6 to the desired number of error log files that you want to maintain.

change-maximum-number-of-error-log-files

You can as-well change the number of Sql Server Error Logs files by executing the following statement. I have tested it in Sql Server 2012 and it worked for me.

EXECUTE SYS.XP_INSTANCE_REGWRITE
	N'HKEY_LOCAL_MACHINE' ,
	N'Software\Microsoft\MSSQLServer\MSSQLServer' ,
	N'NumErrorLogs' ,
	REG_DWORD ,
	99

3. Limiting the Size of the Sql Server Error Logs file

In Sql Server 2012 on wards we can set the maximum size of the each Sql Server Error Log file. For example we can execute the following script in Sql Server 2012, to set the maximum size of each error log file as 10 MB. Setting this value to 10 MB makes sure that the error logs are recycled each time the current log file reaches the 10 MB size.

EXEC SYS.XP_INSTANCE_REGWRITE 
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'ErrorLogSizeInKb', 
	REG_DWORD, 
	10240

4. Changing the Sql Server Error Logs folder location

By default Sql Server ErrorLog files are located in the same drive/path which is used/selected during Sql Server Installation. You may like to store Sql Server Error log files in a drive or path which is other than the one used for Sql Server installation, to avoid filling up of that particular drive. To change the Sql Server ErrorLog files location you may like to read the article: How to change Sql Server ErrorLog files location

How to get User, System and All Databases name in Sql Server

Sql Server, , , , , , , , , , , , , ,

How to get all Databases name in Sql Server

We can write a query like below to get the list of all databases on an instance of an Sql Server. This list includes both the system databases as well as the user created databases.

SELECT name FROM SYS.DATABASES

RESULT:
get-all-databases-in-sql-server

How to get only the User Created Databases name in Sql Server

If you want to get only the User Created databases name from an instance of an Sql Server, then we can write a query like below. Here, I am filtering the know System Databases from the result. As far as I know Sql Server doesn’t provide any mechanism with which we can identify a database as a system database or user created database.

SELECT name AS 'User Created Databases'
FROM SYS.DATABASES
WHERE name NOT IN 
  ('master', 'model', 'msdb', 'tempdb', 'Resource',
       'distribution' , 'reportserver', 'reportservertempdb')

RESULT:
get-all-user-databases-in-sql-server-1

How to get only the System Databases name in Sql Server

If you want to get only the system databases name from an instance of an Sql Server, then we can write a query like below.

SELECT name
FROM SYS.DATABASES
WHERE name IN ('master', 'model', 'msdb', 'tempdb', 'resource',
       'distribution' , 'reportserver', 'reportservertempdb')

RESULT:
get-all-system-databases-in-sql-server-1

The number of System Databases returned varies from one instance of Sql Server to another instance of the Sql Server. For example if you have installed the Sql Server reporting services then only the instance of Sql Server will have the reportserver and reportservertempdb databases.

[ALSO READ] 100 Frequently used queries in Sql Server