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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testDB.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testDB_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:\mssql\data\testDB.mdf',
'Z:\mssql\data\testDB_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:\mssql\FNDBLogTest.trn'
从备份Log查询:
SELECT * FROMfn_dump_dblog(
NULL, NULL,'DISK', 1,'d:\mssql\FNDBLogTest.trn',
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT)
whereOperation ='LOP_INSERT_ROWS'
EXEC sp_who
exec xp_ReadErrorLog
最新文章
- 【干货理解】理解javascript中实现MVC的原理
- math.h--------数学函数
- 存储过程分页 Ado.Net分页 EF分页 满足90%以上
- jQuery 的随机密码生成 .
- 【UVA 1380】 A Scheduling Problem (树形DP)
- [MySQL 5.6] 初识5.6的optimizer trace
- Sitemesh 3 的使用及配置
- 转:jQuery事件绑定.on()简要概述及应用
- 平安某金所奇葩的面经-关于幂等和ROA设计的反思
- Gradle学习笔记之Groovy
- MyBatis sqlsession 简化 使用工具类创建
- ArcGis恢复初始设置(默认设置、出厂设置)的方法
- kerbose常用操作
- Node.js 开发指南
- Nerd的套现ATM机
- 从0移植uboot(三) _编译最小可用uboot
- vue2.0--请求数据
- minSdk(API 26) >; deviceSdk(API 19)解决方式
- SPOJ IM - Intergalactic Map - [拆点最大流]
- MySQL 大表备份、改表