最後更新: 2015-03-12

目錄

• sqlcmd Utility
• SQL Server Command Line Tools
•  

---

sqlcmd Utility

 

指定連那 Server

-S [protocol:]server[instance_name][,port]

# Use trusted connection (Default)

-E

# login

-U login_id

-P password

# 預設 DB

-d Target_DB

# SQL file

-i input_file[,input_file2...]

# The file that receives output from sqlcmd.

-o output_file

Usage Example

Exampe

# SQL Shell

sqlcmd -S "<ComputerName>\<InstanceName>"

sqlcmd -S ".\SQLEXPRESS"

 * The sqlcmd prompt (1>) indicates that you are connected to the specified instance of SQL Server.

# Run Script

sqlcmd -S SQLCLUSTER -i Z:\scripts\shrink-db\shrink-log.sql -o Z:\scripts\shrink-db\shrink-log.log

 

---

Single quotes vs Double quotes

 

    [S]ingle quotes are for [S]trings Literals (date literals are also strings);

    [D]ouble quotes are for [D]atabase Identifiers;

 

---

SQL Server Command Line Tools

 

sp_helpdb

information about all databases in the instance

sp_helpdb [databasename]

name, db_size, owner, dbid, created, status, compatibility_level

 

sp_who / sp_who2    

Gives you process information similar to what you see when using Enterprise Manager.

sp_who2 [spid]

SPID, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch ..

status

suspended

A query gets suspended when it is requesting access to a resource that is currently not available.
This can be a logical resource like a locked row or a physical resource like a memory data page.
The query starts running again, once the resource becomes available.
High disk IO means that a lot of data pages need to be accessed to fulfill the request.

running

sleeping

RUNNABLE

PENDING

The request is waiting for a worker to pick it up.
(This means the request is ready to run but there are no worker threads available to execute the requests in CPU. )

BACKGROUND

The request is a background thread such as Resource Monitor or Deadlock Monitor.
 

connection limit

Default max User connections: 32767

SELECT @@MAX_CONNECTIONS AS 'Max Connections';

To reconfigure SQL Server for fewer connections, use sp_configure.

* It is a per-instance

Connection

SELECT
     COUNT(*)
FROM
     master.dbo.syslockinfo

Connection pool

Most web applications use a "connection pool" for SQL connections.  This is a normal operation.  Leave it alone.

Openning and closing connections is very time consuming.

You do NOT want to open and close several thousand connections per min.

sp_spaceused

shows you how much space has been allocated for the database

sp_spaceused

sp_spaceused objectname

i.e.

use DBName;
GO
sp_spaceused "Table_1";

output

name, rows, reserved, data, index_size, unused

index_size: Total amount of space used by indexes in objname.

unused: Total amount of space reserved for objname but not yet used.

 * database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

sp_help

information about the objects within a database.

    sp_help
    sp_help objectname

 

sp_lock

shows you all of the locks that the system is currently tracking

    sp_lock
    sp_lock spid
    sp_lock spid1, spid2

check_version

Select @@version

 

---

Temp table

 

Local Temporary Tables(#table_name)

* it is only accessible via the current session

Global temporary tables(##table_name)

* Global temporary tables are visible to all sessions
* They are deleted when all users referencing the table disconnect from Microsoft SQL Server.

# create temp tables

* You need to prefix the table name with "#" (hash) to create temporary tables.

create table #tmp (Col1 int, Col2 int);

 

---

Check DB Path

 

<1> 只有 Master DB (sys.database_files)

SELECT * FROM sys.database_files

1    NULL    0    ROWS    1    master    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
2    NULL    1    LOG    0    mastlog    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

<2> 所有 database file (sys.sysaltfiles)

select * from sys.sysaltfiles

# 看上去很亂

# fileid groupid size maxsize growth status perf dbid name filename

<3> sys.master_files

SELECT name, physical_name AS current_file_location FROM sys.master_files

# database_id file_id file_guid type type_desc data_space_id name physical_name state stat_desc size

 

---

Check DB

 

# Checks the logical and physical integrity of all the objects in the specified database

usage

DBCC CHECKDB [(database_name | database_id)] [REPAIR_REBUILD EXTENDED_LOGICAL_CHECKS]

example

DBCC CHECKDB;
GO

output

.....................
CHECKDB found 0 allocation errors and 0 consistency errors in database 'DMS_DB_PH'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

OPTS:

# EXTENDED_LOGICAL_CHECKS
performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

# REPAIR_ALLOW_DATA_LOSS

    Tries to repair all reported errors. These repairs can cause some data loss.

The REPAIR_ALLOW_DATA_LOSS option is not an alternative for restoring from a known good backup.
It is an emergency “last resort” option recommended for use only if restoring from a backup is not possible.

Before performing the repair, create physical copies of the files that belong to this database. This includes the primary data file (.mdf), any secondary data files (.ndf), all transaction log files (.ldf), and other containers that form the database including full text catalogs, file stream folders, memory optimized data, etc.

 

---

Run SQL Script

 

[1]

sqlcmd -S <server> -i C:\<your file here>.sql

[2]

Start SQL Server Management Studio

Connect to your database

File > Open > File and pick your file

Execute it