June 6, 2012 at 7:41 am
hi,
What is wrong with my script below:
i am trying to change multiple database to full recovery mode so that i can shrink them.
SELECT NAME,(SIZE*8)/1024 AS SIZEmb FROM SYS.master_files
WHERE (SIZE*8)/1024 = 8500
declare @ws table(wsid int identity (1,1),dbname nvarchar(1000),wsSizeMb int)
declare @x int, @z int
declare @sql nvarchar(max), @sql2 nvarchar(max),@sql3 nvarchar(max)
declare @dbname nvarchar(1000)
declare @master nvarchar(100)
set @master = 'master'
set @sql = ''
while @x < @z
begin
select @x = 1, @z=MAX(wsid) from @ws
select @dbname = dbname from @ws
insert @ws
SELECT NAME,(SIZE*8)/1024 AS SIZEmb FROM SYS.master_files
WHERE (SIZE*8)/1024 = 8500 AND NAME NOT IN('master','tempdb','msdb','model')
AND file_id =1
SET @sql = 'USE ' + '[master]'+ ' ALTER DATABASE ['+ @dbname + '] SET RECOVERY full WITH NO_WAIT'
--select @sql2 = 'DBCC SHRINKFILE ('+@dbname+ ',3810)'
--select @sql3 = 'ALTER DATABASE '+ @dbname + ' SET RECOVERY Simple WITH NO_WAIT'
exec sp_executesql @sql
select @x=@x+1
print @sql
end
June 6, 2012 at 7:48 am
from a quick look, you are only trying to shrink databases that EQUAL 8500.
maybe you meant > 8500
June 6, 2012 at 7:51 am
you also never set @x or @z to start you while loop.
June 6, 2012 at 7:53 am
First of all I will say that shrinking data files is not best practise and should only ever be done in the event of low disk space and an increase of drive space cannot be acheived, due to you needing to rebuild all indexes which will cause database growth and other factors around shrinking.
But whats wrong with your script, you have = 8500 instead of >= 8500, not every file in every database will be 8500MB, also why do you need to set the DB to full recovery to shrink the file?
Also the logic in all of that script is wrong as you select the DB name from @ws before you have populated it, never set the intial values of @x,@z
June 6, 2012 at 7:58 am
Hi,
thanks guys, i am indeed trying to shrink databases that are 8500mb BECAUSE i have already checked the sizes and i have about 30 Databases of that size so that is correct.
I have run out of Disk space that's why i am doing this even though it's not goos practise.
I have set the starting point for @x as 1, do i have to to the same for @z?
i thought that i do not have to do the same for @z because @z is assigned to the auto increment wsid for the @ws table.
June 6, 2012 at 8:04 am
First off I would run this, to see if the DB's have any free space in them
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
then this to shrink
declare @ws table(wsid int identity (1,1),dbname nvarchar(1000),wsSizeMb int)
declare @x int, @z int
declare @sql nvarchar(max)
declare @dbname sysname
declare @master sysname
set @master = 'master'
set @sql = ''
insert into @ws SELECT NAME,(SIZE*8)/1024 AS SIZEmb FROM SYS.master_files
WHERE ((SIZE*8)/1024) = 8500 AND NAME NOT IN('master','tempdb','msdb','model')
AND file_id =1
select @x = 1, @z=MAX(wsid) from @ws
while @x <= @z
begin
select @dbname = dbname from @ws where wsid = @x
select @sql = 'DBCC SHRINKFILE ('+@dbname+ ',3810)'
exec sp_executesql @sql
select @x=@x+1
print @sql
end
then go through all the DB's and rebuild all indexes
June 6, 2012 at 8:24 am
THE-FHA (6/6/2012)
i am trying to change multiple database to full recovery mode so that i can shrink them.
They'll likely only grow again, are you aware of the ramifications of this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2012 at 1:36 pm
DBCC SHRINKFILE only shrinks files if there is available space. It is not a magic compression tool.
Also, run this and tell me what happens:
declare @x int, @z int
SELECT @x
SELECT @z
SELECT 1 WHERE @x < @z
Jared
CE - Microsoft
June 6, 2012 at 11:44 pm
hi,
i ow get the error
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'wsDatabaseName' for database 'master' in sys.database_files. The file either does not exist, or was dropped.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply