August 22, 2011 at 9:36 am
Does anyone have any script or stored procedure to shrink all the databases in sql server 2008.
Please let me know if you have it.
I have this below script and will not work for sql server 2008.
CREATE PROCEDURE SP_SHRINK_LOG
AS
BEGIN
DECLARE dbNames_cursor CURSOR
--
-- Run this script routinely to control the growth of LDF log files.
--
FOR
SELECT NAME FROM dbo.sysdatabases where name not in ('master', 'model', 'msdb', 'tempdb')
OPEN dbNames_cursor
DECLARE @dbName nvarchar(50)
FETCH NEXT FROM dbNames_cursor INTO @dbName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
declare @logName nvarchar(50)
exec(' use [' + @dbName + '] ' + 'backup log [' + @dbName + '] with truncate_only')
set @logName = @dbName + '_log'
exec(' use [' + @dbName + '] ' + 'declare @logName nvarchar(50) select @logName = name from sysfiles where fileid = 2 set @logName = rtrim(@logName) dbcc shrinkfile (@logName)')
dbcc Shrinkdatabase (@dbName, 0)
END
FETCH NEXT FROM dbNames_cursor INTO @dbName
END
CLOSE dbNames_cursor
DEALLOCATE dbNames_cursor
END
August 22, 2011 at 9:51 am
Good thing it doesn't work.
The command you need to run is backup log. Not truncate log.
If you don't care about losing all the data between full backups change the dbs to simple mode.
Edit : And read this one too
http://qa.sqlservercentral.com/articles/Transaction+Log/72488/
August 22, 2011 at 9:52 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 9:59 am
Backup log with truncate_only is not supported with SQL Server 2008. I have to say that I wouldn’t run this script on other versions that still support the truncate_only option. Running such script breaks the log backup chain for all databases and forces you to run full backup on all databases if you still want to use log backups (and have the ability to restore a database to a point of time).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 22, 2011 at 11:15 am
so this will work right. And after running this I always run full backups anyways.
CREATE PROCEDURE SP_SHRINK_LOG
AS
BEGIN
DECLARE dbNames_cursor CURSOR
--
-- Run this script routinely to control the growth of LDF log files.
--
FOR
SELECT NAME FROM dbo.sysdatabases where name not in ('master', 'model', 'msdb', 'tempdb')
OPEN dbNames_cursor
DECLARE @dbName nvarchar(50)
FETCH NEXT FROM dbNames_cursor INTO @dbName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
declare @logName nvarchar(50)
exec(' use [' + @dbName + '] ' + 'backup log [' + @dbName + '] with backup_log')
set @logName = @dbName + '_log'
exec(' use [' + @dbName + '] ' + 'declare @logName nvarchar(50) select @logName = name from sysfiles where fileid = 2 set @logName = rtrim(@logName) dbcc shrinkfile (@logName)')
dbcc Shrinkdatabase (@dbName, 0)
END
FETCH NEXT FROM dbNames_cursor INTO @dbName
END
CLOSE dbNames_cursor
DEALLOCATE dbNames_cursor
END
August 22, 2011 at 11:20 am
If you want to destroy your server, a shotgun approach is much faster and cheaper. Especially more fun.
Last time I say this, read the articles we linked to.
August 22, 2011 at 11:20 am
No. It won't (there's no backup_log option on Backup Log)
Please go and read my article and stop shrinking your database
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply