Useful Links
http://lextonr.wordpress.com/ - T-SQL Ref
SQL Server 2005 Build Listhttp://www.sqlservercentral.com/articles/Administration/2960/
SQL Server 2008 Build List
http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/
SQL Server 2008R2 Build List
http://www.sqlservercentral.com/articles/SQL+Server+2008+R2/70092/
Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408.aspx
http://msdn.microsoft.com/en-us/library/ms345408(v=sql.90).aspx
Trace Flags
http://msdn.microsoft.com/en-us/library/ms188396.aspx
Creating Stored Procedure Tutorial from Search SQL Server
http://searchsqlserver.techtarget.com/tutorial/SQL-Server-stored-procedures-tutorial-Write-tune-and-get-examples?utm_source=baynote&utm_medium=web&utm_campaign=People+Who+Read+This+Also+Read
USEFUL TRANSACT SQL STATEMENTS.
Mirroring:
Pause mirroring – ALTER DATABASE <database_name> SET PARTNER SUSPEND
Resume – ALTER DATABASE <database_name> SET PARTNER RESUME
Remove – ALTER DATABASE <database_name> SET PARTNER OFF
Remove Witness – ALTER DATABASE <database_name> SET WITNESS OFF
Recover Mirror DB – RESTORE DATABASE <database_name> WITH RECOVERY
To list all the database mirror endpoints - Select * from sys.database_mirroring_endpoints
Increase Timeout - ALTER DATABASE DBNAME SET PARTNER TIMEOUT
Give permission on endpoint – GRANT CONNECT ON ENDPOINT ::<mirroring >TO [domain\username]
Active Connections and Blocking:
EXEC sp_who2 ACTIVE
dbcc page (16, 1,79579) with tableresults
select * from sys.dm_os_waiting_tasks
select * from sys.dm_os_wait_stats
sp_who2 xx xx=SPID
Emergency Repair of Suspect DB:
This is only to be used in an absolute emergency as Data loss is inevitable!!!!!
EXEC sp_resetstatus '<database_name>';
ALTER DATABASE <database_name> SET EMERGENCY
DBCC checkdb('<database_name>')
ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('<database_name>', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <database_name> SET MULTI_USER
USEFUL Log File Commands:
DBCC LOGINFO (‘<database_name’)
The above command will give you information regarding the Log file for the required database. In the status column you will either have a 0 which means that logical file is empty or 2 which means it is not, you can only shrink the log file if there is space at the end of the log file.
DBCC SHRINKFILE
(N’<database_file_name>’, 0, TRUNCATEONLY)
Set Recovery Mode
ALTER DATABASE <database_name> SET RECOVERY FULL
Show Active Connections
SP_WHO sp_WHO2
Stop Trace
exec sp_trace_setstatus
Display I/O bandwidth
select db_name(dbId) as 'Database', * from ::fn_virtualfilestats(null,null) order by IoStallMS desc
Drop all connections to a database
USE master
GO
ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE
GO
ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE
Disable Ghost Cleanup Process
DBCC TRACEON (661, -1) - Turns off (Stop and Start SQL Services to apply), the -1 in the command sets the Trace Flag to be Global. 661 being the Trace Flag for the Global Cleanup Process
DBCC TRACESTATUS - This command will print what Trace Flags are set.
DBCC TRACEOFF (661, -1) - Turns on (Stop and Start SQL Services to apply)
Number of Connections per DB and per login
select db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
group by db_name(dbid)
order by count(*) desc
select loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
group by loginame , nt_username
order by count(*) desc
