Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Command Line Tools To Manage Your Server


By:   |   Read Comments (5)   |   Related Tips: More > Monitoring

Attend these FREE MSSQLTips webcasts >> click to register


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 SHOWCONTIG tablename
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)
Next Steps
  • Get familiar with these command line functions that you can put to use immediately
  • Take time to learn more about these different options and when specific commands should be used and how they should be used
  • Look for future tips on each of these commands


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 25, 2017 - 12:42:02 AM - ayub Back To Top

 Its so helpful

 


Tuesday, January 28, 2014 - 9:47:14 PM - Sarit Hod Back To Top

Can you please mention SQLS*Plus - this is a great free command line tool for SQL Server


Saturday, November 03, 2012 - 2:26:33 AM - Ron Back To Top

Thanks.

Do you have updates for SS2008R2?


Wednesday, July 18, 2012 - 2:01:25 PM - Jeremy Kadlec Back To Top

Sandip,

I would check out these tips:

http://www.mssqltips.com/sql-server-tip-category/25/maintenance/

http://www.mssqltips.com/sql-server-tip-category/91/maintenance-plans/

HTH.

Thank you,
Jeremy Kadlec


Wednesday, July 18, 2012 - 1:51:21 AM - sandip deshmukh Back To Top

information regarding SQL Server Command Line Tools To Manage Your Server is very good with all important information.

thanks,

if posibble please send me the all database script which i can use for Database maintanance.

 

 

thanks & Regards,

Sandip Deshmukh


Learn more about SQL Server tools