Transcription

DBA Cheat SheetServer InfoHardware InfoDatabase InfoRecovery ModelBackupBackup DeviceRestoreSQL Agent JobsTraining InfoServer InformationFind SQL Server VersionSELECT @@VERSIONFind SQL Server Version Informationxp msverFind Server PropertiesSELECT SERVERPROPERTY('productversion') AS 'Product Version',SERVERPROPERTY ('productlevel') AS 'Service Pack',SERVERPROPERTY ('edition') AS 'Edition',@@SERVERNAME As [@@SERVERNAME],CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) COALESCE('' CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As'Instance brary/ms174396.aspxFind Port Number for SQL Server InstancesIf you have more than one SQL instance on the server you’ll need to find the portnumber SQL Server is listening on. The default instance will be listening on port 1433.sp readerrorlog 0, 1, N'Server is listening 5dbdd85/spreaderrorlog?forum sqlgetstartedTopFind Account Running SQL Server AgentIn SQL 2005/2008DECLARE @ServiceAccount NVARCHAR(128);EXEC master.dbo.xp regread'HKEY LOCAL VERAGENT','ObjectName',@ServiceAccount OUTPUT;SELECT @ServiceAccount;@sqlservermamaPage 1

DBA Cheat SheetIn SQL 2012 and aboveSELECTservicename AS 'Service Name',service account,startup type desc AS 'Startup Type',status desc as 'Status',last startup time as 'Last Startup Time'FROM sys.dm server ify-sql-server-service-account-in-t-sql/Find all Available DMVs (SQL 2005 and above)--Find all DMV objects--Types:-V View-TF SQL table-valued function-IF SQL inlined table-valued functionSELECT * FROM sys.all objectsWHERE [name] LIKE '%DM %' AND [type] IN ('V', 'TF', 'IF')AND [schema id] 4; --The sys schema has schema id ry/ms188754.aspx#TopHardware InfoFind Number of ProcessorsTo find the number of cores on your server, type msinfo32 in the Windows search boxResults on my laptop:--For SQL 2005/2008SELECT cpu count AS 'Logical CPUs',hyperthread ratio AS 'Hyperthread Ratio',cpu count/hyperthread ratio AS '# of Physical CPU',physical memory in bytes/1048576 AS 'Physical Memory (MB)'--SQL 2008 also has sqlserver start time field shown belowFROM sys.dm os sys info;@sqlservermamaPage 2

DBA Cheat Sheet--For SQL 2012 and aboveSELECT cpu count AS 'Logical CPUs',hyperthread ratio AS 'Hyperthread Ratio',cpu count/hyperthread ratio AS '# of Physical CPU',physical memory kb/1024 AS 'Physical Memory (MB)',sqlserver start time AS 'SQL Server Start Time'FROM sys.dm os sys info;Reference: aspxFind Server Memory (SQL 2008 and up)SELECT * FROM sys.dm os sys library/bb510493(v sql.100).aspxFind Free Drive SpaceEXEC master.xp fixeddrives;TopDatabase InformationDisplay Database Compatibility LevelSELECT name, compatibility level,version name CASE compatibility levelWHEN 65 THEN 'SQLWHEN 70 THEN 'SQLWHEN 80 THEN 'SQLWHEN 90 THEN 'SQLWHEN 100 THEN 'SQLWHEN 110 THEN 'SQLWHEN 120 THEN 'SQLWHEN 130 THEN 'SQLENDFROM 16'Change Compatibility LevelUSE [master]GOALTER DATABASE AdventureWorks SET COMPATIBILITY LEVEL 110;List all Databases with dbid , Data FilenameSELECT name AS 'Database',dbid,crdate AS 'Create Date',filename as 'Data Filename'FROM sys.sysdatabases;Find Database ID from the Database NameSELECT db id(' AdventureWorks ') AS 'Database ID';Find Database Owner for all DatabasesSQL 2000 Syntax:SELECT suser sname(sid) FROM master.dbo.sysdatabases;SQL 2005/2008/2012/2014 Syntax:@sqlservermamaPage 3

DBA Cheat SheetSELECT name AS 'Database', suser sname(owner sid) AS 'Database Owner'FROM sys.databases;Reference: aspxTopFind Database Files, Locations, and File Sizes--List Database Files, locations, and sizesSELECTDB.name AS 'Database',MF.Name AS 'Logical File Name',MF.physical name AS 'Physical File',MF.state desc AS 'Status',CAST((MF.size*8)/1024 AS VARCHAR(26)) ' MB' AS 'File Size (MB)',CAST(MF.size*8 AS VARCHAR(32)) ' Bytes' as 'File Size (Bytes)'FROMsys.master files MFINNER JOIN sys.databases DB ON DB.database id MF.database idORDER m/en-us/library/ms178534.aspxFind All User Stored Procedures and FunctionsSystem stored procedures are prefixed by “sys”; user stored procedures default to beingprefixed by “dbo”. To find both user functions and stored procedures run the followingscript on the desired database.EXECUTE [dbo].[sp stored procedures] @sp owner ibrary/ms190504.aspxFind Creation Date of all Stored Procedures and Functions--List of all stored proceduresSELECT * FROM INFORMATION SCHEMA.ROUTINESWHERE ROUTINE TYPE N'PROCEDURE' and ROUTINE SCHEMA N'dbo' ;--List of all functionsSELECT * FROM INFORMATION SCHEMA.ROUTINESWHERE ROUTINE TYPE N'FUNCTION' and ROUTINE SCHEMA N'dbo' y/ms188757.aspxTopFind all of the user tables in the current database--Find all the user tables in the current databaseSELECT * FROM INFORMATION SCHEMA.TABLES ORDER BY TABLE brary/ms186224.aspxFind Last Time Database was AccessedThe code from the website below shows you the last time any index was accessed. Thisuses a DMV so the information will be cleared out after a server restart.SELECTlast user seek MAX(last user seek),last user scan MAX(last user scan),@sqlservermamaPage 4

DBA Cheat Sheetlast user lookup MAX(last user lookup),last user update MAX(last user update)FROMsys.dm db index usage statsWHERE[database id] DB edTopFind Database Free Space--Finds the size of the current database--In first result set:-database size - total size in MB of both data and log files-unallocated space - free space in database--In second result set:-data - total space in KB used by data-index size - total space in KB used by indexesEXEC sp us/library/ms188776.aspxFind Transaction Log Size for All DatabasesThis command will show the space of the transaction logs for all databases, along withthe percentage of the log file that’s used.DBCC .com/en-us/library/ms189768.aspxTopRecovery ModelThe recovery model determines whether you can restore to a point in time or not andhow long data is kept in the transaction log.List Recovery Model for all DatabasesSELECT name AS [Database Name],recovery model desc AS [Recovery Model]FROM sys.databases;GOReferences:Free [email protected] 5

DBA Cheat SheetSimple FULL Backup and Verify Backup CommandsTo verify that a backup file is good without performing a restore, use RESTOREVERIFY ONLY.BACKUP DATABASE MYDATABASETO DISK 'e:\Microsoft ASE backup 20130919.bak'WITH STATS 10;RESTORE VERIFYONLY FROM DISK 'e:\Microsoft ASE backup .com/en-us/library/ms188902.aspxCreate Backup File Name with DateDECLARE @Today varchar(50)SELECT @Today ),120)),'-',''),'',' '),':','')SELECT 'S:\movies ' @Today '.bak';--Results:-S:\movies 20150720 152517.bakBackup Database with Date and TimeDECLARE @MyFileName varchar(50)SELECT @MyFileName 'S:\movies FULL ' ),120)),'-',''),' ',' '),':','') '.bak'BACKUP DATABASE movies TO DISK @MyFileName WITH NOFORMAT, NOINIT, SKIP, STATS 10;Backup Transaction LogIf a database is in Full recovery mode the transaction log needs to be backed upregularly so the log file doesn’t grow out of control.--Backup the transaction log to a backup device called Movies NoonBACKUP LOG Movies TO Movies s/library/ms179313.aspxFind Databases in Full Recovery without TLog BackupsSELECT D.[name] AS [database name], D.[recovery model desc]FROM sys.databases D LEFT JOIN(SELECT BS.[database name],MAX(BS.[backup finish date]) AS [last log backup date]FROM msdb.dbo.backupset BSWHERE BS.type 'L'GROUP BY BS.[database name]) BS1 ON D.[name] BS1.[database name]WHERE D.[recovery model desc] 'SIMPLE'AND BS1.[last log backup date] IS NULLORDER BY thout-transaction-log-backups/@sqlservermamaPage 6

DBA Cheat SheetTopBackup DeviceCreate Backup DeviceCreate backup devices for movies databaseEXEC master.dbo.sp [email protected] N'disk',@logicalname N'movies 0000 Tran',@physicalname N'F:\MSSQL\Backup\Hourly Backups\movies 0000 us/library/ms188409%28v sql.105%29.aspxList All Backup DevicesSELECT * FROM sys.backup devices;Delete Backup DeviceEXEC sp dropdevice 'movies 0600 MWF ibrary/ms188903.aspxTopRestoreSimple RestoreThis does a simple full database restore of the MySampleDB database.First it finds the logical filenames using the query below. This is needed if the location ofthe data files is different from the source system, which is common in a refresh fromproduction to test. In our organization often the test database server only has 2 driveswhile production has at least 3 drives.sp helpdb MySampleDB;Results:namefilenameE:\Microsoft SQLMySampleDB1 Data DFE:\Microsoft SQLMySampleDB1 Log Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MySampleDB log.ldfIf the logical file name doesn’t match the data file name or the location is not the defaultdatabase location you’ll need to include the move arguments.--Verify no one is connected to the database to restore. You can’t restore a databasethat anyone is connected to.sp who2;RESTORE DATABASE MySampleDB FROM DISK 'E:\Restore\MySampleDB FULL 20150727 232123.bak'WITH FILE 1,MOVE N'MySampleDB1 Data' TO N'E:\Microsoft B.MDF',@sqlservermamaPage 7

DBA Cheat SheetMOVE N'MySampleDB1 Log' TO N'E:\Microsoft B log.ldf',REPLACE, STATS 10;Restore Database to Different Location than Backup SourceIf the dev/test server has different drive configuration than the production system, whenyou restore a production database to a dev/test server you’ll need to use the MOVEoption.RESTORE DATABASE MoviesFROM DISK 'E:\Restore\Movies FULL 20131217 232618.bak'WITH RECOVERY,REPLACE,STATS 10,MOVE 'Movies' TO 'E:\Microsoft SQL MOVE 'Movies log' TO 'E:\Microsoft SQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\Movies log.ldf';Find Last Restore Date of DatabaseThis script lists the last restore date of the desired database using the history tables inthe msdb database.DECLARE @dbname sysname, @days intSET @dbname 'TSQL2012' --substitute for whatever database name you wantSET @days -30 --previous number of days, script will default to 30SELECTrsh.destination database name AS [Database],rsh.user name AS [Restored By],CASE WHEN rsh.restore type 'D' THEN 'Database'WHEN rsh.restore type 'F' THEN 'File'WHEN rsh.restore type 'G' THEN 'Filegroup'WHEN rsh.restore type 'I' THEN 'Differential'WHEN rsh.restore type 'L' THEN 'Log'WHEN rsh.restore type 'V' THEN 'Verifyonly'WHEN rsh.restore type 'R' THEN 'Revert'ELSE rsh.restore typeEND AS [Restore Type],rsh.restore date AS [Restore Started],bmf.physical device name AS [Restored From],rf.destination phys name AS [Restored To]FROM msdb.dbo.restorehistory rshINNER JOIN msdb.dbo.backupset bs ON rsh.backup set id bs.backup set idINNER JOIN msdb.dbo.restorefile rf ON rsh.restore history id rf.restore history idINNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media set id bs.media set idWHERE rsh.restore date DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search forprevious daysAND destination database name ISNULL(@dbname, destination database name) --if nodbname, then return allORDER BY rsh.restore history id tabase-was-restored/TopSQL Agent [email protected] 8

DBA Cheat SheetList Jobs and Job Steps--List all jobs that contain "TTHSA" in their name"USE msdbSELECT * FROM sysjobs WHERE name LIKE '%TTHSA%'ORDER BY name;--List all job steps that have "%TTHSA " their step nameSELECT * FROM sysjobsteps WHERE step name LIKE '%TTHSA %'ORDER BY step name;Create JobSteps to create a SQL Agent job:1. Use sp add job to create the job2. Use sp add jobstep to create the job steps3. Use sp add schedule to specify when the job will run4. Use sp attache schedule to connect the job schedule to the job5. Use sp add jobserver to specify the server that will run the jobReference: /en-us/library/ms181153(v sql.105).aspxDelete JobUSE msdb;GOEXEC sp delete [email protected] name N'NightlyBackups';GODisabling JobsUSE msdbGO--Use this script to find the name of the job to disableSELECT * FROM sysjobs ORDER BY name;Disable one jobEXEC msdb.dbo.sp update [email protected] name 'My job name',@enabled 0 ;Enable a job - set @enabled 1Reference: aspxDisable all the enabled jobs containing “Hourly Backup” in their names--Creates the statements to disable specific SQL Agent jobsDECLARE @sql NVARCHAR(max) ''[email protected] N'exec msdb.dbo.sp update job @job name ''' name N''', @enabled 0;' FROM msdb.dbo.sysjobsWHERE enabled 1 and name LIKE '%Hourly Backup%'ORDER BY name;PRINT @sql;--EXEC (@sql);[email protected] 9

DBA Cheat SheetChange the owner of a job to saDECLARE @sql NVARCHAR(max) ''[email protected] N'exec msdb.dbo.sp update job @job name ''' name N''',@owner login name ''sa'';' FROM msdb.dbo.sysjobsWHERE enabled 1 AND name LIKE '%CDW%'ORDER BY name;PRINT @sql;--EXEC vermamaPage 10

DBA Cheat SheetTraining InfoFree Newsletters com/Free eBooks http://www.redgate.com/community/books/?&gclid UZI7tZLF873bUSgp4domo4GsOXSRoC9HHw S Tips Keyboard Shortcuts: spx?f 255&MSPPError -2147217396 Registered Servers: ering-sqlserver-instances-for-easier-management/ Import and Export Registered -machines/ Display Line Numbers in Query udio-query-window/ Assign Colors in SSMS Based on -query-windows-based-on-environment/ Getting Started with Code l-server-2012/ Cycle Clipboard Ring rver2012-cycle-clipboard-ring-feature/ Zoom Feature for Query server-management-studio/[email protected] 11

DBA Cheat SheetWebsitesPASS 24 Hours of PASS: http://www.sqlpass.org/Events/24HoursofPASS.aspx SQL Saturday: http://www.sqlsaturday.com/ Virtual alChapters.aspx White papers, session recordings: http://www.sqlpass.org/LearningCenter.aspxBrent Ozar Training Videos:https://learnfrom.brentozar.com/ First Aid Scripts: https://www.brentozar.com/first-aid/MSSQL Tips: /Pragmatic Works Free Webinars: http://pragmaticworks.com/Training/Courses#subject SQLServerSQL Server Central Stairway Articles: http://www.sqlservercentral.com/stairway/ Forums: .aspxSQL Skills: https://www.sqlskills.com/sql-server-resources/ Whitepapers: -serverwhitepapers/ Resources: Free Training LinksBrent Ozar os/Pragmatic Works Pragmatic Works gives free webinars every #type FreeSQL Skills These videos are geared towards the material needed to pass the MCM 2008exam. While the information is a little dated, a lot of it is still very rces/sql-server-mcm-training-videos/Online Training Brent Ozar: https://learnfrom.brentozar.com/ Pluralsight: https://www.pluralsight.com/pricing Pragmatic Works: http://pragmaticworks.com/Training/Courses#type OnDemand SQL Skills: https://www.sqlskills.com/sql-server-training/ CBT Nuggets: https://[email protected] 12

WHEN 65 THEN 'SQL Server 6.5' WHEN 70 THEN 'SQL Server 7.0' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008/R2' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' WHEN 130 THEN 'SQL Server 2016' END FROM