SQL Server – How to change SQL Server ERRORLOG location

July 8, 2013 3 comments

By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MSSQL\Log" 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 Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG"

to "-eC:\Logs\ERRORLOG"

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:\Logs\SQLAGENT.OUT'

GO

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

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 – Update Table with INNER JOIN

June 18, 2013 1 comment

Often we may need to update a column in a table based of another column in another table.

In SQL Server you can do this using UPDATE statement by joining tables together.

To understand this better let’s take a look at below contrived example.

USE [SqlAndMe]

GO

 

SELECT CustomerID, Name, OrderAmount

FROM   dbo.Customers

GO

 

SELECT OrderID, CustomerID, Amount

FROM   dbo.Orders

GO

Result Set:

CustomerID    Name          OrderAmount

3             Curtis        NULL

4             Lanna         NULL

5             Marlin        NULL

6             Henry         NULL

 

(4 row(s) affected)

 

OrderID       CustomerID    Amount

107           6             8745.00

123           5             4582.00

643           3             5693.00

 

(3 row(s) affected)

In the above data I want to update OrderAmount column of dbo.Customers with values from Amount column of dbo.Orders.

To achieve this we can use UPDATE statement as below:

UPDATE CUST

SET    CUST.OrderAmount = ORDR.Amount

FROM   dbo.Customers CUST

INNER JOIN dbo.Orders ORDR ON CUST.CustomerID = ORDR.CustomerID

GO

 

(3 row(s) affected)

OrderAmount column in dbo.Customers Table is now updated based on JOIN condition.

SELECT CustomerID, Name, OrderAmount

FROM   dbo.Customers

GO

CustomerID    Name          OrderAmount

3             Curtis        5693.00

4             Lanna         NULL

5             Marlin        4582.00

6             Henry         8745.00

 

(4 row(s) affected)

 

I have used a simple example here to explain the concept easily

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 get sizes of all databases on a server

June 10, 2013 8 comments

To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases.

The columns of interest for retrieving database size information are:

Column Name Description
database_id ID of the database to which the file belongs to
type_desc Description of file type. It can be ROWS, LOG, FILESTREAM or FULLTEXT
size File size in number of 8KB pages

Using this information we can retrieve database sizes using below query:

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

— Uncomment if you need to query for a particular database

— WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(database_id), Type_Desc),

                     (DB_NAME(database_id))

              )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

Result Set:

 Database Name        Type                 Size in MB

AdventureWorks2012   Data File(s)         189.00

AdventureWorks2012   Log File(s)          0.75

AdventureWorks2012   NULL                 189.75

Credit               Data File(s)         170.94

Credit               Log File(s)          10.00

Credit               NULL                 180.94

master              Data File(s)          4.00

master              Log File(s)           0.75

master              NULL                  4.75

model                Data File(s)         3.06

model                Log File(s)          0.75

model                NULL                 3.81

msdb                 Data File(s)         16.69

msdb                 Log File(s)          19.63

msdb                 NULL                 36.31

Northwind            Data File(s)         4.25

Northwind            Log File(s)          3.06

Northwind            NULL                 7.31

pubs                 Data File(s)         3.25

pubs                 Log File(s)          3.06

pubs                 NULL                 6.31

SqlAndMe             Data File(s)         137.88

SqlAndMe             Log File(s)          19.13

SqlAndMe             NULL                 157.00

tempdb              Data File(s)          8.00

tempdb              Log File(s)           0.50

tempdb              NULL                  8.50

 

(27 row(s) affected)

The above query gets sizes for Data Files and Log Files and displays a total using GROUPING SETS.

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 Detach a Database

June 3, 2013 Leave a comment

To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.

There are two different methods available to detach a database from a server.

Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:

To detach a database using SQL Server Management Studio:

1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.

image

2. Check the "Drop Connections" checkbox and click "OK" to detach the database

image

Now the database is detached from server and you can move .mdf and .ldf files related to database.

 

Method 2: Detach database using T-SQL:

To detach a database from a server using T-SQL you can use below code:

USE [master]

GO

 

ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'

GO

Result Set:

Command(s) completed successfully.

This detaches database from server.

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 – Filtering objects in Object Explorer – Management Studio

May 28, 2013 4 comments

When you are working on a database with a large number of objects sometimes it becomes a challenge to locate objects. Object Explorer in SQL Server Management Studio lists all objects according to category and it may be hard to locate specific object.

To resolve this you can Object Explorer provides filtering for objects. You can apply a filter in Object Explorer for common object categories. i.e. Table, Views, Stored Procedures etc.

For example, if we need to locate a stored procedure which has "Sales" in name we can use Object Explorer filtering as below:

Step 1: Right Click on Stored Procedures category in required database and select Filter > Filter Settings:

image

Step 2: In the Filter Settings dialog box, specify "Sales" in Value field, and click on OK to apply filter:

image

After applying this filer Object Explorer will only show stored procedures matching the specified filter criteria, only those which have word "Sales" in their name in this case:

image

Step 3: To clear the filter and list all objects again, right click on filtered category and select Filter > Remove Filter. This will clear the filter applied.

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