First,review the transaction log size prior to the shrinking process.
USE [YourDatabaseNameHere]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO
?
Second,set the database recovery model to 'simple'.?
USE [YourDatabaseNameHere]
GO
ALTER DATABASE [YourDatabaseNameHere] SET RECOVERY SIMPLE
GO
?
Third,issue a checkpoint against the database to write the records from the transaction log to the database.
USE [YourDatabaseNameHere]
GO
CHECKPOINT
GO
?
Fourth,truncate the transaction log.
USE [YourDatabaseNameHere]
GO
BACKUP LOG [YourDatabaseNameHere] WITH NO_LOG
GO
?
Fifth,record the logical file name for the transaction log to use in the next step.
USE [YourDatabaseNameHere]
GO
SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO?
Sixth,to free the unused space in your transaction log and return the space back to the operating system,shrink the transaction log file.
USE [YourDatabaseNameHere]
GO
DBCC SHRINKFILE ([FileNameFromPreviousStep],[NeededFileSize])
GO
?
Seven,review the database transaction log size to verify it has been reduced.
USE [YourDatabaseNameHere]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO
Next Steps
- Review your key SQL Server databases to determine if the transaction log?growth is out of control.
- Review this code and modify it for one of your databases.
- Once the scripts are modified,test the scripts in a test environment to ensure they meet your needs.
- Schedule time to shrink your databases and communicate the configuration changes.
- Continue to monitor the database sizes and the available disk space on your servers.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
