Database Administration | SQL Conjuror

SQL Server – View Recovery Model using T-SQL

By Enrique 30/08/2016 30/04/2020 SQL SERVER, Database Administration

Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

Use the script below to view the database recovery model.

SELECT name AS [Database Name],

recovery_model_desc AS [Recovery Model]

FROM sys.databases

The following table summarizes the three recovery models.

Facebook

Google

Twitter

2 Comments

SQL Server – Resolve Error 5030 to Rename Database

By Enrique 27/08/2016 25/05/2020 SQL SERVER, Database Administration

When you want to rename your database and you hit the error below, you need to set the database to Single User Mode. After you rename your database, then you set the database back to Multi-User mode.

Msg 5030, Level 16, State 2, Line 1

The database could not be exclusively locked to perform the operation

Follow the steps below to rename your database.

Set the database to single-mode

ALTER DATABASE OLD_DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

2. Rename the Database

ALTER DATABASE OLD_DBName MODIFY NAME = NEW_DBNAME;

3. Set the database to Multi-user mode

ALTER DATABASE NEW_DBNAME SET MULTI_USER WITH ROLLBACK IMMEDIATE;

You may check out this blog post where I show a better way to rename a database.

Cheers!

SQL Server – Change Database Owner

By Enrique 23/08/2016 30/04/2020 SQL SERVER, Database Administration, T-SQL

Below is the T-SQL script to change DB owner.

EXEC sp_changedbowner 'owner_name';

SQL Server – Enabling Service Broker

By Enrique 22/08/2016 30/04/2020 SQL SERVER, Database Administration

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation”. Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

--Enable Service Broker

ALTER DATABASE DB_Name SET ENABLE_BROKER;

--Verify status of Service Broker. A status of 1 means that it is enabled (0 means it is disabled)

SELECT is_broker_enabled

FROM sys.databases

WHERE name = 'DB_Name'

SQL Server – SQL Server Agent Job History

By Enrique 08/07/2016 30/04/2020 SQL SERVER, Database Administration

The other day, I was troubleshooting a database slowness issue when the lead application developer asks me whether or not there are any SQL Jobs running during that time. The T-SQL script below helped me to retrieve that info. This T-SQL script will check the SQL Agent Job history.

SELECT

j.name AS 'JobName',

run_date,

run_time,

msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobhistory h

ON j.job_id = h.job_id

WHERE j.enabled = 1 --Only Enabled Jobs

ORDER by JobName, RunDateTime DESC

SQL Server – Get the Table Structure Using T-SQL

By Enrique 27/06/2016 03/05/2020 SQL SERVER, Database Administration, T-SQL

When you are writing a SELECT statement and you just want to limit the number of columns, you would use the sp_help stored procedure to research the underlying table structure as shown below.

The sp_help system stored procedure will return all the data about a particular table when you pass the table name into the stored procedure.

But If you don't want to sort through all that information, it is possible to get only the names of the columns by executing a query that selects all columns with a false WHERE clause as shown below.

SELECT *

FROM [HumanResources].[Employee]

WHERE 1=0

This query tells SQL Server to return all the columns and rows where one equals zero. Because one can never equal zero, SQL Server will return the column names from the table with no data.

Facebook

Google

Twitter