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.
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:
Replace "-eC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG"
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"
Provide new location for Log file in "Error Log File" path:
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:
@errorlog_file = N'C:\Logs\SQLAGENT.OUT'
Execute this code and restart SQL Server agent to apply changes.
Hope This Helps!
If you have lost the password for sa account or does not have any administrative account and you are locked out of SQL Server you can still login to SQL Server as an Administrator using Local administrator account.
You can use any account which is part of local Administrators group. To be able to login using local admin account you need to stop and start SQL Server in single user mode.
Using below method you can restore access to your server by resetting forgotten administrator password or by creating a new Administrative account in SQL Server.
We need to perform below steps to create new administrator account in SQL Server
Step 1. Stop SQL Server from Services Console or SQL Server Configuration Manager
Step 2. Start SQL Server in single user mode using -m startup parameter
You can add -m switch as a startup parameter using SQL Server Configuration Manager or You can start SQL Server in single user mode from command prompt using below command:
C:\> sc start MSSQLSERVER -m
Replace MSSQLSERVER with service name on your system
Before you start SQL Server in single user mode make sure SQL Server Agent is disabled
Step 3. Connect to SQL Server using SQLCMD or SQL Server Management Studio
To connect to SQL Server when running in single user mode using SQL Server Management Studio use "Database Engine Query" from File Menu > New option *
* You cannot connect using Object Explorer while SQL Server is running in single user mode as Object Explorer requires 2 separate connections to server
To connect to SQL Server using SQLCMD, run below on command prompt:
C:\>SQLCMD -S (local)
Step 4. Create New Administrator account or reset password for an existing account
When connected to SQL Server using SQLCMD or SQL Server Management Studio execute below script to create a new administrator account:
1> CREATE LOGIN [NewAccount] WITH PASSWORD = N'Welcome',
2> DEFAULT_DATABASE = [master],
3> CHECK_EXPIRATION = OFF,
4> CHECK_POLICY = OFF
2> ALTER SERVER ROLE [sysadmin]
3> ADD MEMBER [NewAccount]
To Reset password of an existing account use below code:
1> ALTER LOGIN [ExistingAccount] WITH PASSWORD = N'Welcome'
Replace [ExistingAccount] with login name for which you want to reset the password, and replace 'Welcome' with new password, make sure new password meets the security policies
Step 5. Stop SQL Server Service using Services Console or SQL Server Configuration Manager
Step 6. Start SQL Server Service normally using Services Console or SQL Server Configuration Manager
Now you can login to SQL Server using the newly created Administrator account.
Hope This Helps!
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:
|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) )
— Uncomment if you need to query for a particular database
— WHERE database_id = DB_ID(‘Database Name’)
GROUP BY GROUPING SETS
ORDER BY DB_NAME(database_id), Type_Desc DESC
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!
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.
2. Check the "Drop Connections" checkbox and click "OK" to detach the database
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:
ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'
Command(s) completed successfully.
This detaches database from server.
Hope This Helps!
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:
Step 2: In the Filter Settings dialog box, specify "Sales" in Value field, and click on OK to apply filter:
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:
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!