·þÎñÆ÷ϵͳ¼¯³É¼°Êý¾Ý·þÎñÖÐÐÄ

 

Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
Ò»¼¶À¸Ä¿
¡¾æŠ€æœ¯èµ„æ–?uppage=news.asp¡¿¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­>>>

´¦ÀíSQL-serverÊý¾Ý¿â¸½¼Óʧ°Ü

¿ìËÙɾ³ýÊý¾Ý¿âÖÐËùÓбíÖеÄÊý¾Ý

select ''''truncate table '''' + Name + '''''''' from sysobjects where xtype=''''U'''' order by name asc

¸ÃÌõÓï¾äÖ´ÐÐÖ®ºó»á½«Êý¾Ý¿âÖÐËùÓÐµÄ±í¶¼²éѯ³öÀ´£¬¸´ÖƳöÀ´Ö®ºóÖ´ÐÐtruncateÓï¾ä¼´¿É

*******************

alter database zygl3_data set emergency

Ò»¡¢¸½¼ÓSQL2005Êý¾Ý¿âʧ°Ü½â¾ö·½·¨

²Î¿¼»Ö¸´·½·¨£º

1¡¢Í£Ö¹Êý¾Ý¿â·þÎñ¡£

2¡¢½«ÐèÒª»Ö¸´µÄÊý¾Ý¿âÎļþ¸´ÖƵ½ÁíÍâµÄλÖá£

3¡¢Æô¶¯Êý¾Ý¿â·þÎñ¡£

4¡¢È·ÈÏÒª»Ö¸´µÄÊý¾Ý¿âÎļþÒѾ­³É¹¦¸´ÖƵ½ÁíÍâµÄλÖã¬È»ºóÔÚSQL Server Management StudioÖÐɾ³ýÒª»Ö¸´µÄÊý¾Ý¿â¡£

5¡¢Ð½¨Í¬ÃûµÄÊý¾Ý¿â(Êý¾Ý¿âÎļþÃûÒ²ÒªÏàͬ)¡£

6¡¢Í£Ö¹Êý¾Ý¿â·þÎñ¡£

7¡¢ÓõÚ2²½Öб¸·ÝµÄ.mdfÎļþ¸²¸ÇÐÂÊý¾Ý¿âµÄͬÃûÎļþ¡£

8¡¢Æô¶¯Êý¾Ý¿â·þÎñ¡£

9¡¢ÔËÐÐalter database dbname set emergency£¬½«Êý¾Ý¿âÉèÖÃΪemergency mode

10¡¢ÔËÐÐÏÂÃæµÄÃüÁî¾Í¿ÉÒÔ»Ö¸´Êý¾Ý¿â£º

use master
declare @databasename varchar(255)
set @databasename=''''ÄãµÄÊý¾Ý¿âÃû''''
exec sp_dboption @databasename, N''''single'''', N''''true''''½«Ä¿±êÊý¾Ý¿âÖÃΪµ¥Óû§×´Ì¬
dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)
dbcc checkdb(@databasename,REPAIR_REBUILD)
exec sp_dboption @databasename, N''''single'''', N''''false''''½«Ä¿±êÊý¾Ý¿âÖÃΪ¶àÓû§×´Ì¬


Èç¹ûÄãÒ»Çж¼Ë³µÃµÄ»°¾ÍÈçÉϵIJ½Öè¾Í¿ÉÒԳɹ¦ÁË£¬ÏÂÃæÎÒÃǽ«¶ÔÌØÊâµÄ״̬½øÐзÖÎö

1¡¢Ò»¸ö»ò¶à¸öÎļþÓëÊý¾Ý¿âµÄÖ÷Îļþ²»Æ¥Åä¡£Èç¹ûÊdz¢ÊÔ¸½¼ÓÊý¾Ý¿â£¬ÇëʹÓÃÕýÈ·µÄÎļþÖØÊԸòÙ×÷¡£Èç¹ûÕâÊÇÏÖÓÐÊý¾Ý¿â£¬ÔòÎļþ¿ÉÄÜÒÑË𻵣¬Ó¦¸Ã´Ó±¸·Ý½øÐл¹Ô­¡£
ÈÕÖ¾Îļþ ''''E:/Program Files/Microsoft SQL Server 2005/MSSQL.1/MSSQL/DATA/dnt2_db_log.ldf'''' ÓëÖ÷Îļþ²»Æ¥Åä¡£¸ÃÎļþ¿ÉÄÜÀ´×ÔÁíÒ»Êý¾Ý¿â£¬»òÕß¿ÉÄÜÒÔÇ°ÖØÐÂÉú³ÉÁËÈÕÖ¾¡£
ÏûÏ¢ 5123£¬¼¶±ð 16£¬×´Ì¬ 1£¬µÚ 5 ÐÐ
³¢ÊÔ´ò¿ª»ò´´½¨ÎïÀíÎļþ ''''F:/ÏîÄ¿/°®ÉçÇø/Êý¾Ý¿â/dnt2_db_log.ldf'''' ʱ£¬CREATE FILE Óöµ½²Ù×÷ϵͳ´íÎó 3(ϵͳÕÒ²»µ½Ö¸¶¨µÄ·¾¶¡£)¡£
ÏûÏ¢ 5024£¬¼¶±ð 16£¬×´Ì¬ 2£¬µÚ 5 ÐÐ
ÔÚ sysfiles1 ÖÐÕÒ²»µ½Ö÷ÈÕÖ¾ÎļþËù¶ÔÓ¦µÄÌõÄ¿¡£ÎÞ·¨Öؽ¨ÈÕÖ¾¡£
ÏûÏ¢ 5028£¬¼¶±ð 16£¬×´Ì¬ 2£¬µÚ 5 ÐÐ
ϵͳÎÞ·¨¼¤»î×ã¹»µÄÊý¾Ý¿âÀ´Öؽ¨ÈÕÖ¾¡£

½â¾ö·½°¸:ÊÖ¶¯´´½¨E:/Program Files/Microsoft SQL Server 2005/MSSQL.1/MSSQL/DATAĿ¼¾Í¿ÉÒÔÁË,È»ºóÔÙÖ´ÐÐ

ÉÏÊöµÚ10²½²½Öè,¾Í»áÔÚE:/Program Files/Microsoft SQL Server 2005/MSSQL.1/MSSQL/DATA²úÉúÒ»¸öÈÕÖ¾Îļþ¡£Êý¾Ý¿â¾Í¿ÉÒÔÕý³£Ê¹ÓÃÁË


2¡¢ÁíÍâÖµµÃ˵˵µÄ¾ÍÊÇ£¬Èç¹û¸ÃÊý¾ÝÔÚ±¸·Ýʱ·¢Éú
System.Data.SqlClient.SqlError: ÔÚ´¦Àí ''''BackupMetadata'''' ÔªÊý¾Ý(Êý¾Ý¿â ID 6£¬Îļþ ID 65537)ʱ³ö´í¡£ (Microsoft.SqlServer.Smo)£¬ËµÃ÷ÔÚ¸½¼Ó¹ý³ÌÖÐûÓвúÉúÈ«ÎÄË÷ÒýÎļþ

½â¾ö·½·¨:
ÓÒ¼üÊý¾Ý¿â£­>ÊôÐÔ£­>Îļþ£­>Ñ¡ÔñʹÓÃÈ«ÎÄË÷Òý
Èç¹ûÖ´ÐÐÕý³£ÄÇô¾Í¿ÉÒÔÖ±½Ó½øÐб¸·Ý²Ù×÷ÁË¡£

¶þ¡¢Ò»Ð©²»´íµÄsqlÓï¾ä

1¡¢ËµÃ÷£º¸´ÖƱí(Ö»¸´Öƽṹ,Ô´±íÃû£ºa бíÃû£ºb) (Access¿ÉÓÃ)
·¨Ò»£ºselect  into b from a where 11
·¨¶þ£ºselect top 0  into b from a
Ò²¿ÉÒÔSELECT * INTO  ÐÂ±ê  FROM ԭʼ±í  where 1=2

2¡¢ËµÃ÷£º¿½±´±í(¿½±´Êý¾Ý,Ô´±íÃû£ºa Ä¿±ê±íÃû£ºb) (Access¿ÉÓÃ)
insert into b(a, b, c) select d,e,f from b

3¡¢ËµÃ÷£º¿çÊý¾Ý¿âÖ®¼ä±íµÄ¿½±´(¾ßÌåÊý¾ÝʹÓþø¶Ô·¾¶) (Access¿ÉÓÃ)
insert into b(a, b, c) select d,e,f from b in ¡®¾ßÌåÊý¾Ý¿â¡¯    where Ìõ¼þ
Àý×Ó£º..from b in ''''&Server.MapPath(.)&data.mdb &'''' where..

4¡¢ËµÃ÷£º×Ó²éѯ(±íÃû1£ºa ±íÃû2£ºb)
select a,b,c from a where a IN (select d from b )    »òÕß    select a,b,c from a where a IN (1,2,3)

5¡¢ËµÃ÷£ºÏÔʾÎÄÕ¡¢Ìá½»È˺Í×îºó»Ø¸´Ê±¼ä
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where

table.title=a.title) b

6¡¢ËµÃ÷£ºÍâÁ¬½Ó²éѯ(±íÃû1£ºa ±íÃû2£ºb)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7¡¢ËµÃ÷£ºÔÚÏßÊÓͼ²éѯ(±íÃû1£ºa )
select  from (SELECT a,b,c FROM a) T where t.a  1

8¡¢ËµÃ÷£ºbetweenµÄÓ÷¨,betweenÏÞÖƲéѯÊý¾Ý·¶Î§Ê±°üÀ¨Á˱߽çÖµ,not between²»°üÀ¨
select  from table1 where time between time1 and time2
select a,b,c, from table1 where a not between ÊýÖµ1 and ÊýÖµ2

9¡¢ËµÃ÷£ºin µÄʹÓ÷½·¨
select  from table1 where a [not] in (¡®Öµ1¡¯,¡¯Öµ2¡¯,¡¯Öµ4¡¯,¡¯Öµ6¡¯)

10¡¢ËµÃ÷£ºÁ½ÕŹØÁª±í£¬É¾³ýÖ÷±íÖÐÒѾ­ÔÚ¸±±íÖÐûÓеÄÐÅÏ¢
delete from table1 where not exists ( select  from table2 where table1.field1=table2.field1 )

11¡¢ËµÃ÷£ºËıíÁª²éÎÊÌ⣺
select  from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where

.....

12¡¢ËµÃ÷£ºÈճ̰²ÅÅÌáÇ°Îå·ÖÖÓÌáÐÑ
SQL select  from Èճ̰²ÅÅ where datediff(''''minute'''',f¿ªÊ¼Ê±¼ä,getdate())5

13¡¢ËµÃ÷£ºÒ»Ìõsql Óï¾ä¸ã¶¨Êý¾Ý¿â·ÖÒ³
select top 10 b. from (select top 20 Ö÷¼ü×Ö¶Î,ÅÅÐò×ֶΠfrom ±íÃû order by ÅÅÐò×ֶΠdesc) a,±íÃû b where

b.Ö÷¼ü×ֶΠ= a.Ö÷¼ü×ֶΠorder by a.ÅÅÐò×Ö¶Î

14¡¢ËµÃ÷£ºÇ°10Ìõ¼Ç¼
select top 10  form table1 where ·¶Î§

15¡¢ËµÃ÷£ºÑ¡ÔñÔÚÿһ×ébÖµÏàͬµÄÊý¾ÝÖжÔÓ¦µÄa×î´óµÄ¼Ç¼µÄËùÓÐÐÅÏ¢(ÀàËÆÕâÑùµÄÓ÷¨¿ÉÒÔÓÃÓÚÂÛ̳ÿÔÂÅÅÐаñ,

ÿÔÂÈÈÏú²úÆ··ÖÎö,°´¿ÆÄ¿³É¼¨ÅÅÃû,µÈµÈ.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16¡¢ËµÃ÷£º°üÀ¨ËùÓÐÔÚ TableA Öе«²»ÔÚ TableBºÍTableC ÖеÄÐв¢Ïû³ýËùÓÐÖظ´ÐжøÅÉÉú³öÒ»¸ö½á¹û±í
(select a from tableA ) except (select a from tableB) except (select a from tableC)

17¡¢ËµÃ÷£ºËæ»úÈ¡³ö10ÌõÊý¾Ý
select top 10  from tablename order by newid()

18¡¢ËµÃ÷£ºËæ»úÑ¡Ôñ¼Ç¼
select newid()

19¡¢ËµÃ÷£ºÉ¾³ýÖظ´¼Ç¼
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

20¡¢ËµÃ÷£ºÁгöÊý¾Ý¿âÀïËùÓеıíÃû
select name from sysobjects where type=''''U''''

21¡¢ËµÃ÷£ºÁгö±íÀïµÄËùÓеÄ
select name from syscolumns where id=object_id(''''TableName'''')

22¡¢ËµÃ÷£ºÁÐʾtype¡¢vender¡¢pcs×ֶΣ¬ÒÔtype×Ö¶ÎÅÅÁУ¬case¿ÉÒÔ·½±ãµØʵÏÖ¶àÖØÑ¡Ôñ£¬ÀàËÆselect ÖеÄcase¡£
select type,sum(case vender when ''''A'''' then pcs else 0 end),sum(case vender when ''''C'''' then pcs else 0

end),sum(case vender when ''''B'''' then pcs else 0 end) FROM tablename group by type
ÏÔʾ½á¹û£º
type    vender     pcs
µçÄÔ     A          1
µçÄÔ     A          1
¹âÅÌ     B          2
¹âÅÌ     A          2
ÊÖ»ú     B          3
ÊÖ»ú     C          3

23¡¢ËµÃ÷£º³õʼ»¯±ítable1
TRUNCATE TABLE table1

24¡¢ËµÃ÷£ºÑ¡Ôñ´Ó10µ½15µÄ¼Ç¼
select top 5  from (select top 15  from table order by id asc) table_±ðÃû order by id desc

25,²é¿´µ±Ç°²Ù×÷µÄÊý¾Ý¿â
¡¡¡¡select db_name(dbid) from master.dbo.sysprocess where spid=@@spid
Trackback httptb.blog.csdn.NETTrackBack.aspxPostId=379405

·¢²¼Ê±¼ä:2017/6/11 ÔĶÁ:11450´Î À´Ô´:ÑĮ̀Êý¾Ý»Ö¸´ÀÏÀî
 


¡¡
技术资æ–?uppage=news.asp·ÖÀà
   
  ÐÐÒµÐÂÎÅ
 
  ¹«Ë¾¶¯Ì¬
 
  ¼¼Êõ×ÊÁÏ
 
  µçÄÔάÐÞ
 
  »Ö¸´°¸Àý
 
  SQLÊý¾Ý¿â
 
  ´ÅÅÌÕóÁÐ
 
  ·þÎñÆ÷
 
  ²ÆÎñÈí¼þ
 
  ÍøÂçÎÊÌâ
 
  linux-XFS
 
  Æ»¹ûµçÄÔ&ϵͳ
 
  °²×¿ÏµÍ³Ïà¹Ø
 
  °ì¹«Îļþ
 
  ²Ù×÷ϵͳ
 
  ÈÕ³£Éú»î(ÑĮ̀)
 
  ÍøÕ¾Ïà¹Ø
 
   
¡¡
ÓÑÇéÁ´½Ó
¡¡
¡¡
 
 
 
   
¹«Ë¾µØÖ·£ºÑĮ̀µçÄÔÊг¡#308
µç»°£º0535-6688830 E-Mail:163@163.com
Copyright©2011-2012 ÑĮ̀Èð³Û¿ÆóÓÐÏÞ¹«Ë¾ All Rights Reserved.
³ICP±¸11014811ºÅ-1
ÄúÊDZ¾Õ¾µÚ λ·ÃÎÊÕß