Archive

Posts Tagged ‘software’

SQL Server – Changing Default Database Location for Server

March 18, 2013 2 comments

When you create a new database in SQL Server without explicitly specifying database file locations, SQL Server created files in default location. This default location is configured when installing SQL Server.

If you need to change this default location once SQL Server is installed, you can change this in server properties.

Method 1: Change default database location via SQL Server Management Studio:

Step 1. Right Click on Server and Select "Properties".

image

Step 2. in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. You can also change default backup location here.

image

Step 3. Click on "OK" to apply changes.

All new databases will be created to new location unless specified explicitly.

 

Method 2: Change default database location using TSQL Code:

You can also change default database location using TSQL code, use below code to change data, log and backup location:

USE [master]

GO

 

— Change default location for data files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'Software\Microsoft\MSSQLServer\MSSQLServer',

       N'DefaultData',

       REG_SZ,

       N'C:\MSSQL\Data'

GO

 

— Change default location for log files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'Software\Microsoft\MSSQLServer\MSSQLServer',

       N'DefaultLog',

       REG_SZ,

       N'C:\MSSQL\Logs'

GO

 

— Change default location for backups

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'Software\Microsoft\MSSQLServer\MSSQLServer',

       N'BackupDirectory',

       REG_SZ,

       N'C:\MSSQL\Backups'

GO

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Start/Stop SQL Server from command line

March 11, 2013 1 comment

You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. You can also perform these operation using command line in Windows. For this you must run command prompt with Administrative privileges.

Method 1: Start/Stop SQL Server using NET command:

You can start/stop SQL Server from command line using NET command as below:

NET Action Service Name or Service Display Name

Where Action can be Start or Stop, and you can either specify Service Name or Service Display Name. Service Name is actual name which is registered as service and Service Display Name is a Descriptive Name for Service. For example, for default instance of SQL Server Service Name is "MSSQLSERVER", and Service Display Name is "SQL Server (MSSQLSERVER)". Below is a screenshot of Service Properties to explain this:

image

 

To START SQL Server Service, use:

NET start MSSQLSERVER

OR

NET start "SQL Server (MSSQLSERVER)"

 

To STOP SQL Server Service, use:

NET stop MSSQLSERVER

OR

NET stop "SQL Server (MSSQLSERVER)"

 

Method 2: Start/Stop SQL Server using SC command line utility:

The main advantage of using SC utility to start services is you can specify startup parameters while starting a service:

You can start/stop SQL Server from command line using SC utility as below:

SC Action Service Name or Service Display Name [Optional Parameters]

 

To START SQL Server Service, use:

SC start MSSQLSERVER

OR

SC start "SQL Server (MSSQLSERVER)"

To START SQL Server Service with parameters, use:

SC start MSSQLSERVER -m

OR

SC start "SQL Server (MSSQLSERVER)" -m

* The -m switch starts SQL Server in single user mode.

To STOP SQL Server Service, use:

SC stop MSSQLSERVER

OR

SC stop "SQL Server (MSSQLSERVER)"

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to Rename Table/Column in SQL Server

March 4, 2013 1 comment

To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.

Method 1: To rename an object using SQL Server Management Studio:

Step 1. Right Click on the object in Object Explorer and select "Rename"

image

Step 2. Specify new name and press Enter.

image

You can rename any object using object in Object Explorer.

 

Method 2: Renaming an object using sp_rename:

You can also rename using sp_rename system stored procedure.

sp_rename takes below arguments:

Parameter Description
@objname Object Name. When renaming a column you need to specify table name.column name optionally you can also prefix schema name
@newname New name for the specified object
@objtype Type of the object. You can rename below objects using sp_rename:
COLUMN
DATABASE
INDEX
OBJECT
STATISTICS
USERDATATYPE
Default value for this parameter is TABLE

Following example demonstrates how you can use sp_rename to rename table and column names:

Let’s create a Test Table to work with:

USE [SqlAndMe]

GO

 

CREATE TABLE tblProduct

(     

       ID     INT,

       pName  NVARCHAR(50)

)

GO

Now we can rename the Table Name and column names as below:

— Rename table tblProduct to Table_Products

EXEC sp_rename 'tblProduct', 'Table_Products'

GO

 

— Rename Column ID to ProductID

EXEC sp_rename 'Table_Products.ID', 'ProductID', 'COLUMN'

GO

 

— Rename Column pName to ProductName

EXEC sp_rename 'Table_Products.pName', 'ProductName', 'COLUMN'

GO

Result Set:

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

You can verify that the changes have been made by issuing a SELECT against the table using new table name. SQL Server is also nice enough to warn us that renaming an object can break scripts and stored procedures. What this means is if you have used table/column names in any script/stored procedure it will not be updated automatically, you will need to go through your scripts/stored procedures manually and update them accordingly.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Finding out Database creation time

February 19, 2013 1 comment

Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.

Using sp_helpdb:

You can find the creation time of database using sp_hepldb system procedure.

EXEC sp_helpdb 'SqlAndMe'

GO

This will return database creation time along with other details:

image

Using catalog view sys.databases:

Database creation time is also available in sys.databases catalog view:

SELECT      create_date

FROM        sys.databases

WHERE       name = 'SqlAndMe'

GO

Result Set:

create_date

2012-11-14 15:19:31.987

 

(1 row(s) affected)

 

However, if the database has been detached and reattached to server, the creation time will be changed to attach time. Also, if the database has been restored from a backup after dropping the database creation time will be changed.

Actual creation time of database is stored in the boot page of the database which is retained after restore or detach/attach. This is stored as dbi_crdate.

You can use DBCC PAGE to read creation time of database from boot page:

DBCC TRACEON(3604)

GO

 

DBCC PAGE('SqlAndMe', 1, 9, 3)

GO

 

Result Set (Trimmed):

dbi_createVersion = 661              dbi_ESVersion = 0                   

dbi_nextseqnum = 1900-01-01 00:00:00.000        dbi_crdate = 2011-11-07 21:12:46.357

dbi_filegeneration = 2              

dbi_checkptLSN

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Cycle Error Logs for SQL Server and SQL Server Agent

February 8, 2013 1 comment

SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is renamed from ERRORLOG to ERRORLOG.1, ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

Restarting SQL Server will cycle the error logs. You can also use sp_cycle_errorlog to cycle error logs without restarting SQL Server.

Below is an example how to use it:

EXEC sp_Cycle_ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

You can verify that the log has cycled using Management Studio or you can use xp_ReadErrorLog also.

 

Same effect can be achieved by using DBCC ErrorLog. In fact, sp_Cycle_ErrorLog calls DBCC ErrorLog to cycle error logs.

DBCC ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

DBCC ErrorLog and sp_Cycle_ErrorLog only cycles SQL Server error logs. It does not cycle SQL Server Agent error logs. In order to achieve this you can restart SQL Server Agent or use another stored procedure sp_Cycle_Agent_ErrorLog.

USE   [msdb]

GO

 

EXEC  sp_Cycle_Agent_ErrorLog

GO

 

Result Set:

Command(s) completed successfully.

 

You can verify that the SQL Server Agent log has cycled using xp_ReadErrorLog.

* Use sp_helptext to see what is called from sp_Cycle_Agent_ErrorLog.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Attach Database with MDF file only

January 30, 2013 1 comment

Earlier on my blog I posted about how you can attach a database using T-SQL when no log file is available. You can catch that here. In this post we will see how it can be attached using GUI.

To attach a database with no .ldf file, follow below steps:

1. Open Attach Database dialog box by selecting "Attach" from context menu of database node in Object Explorer:

image

2. Click "Add" button to locate the .mdf file, and click "OK":

image

image

3. Once you locate the .mdf file for database, it will automatically fill details for log file name and path, also a message will be displayed if .ldf is not available where it should be:

image

4. Since we do not have .ldf file available, select the row for log file and click on "Remove", and then click on "OK" to attach the database:

image

5. This will force SQL Server to rebuild log file for the database. Once the database is attached you can verify that log file has been rebuilt using following T-SQL:

USE [SqlAndMe]

GO

 

SELECT      name, physical_name

FROM        sys.database_files

GO

Results:

name          physical_name
———–   ———————–
SqlAndMe      C:\Database\Data\SqlAndMe.mdf
SqlAndMe_log  C:\Database\Log\SqlAndMe_log.LDF

(2 row(s) affected)

 

You can also do this using T-SQL, read Attach a single MDF file – Database for details.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Check Enabled Trace Flags for Server

January 24, 2013 2 comments

In SQL Server you can enable a Trace Flag at session (effective for current session only) level and global level. If you are not sure which Trace Flags have been enabled you can use DBCC TRACESTATUS() command to get a list of enabled Trace Flags.

DBCC TRACESTATUS() takes two arguments <Trace Flag Number> and <Trace Scope>.

It can be used in following different ways.

To display Trace Flags which are enabled in current session + globally enabled Trace Flags

DBCC TRACESTATUS(-1)

GO

 

DBCC TRACESTATUS()

GO

To display status of individual Trace Flags which are enabled.

DBCC TRACESTATUS(3023, 3604)

GO

The output of DBCC TRACESTATUS() is as below:

image

 

This status of Trace Flags can also be obtained from SQL Server error logs. Although it required more effort you can use xp_ReadErrorLog to get this information from error logs using below T-SQL:

EXEC xp_ReadErrorLog 0, 1, 'DBCC TRACE'

GO

You can find more information on xp_ReadErrorLog here.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe