Archive

Posts Tagged ‘sql server management studio’

SQL Server – Displaying line numbers in Query Editor – SSMS

20.01.2014 No comments

You can enable line numbers to be displayed in SSMS Query Editor. This is extremely useful when working on a large module.

To enable line numbers in Query Editor windows, follow below steps:

Step1: Go to Tools > Options

image

Step2: In the Options dialog box navigate to Text Editor > Transact-SQL > General

image

Step 3: Check “Line Numbers” and click on “OK”

image

Now, when a query window is opened Line Numbers will be displayed:

image

Hope This Helps!

Vishal

SQL Server – How to change SQL Server ERRORLOG location

08.07.2013 3 comments

By default SQL Server ERRORLOG is stored in "C:Program FilesMicrosoft SQL ServerInstanceFolderMSSQLLog" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service.

image

To change the location of ERRORLOG you need to modify the startup parameter -e.

For example, if you need to move logs to C:Logs then replace the startup parameter as below:

Step 1:

Replace "-eC:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLLogERRORLOG"

to "-eC:LogsERRORLOG"

Step 2:

Restart SQL Server Service to apply changes. The ERRORLOG will be created to "C:Logs" after SQL Server is restarted.

 

To change location for SQL Server Agent Logs:

Location for SQL Server Agent Log can be changed using T-SQL or SQL Server Management Studio.

Method 1: To change SQL Server Agent Log location right click on "Error Logs" node and select "Configure"

image

Provide new location for Log file in "Error Log File" path:

image

Click on "OK" and restart SQL Server Agent to apply changes.

Method 2: To change log location using T-SQL

To change log location using T-SQL you can use below code:

USE [msdb]

GO

EXEC   msdb.dbo.sp_set_sqlagent_properties

       @errorlog_file = N'C:LogsSQLAGENT.OUT'

GO

Execute this code and restart SQL Server agent to apply changes.

Hope This Helps!

Vishal

SQL Server – Saving Changes Not Permitted in Management Studio

01.04.2013 1 comment

SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:

image

Here, I have tried changing data type for 'Name' column from NVARCHAR(50) to CHAR(50). Since this requires table re-creation Management Studio does not allow this operation to be completed, and it is doing so for your own good!

This activity can be completed without re-creating table if you are using T-SQL. Following T-SQL code can be used to avoid table re-creation:

USE [SqlAndMe]

GO

 

ALTER TABLE [dbo].[Customers]

ALTER COLUMN Name CHAR(50)

GO

The above statement will change the data type of the 'Name' column to CHAR(50) without having to re-create table.

We are better off with this option enabled. Although, if you are a GUI fan you can disable this safety net.

1. Go to Tools > Options

2. Go to Designers > Table and Database Designers

3. Uncheck Prevent saving changes that require table re-creation.

image

You can find more information on why not to disable this option here:

SQL Server – ALTER COLUMN – Management Studio v. T-SQL

Hope This Helps!

Vishal

SQL Server – Changing Default Database Location for Server

18.03.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'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'DefaultData',

       REG_SZ,

       N'C:MSSQLData'

GO

 

— Change default location for log files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'DefaultLog',

       REG_SZ,

       N'C:MSSQLLogs'

GO

 

— Change default location for backups

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'BackupDirectory',

       REG_SZ,

       N'C:MSSQLBackups'

GO

Hope This Helps!

Vishal

SQL Server – Check SQL Agent Job History using T-SQL

14.05.2012 4 comments

To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.

To Open Log File Viewer,

1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:

image

 

Alternatively, you can also use below T-SQL code to check Job History:

SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        sysjobs JOB

INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id

/* WHERE    JOB.name = 'Job1' */

ORDER BY    HIST.run_date, HIST.run_time

Output:

JobName  Step StepName      Message       Status     RunDate   RunTime   Duration

Job1     1    Step1         Executed a..  Succeeded  20120416  173935    10

Job1     0    (Job outcome) The job su..  Succeeded  20120416  173935    10

Job2     1    Step1         Executed a..  Succeeded  20120416  174037    10

Job2     0    (Job outcome) The job su..  Succeeded  20120416  174037    10

 

Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.

Hope This Helps!

Vishal