MS SQL Frequently

===Check sql service==============

services.msc and check for sqlserver/agent

mssqlserver : Default Instace

sqlexpress  :  Named Instance

mssqlserver agent

To  open the sql server managemen studio

2005-- sqlwb

R12/2008—ssms

Version of sql server

SELECT @@VERSION

Post number details
SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL

======queries=====================

          database status,to check recovery mode

select name,state_desc, recovery_model_desc,log_reuse_wait_desc,* from sys.databases

          refresh/clone date

use msdb

go

select restore_date,destination_database_name from dbo.restorehistory

====== Space/ File  alert ===============

   .mdf ->  main datafile

   .ndf  -> next datafile 

   .ldf -> transaction log file

xp_fixeddrives  - disk drive size

sp_spaceused   - database size

sp_helpfile  - log file size

sp_spaceused 'sitecontrol'   - Table size

sp_helpuser 'Username'   - User details 

sp_readerrorlog - ' Read error log '



dbcc sqlperf(logspace) : To check logfile space.

  DBCC SHRINKFILE (DataFile1, 7);

  DBCC SHRINKFILE 'SMS_D00'

   truncate table sitecontrol

=======table size in ms sql server=============

SELECT

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

    sys.schemas s ON t.schema_id = s.schema_id

WHERE

    t.NAME NOT LIKE 'dt%'

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255

GROUP BY

    t.Name, s.Name, p.Rows

ORDER BY

    t.Name

============to take  backup from query window=============

transactional log backup

BACKUP LOG EVCentera1

TO DISK = 'O:\Backup\EVCentera1_one.TRN'

WITH STATS = 1

Database backup

BACKUP DATABASE EVC
 TO DISK = 'O:\Backup\EVC.bak'

====To see the longest running transaction on your SQL Server instance============

--use tempdb

--go

dbcc opentran

sp_who2 active

select DB_NAME(database_id),* from sys.dm_exec_requests
where session_id>51

 SELECT * FROM sys.dm_exec_sessions

WHERE session_id = spid 

OR

sp_who2 spid

to find the sql stetement

DBCC INPUTBUFFER(spid)

====backup info============================

SELECT TOP 100

s.database_name,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

--WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY backup_start_date DESC, backup_finish_date

=== Log Shipping===================

Check on secondary/standby node

Check the secondary database status

SELECT secondary_database,

restore_mode,

disconnect_users,

last_restored_file

 FROM msdb.dbo.log_shipping_secondary_databases

 If mode is 1 then standby/reporting and if mode is 0 then not available to end users/DR

Last log applied

 SELECT secondary_server,

secondary_database,

primary_server,

primary_database,

last_copied_file,

last_copied_date,

last_restored_file,

last_restored_date

from msdb.dbo.log_shipping_monitor_secondar

------------------------------Data file size----------------------------

if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go

insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'


go

select * from #dbsize
-------------------log size--------------------------------------
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go

insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'

go

select * from #logsize