SQL: Random Notes

How to set the database from Single_User to Multi_User

ALTER DATABASE <databasename>
SET MULTI_USER;

If you're working on SQL Server Management, and one query window is connected to the database you'd like to alter, switch the database to master and run the alter query again.

Or to Single_User:
ALTER DATABASE <databasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATE


To set the recovery model to simple:
ALTER DATABASE <database name> SET RECOVERY SIMPLE WITH NO_WAIT


Get some local system information:

SELECT NAME as "Server" FROM SYS.SERVERS; -- list of servers including linked servers
SELECT HOST_NAME() as "Host";
SELECT @@version as "Version";


To get some backup information - when the database was last backed up

 select database_name, type, backup_size, backup_start_date, backup_finish_date, DATEDIFF(dd,backup_start_date,getDate()) as "days"
from msdb.dbo.backupset s
where s.backup_set_id in
(
select max(s1.backup_set_id)
from msdb.dbo.backupset s1
where s1.database_name = s.database_name and s1.type = s.type
) and s.database_name in
(
select name
from msdb.sys.databases
)
order by database_name, type;

Log space
 DBCC sqlperf(logspace);


Database size

 select s.instance_name 'database', s.cntr_value DataSize, (select s1.cntr_value from sys.sysperfinfo s1 where s1.instance_name=s.instance_name and counter_name in ('Log File(s) Size (KB)')) LogFileSize,
(select s1.cntr_value from sys.sysperfinfo s1 where s1.instance_name=s.instance_name and counter_name in ('Log File(s) Used Size (KB)')) LogFileUsedSize
from sys.sysperfinfo s
where s.counter_name in ('Data File(s) Size (KB)')
and s.instance_name not in ('_Total', 'mssqlsystemresource')
order by s.instance_name;