用友软件数据库收缩操作
2019-1-3 0:0:0 wondial
对应版本:用友U其他
对应产品线:其他
对应模块:MSSQL SERVER模块
问题现象:我们一用户在记帐是出现如所附图片的错误提示,不能记帐。我打电话问了一个工程师,他说收缩一下数据库,但我不知道如何操作,请赐教,谢谢!
问题原因:如何收缩数据库问题。
解决方案:
--说明:压缩数据库日志文件--操作时,只用在查询分析器中执行此脚本文件即可。
--执行前用目的数据库名称替换脚本中的”ufdata_999_2018”
--默认在master数据库下执行即可backup log ufdata_999_2018 with no_log
--提供有关所有数据库中的事务日志空间使用情况的统计信息。dbccsqlperf(logspace)
--收缩数据库dbccshrinkdatabase(ufdata_999_2018,truncateonly)
--查看指定spid正在执行的SQL
select top 1 total_elapsed_time,substring(text,(statement_start_offset+2)/2, (case when statement_end_offset = -1 then (len(convert(nvarchar(MAX),text))*2) else statement_end_offset end - statement_start_offset) /2 ),a.* from sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(sql_handle)
where a.session_id=78
--锁定与被锁定间的链状关系
select t1.resource_type as [资源锁定类型]
,db_name(resource_database_id) as [数据库名]
,t1.resource_associated_entity_id as [锁定的对象]
,t1.request_mode as [等待者需求的锁定类型]
,t1.request_session_id as [等待者sid]
,t2.wait_duration_ms as [等待时间]
,(select text from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as [等待者要执行的批次]
,(select substring(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1
then datalength(qt.text)
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as [等待者正要执行的语法]
,t2.blocking_session_id as [锁定者sid]
,(select text from sys.sysprocesses as p
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as [锁定者的语法]
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
注册使用U9的CLR函数
--直接执行
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO
--将yourdatabasename替换
/****/
Alter Database yourdatabasename SET TRUSTWORTHY ON
go
use yourdatabasename
go
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
go
/****/
关于数据库日志文件大小的问题说明www.kuaiji66.com
DBCC SQLPERF (LOGSPACE)
GO
SELECT NAME,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc
FROM sys.databases
--收缩数据库
DBCC SHRINKDATABASE(库名)
清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
--查看portal的操作信息日志
select top 100 *
from UBF_Portal_OperationRecord o where o.OccurrenceTime >'2011-11-29 16:25:00.000'
and o.OccurrenceTime <'2011-11-29 16:30:00.000'
--列出使用cpu最多的前50名sql
SELECT q.[text],q.dbid,q.objectid,q.number,q.encrypted,highest_cpu_queries.*
FROM
(SELECT TOP 50 qs.* FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q ORDER BY highest_cpu_queries.total_worker_time DESC
--列出最经常编译的存储过程
SELECT TOP 25 sql_text.[text],sql_handle,plan_generation_num,execution_count,dbid,objectid
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num>1 ORDER BY plan_generation_num DESC
--查看索引的统计更新
dbcc show_statistics(PM_RcvLine,UFIDA_U9_PM_Rcv_RcvLine_BusinessKey_Index)
--重建索引
alter index UFIDA_U9_PM_Rcv_RcvLine_BusinessKey_Index on PM_RcvLine rebuild
--手工更新统计更新
update statistics PM_RcvLine
--查看表的某个索引的索引字段和包涵索引字段
SELECT c.name index_name,ic.is_included_column
FROM sys.index_columns ic INNER JOIN sys.indexes i ON ic.[object_id]=i.[object_id] AND ic.index_id=i.index_id INNER JOIN
sys.tables t ON i.[object_id]=t.[object_id] INNER JOIN sys.[columns] c ON ic.[object_id]=c.[object_id] AND ic.column_id=c.column_id
WHERE t.name='zhanggang' AND i.name='bbb'
----查表碎片,Base_UOM只有一个clusted索引。碎片率
SELECT
OBJECT_NAME(a.[object_id]) AS [TableName],
a.index_id,
NAME [indexname],
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS a
JOIN sys.indexes AS b ON a.[object_id]=b.[object_id] AND a.index_id=b.index_id AND OBJECT_NAME(a.[object_id]) IN
('CBO_ItemMaster','Base_UOM') ORDER BY a.avg_fragmentation_in_percent DESC
用友U9一些常识
--根据表名查找相关实体信息
SELECT a.[FullName], a.[Name], b.[DisplayName] ,
a.[ClassType], a.[ID] FROM [UBF_MD_Class]
as a left join UBF_MD_Class_trl as b on a.Local_ID=b.Local_ID
where 1=1 and ClassType in('1','2','3' ) and
( b.sysmlflag='zh-CN' or b.sysmlflag is null )
and a.Name like 'PMStatistics%' or b.DisplayName
like 'PMStatistics%' order by [Name]
go
--改进版:
DECLARE @name NVARCHAR(64)
DECLARE @sql NVARCHAR(800)
SET @name='InvToGL'
SET @sql = 'SELECT a.[FullName], a.[Name], b.[DisplayName] ,
a.[ClassType], a.[ID] FROM [UBF_MD_Class]
as a left join UBF_MD_Class_trl as b on a.Local_ID=b.Local_ID
where 1=1 and ClassType in(''1'',''2'',''3'' ) and
( b.sysmlflag=''zh-CN'' or b.sysmlflag is null )
and a.Name like ''%'+@name+'%'' or b.DisplayName
like ''%'+@name+'%'' order by [Name] '
EXEC (@sql)
--查一个表有多少字段
SELECT a.* FROM syscolumns a,sysobjects s WHERE a.id=s.id AND s.[name]='table'
如果您的问题还没有解决,可以到 T+搜索>>上找一下答案
相关阅读
- 用友U8不同的核算方式如何影响存货成本2021-10-9 10:15:1
- 用友U8生产制造-物料清单的定义以及应用准备详解2021-9-26 11:39:40
- 用友U8哪些办法可以让库存更准确详解2021-9-26 11:24:19
- UFO升级后口令失效问题2021-7-30 8:55:1
- 成本管理效率问题2021-7-30 8:55:1
- 成本管理中所有录入表及报表都需要增加工序说明2021-7-30 8:55:1
- 降级品成本应该如何分配2021-7-30 8:55:1
- 库存期初从存货取数后不能保存,报错2021-7-30 8:55:1
- 用友u8软件固定资产与总账对账不平2021-6-8 8:50:59
- U8预付不能冲应付解决方法2021-6-8 8:44:38
最新信息
某股份有限公司按法定程序报经批准后采用收购本公司股票方式减资,回购股票支付价款低于股票面值总额的,所注销库存股账面余额与冲减股本的差额应计入( )。-A、盈余公积 -B、营业外收入 -C、资本公积 -D、未分配利润 某股份有限公司按法定程序报经批准后采用收购本公司股票方式减资,回购股票支付价款低于股票面值总额的,所注销库存股账面余额与冲减股本的差额应计入( )。A、盈余公积 B、营业外收入 C、资本公积 D、未分配利润[]
C
回购股票的账务处理。如果回购股票支付的价款低于面值总额的,所注销库存股的账面余额与所冲减股本的差额作为增加“资本公积—股本溢价”处理。