Script which will shrink all databases

Hi,
May somebody send script which will shrink all databases on server
(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
I need do this at many servers, so I can't do this manually.
Thanks
Regards
On Aug 9, 12:41 pm, anxc...@.gmail.com wrote:
> Hi,
> May somebody send script which will shrink all databases on server
> (SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
> I need do this at many servers, so I can't do this manually.
> Thanks
> --
> Regards
There are multiple ways of doing this.
Create a .CMD file with this statement:
------
sqlcmd -s <servername1> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername2> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername3> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername4> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername5> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
------
Replace the <servername> with your servername
For each server add a new line and with the changed servername
(to create a CMD file: open notepad and paste the final script and
save it as scriptfile.cmd)
Once you have the CMD file, open commandprompt and go to the drive
location and enter the name of the file
In my case it is:
------
c:\dbccscript.cmd
------
This will display all the output to the commandprompt. You can
redirect all the output to a log/txt file using -o switch in the cmd
script.
Note that sqlcmd is a new utility in MS SQL server 2005.
in SQL Server 2000 you can achieve the same using "OSQL" books online
has enuf info on this to help you rewrite it for osql.
Regards,
schal
|||Thanks shal, unfortunately I don't have "shell - cmd" access to
server, I have to shrink all databases from SQL script only.
|||What schal posted will shrink all databases for the server you are connected to:
EXEC sp_MSForEachDB @.Command1 = N'DBCC SHRINKDATABASE (?, 10)', @.replacechar = '?'"
But I strongly suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1186682763.588386.295740@.19g2000hsx.googlegro ups.com...
> Thanks shal, unfortunately I don't have "shell - cmd" access to
> server, I have to shrink all databases from SQL script only.
>
|||It really works, I forgot that is normal storage procedure which I can
run from Query, sorry.
But what if I'd like first use SHRINKFILE all log files (all .LDF
files at server), log files because it takes me much more space than
databases? Logical name of log file is usually DatabaseName_Log.
How shrink log file for all databases, is it similar procedure?
|||Try this!
It′s recovery system space quickly:
use master
go
declare curdb cursor for select name from sysdatabases where name not in
('tempdb','model','master')
declare @.nmdb varchar(50)
declare @.cmd varchar(1000)
open curdb
fetch next from curdb into @.nmdb
while @.@.fetch_status = 0
begin
print '======='
print @.nmdb
print '======='
select @.cmd = 'Use ' + @.nmdb
select @.cmd = @.cmd + ' if exists(select 1 from sysobjects where name =
''limpa'')
begin
DROP TABLE LIMPA
end
create table limpa (char1 char(4000))
Set NoCount On
dbcc shrinkFILE (2, notruncate)
dbcc shrinkFILE (2, truncateonly)
dbcc shrinkFILE (1, notruncate)
dbcc shrinkFILE (1, truncateonly)
declare @.i int
select @.i = 0
While (@.i < 100)
begin
insert into limpa values (''a'')
select @.i =@.i + 1
end
backup log ' + @.nmdb + ' with truncate_only
drop table limpa'
Execute (@.cmd)
fetch next from curdb into @.nmdb
end
deallocate curdb
set nocount off
anxcomp@.gmail.com wrote:
>Hi,
>May somebody send script which will shrink all databases on server
>(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
>I need do this at many servers, so I can't do this manually.
>Thanks
>--
>Regards
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
|||Thanks, if thought it is simples way to do this. Could you me tell me
how at loop take:
1. name for all databases (without system dbs)
2. logical name of databases
2. logical name of transaction logs.
I need this information for this
BACKUP LOG @.DB_NAME
DBCC SHRINKFILE (@.DB_LOG_NAME)
I'd like this two lines will execute for all database.
Thanks
Regards
|||I wrote my own script and I'd like share it with you:
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'
SET NOCOUNT ON
DECLARE @.VarDBLogicalName nvarchar(128)
DECLARE @.VarDBName nvarchar(128)
DECLARE @.VarRowCount int
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.rowcount
WHILE @.VarRowCount <> 0
BEGIN
EXEC(' use ' + @.VarDBName + ' backup log '+ @.VarDBName + ' with no_log
dbcc shrinkfile(''' + @.VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @.VarDBName
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName =
DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
This works but use something what Microsoft NOT recommend "backup log
with no_log" you can modify it how you like
Regards