Database Management

To view the database size: I have used 3 types of option to view database size:

EXEC sp_spaceused

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'TestDB'
GO

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

Some of the Maintenance DBCC Statements are:
DBCC CLEANTABLE

DBCC INDEXDEFRAG (databasename, tablename,indexname)
defragment clustered and nonclustered indexes on tables and views

DBCC DBREINDEX(tablename)
rebuilds an index for a table or all indexes defined for a table.

DBCC SHRINKDATABASE(databasename)
Shrinks the size of the data and log files in the specified database
Avoid executing this command during busy periods in production

DBCC SHRINKFILE(file_id) Use exec sp_helpfile
allows you to shrink the size of individual data and log files
Avoid, as in most cases the database will just regrow and shrinking data files causes fragmentation.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC SHRINKFILE
DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:
DBCC dllname (FREE)
DBCC HELP
DBCC FREESESSIONCACHE 
DBCC TRACEOFF - used to disable tracing
DBCC FREESYSTEMCACHE
DBCC TRACEON - used to enable tracing

Some of the Informational DBCC Statements are:
DBCC INPUTBUFFER(sessionid)
to view the last statement sent by the client connection to SQL Server

DBCC SHOWCONTIG
DBCC OPENTRAN

DBCC SQLPERF(logspace)
To see transaction log size of each database on Server

DBCC SHOWFILESTATS
Show Total Extents and used extents for database

DBCC OUTPUTBUFFER

DBCC TRACESTATUS
used to know trace status with TraceFlag,Status,Global,Session

DBCC PROCCACHE

DBCC USEROPTIONS
Returns the SET options active (set) for the current connection

DBCC SHOW_STATISTICS


Some of the Validation DBCC Statements are:
DBCC CHECKALLOC
checks page usage and allocation in the database.

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG
Checks for catalog/tables consistency within the specified database

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS 
Checks the integrity of a specified constraint or all constraints on a specified table in the current database

DBCC CHECKTABLE(tablename)
It verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets on table.

DBCC CHECKDB

T-SQL Query to find all members in server role:

SELECT SUSER_NAME(members.role_principal_id) AS [ServerRole]
   ,logins.name AS 'RoleMember'
   ,'EXEC sp_addsrvrolemember ''' +logins.name+''', '''+
   SUSER_NAME(members.role_principal_id)+'''' AS [Command to add role members]
FROM sys.server_role_members members, sys.server_principals logins
WHERE members.role_principal_id >=3 AND members.role_principal_id <=10 AND
members.member_principal_id = logins.principal_id
--and logins.name <>'sa' --Excluding system administrator

T-SQL to get the list of objects modified in x number of days:

DECLARE @Days int
SET @Days=300 -- Specify the number of days
SELECT name AS ObjectName
  ,SCHEMA_NAME(schema_id) AS SchemaName
  ,type_desc AS ObjectType
  ,create_date AS ObjectCreatedOn
  ,modify_date As ObjectModifiedOn
FROM sys.objects
WHERE modify_date > GETDATE() - @Days
ORDER BY modify_date;
GO