SQL Server 的mdf文件过大如何处理?现在为22G

yangjinhui 2008-06-25 09:15:02
我们学校上了一套OA系统,经过长时间的使用,SQL Server的mdf的文件总是突飞增长,达到了22G,不知道如何为什么,使用了很多方法都处理不了,造成的后果是现在查询速度过慢,有时候甚至不能打开文件,请问大家如何处理?急需解决?
...全文
1926 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
cxmcxm 2008-06-25
  • 打赏
  • 举报
回复
1.笨方法,升级系统!单硬盘转为硬盘阵列。
2.看能不能将数据库分为多个,旧的不常用的一个数据库,新的数据一个数据库,或一年一数据库。
3.重新创建数据库,将数据库分为多个文件组,不同的表放于不同的文件组中,分别存于不同的磁盘中。
hery2002 2008-06-25
  • 打赏
  • 举报
回复
一般造成mdf文件过大的原因是:
1.索引碎片过多,
2.频繁的数据库操作,如临时表/存储过程等的占用大量的空间,而没有及时释放.
3.异常状况下占用的数据库空间没有释放掉.....
hery2002 2008-06-25
  • 打赏
  • 举报
回复
1.磁盘整理,
2.数据库碎片整理,
3.重建数据库索引.
4.收缩数据库文件
5.收缩日志文件.
-------------
如果以上方式不行的话,
建议重建数据库.
hery2002 2008-06-25
  • 打赏
  • 举报
回复
查看一下是否的确有这么多数据.
OA系统有这么大的数据量么?
关注......
oh_my_god 2008-06-25
  • 打赏
  • 举报
回复
可能是数据量过大造成的,也可能是磁盘使用时间太长,产生的磁盘脆片太多造成的。先整理磁盘碎片,然后 dbcc checkdb 修复一下数据库看看
yangjiexi 2008-06-25
  • 打赏
  • 举报
回复

--数据量大确实是个头疼的问题,最近也在想办法,
--想到好的再告诉你了!呵呵!努力!
yangjinhui 2008-06-25
  • 打赏
  • 举报
回复
shrink什么意思
yangjinhui 2008-06-25
  • 打赏
  • 举报
回复
收缩已经试过了,没有管用,还有别的什么发吗
一品梅 2008-06-25
  • 打赏
  • 举报
回复
shrink一下下
nzperfect 2008-06-25
  • 打赏
  • 举报
回复
收缩一下试试
lff642 2008-06-25
  • 打赏
  • 举报
回复
会不会是数据量很大,造成的呢?
royal1 2008-06-25
  • 打赏
  • 举报
回复
如果收缩没用,你应该分析下表空间的使用情况,如下:

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

create table tablespaceinfo --创建临时表
(nameinfo varchar(50) , --表名
rowsinfo int , --表中现有的行数
reserved varchar(20) , --表空间总量
datainfo varchar(20) , --表中数据的空间量
index_size varchar(20) , --表中索引使用的空间量
unused varchar(20) ) --表中未用的空间量

delete from tablespaceinfo

declare @tablename varchar(255)
declare @cmdsql varchar(500)

DECLARE Info_cursor CURSOR FOR
select name
from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTable') = 1
and name not like N'#%%' order by name

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename

FETCH NEXT FROM Info_cursor
INTO @tablename
END

CLOSE Info_cursor
DEALLOCATE Info_cursor
GO


--表空间信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
ChinaITOldMan 2008-06-25
  • 打赏
  • 举报
回复
用楼上的方法试一试
由于种种原因,我们如果当时仅仅备份了mdf文件,那么恢复起来就是一件很麻烦的事情了。   如果您的mdf文件是当前数据库产生的,那么很侥幸,也许你使用sp_attach_db或者sp_attach_single_file_db可以恢复数据库,但是会出现类似下面的提示信息   设备激活错误。物理文件名 ’C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF’ 可能有误。   已创建名为 ’C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.LDF’ 的新日志文件。   但是,如果您的数据库文件是从其他计算机上复制过来的,那么很不幸,也许上述办法就行不通了。你也许会得到类似下面的错误信息 服务器: 消息 1813,级别 16,状态 2,行 1   未能打开新数据库 ’test’。CREATE DATABASE 将终止。   设备激活错误。物理文件名 ’d:\test_log.LDF’ 可能有误。   应该怎么办呢?下面我们举例说明恢复办法。   A.我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server EntERPrise Manager里面建立。   B.停掉数据库服务器。   C.将刚才生成的数据库的日志文件test_log.ldf删除,用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。   D.启动数据库服务器。此时会看到数据库test的状态为“置疑”。这时候不能对此数据库进行任何操作。   E.设置数据库允许直接操作系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器,按右键,选择“属性”,在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现。   use master   go   sp_configure ’allow updates’,1   go   reconfigure with override   go   F.设置test为紧急修复模式   update sysdatabases set status=-32768 where dbid=DB_ID(’test’)   此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读\置疑\脱机\紧急模式”可以看到数据库里面的表,但是仅仅有系统表   G.下面执行真正的恢复操作,重建数据库日志文件   dbcc rebuild_log(’test’,’C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf’)   执行过程中,如果遇到下列提示信息:   服务器: 消息 5030,级别 16,状态 1,行 1   未能排它地锁定数据库以执行该操作。   DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。   说明您的其他程序正在使用该数据库,如果刚才您在F步骤中使用SQL Server Enterprise Manager打开了test库的系统表,那么退出SQL Server Enterprise Manager就可以了。   正确执行完成的提示应该类似于: 告: 数据库 ’test’ 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项,并且可能需要删除多余的日志文件。   DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。   此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为“只供DBO使用”。此时可以访问数据库里面的用户表了。   H.验证数据库一致性(可省略)   dbcc checkdb(’test’)   一般执行结果如下:   CHECKDB 发现了 0 个分配错误和 0 个一致性错误(在数据库 ’test’ 中)。   DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。   I.设置数据库为正常状态   sp_dboption ’test’,’dbo use only’,’false’   假如没有出错,现在你就可以正常的使用恢复后的数据库啦。   J.最后一步,我们要将步骤E中设置的“允许对系统目录直接修改”一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然,我们可以在SQL Server Enterprise Manager里面恢复,也可以使用如下语句完成   sp_configure ’allow updates’,0   go   reconfigure with override   go
本课程根据讲师十多年在世界500强外企的生产环境中的SQL Serer数据库管理和项目实施经验倾心打造。课程系统性强,知识体系完整,覆盖90%以上的企业环境下SQL Server高可用场景,课程中不仅演示详细的操作步骤,更加突出最常见的故障和问题,让学员少走“弯路”,不只是让学员学会“操作”更能让学员“操作”的规范,满满的干货分享,一些课程资料(架构图、部署规划表格等)不仅可以帮助学员掌握技能,也可以作为学员在企业生产环境中实施SQL Server高可用的配置文档、操作手册等。课程的实验环境介绍:1)全部基于微软域环境和企业版SQL Server AOAG - 95%以上的企业环境都是在域环境中,不介绍非域环境和标准版的SQL Server高可用性组,这的配置在企业中较罕见,没有实践意义,不浪费学员时间。2)相应域环境已提前部署和配置好 - 学员导入虚拟机即可开始实验,无需从零开始搭建域环境,所有实验中SQL Server均已加域,直入主题,节省大量时间。3)最新的Windows Server故障转移集群(WS2016、WS2019)和最新版本的SQL ServerSQL2017、SQL2019) -  WS2016-SQL2017与WS2019-SQL2019是目前大多数企业SQL Server高可用的主要平台,基于微软产品生命周期现在一些企业也在讲早期的AOAG向这两个版本迁移,掌握这两种组合不仅让学员学会,更能学有所用。本课程为后续SQL Server进阶课程铺垫,是通向SQL Server DBA 专家的必经之路,讲师每周答疑两次。所有课程资料包括:课程PPT、架构图、部署规划表格、各类脚本学员均可下载。     
几天前在盒子看到绿色Sql server很高兴,现将本人对绿色SQL Server的现实思路作一说明,不正确的地方还请指教。1. Sqlservr.exe 运行参数。 Sql Server的启动可以为Windows服务方式(默认),也可以以应用程序方式启动。下面介绍sqlservr.exe以应用程序方式启动时的参数问题。sqlservr 应用程序用法:sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f] [-eerror_log_path] [-lmaster_log_path] [-m] [-n] [-Ttrace#] [-v] [-x] [-g number] [-O] [-y number]-sinstance_name 指定要连接到的 SQL Server 实例。如果未指定命名实例,sqlservr 将启动 SQL Server 默认实例。重要 启动 SQL Server 实例时,必须从实例所在的适当目录使用 sqlservr 应用程序。对于默认实例,从 MSSQLBinn 目录运行 sqlservr。对于命名实例,在 MSSQL$instance_nameBinn 目录运行 sqlservr。-c 表示以独立于 Windows NT 服务控制管理器的方式启动 SQL Server 实例。当从命令提示符下启动 SQLServer 时,可使用该选项缩短启动 SQL Server 的时间。(注意:当使用该选项时,无法通过使用 SQLServer 服务管理器或 net stop 命令停止 SQL Server,而且如果已从 Microsoft Windows NT? 系统注销,则 SQL Server 将停止运行。)-dmaster_path指出 master 数据库文件的完全合法路径。在 -d 和 master_path 之间没有空格。-f以最小配置模式启动服务器。然后,系统管理员可对配置选项重新配置(使用 sp_configure 系统存储过程)。-eerror_log_path表示错误日志文件的完全合法路径。如果未指定路径,则默认实例的默认位置是 x:Program FilesMicrosoft SQL ServerMSSQLLogErrorlog,命名实例的默认位置是 x:Program FilesMicrosoft SQL ServerMSSQL$instance_nameLogErrorlog。在 -e 和 error_log_path 之间没有空格。-lmaster_log_path指示 master 数据库事务日志文件的完全合法路径。在 -l 和 master_log_path 之间没有空格。-m表示在单用户模式下启动 SQL Server 实例。如果 SQL Server 是以单用户模式启动的,则只能连接一个用户。CHECKPOINT 机制(它确保完成的事务定期从磁盘高速缓存写入数据库设备中)将不启动。(一般情况下,如果您遇到需要修复的系统数据库问题时,可使用此选项。)-n表示您不想使用 Windows NT 应用程序日志来记录 SQL Server 事件。如果用 -n 选项启动 SQL Server 实例,则最好也使用 -e 选项,否则将不会记录 SQL Server 事件。-Ttrace#表示应使用指定的有效跟踪标记 (trace#) 来启动 SQL Server 实例。跟踪标志用来启动具有非标准行为的服务器。有关可用跟踪标记 (trace#) 的更多信息,请参见跟踪标记。重要 当指定跟踪标志时,请使用 –T 来传递跟踪标志号。SQL Server 接受小写字母 t (-t);然而,-t 还设置 SQL Server 支持工程师所需的其它内部跟踪标记。-v显示服务器的版本号。-x禁用维护 CPU 统计。-g memory_to_reserve指定内存的兆字节整数,该内存被保留下来用于 SQL Server 2000 内部(进程内)运行的其它应用程序。-O指定不需要分布式 COM (DCOM),从而禁用异类查询。-y error_number如果 SQL Server 2000 遇到在该选项中指定的错误信息,它将把表象堆栈跟踪写入错误日志。可以使用多个 –y 参数指定多个错误。2. 系统数据库路径问题. 一般情况下,正常安装sql server之后,master等系统数据库的路径都是被设置为绝对路径,如果要改正数据库的路径为相对路径,可以使用以下SQl语句: sql:=format(' update sysaltfiles set filename='%s' where filename='%s' ', ['...data'+extractfilename(MDFFile),MDFFile]); ADOQuery1.Close; ADOQuery1.SQL.Text:=sql; ADOQuery1.ExecSQL; sql:=format(' update sysaltfiles set filename='%s' where filename='%s' ', ['...data'+extractfilename(LogFile),LogFile]); ADOQuery1.Close; ADOQuery1.SQL.Text:=sql; ADOQuery1.ExecSQL; sql:=Format('update sysdatabases set filename='%s' where name='%s'', ['...data'+extractfilename(MDFFile),Edit1.text]); ADOQuery1.Close; ADOQuery1.SQL.Text:=sql; ADOQuery1.ExecSQL;大家打开sysaltfiles 表和sysdatabases 一看就知道了,不多说了。除了系统数据库,其它的数据库也可以使用这种方法修改为相对路径。 使用相对路径的好处是可以让你的绿色Sql server随时copy到任意目录或是任意其它机器上启动,并且原来的数据库全部可以使用,如果是绝对路径到了其它机器就不行了。3. 注册表问题: 大家可以看下面的注册表文件Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$name][HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameMSSQLServer]"AuditLevel"=dword:00000000"DefaultLogin"="guest""ListenOn"=hex(7):53,00,53,00,4d,00,53,00,53,00,48,00,37,00,30,00,00,00,53,00, 53,00,4e,00,45,00,54,00,4c,00,49,00,42,00,00,00,00,00"LoginMode"=dword:00000000"Map_"="""Map#"="-""Map$"="""SetHostName"=dword:00000000"Tapeloadwaittime"=dword:ffffffff"uptime_pid"=dword:00001044"uptime_time_utc"=hex:26,57,c8,b3,fb,bb,c5,01[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameMSSQLServerCurrentVersion]"CurrentVersion"="8.00.194""RegisteredOwner"="""SerialNumber"="""CSDVersionNumber"=dword:00000300"CSDVersion"="8.00.761""Language"=dword:00000804"checksum"=hex:37,35,32,32,63,31,35,38,61,65,37,64,34,63,64,37,35,30,64,61,30, 33,34,62,37,64,63,32,30,61,36,36,39,37,62,34,61,35,65,36,63,66,62,61,38,37, 39,36,37,31,66,33,39,61,36,65,35,64,30,33,37,36,37,65,64,36,33,30,61,39,63, 36,66,37,36,30,35,65,32,66,34,66,31,33,63,39,33,63,34,66,62,66,34,66,64,38, 64,65,36,64,35,34,37,33,35,39,35,32,36,63,61,32,36,63,33,32,64,34,36,33,65, 36,39,34,30,65,61,37,64,35,30,61,66,34,64,32,66,38,37,36,30,61,39,64,30,61, 32,36,64,65,66,66,00[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameMSSQLServerParameters][HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameMSSQLServerSuperSocketNetLib]"ProtocolList"=hex(7):74,00,63,00,70,00,00,00,6e,00,70,00,00,00,00,00[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameMSSQLServerSuperSocketNetLibNp]"PipeName"="\.pipeMSSQL$$namesqlquery"[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameMSSQLServerSuperSocketNetLibTcp]"TcpHideFlag"=dword:00000000"TcpDynamicPorts"="$port""TcpPort"="$port"[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server$nameSetup]"FeatureName"="SqlRun""FirstStart"=dword:00000000"ProductCode"="{E09B48B5-E141-427A-AB0C-D3605127224A}""SQLDataRoot"="$path""SQLPath"="$path"这是注册一个Sql Server实例所要使用的注册表,把里面的$name,$path,$port替换成你定义的实例名,当前程序所有路径,监听端口,然后导入注册表就可以了。4. 启动Sql Server iPath:=ExtractFilePath(Application.ExeName); CommandLine:=PChar(iPath+'binnsqlservr.exe -d...datamaster.mdf -l...datamastlog.ldf -e...Loglog.txt+Edit1.Text); CreateProcess( nil, CommandLine, nil, nil, true, CREATE_NO_WINDOW, nil, PChar(iPath+'binn'), StartupInfo, ProcessInformation )也可以直接写个.bat运行就ok了.5. 启动日志问题。 这个问题最简单,因为在执行sqlservr.exe时指定的有log文件,你只用用个定时器(50ms)读文件,就可以在程序中显示出来启动日志了,呵呵,这只是个思想,具体的自已想办法吧. 6. 停止服务 用CreateProcess可以得到进程ID,TerminateProcess就关了sql server进程,服务就停了,简单吧。根据上面的6个步骤,你就可以自已搞定一个绿色的Sql Server了.并且你可以根据不同的sql server版本,整理出所有版本的绿色sql server。绿色 sql server 企业版绿色 sql server 个人版绿色 MSDE... ...这样以后遇到什么操作系统都全部搞定。 最后说一句,只要你能把几个Sql server版本的数据整理好(主要是系统数据库的路径问题(好像现在盒子已经有绿色企业版的文件了),DLL全部copy就OK了),都可以使用我的管理工具进行管理,呵呵,当然你也可以作出自已的管理器。我的管理器,主要是为了我自已用,大家如果用着可以,请多提意见。由于sql server数据太大,并且盒子上已经有了,这次只上传了exe和目录结构。不知道说的是否正确,如果还有什么问题,我们一起交流. Email: 11826088@163.com

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧