What Is Filling Up The Transaction Log

SELECT db.[name] AS [Database Name] ,
 db.recovery_model_desc AS [Recovery Model] ,
 db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
 ls.cntr_value AS [Log Size (KB)] ,
 lu.cntr_value AS [Log Used (KB)] ,
 CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
 AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
 db.[compatibility_level] AS [DB Compatibility Level] ,
 db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
 INNER JOIN sys.dm_os_performance_counters AS lu
 ON db.name = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls
 ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;


Reference: http://www.sqlservercentral.com/scripts/DMV/71997/

Find what script is being run by specific SPID number

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

Difference between Detaching database and bringing database Offline

SQL Server Offline and Detach Database




Detach Database/Attach Database: The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

you can attach a copied or detached SQL Server database. When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:

• For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.
  If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.
• If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.

Database Offline: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.


Use OFFLINE and ONLINE
1) If you are trying to make the database temporary unavailable for a period of time, you could take the database OFFLINE, and make it available by bringing it ONLINE whenever it is or you are ready.

2a) If you want to move the database or log files to different physical location or changing the database and/or log physical file name, AND keep them within the same SQL Server instance.
- Before you take the database offline, you need to know the logical name of the file. The name field is the logical name for the physical file name that you are about to change the path. Record the logical name for later use.

SELECT name, type, type_desc, physical_name, state, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase')

- Take the database OFFLINE.
ALTER DATABASE YourDatabase SET OFFLINE
WITH ROLLBACK IMMEDIATE;

-Move the database and/or log files to different location.
- Update the database and/or log files path registered on its SQL Server instance. Execute the T-SQL command for each database and log files,

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
FILENAME = 'Your Database or Log new physical path')

Bring the database ONLINE.
ALTER DATABASE YourDatabase SET ONLINE;

If you just want to change the logical name, you dont have to take the database OFFLINE. You could change it on SSMS or T-SQL.

Right click on the database > Properties > Select Page > Change it under Logical Name column > OK.

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
NEWNAME = YourFileNewLogicalName)

2b) If you are relocating a file during part of the scheduled disk maintenance process, you may need to change the file path registered on its SQL Server instance before taking the database OFFLINE. This assure the server has the right path for the database and log files when the instance restart, after being shut down for maintenance.
- When the database and/or log file path is successfully updated on the instance, a message will show; 'The file "YourDatabaseFile" has been modified in the system catalog. The new path will be used the next time the database is started.'

Use DETACH and ATTACH,
1) If you want to move the database and log files to a different SQL Server instance or another server.
- Rollback active transaction and gain exclusive access. Detach the database. (optional: update statistics) 

ALTER DATABASE YourDatabase SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

EXEC sp_detach_db 'YourDatabase', 'true'

- Move database and log files to different location.
- Attach the database on different instance on same/another server. 

USE master;

CREATE DATABASE YourDatabase
ON (FILENAME = 'the physical path of your database file')
LOG ON (FILENAME = 'the physical path of your log file')
FOR ATTACH;

- If the database has previously set to single user, you may want to set it to multiple user access option.

ALTER DATABASE YourDatabase SET MULTI_USER

2) Even though you can choose to detach and attach the database back on the same SQL Server instance for scenario like changing physical path or file name, it is recommended to use the OFFLINE and ONLINE methods due to the restriction and limitation on DETACH. When you detach a database, you remove the database from the instance. It is required to remove the database from any participation of replication, mirroring and snapshot.

Reference: 

Script to find SQL Server Engine Edition

Finding SQL Server EngineEdition:

SELECT SERVERPROPERTY('EngineEdition');

Database Engine edition of the instance of SQL Server installed on the server.
1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)
2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services)
5 = SQL Database

Description:

The EngineEdition property returns a value of 2 through 5

Value 1: 1 isn’t a valid value in versions after SQL Server 2000,

Value 2: If value is 2, edition is either Standard, Web, or Business Intelligence, and fewer features are available. The features in Enterprise edition (as well as in Developer and Enterprise Evaluation editions) that aren’t in Standard edition generally relate to scalability and high-availability features, but other Enterprise-only features are available

Value 3: A value of 3 indicates that SQL Server edition is either Enterprise, Enterprise Evaluation, or Developer. These three editions have exactly the same features and functionality.

Value 4: A value of 4 for EngineEdition indicates that your SQL Server edition is Express, which includes SQL Server Express, SQL Server Express with Advanced Services, and SQL Server Express with Tools.

Value 5: Value of 5 for EngineEdition indicates that SQL Azure, a version of SQL Server that runs as a cloud-based service. Although many SQL Server applications can access SQL Azure with only minimum modifications because the language features are very similar between SQL Azure and a locally installed SQL Server.

Script to find what Permissions apply to what class of securables in SQL Server

SELECT *
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY class_desc