To check the date and time SQL Server last restarted, execute the T-SQL script below.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
No comments
Tag: Query Tuning
SQL Server – View Recovery Model using T-SQL
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
GO
The following table summarizes the three recovery models.
SQL SERVER – LCK_M_XXX – Wait Type
Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data, at the same time. In simpler words, it maintains the integrity of data by protecting (or preventing) access to the database object.
LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.
LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.
LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock.
LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.
LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.
LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.
LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.
LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.
LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.
LCK_M_U
Occurs when a task is waiting to acquire an Update lock.
LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.
LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.
SQL Server – Resolve Error 5030 to Rename Database
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
SQL Server – Identifying Current Running Queries with Database Name
Use the script below to get the current running queries on your server.
SELECT sqltext.TEXT ,DB_Name(req.database_id) as DatabaseName ,sess.last_request_start_time ,req.total_elapsed_time/1000 as total_elapsed_time ,sess.session_id AS SessionID ,req.blocking_session_id AS BlockedBy ,req.command ,req.cpu_time AS CPU_Time ,sess.HOST_NAME as RunningFrom ,sess.login_name ,wait_type ,wait_time ,sess.status ,req.reads ,req.writes ,req.logical_reads ,sess.program_name FROM sys.dm_exec_requests req INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id AND sess.is_user_process = 1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext WHERE DB_Name(req.database_id) NOT IN ('master','tempdb') ORDER BY total_elapsed_time DESC