SQL Server Command Line Tools to Manage Your Server

SQL Server Command Line Tools to Manage Your Server

Problem
There are several useful commands and functions that are available in SQL Server, but not knowing what they are or where to find more information about them is sometimes a problem.  Having these commands at your fingertips is very helpful when trying to solve a problem or for just doing general analysis on your database instances.

Solution
Following is a list of useful commands that can be run using Query Analyzer.  A lot of this information can be retrieved using Enterprise Manager, but it is often faster to use these commands directly in Query Analyzer as well as more detailed information is provided.  Each of these commands has different functionality and knowing that the command exists and what types of information they produce is extremely helpful in managing your SQL Server environment

Command	Purpose	Sample Usage
sp_helpdb	This gives you information about all databases in the instance or specific information about one database.	sp_helpdb sp_helpdb databasename
fn_virtualfilestats	This command will show you the number of read and writes to a data file.  Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.	SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid)          SELECT * FROM :: fn_virtualfilestats(1, 1)
fn_get_sql()	Returns the text of the SQL statement for the specified SQL handle.  This is similar to using DBCC INPUTBUFFER, but this command will show you additional information.  This can also be embedded in a process easier then using the DBCC command MSSQLTips additional info	DECLARE @Handle binary(20)  SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 SELECT * FROM ::fn_get_sql(@Handle)
sp_lock	This command shows you all of the locks that the system is currently tracking  This is similar to information you can see in Enterprise Manager.	sp_lock sp_lock spid sp_lock spid1, spid2
sp_help	This command gives you information about the objects within a database.  The command without an objectname will give you a list of all objects within the database.	sp_help sp_help objectname
sp_who2	Gives you process information similar to what you see when using Enterprise Manager.	sp_who2 sp_who2 spid
sp_helpindex	Gives you information about the indexes on a table as well as the columns used for the index. MSSQLTips additional info	sp_helpindex objectname
sp_spaceused	This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.	sp_spaceused sp_spaceused objectname
DBCC CACHESTATS	Displays information about the objects currently in the buffer cache.	DBCC CACHESTATS
DBCC CHECKDB	This will check the allocation of all pages in the database as well as check for any integrity issues.	DBCC CHECKDB
DBCC CHECKTABLE	This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.	DBCC CHECKTABLE (‘tableName')
DBCC DBREINDEX	This command will reindex your table.  If the indexname is left out then all indexes are rebuilt.  If the fillfactor is set to 0 then this will use the original fillfactor when the table was created. MSSQLTips additional info	DBCC DBREINDEX (tablename, indexname,fillfactor) DBCC DBREINDEX (authors, '', 70) DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
DBCC PROCCACHE	This command will show you information about the procedure cache and how much is being used.  Spotlight will also show you this same information.	DBCC PROCCACHE
DBCC MEMORYSTATUS	Displays how the SQL Server buffer cache is divided up, including buffer activity.	DBCC MEMORYSTATUS
DBCC SHOWCONTIG	This command gives you information about how much space is used for a table and indexes.  Information provided includes number of pages used as well as how fragmented the data is in the database.	DBCC SHOWCONTIG DBCC SHOWCONTIG WITH ALL_INDEXES DBCC SHOWCONTIGtablename
DBCC SHOW_STATISTICS	This will show how statistics are laid out for an index.  You can see how distributed the data is and whether the index is really a good candidate or not.	DBCC SHOW_STATISTICS (tablename, indexname)
DBCC SHRINKFILE	This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to.  Use the sp_helpdb command along with the database name to see the actual file names used. MSSQLTips additional info	DBCC SHRINKFILE (filename, size in MB) DBCC SHRINKFILE (DataFile, 1000)
DBCC SQLPERF	This command will show you much of the transaction logs are being used.	DBCC SQLPERF(LOGSPACE)
DBCC TRACEON	This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.	DBCC TRACEON(traceflag)
DBCC TRACEOFF	This command turns off a trace flag.	DBCC TRACEOFF(traceflag)

New SQL Installations Check List   - Post Installation
1	Verify that SQL Server and dependency services configured are up and running
2	Verify that SQL Server Client tools are present on all nodes
3	Verify the Services are running under valid Service Account
4	Verify the Edition, version and appropriate Service packs installed.
5	Ensure windows fire wall is enabled and appropriate ports are enabled on the windows firewall
6	Confirm that all the protocols are enabled and Server is listening on all protocols
7	Verify that you are able to connect to SQL Server and through all protocols remotely/locally.
8	Provide access to the appropriate security groups/accounts
9	Lite Speed Installation
10	Tempdb Configuration (No Of Data files = No. Of CPUs)
11	AWE Enabled (32bit)
12	PAE Switch
13	Max Server Memory
14	Min Server Memory
15	Max Degree of Parellelism
16	Lock Pages in Memory
17	Enable Remote Connections and Check for the connectivity
18	Enable Fire wall and add the Port no in exception list
19	Create Admin database
20	Backup jobs (User & System)
21	WWSQLDB Growth job
22	Update Stats & Index maintenance jobs
23	Register the Server Name as part of tbl_server_List in EDSQLSvr
24	Raise a Request with the DPM team to have a tape backups

SQL Server BACKUP DATABASE command

SQL Server BACKUP DATABASE command    (BACKUP DATABASE)

OverviewThere are only two commands for backup, the primary is BACKUP DATABASE.  This allows you to do a complete backup of your database as well as differential, file, etc. backups depending on the options that you use.

ExplanationThe BACKUP DATABASE command gives you many options for creating backups.  Following are different examples.

Create a full backup to diskThe command is BACKUP DATABASE databaseName.  The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' GO

Create a differential backupThis command adds the "WITH DIFFERENTIAL" option.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH DIFFERENTIAL GO

Create a file level backupThis command uses the "WITH FILE" option to specify a file backup.  You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILE = 'TestBackup' TO DISK = 'C:\TestBackup_TestBackup.FIL' GO

Create a filegroup backupThis command uses the "WITH FILEGROUP" option to specify a filegroup backup.  You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' TO DISK = 'C:\TestBackup_ReadOnly.FLG' GO

Create a full backup to multiple disk filesThis command uses the "DISK" option multiple times to write the backup to three equally sized smaller files instead of one large file.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks_1.BAK', DISK = 'D:\AdventureWorks_2.BAK', DISK = 'E:\AdventureWorks_3.BAK' GO

Create a full backup with a passwordThis command creates a backup with a password that will need to be supplied when restoring the database.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH PASSWORD = 'Q!W@E#R$' GO

Create a full backup with progress statsThis command creates a full backup and also displays the progress of the backup.  The default is to show progress after every 10%.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH STATS GO

Here is another option showing stats after every 1%.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH STATS = 1 GO

Create a backup and give it a descriptionThis command uses the description option to give the backup a name.  This can later be used with some of the restore commands to see what is contained with the backup.  The maximum size is 255 characters.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' WITH DESCRIPTION = 'Full backup for AdventureWorks' GO

Create a mirrored backupThis option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK' WITH FORMAT GO

Specifying multiple optionsThis next example shows how you can use multiple options at the same time.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK' WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$' GO