如何缩小 TFS 数据库大小 [英] How to shrink TFS database size
问题描述
我们有一个 TFS2010 环境.很长一段时间以来，规模每周都在增长.
我们删除了很多旧的分支和团队项目.我们还在几个项目中使用了测试附件清洁器，就像 Brian Harry 在他的帖子中所说的那样.http:///blogs.msdn.com/b/bharry/archive/2011/10/31/tfs-databases-growth-out-of-control.aspx
数据库没有变小.我也尝试多次使用 destroy 命令，但没有任何帮助.
我检查了我能想到的所有日志，但找不到任何错误.
有人有什么建议吗?
谢谢
使用评论中询问的查询结果进行
<前>TableName SchemaName RowCounts TotalSpaceKB UsedSpaceKB UnusedSpaceKBFieldsDataArchive dbo 0 0 0 0tbl_AuditLog dbo 41710 5168 3800 1368tbl_AuthorizationUpdateLock dbo 1 16 16 0tbl_BuildOutput dbo 0 0 0 0tbl_BuildServerProperties dbo 1 16 16 0tbl_BuildSqlNotification dbo 124445 8432 6544 1888tbl_Counter dbo 3 16 16 0tbl_LastChangeId dbo 1 16 16 0tbl_Replication dbo 1 16 16 0tbl_Repository dbo 1 16 16 0TempADObjectMemberships dbo 0 0 0 0TempADObjects dbo 0 0 0 0模板 dbo 7 41328 41280 48查看TFS数据库事务日志不合理增长后我找不到增长的确切原因，也无法控制日志的自动收缩.
我在非生产服务器上尝试了几天这个脚本，只有在我切换到生产服务器之后.
破坏 SQL Server 2012 &TFS 2015
以下脚本自动收缩事务日志.
使用 SQL 作业
脚本部分:
1) 断开与特定数据库的所有连接
2) 将备份模式切换为简单
3) 将数据库日志大小设置为无限制
4) 将日志文件缩小到 200mb(或您希望的任何大小)
5) 将最大尺寸设置为 50000(或您希望的任何尺寸)
6) 将备份模型设置为完整
在 140GB 的数据库上运行脚本大约需要 3 -5 秒
USE [master]走ALTER DATABASE [Tfs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE走ALTER DATABASE [Tfs] SET RECOVERY SIMPLE WITH NO_WAIT走ALTER DATABASE [Tfs] MODIFY FILE (NAME = N'Tfs_log', MAXSIZE = UNLIMITED)走使用 [Tfs]走DBCC SHRINKFILE (N'Tfs_log', 200)走ALTER DATABASE [Tfs] 修改文件 (NAME = N'Tfs_log', MAXSIZE = 50000)使用 [大师]走ALTER DATABASE [Tfs] SET RECOVERY FULL WITH NO_WAIT走ALTER DATABASE [Tfs] SET MULTI_USER走We have a TFS2010 environment. The size is growing every week for a long time now.
We deleted a lot of old branches and team projects. We also used the test attachment cleaner for several projects like Brian Harry said in his post. http://blogs.msdn.com/b/bharry/archive/2011/10/31/tfs-databases-growing-out-of-control.aspx
The database isn't getting any smaller. I also tried to use the destroy command for several times but nothing is helping.
I checked every log I could think of but can't find any error about it.
Anyone a suggestion?
Thanks
Edit with result of the query that is asked in the comments:
TableName SchemaName RowCounts TotalSpaceKB UsedSpaceKB UnusedSpaceKB FieldsDataArchive dbo 0 0 0 0 tbl_AuditLog dbo 41710 5168 3800 1368 tbl_AuthorizationUpdateLock dbo 1 16 16 0 tbl_BuildOutput dbo 0 0 0 0 tbl_BuildServerProperties dbo 1 16 16 0 tbl_BuildSqlNotification dbo 124445 8432 6544 1888 tbl_Counter dbo 3 16 16 0 tbl_LastChangeId dbo 1 16 16 0 tbl_Replication dbo 1 16 16 0 tbl_Repository dbo 1 16 16 0 TempADObjectMemberships dbo 0 0 0 0 TempADObjects dbo 0 0 0 0 Templates dbo 7 41328 41280 48
After viewing unreasonable growth of the transaction log of the TFS database I couldn’t find the exact cause of the growth and couldn’t also control automatically shrinking of the log.
Similar solution without the full database backup
I tried this script several days on non-production server and only after i switched to production server.
Ruining on SQL server 2012 & TFS 2015
the following script automatically shrink the transaction log.
Run this script after full back up using the SQL job
Scripts parts:
1) Disconnect all connection to specific database
2) Switch Backup model to simple
3) Set database log size to unlimited
4) Shrink log file to 200mb (or any size you wish)
5) Set max size to 50000 (or any size you wish)
6) Set Backup model to full
Script run takes about 3 -5 seconds on a 140GB DB
USE [master]
GO
ALTER DATABASE [Tfs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [Tfs] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [Tfs] MODIFY FILE ( NAME = N'Tfs_log', MAXSIZE = UNLIMITED)
GO
USE [Tfs]
GO
DBCC SHRINKFILE (N'Tfs_log' , 200)
GO
ALTER DATABASE [Tfs] MODIFY FILE ( NAME = N'Tfs_log', MAXSIZE = 50000)
USE [master]
GO
ALTER DATABASE [Tfs] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [Tfs] SET MULTI_USER
GO
这篇关于如何缩小 TFS 数据库大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
