Upload File

sql server command line tools to manage your server

  • Home
  • Documents
  • SQL Server Command Line Tools to Manage Your Server
4
enter email address Join Latest SQL Server Tips SQL Server Monitoring with Powershell and the SQL Server Platform: Data Collection (Part 1 of 3) The SQL Server Job Management You Wish You Had SQL Server Command Line Tools To Manage Your Server By: Greg Robidoux | Read Comments (4) | Related Tips: More > Monitoring Problem There are several useful commands and functions that are available in SQL Server, but not knowing what they are where to find more information about them is sometimes a problem. Having these commands at your fingertips is 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 retrie 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 comman 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

Upload: nikhil-vyas

Post on 13-Sep-2015

233 views

Category:

Documents


7 download

Report

DESCRIPTION

important sql tools

TRANSCRIPT

  • 5/14/2015 SQLServerCommandLineToolsToManageYourServer

    http://www.mssqltips.com/sqlservertip/1029/sqlservercommandlinetoolstomanageyourserver/ 1/4

    enteremailaddress Join

    LatestSQLServerTips

    SQLServerMonitoringwithPowershellandtheSQLServerPlatform:DataCollection(Part1of3)

    TheSQLServerJobManagementYouWishYouHad

    SQLServerCommandLineToolsToManageYourServer

    By:GregRobidoux|ReadComments(4)|RelatedTips:More>Monitoring

    ProblemThereareseveralusefulcommandsandfunctionsthatareavailableinSQLServer,butnotknowingwhattheyareorwheretofindmoreinformationaboutthemissometimesaproblem.Havingthesecommandsatyourfingertipsisveryhelpfulwhentryingtosolveaproblemorforjustdoinggeneralanalysisonyourdatabaseinstances.

    SolutionFollowingisalistofusefulcommandsthatcanberunusingQueryAnalyzer.AlotofthisinformationcanberetrievedusingEnterpriseManager,butitisoftenfastertousethesecommandsdirectlyinQueryAnalyzeraswellasmoredetailedinformationisprovided.EachofthesecommandshasdifferentfunctionalityandknowingthatthecommandexistsandwhattypesofinformationtheyproduceisextremelyhelpfulinmanagingyourSQLServerenvironment

    Command Purpose SampleUsagesp_helpdb Thisgivesyouinformationaboutalldatabasesinthe

    instanceorspecificinformationaboutonedatabase. sp_helpdbsp_helpdbdatabasename

    fn_virtualfilestats Thiscommandwillshowyouthenumberofreadandwritestoadatafile.Usesp_helpdbwiththedatabasenametoseethelogicalfilenumbersforthedatafilesandthedatabaseid.

    SELECT*FROM::fn_virtualfilestats(dabaseid,logicalfileid)SELECT*FROM::fn_virtualfilestats(1,1)

    fn_get_sql() ReturnsthetextoftheSQLstatementforthespecifiedSQLhandle.ThisissimilartousingDBCCINPUTBUFFER,butthiscommandwillshowyouadditionalinformation.ThiscanalsobeembeddedinaprocesseasierthenusingtheDBCCcommand

    MSSQLTipsadditionalinfo

    DECLARE@Handlebinary(20)SELECT@Handle=sql_handleFROMsysprocessesWHEREspid=52SELECT*FROM::fn_get_sql(@Handle)

    sp_lock ThiscommandshowsyouallofthelocksthatthesystemiscurrentlytrackingThisissimilartoinformationyoucanseeinEnterpriseManager.

    sp_locksp_lockspidsp_lockspid1,spid2

    sp_help Thiscommandgivesyouinformationabouttheobjectswithinadatabase.Thecommandwithoutanobjectnamewillgiveyoualistofallobjectswithinthedatabase.

    sp_helpsp_helpobjectname

    sp_who2 GivesyouprocessinformationsimilartowhatyouseewhenusingEnterpriseManager. sp_who2

    sp_who2spid

    sp_helpindex Givesyouinformationabouttheindexesonatableaswellasthecolumnsusedfortheindex.

    MSSQLTipsadditionalinfo

    sp_helpindexobjectname

    sp_spaceused Thiscommandshowsyouhowmuchspacehasbeenallocatedforthedatabase(orifspecifiedanobject)andhowmuchspaceisbeingused.

    sp_spaceusedsp_spaceusedobjectname

    DBCCCACHESTATS Displaysinformationabouttheobjectscurrentlyinthebuffercache. DBCCCACHESTATS

    DBCCCHECKDB Thiswillchecktheallocationofallpagesinthedatabaseaswellascheckforanyintegrityissues. DBCCCHECKDB

  • 5/14/2015 SQLServerCommandLineToolsToManageYourServer

    http://www.mssqltips.com/sqlservertip/1029/sqlservercommandlinetoolstomanageyourserver/ 2/4

    TroubleshootingCommonSQLServerBulkInsertErrors

    SQLServerAnalysisServiceLevelsofGranularity

    MonitorSQLServerTransactionLogFileFreeSpace

    FreeSQLServerLearning

    Avoiding,Detecting,andRepairingSQLServerDatabaseCorruption

    IsHApittingSQLDatabaseAdminsagainstVMAdmins?KeepingthePeacewithSANLessclusters.

    TheArtofStressFreeDatabaseAdministration

    SQLServerAuditwithSQLComplianceManagerandSQLSecure

    HowtoRollyourOwnValue,RegExandSoundExPatternProfilerinSSIS

    GregRobidouxisthePresidentofEdgewoodSolutionsandacofounderofMSSQLTips.com.

    Viewallmytips

    RelatedResources

    MoreSQLServerDBATips...

    DBCCCHECKTABLE Thiswillchecktheallocationofallpagesforaspecifictableorindexaswellascheckforanyintegrityissues. DBCCCHECKTABLE

    (tableName')

    DBCCDBREINDEX Thiscommandwillreindexyourtable.Iftheindexnameisleftoutthenallindexesarerebuilt.Ifthefillfactorissetto0thenthiswillusetheoriginalfillfactorwhenthetablewascreated.

    MSSQLTipsadditionalinfo

    DBCCDBREINDEX(tablename,indexname,fillfactor)DBCCDBREINDEX(authors,'',70)DBCCDBREINDEX('pubs.dbo.authors',UPKCL_auidind,80)

    DBCCPROCCACHE Thiscommandwillshowyouinformationabouttheprocedurecacheandhowmuchisbeingused.Spotlightwillalsoshowyouthissameinformation.

    DBCCPROCCACHE

    DBCCMEMORYSTATUS DisplayshowtheSQLServerbuffercacheisdividedup,includingbufferactivity. DBCCMEMORYSTATUS

    DBCCSHOWCONTIG Thiscommandgivesyouinformationabouthowmuchspaceisusedforatableandindexes.Informationprovidedincludesnumberofpagesusedaswellashowfragmentedthedataisinthedatabase.

    DBCCSHOWCONTIGDBCCSHOWCONTIGWITHALL_INDEXESDBCCSHOWCONTIGtablename

    DBCCSHOW_STATISTICS Thiswillshowhowstatisticsarelaidoutforanindex.Youcanseehowdistributedthedataisandwhethertheindexisreallyagoodcandidateornot.

    DBCCSHOW_STATISTICS(tablename,indexname)

    DBCCSHRINKFILE Thiswillallowyoutoshrinkoneofthedatabasefiles.Thisisequivalenttodoingadatabaseshrink,butyoucanspecifywhatfileandthesizetoshrinkitto.Usethesp_helpdbcommandalongwiththedatabasenametoseetheactualfilenamesused.

    MSSQLTipsadditionalinfo

    DBCCSHRINKFILE(filename,sizeinMB)DBCCSHRINKFILE(DataFile,1000)

    DBCCSQLPERF Thiscommandwillshowyoumuchofthetransactionlogsarebeingused. DBCC

    SQLPERF(LOGSPACE)

    DBCCTRACEON Thiscommandwillturnonatraceflagtocaptureeventsintheerrorlog.TraceFlag1204capturesDeadlockinformation.

    DBCCTRACEON(traceflag)

    DBCCTRACEOFF Thiscommandturnsoffatraceflag.DBCCTRACEOFF(traceflag)

    NextSteps

    GetfamiliarwiththesecommandlinefunctionsthatyoucanputtouseimmediatelyTaketimetolearnmoreaboutthesedifferentoptionsandwhenspecificcommandsshouldbeusedandhowtheyshouldbeusedLookforfuturetipsoneachofthesecommands

    LastUpdate:7/28/2006

    Abouttheauthor

    Print Tweet 0 BecomeapaidauthorShare 0Like

  • 5/14/2015 SQLServerCommandLineToolsToManageYourServer

    http://www.mssqltips.com/sqlservertip/1029/sqlservercommandlinetoolstomanageyourserver/ 3/4

    SQLServerSecurityEssentials

    EnterEmailAddress Join

    LearnMore

    Postacommentorlettheauthorknowthistiphelpedyou.Allcommentsarereviewed,sostayonsubjectorwemaydeleteyourcomment.

    *Name *Email Notifyforupdates

    ***NOTE***IfyouwanttoincludecodefromSQLServerManagementStudio(SSMS)inyourpost,pleasecopythecodefromSSMSandpastethecodeintoatexteditorlikeNotePadbeforecopyingthecodebelowtoremovetheSSMSformatting.

    Note:youremailaddressisnotpublished.Requiredfieldsaremarkedwithanasterisk(*)

    GetfreeSQLtips:

    *EnterCode SaveComment

    Tuesday,January28,20149:47:14PMSaritHod ReadTheTip

    CanyoupleasementionSQLS*PlusthisisagreatfreecommandlinetoolforSQLServer

    Saturday,November03,20122:26:33AMRon ReadTheTip

    Thanks.

    DoyouhaveupdatesforSS2008R2?

    Wednesday,July18,20122:01:25PMJeremyKadlec ReadTheTip

    Sandip,

    Iwouldcheckoutthesetips:

    http://www.mssqltips.com/sqlservertipcategory/25/maintenance/

    http://www.mssqltips.com/sqlservertipcategory/91/maintenanceplans/

    HTH.

    Thankyou,JeremyKadlec

    Wednesday,July18,20121:51:21AMsandipdeshmukh ReadTheTip

    informationregardingSQLServerCommandLineToolsToManageYourServerisverygoodwithallimportantinformation.

    thanks,

    ifposibblepleasesendmethealldatabasescriptwhichicanuseforDatabasemaintanance.

    thanks&Regards,

    SandipDeshmukh

    Paragraph

  • 5/14/2015 SQLServerCommandLineToolsToManageYourServer

    http://www.mssqltips.com/sqlservertip/1029/sqlservercommandlinetoolstomanageyourserver/ 4/4

    SponsorInformation

    Follow

    GetFreeSQLTips

    Twitter

    LinkedIn

    Google+

    Facebook

    Pinterest

    RSS

    Learning

    DBAs

    Developers

    BIProfessionals

    Careers

    QandA

    Today'sTip

    Resources

    Tutorials

    Webcasts

    Whitepapers

    Tools

    Search

    TipCategories

    SearchByTipID

    TopTen

    Authors

    Community

    FirstTimer?

    Pictures

    FreeTshirt

    Contribute

    Events

    UserGroups

    AuthoroftheYear

    MoreInfo

    Join

    About

    Copyright

    Privacy

    Disclaimer

    Feedback

    Advertise

    Copyright(c)20062015EdgewoodSolutions,LLCAllrightsreservedSomenamesandproductslistedaretheregisteredtrademarksoftheirrespectiveowners.


SQL Server Hardening - Cisco · SQL Server Hardening • SQLServerHardeningConsiderations,page1 • SQLServer2014SecurityConsiderations,page3 SQL Server Hardening Considerations

WordPress.comUpgrade from SQL Server 2000, SQL Server 2005 or SQL Server 2008 Launch a wizard to upgrade SQL Server 2000, SQL Server 2005 or SQL Server 2008 to SQL 2008 RI. Search

Sql command

Sql server ___________ (advance sql)

SQL Server 2014 and the Data Platform - EOS Solutions · SQL Server 2000 SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Server 2012 XML KPIs Management Studio Mirroring Compression

sql server dba training | sql server dba training online | sql server dba course

Microsoft - latesttest.com. Run the DBCC TRACEON 1222 command from a query window and review the SQL ... You administer a Microsoft SQL Server 2012 server that hosts a …

DBI319. Demo SQL Server 2005 SP2 SQL Server 2008 SQL Server 2000 SP4 SQL Server 2008 R2 SQL Server 2008 SP2 SQL Server 2008 R2 SQL

SQL Server 2005: SQL Server and ADO - download.microsoft.comdownload.microsoft.com/documents/australia/teched2005/hol/HOL06… · SQL Server 2005: SQL Server and ADO.NET Page 3 of

SQL Server 2012 - SQL, Transact SQL

Steps to install SQL Server 2017cis.csuohio.edu/.../SQL...Installation_Guide.pdf · Windows Server, Visio, Visual Studio, SQL Server, Project, Access, etc. 5. Select SQL Server 2017

Modern Storage Strategies for SQL Server - ActualTech Media€¦ · SQL Server I/O Performance 19 SQL Server Workload Patterns 20 Architecting the SQL Server I/O Stack 22 SQL Server

We Practice What We Teach. Public Web Site Outages Web Servers SQL Server SQL Server SQL Server SQL Server SQL Server SQL Server SQL Server SQL Server

Microsoft SQL Server - SQL Server + Visual Studio Presentation

Microsoft SQL Server 2005. Versioni di Microsoft SQL Server 2005 SQL Server EXPRESS Edition SQL Server MOBILE Edition SQL Server WORGROUP Edition SQL

SQL Server on Linux Fundamentals for DBAs · Docker Engine 1.8+ Command What it does ... fixes/security updates mssql-server-2017-gdr SQL Server 2017 GDR repository for critical updates

Backup service help - SERVER4YOU · Microsoft SQL Server 2014 Microsoft SQL Server 2012 Microsoft SQL Server 2008 R2 Microsoft SQL Server 2008 Microsoft SQL Server 2005 2.4 Supported

Advanced SQL Server Troubleshooting SQL Server 2008... · Bring your SQL Server Bring your SQL Server installations to installations to a new level of excellence! Advanced SQL Server

MySQL MySQL & SQL server & SQL server - Prva stranawebdizajn.ict.edu.rs/sites/default/files/kurs_material/Web... · MySQL MySQL & SQL server & SQL server Drr Nenad KojiNenad Koji

Volume 1: Sybase Adaptive Server™ Enterprise ...dis.unal.edu.co/~icasta/man_sybase/svrtsg1.pdfSQL Server/DBM, SQL Server Manager, ... Command: dbcc traceon ... dbcc checkstorage

Deploy a modern data platform with SQL Server 2019 on Red ... · SQL Server command-line tools (sqlcmd, bcp) available for Linux and MacOS Existing Windows tools such as SQL Server

SQL Injection - USF Computer Scienceejung/courses/683/lectures/sql.pdf · Another SQL Injection Example To authenticate logins, server runs this SQL command against the user database:

SQL Server Heterogêneo: SQL Server + BigData

SQL Server Setup - using SQL Server authentication

SQL Server DB (dbreport) Report : SQL Server Objects Reportsqlserverbook.sourceforge.net/samples/docbook-full-report.pdf · SQL Server DB (dbreport) Report : SQL Server Objects Report

SQL Server Data Mining for SQL Server Professionals

SQL-Server 2012 Always On. SQL Server “SQL-Server 2012” Highlights High Availability SQL Server AlwaysOn Security & Manageability User-Defined Server

Chapter 4 SQL. SQL server Microsoft SQL Server is a client/server database management system. Microsoft SQL Server is a client/server database management

440900914440900 - kutub-download.com · 440900914440900 2 : 1-Microsoft SQL Server 2-Visual Basic.NET Visual Basic.NET SQL SQL Server SQL Server Microsoft SQL Server SQL

How to Connect to CDL SQL Server Database via Internet · 5. Using SQL Server and SQL Server Linked Server Connect to CDL SQL Server using SQL server Management studio is very easy

SQL Server 2008 Report Builder 2 - fromelard.free.frfromelard.free.fr/Scripts/SQL-Server-Report-Builder/SQL-Server... · SQL Server 2008 Report Builder 2.0 Installation et utilisation

Power BI Sales Deck - download.microsoft.comdownload.microsoft.com/.../Tehnologijas_sql2014-A3.pdf · SQL Server 2000 SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Server

Geek Guide > SQL Server on Linux - suse.com · Microsoft SQL Server is an offering that includes not ... SQL Server 2012, SQL Server 2014 and SQL Server 2016). SQL Server works on

SQL Server Benchmarking The Powershell speedometer Server Benchmarking.pdf · SQL SERVER BENCHMARKING THE POWERSHELL SPEEDOMETER. ... Gather and parse SQL Server information ... SQL

Using Delphix Server with Microsoft SQL Server (BETA) · Using Delphix Server with Microsoft SQL Server (BETA) 9 Working with the Command Line Interface The Command Line Interface