The tip teaches us how to use Policy Based Management in SQL 2008 to work out which databases have extensive Virtual Log File fragmentation. So far, so good. But hark! I hear you cry! I only have one SQL 2008 Server. Everything else is still SQL 2000 or SQL 2005. There is no Policy Based Management tool there.
Panic not. If you have set up a Central Management Server in SQL 2008, and registered your servers within it, you can evaluate the Policy against all your servers. Just right click on the group you want to check, pick 'Evaluate Policies', choose your source (i.e. the SQL 2008 box on which you set up the initial policy), and let it run. It tends to look as though it's stuck if one of the servers in the group you've chosen isn't running SQL or is turned off, but leave it to run, and, eventually it'll stop trying to connect to that box, and move onto the next one.Once you know what's up, you can follow the instructions here. I did make up a handy script to make life a little easier, and I cannot emphasise enough that you must back everything up properly before you get going. It will save a vast amount of heartache in the long run. Honest truly.
USE [databasename]
-- Backup the Log File
BACKUP LOG [databasename] TO devicename
GO
-- Set recovery to simple mode, otherwise the log file refuses to shrink
ALTER DATABASE [databasename]
SET recovery simple
GO
-- Shrink the log file
DBCC shrinkfile ( transactionloglogicalfilename , truncateonly )
GO
-- Grow it in ONE BIG STEP
ALTER DATABASE [databasename]
MODIFY FILE
(
NAME = transactionloglogicalfilename
, SIZE = newtotalsize --MB
)
GO
-- Set recovery back to full mode
ALTER DATABASE databasename
SET recovery FULL
GO
Finally, run a full backup, so that all future transaction log backups have something to work from.
(I used the instant SQL formatter to format the SQL for this post. It looks, um, interesting....)
No comments:
Post a Comment