Help with script, shrink multiple data files

  • 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

  • from a quick look, you are only trying to shrink databases that EQUAL 8500.

    maybe you meant > 8500

  • you also never set @x or @z to start you while loop.

  • 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

    http://www.sqlservercentral.com/articles/SHRINKFILE/71414/

  • 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.

  • 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

  • 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" 😉

  • 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

  • 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