How to move SQL database files to a different location

Sometimes when SQL database files (.mdf and .ldf files) grow too large, the easiest solution might be to move them to a different location so we can free up disk space quickly.

Here are the steps to move these files:

1. Turn off all services or processes that are using the database

2. Note down the file locations of the .mdf and ldf files (by default, they are located at SQL Server application directory e.g., \\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

3. Launch SQL Management Studio

4. Login and access the SQL server instance has the database

5. Click and highlight the database

6. Open Query Editor

7. Run following SQL script (please change the database name and location to suit)

1 2 3 4 5 6 7 8 9

ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Data,                     FILENAME = 'E:\New_location\AdventureWorks2014_Data.mdf');   GO   ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Log,                     FILENAME = 'E:\New_location\AdventureWorks2014_Log.ldf');   GO

8. Run following SQL script to set the database to be offline

1 2	ALTER DATABASE AdventureWorks2014 SET OFFLINE;   GO

Note: if the database is too large and this statement may take a while to run. Or you can use the following statement instead.

1 2	ALTER DATABASE AdventureWorks2014 SET OFFLINE WITH ROLLBACK IMMEDIATE;   GO

9. Browse to the files location (we noted down in Step 2) and move the files to the new location

10. In Query Editor, run following SQL script to bring the database online again

1 2	ALTER DATABASE AdventureWorks2014 SET ONLINE;   GO

Note: if it fails with permission error, please change the user who runs the SQL services. You may do that by following the steps below via Services application.

1. Right click on the SQL Server service e.g., like the one highlighted in the screen shot below

2. Choose Properties option

3. Click Log On tab to change to the ideal user as required

OR

You may also grant access permission to the SQL Server service user on the new folder, where the database files are.

11. You may verify if the database is made online successfully by running the following statement

1 2 3 4

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus FROM sys.master_files   WHERE database_id = DB_ID(N'AdventureWorks2014')   GO

This article is originally sourced from this one and you may find more details there. Thank you. 

A safe way to convert string to date time in SQL

Over the years while working with SQL Server, I found a safe way of converting string to date time value to be used in SQL. It will have zero risk of being parsed in WHERE clause or cause any performance issues.

Examples:

Convert to DATE type value

SELECT {d '2021-03-01'}

Convert to DATETIME type value

SELECT {ts '2021-03-01 06:10:00'}

Check port number usage in Windows - Netstat command explained

We know Netstat command can list all current in use ports. However, often I found it is hard to understand the result that listed by this command OR to know what parameters to use to help me diagnose the problem better. I have googled many sites and found the following examples. I hope this can be helpful to someone and can be useful for my own references in the future.

The following arguments are often useful when using Netstat command:

netstat -ano

-a parameter lists all computer's connections and listening ports
-n parameter displays addresses and port numbers in numerical format
-n parameter outputs the process ID (PID) responsible for the connection

Note: you can also use -b flag which outputs the EXE and its associated DLLs that are using the TCP and UDP ports.

The Netstat command can also have filter:

netstat -an 1 | find "4444" | find "ESTABLISHED"

In this example, Netstat will not display an output until it finds an established connection on port 4444, and it will include the source IP address connected to the port, a helpful bit of information in an investigation.

*Note: The –b and –o options are not available on Windows 2000 and be aware that running them with the interval option would be a drain on a system's resources.

The above information is sourced from http://www.computerweekly.com/tip/How-to-use-a-netstat-command-in-Windows-to-watch-open-ports

Some other sites that with useful information about Netstat command:

https://linuxacademy.com/blog/linux/netstat-network-analysis-and-troubleshooting-explained/

References of Netstat from https://technet.microsoft.com/en-us/library/bb490947.aspx

Netstat

Displays active TCP connections, ports on which the computer is listening, Ethernet statistics, the IP routing table, IPv4 statistics (for the IP, ICMP, TCP, and UDP protocols), and IPv6 statistics (for the IPv6, ICMPv6, TCP over IPv6, and UDP over IPv6 protocols). Used without parameters, netstat displays active TCP connections.

Syntax

netstat [-a] [-e] [-n] [-o] [-p Protocol] [-r] [-s] [Interval]

Top of page 

Parameters

-a   : Displays all active TCP connections and the TCP and UDP ports on which the computer is listening.

-e   : Displays Ethernet statistics, such as the number of bytes and packets sent and received. This parameter can be combined with -s.

-n   : Displays active TCP connections, however, addresses and port numbers are expressed numerically and no attempt is made to determine names.

-o   : Displays active TCP connections and includes the process ID (PID) for each connection. You can find the application based on the PID on the Processes tab in Windows Task Manager. This parameter can be combined with -a, -n, and -p.

-p   Protocol   : Shows connections for the protocol specified by Protocol. In this case, the Protocol can be tcp, udp, tcpv6, or udpv6. If this parameter is used with -s to display statistics by protocol, Protocol can be tcp, udp, icmp, ip, tcpv6, udpv6, icmpv6, or ipv6.

-s   : Displays statistics by protocol. By default, statistics are shown for the TCP, UDP, ICMP, and IP protocols. If the IPv6 protocol for Windows XP is installed, statistics are shown for the TCP over IPv6, UDP over IPv6, ICMPv6, and IPv6 protocols. The -p parameter can be used to specify a set of protocols.

-r   : Displays the contents of the IP routing table. This is equivalent to the route print command.

Interval   : Redisplays the selected information every Interval seconds. Press CTRL+C to stop the redisplay. If this parameter is omitted, netstat prints the selected information only once.

/?   : Displays help at the command prompt.

Top of page 

Remarks

• Parameters used with this command must be prefixed with a hyphen (-) rather than a slash (/).
• Netstat provides statistics for the following:
  • Proto
    The name of the protocol (TCP or UDP).
  • Local Address
    The IP address of the local computer and the port number being used. The name of the local computer that corresponds to the IP address and the name of the port is shown unless the -n parameter is specified. If the port is not yet established, the port number is shown as an asterisk (*).
  • Foreign Address
    The IP address and port number of the remote computer to which the socket is connected. The names that corresponds to the IP address and the port are shown unless the -n parameter is specified. If the port is not yet established, the port number is shown as an asterisk (*).
  • (state)
    Indicates the state of a TCP connection. The possible states are as follows:
    CLOSE_WAIT
    CLOSED
    ESTABLISHED
    FIN_WAIT_1
    FIN_WAIT_2
    LAST_ACK
    LISTEN
    SYN_RECEIVED
    SYN_SEND
    TIMED_WAIT
    For more information about the states of a TCP connection, see RFC 793.
• This command is available only if the Internet Protocol (TCP/IP) protocol is installed as a component in the properties of a network adapter in Network Connections

Top of page 

Examples

To display both the Ethernet statistics and the statistics for all protocols, type the following command:

netstat -e -s

To display the statistics for only the TCP and UDP protocols, type the following command:

netstat -s -p tcp udp

To display active TCP connections and the process IDs every 5 seconds, type the following command:

nbtstat -o 5

To display active TCP connections and the process IDs using numerical form, type the following command:

nbtstat -n -o

Top of page 

Formatting legend

Format	Meaning
Italic	Information that the user must supply
Bold	Elements that the user must type exactly as shown
Ellipsis (...)	Parameter that can be repeated several times in a command line
Between brackets ([])	Optional items
Between braces ({}); choices separated by pipe (|). Example: {even|odd}	Set of choices from which the user must choose only one
Courier font	Code or program output

How to show server name with an IP address

Use command-link command Ping, we can check the connectivity to another computer. It can also show the server name if you only know the IP address. The command should be:

ping -a [IP address]

The following is detailed references of Ping command and sourced from https://technet.microsoft.com/en-us/library/bb490968.aspx

Ping

Verifies IP-level connectivity to another TCP/IP computer by sending Internet Control Message Protocol (ICMP) Echo Request messages. The receipt of corresponding Echo Reply messages are displayed, along with round-trip times. Ping is the primary TCP/IP command used to troubleshoot connectivity, reachability, and name resolution. Used without parameters, ping displays help.

Syntax

ping [-t] [-a] [-n Count] [-l Size] [-f] [-i TTL] [-v TOS] [-r Count] [-s Count] [{-j HostList | -k HostList}] [-w Timeout] [TargetName]

Top of page 

Parameters

-t   : Specifies that ping continue sending Echo Request messages to the destination until interrupted. To interrupt and display statistics, press CTRL-BREAK. To interrupt and quit ping, press CTRL-C.

-a   : Specifies that reverse name resolution is performed on the destination IP address. If this is successful, ping displays the corresponding host name.

-n   Count   : Specifies the number of Echo Request messages sent. The default is 4.

-l Size   : Specifies the length, in bytes, of the Data field in the Echo Request messages sent. The default is 32. The maximum size is 65,527.

-f   : Specifies that Echo Request messages are sent with the Don't Fragment flag in the IP header set to 1. The Echo Request message cannot be fragmented by routers in the path to the destination. This parameter is useful for troubleshooting path Maximum Transmission Unit (PMTU) problems.

-i TTL   : Specifies the value of the TTL field in the IP header for Echo Request messages sent. The default is the default TTL value for the host. For Windows XP hosts, this is typically 128. The maximum TTL is 255.

-v   TOS   : Specifies the value of the Type of Service (TOS) field in the IP header for Echo Request messages sent. The default is 0. TOS is specified as a decimal value from 0 to 255.

-r   Count   : Specifies that the Record Route option in the IP header is used to record the path taken by the Echo Request message and corresponding Echo Reply message. Each hop in the path uses an entry in the Record Route option. If possible, specify a Count that is equal to or greater than the number of hops between the source and destination. The Count must be a minimum of 1 and a maximum of 9.

-s   Count   : Specifies that the Internet Timestamp option in the IP header is used to record the time of arrival for the Echo Request message and corresponding Echo Reply message for each hop. The Count must be a minimum of 1 and a maximum of 4.

-j   HostList   : Specifies that the Echo Request messages use the Loose Source Route option in the IP header with the set of intermediate destinations specified in HostList. With loose source routing, successive intermediate destinations can be separated by one or multiple routers. The maximum number of addresses or names in the host list is 9. The host list is a series of IP addresses (in dotted decimal notation) separated by spaces.

-k   HostList   : Specifies that the Echo Request messages use the Strict Source Route option in the IP header with the set of intermediate destinations specified in HostList. With strict source routing, the next intermediate destination must be directly reachable (it must be a neighbor on an interface of the router). The maximum number of addresses or names in the host list is 9. The host list is a series of IP addresses (in dotted decimal notation) separated by spaces.

-w   Timeout   : Specifies the amount of time, in milliseconds, to wait for the Echo Reply message that corresponds to a given Echo Request message to be received. If the Echo Reply message is not received within the time-out, the "Request timed out" error message is displayed. The default time-out is 4000 (4 seconds).

TargetName   : Specifies the destination, which is identified either by IP address or host name.

/?   : Displays help at the command prompt.

Top of page 

Remarks

• You can use ping to test both the computer name and the IP address of the computer. If pinging the IP address is successful, but pinging the computer name is not, you might have a name resolution problem. In this case, ensure that the computer name you are specifying can be resolved through the local Hosts file, by using Domain Name System (DNS) queries, or through NetBIOS name resolution techniques.
• This command is available only if the Internet Protocol (TCP/IP) protocol is installed as a component in the properties of a network adapter in Network Connections

Top of page 

Examples

The following example shows ping command output:

C:\>ping example.microsoft.com

Pinging example.microsoft.com [192.168.239.132] with 32 bytes of data:

Reply from 192.168.239.132: bytes=32 time=101ms TTL=124

Reply from 192.168.239.132: bytes=32 time=100ms TTL=124

Reply from 192.168.239.132: bytes=32 time=120ms TTL=124

Reply from 192.168.239.132: bytes=32 time=120ms TTL=124

To ping the destination 10.0.99.221 and resolve 10.0.99.221 to its host name, type:

ping -a 10.0.99.221

To ping the destination 10.0.99.221 with 10 Echo Request messages, each of which has a Data field of 1000 bytes, type:

ping -n 10 -l 1000 10.0.99.221

To ping the destination 10.0.99.221 and record the route for 4 hops, type:

ping -r 4 10.0.99.221

To ping the destination 10.0.99.221 and specify the loose source route of 10.12.0.1-10.29.3.1-10.1.44.1, type:

ping -j 10.12.0.1 10.29.3.1 10.1.44.1 10.0.99.221

Top of page 

Formatting legend

Format	Meaning
Italic	Information that the user must supply
Bold	Elements that the user must type exactly as shown
Ellipsis (...)	Parameter that can be repeated several times in a command line
Between brackets ([])	Optional items
Between braces ({}); choices separated by pipe (|). Example: {even|odd}	Set of choices from which the user must choose only one
Courier font	Code or program output

No result is rendered in Preview mode when using Crystal reports in .NET web application

We encountered this issue with several different reports on different servers. The report would render fine in other modes such as Microsoft Excel or PDF. But when viewing in the default Preview mode, we are not able to see anything loaded and just a blank page. There is no specific information or error logged, either.

All reports may have been working at one stage on the same server before. It can be likely related to the reinstall of Crystal Report runtime on the server but we are not sure the definite cause of the problem. However, the solution is to copy the files according to the steps below.

1) Run the report as usual while having the browser’s dev tool open and Network monitor is running
2) While the report is loading in Preview mode, you will find there will be some 404 error appears in the monitor screen
3) Once the page is loaded, check the 404 error details and see what files were missing
4) The file path would be something starts with c:\inetpub\wwwroot\aspnet_client
5) Navigate to the file path. The two folders that start with 4_0 and 2_0 may contain same files. So you can modify the folder name and copy the right files accordingly. If a folder is completely missing, you may create a new one and copy the content from the existing 4_0 folder.

Note: if your virtual directory is not hosted on the c:\, you may need to do this to the same drive and the entire c:\inetpub folder is required on that drive as well.

Thank you for the person who posted the solution here and there are some variations of this problem that can be fixed in the same way.

SQL server database collation explained

When we installing a new SQL server instance, we have to choose a database collation. This will be the default collation for all databases that going to be created on this instance later on.

However, this default database collation can be overridden in following ways.

1. When creating a new database, we can specify a different collation for the database.

For example

CREATE DATABASE DbWithNewLanguage COLLATE SQL_Latin1_General_CP1_CI_AS

2. When creating a new table, we can specify different collations for individual column.

For example

CREAT TABLE MixLanguageTable (
ID INT IDENTITY,
EnglishName nvarchar(150) COLLATE Latin1_General_CS_AS,
GreekName nvarchar(150) COLLATE Greek_CS_AS_KS,
JapaneseName nvarhcar(150) COLLATE Japanese_90_CI_AS_KS_WS
)

3. When querying the tables, we can specify different collations.

For example

--EnglishName column in the example above is case sensitive. But we can change the collation so when we query the table on this column, we can set it to be case insensitive.

SELECT * FROM dbo.MixLanguageTable WHERE EnglishName = ‘test name’ COLLATE Latin1_General_CI_AS

Things to know about TRY...CATCH in T-SQL

With TRY…CATCH statement in T-SQL, I attempted to use it for a table name resolution the other day. However, I found the statement will not handle following errors, so it will not work if we are using this statement for checking the table name if it exists or not.

1. Compile errors, such as syntax errors, that prevent a batch from running.
2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Meanwhile, when using SET XACT_ABORT ON setting, if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. However, this will not help if we want to resolve the “table name does not exist” error.

Reference:

http://stackoverflow.com/questions/15015051/sql-try-catch-doesnt-catch-an-error-when-attempting-to-access-a-table-that-it