June 1, 2012 at 12:30 am
Morning all
I've got the code below which should loop through all the current databases and shrink them and their associated log files.
-- Drop temp table if it already exists
if exists(select * from tempdb.dbo.sysobjects where name like '%#db_config%')
drop table #db_config
-- Variable creation section
declare @top1 varchar(150)
declare @top1_log varchar(150)
----- Create temp table and set system variables
set nocount off
create table #db_config
(name_col varchar(30),
db_size varchar(20),
owner varchar(50),
dbid int,
created varchar(20),
status varchar(255),
compatibility_level varchar(20))
-- Get database initial sizes and insert into tracking table
insert #db_config
exec sp_helpdb
insert into sandpit.dbo.DB_Size_Tracking
(Action_Type
,Action_Time
,[DB_Name]
,DB_Size)
select
'Before'
,getdate()
,name_col
,db_size
from
#db_config
-- Get the initial database to shrink and then loop round to do them all
set @top1= (select top 1 name_col from #db_config)
set @top1_log=@top1 + '_log'
while @top1 is not null
begin
----- Shrink Log file -----
execute ('use [' + @top1 + ']')
CHECKPOINT;
DBCC SHRINKFILE (@top1_log)
----- Shrink actual database -----
use master
CHECKPOINT;
DBCC SHRINKDATABASE(@top1)
-- Remove the database name from the temp table and get the next database to shrink
delete from #db_config where name_col=@top1
set @top1= (select top 1 name_col from #db_config)
set @top1_log=@top1 + '_log'
end
-- make sure the temp table is empty ready for new database sizes
truncate table #db_config
-- Get database new sizes and insert into tracking table
insert #db_config
exec sp_helpdb
insert into sandpit.dbo.DB_Size_Tracking
(Action_Type
,Action_Time
,[DB_Name]
,DB_Size)
select
'After'
,getdate()
,name_col
,db_size
from
#db_config
I've finally sorted out the logic and various other issues, but this one has me stumped.
We have a databases called "BT data" which is the first on the list.
According to this mornings error message, it can't find a file called "BT Data_Log" in database Master (which is fair enough because it probably doesn't exist in there).
However, this line:-
execute ('use [' + @top1 + ']')
should be telling SQL Server to change database .... shouldn't it?
Am I missing something obvious on this one?
Any help gratefully accepted.
June 1, 2012 at 1:39 am
First off I'm going to say, that I hope you understand that shrinking a database is not recommended or best practise and ensure that you perform index maintenance after your shirnk (which will cause the DB to grow again). Only time I would warrent a shrink of the database would be when there was a true emergency on disk space.
Now if you stick a SELECT DB_NAME() in under the execute ('use ['........) statement, you will see that the database never changes context, so you will always be trying to shrink the originiating databases log.
June 1, 2012 at 2:26 am
We are swiftly running out of space on the drive(s) so this does need to be done.
The indexes will be rebuilt afterwards where necessary as a separate job step.
If that's the case, how would I shrink the log file?
June 1, 2012 at 2:36 am
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'DBCC SHRINKFILE ('+name+'_log);'+ CHAR(13) + CHAR(10)
FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
SELECT @sql
EXEC (@SQL)
This should do the job, but it is on the assumption that all your log files end with _log
June 1, 2012 at 2:49 am
Will that do all the log files in one go then?
Or will I have to loop through like I was doing originally?
June 1, 2012 at 2:58 am
all in one go.
June 1, 2012 at 3:15 am
I would recommend that you investigate how much free space you have inside each database before going ahead with this. If your files are full, this isn't going to have any effect. If operations such as index maintenance or batch processing require a lot of free space, then your files are going to grow again, wasting resources and causing physical fragmentation. Your best bet is to persuade whoever controls the purse strings to buy you some more disk. Make sure you go armed with growth figures in order to support your case.
If you go ahead with this, don't assume that all log file names end with _log. Some databases may have more than one log file, for example. Use sys.database_files to build your DBCC statements.
John
June 1, 2012 at 3:41 am
Anthony - thanks for that, I'll give it a try and see what happens.
Just one quick question, why have you ignored master, model, msdb and tempdb in your code?
John - sadly not going to happen, I've already tried. I've done this before on a couple of databases (but ignored the log files) and saved 13GB without any noticeable affectation of performance.
We want to set this up as a regular thing so I'm investigating the best way of doing it.
June 1, 2012 at 3:46 am
But that's my whole point. You've done it before and now you're having to do it again. That should tell you that the space that the database files have grown into is needed. The more you do this, the more time and resources you waste, and the more likely it is to have an adverse effect on performance.
If I can't talk you out of this, then please read about VLFs so that you get an understanding of how log growth can affect performance.
John
June 1, 2012 at 3:51 am
richardmgreen1 (6/1/2012)
Anthony - thanks for that, I'll give it a try and see what happens.Just one quick question, why have you ignored master, model, msdb and tempdb in your code?
John - sadly not going to happen, I've already tried. I've done this before on a couple of databases (but ignored the log files) and saved 13GB without any noticeable affectation of performance.
We want to set this up as a regular thing so I'm investigating the best way of doing it.
Because the log files dont end _log for the system databases so you will get errors on the execution. As I said it assumes that all your logical log file names end _log which they might do, if not you will need to build in a call to sys.database_files to get the proper logical names.
I'm with John on this one, you really need to get more disk space as your just going to waste resource and time, and will eventually hit a point where you wont be able to shirnk and rebuild due to not reclaiming enough space, so you will just make thing worse
June 1, 2012 at 4:03 am
Thanks for the input folks.
Looks like it's off to the bean-counters again. :crying:
June 1, 2012 at 4:04 am
I have a need to shrink database files for managing available disk space. I completely understand and agree that it is by no means best practice, but on dev / test environments it's not such a big issue and cost is a factor.
I use the following script to do so; does the trick for me.
use master
GO
declare @sysDB as nvarchar(50)
declare @sql as nvarchar(200)
declare @DBName as nvarchar(50)
--determine version of SQL Server in order to get correct syntax for system databases table.
if (select substring(cast(SERVERPROPERTY('productversion') as varchar(100)), 1,1))=8
begin
-- declare cursor using 2000 syntax for dbname
--set @sysDB = 'sysdatabases'
declare shrinky cursor
for
select name from sysdatabases
where name not in ('master', 'model', 'msdb')
end
else
begin
-- declare cursor using 2005/8 syntax for dbname
--set @sysDB = 'sys.databases'
declare shrinky cursor
for
select name from sys.databases
where name not in ('master', 'model', 'msdb')
end
open shrinky
fetch next from shrinky
into @DBName
while @@FETCH_STATUS = 0
begin
set @sql = 'DBCC SHRINKDATABASE ('+@DBName+', 1)'
exec sp_executesql @sql
fetch next from shrinky into @DBName
end
close shrinky
deallocate shrinky
hmm... maybe worth noting, that all DBs are set to simple recovery mode also. log files for "Full" probably wont get any movement from the above script.
June 1, 2012 at 5:27 am
allymcintosh (6/1/2012)
declare shrinky cursorfor
select name from sysdatabases
where name not in ('master', 'model', 'msdb')
You do know that shrinking TempDB while in use can cause corruption that will require a restart of SQL Server to fix?
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
June 1, 2012 at 6:07 am
Thanks, not something I've had happen so far but I will take note.
I tend to only shrink manually, when I know nothing else is happening, out of hours, when there's no jobs running etc. 🙂
June 1, 2012 at 5:12 pm
The DBCC SHRINKDATABASE() will shrink the log file also, so you don't need to do it separately; thus, you can just comment out the "DBCC SHRINKFILE".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply