SQL Server遇到的错误和有用的tools

1.The target principal name is incorrect. Cannot generate SSPI context.
检查IIS的profile，可能是密码错误

2.The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Generally, It's not a good idea to specify an ORDER BY in a View,However, if you need to, add
SELECT TOP 100 PERCENT ...... FROM

3.生成schema和table的腳本
Right click your database -> click "Tasks" -> then click "Generate Scripts"
Then you will see an interface : "Generate scripts for database objects",

->click "Next", then "Select specific database objects",
-> then "Tables" with specific tables.
-> "Specify how script should be saved or published", and click "Advanced" , In the options, choose "Types of data to script" and modify it to "Data only".
-> click "Next" and then finish.

4.Backup failing : "The backup of full-text catalog 'QCFTCAT' is not permitted because it is not online.

alter database testDB set single_user

sp_detach_db 'testDB' -- Untick Keep Full Text Catalogs

sp_attach_db 'testDB',
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData estDB.mdf',
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData estDB_log.ldf'

select * from sys.databases
ALTER DATABASE testDB SET RECOVERY SIMPLE

此方法不知为何还会有database失败，后来使用task->copy database
...也还是不行，
后来直接拷贝mdf,ldf文件过去然后sp_attach_db

5.空间不够!!!
事务日志已满请参阅sys.databases中的log_reuse_wait_desc列解决办法
>>The transaction log for database 'PTO' is full. To find out why space in the log cannot be reused , see the log_reuse_wait_desc column in sys.databases

sp_helpdb EmpService
DBCC SQLPERF(LOGSPACE)
dbcc loginfo
select * from sys.database_files

backup log EmpService with no_log
dbcc shrinkdatabase(EmpService)

有時候有效,但耗時很久！
有時候報錯!
有時候佔用空間更大了！
以上都是杯水車薪，把log放另一個磁盤更直接些!步骤如下:

select * from sys.database_files
sp_helpfile

alter database testDB
set single_user with rollback immediate
use master
sp_detach_db 'testDB'
拷貝文件
use master
sp_attach_db 'testDB',
'Z:mssqldata estDB.mdf',
'Z:mssqldata estDB_log.ldf'

這種方法有時卡在拷貝文件那一步,總是提示
It is being used by another person or program.
必須offline了后才能拷貝
ALTER DATABASE testDB SET offline

ALTER DATABASE testDB SET online

http://technet.microsoft.com/en-us/magazine/gg452698.aspx

6.Cannot detach the database "testDB" because it is currently in use.
use master
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name like '%testDB%'

go
kill 66 -- kill spid

7.查询占用空间
exec sp_spaceused testtable
select name from sysobjects where xtype='u' order by name

SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

Database 服務器上的磁盤空間
exec master.dbo.xp_fixeddrives

use testDB
exec sp_spaceused
dbcc shrinkdatabase(testDB)
整個database佔用的空間
exec sp_spaceused

每個表佔用空間
exec sp_spaceused tableName

8.SQL server 数据库被标记为RESTORING的处理方式
http://limindo.blog.163.com/blog/static/264758562010116115412141/

restore database dblogTest with recovery
ALTER DATABASE dblogTest SET SINGLE_USER
ALTER DATABASE dblogTest SET MULTI_USER

9.备份Log

backup log FNDBLogTesttodisk='d:mssqlFNDBLogTest.trn'

从备份Log查询：
SELECT * FROMfn_dump_dblog(

NULL, NULL,'DISK', 1,'d:mssqlFNDBLogTest.trn',

DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,

DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT)

whereOperation ='LOP_INSERT_ROWS'

EXEC sp_who
exec xp_ReadErrorLog