OK, it's been so long since my last blog that I'm surprised my last blog is still here! I am, however, glad it still is, and whilst this might not be the most revolutionary entry, I hope you find it useful.
So the scenerio is this: I have a database that has mulitple file groups and I would like to move to another machine with less disk space, or the log has grown too big and needs shrinking below its original size. In SQL 2000, and as far as I could tell, in SQL 2005, its not possible to shrink the log below the size that it was created at. The significance of the multiple file groups is that I can't use sp_attach_single_file_db which could create a new logfile.
The following works by creating a database 'for attach', which creates a database from existing data files, rebuilding the log if it does not exist. Notice that for SQL 2000|5 I don't actually specify anything for the new log, and nor must SQL be able to find the existing log, otherwise it will just attach the existing on. SQL 2005 improves on the 2000 script by explicitly adding the switch attach_rebuild_log. Watch out though, for both 2000 and 2005 , the new log file will appear in same directory as the datafile so be prepared to move it later.
So, first create a dummy database:
create database dbDummy on primary (name = 'Primary',filename = 'd:\dump\primay.mdf',size = 2), filegroup FG_User (name = 'FG_User_1',filename = 'd:\dump\fg_user_1.ndf',size = 1)log on (name = 'dummy_log',filename = 'd:\dump\dummy_log.ldf',size = 5)
This will work for 2000 or 2005. ShrinkFile will not take the log to anything less than 5Mb. Also note that for 2005, the primary data file must at least the size of the model data file or you will receive a warning. Next, we're going to take advantage of the create for attach, so dettach the database. This will work in sql 2000 or 2005:
Now rename the log file so SQL cannot find it (or delete - the choice is yours). If you don't, SQL will use the old log. For SQL 2000, the attach command is:
create database dbDummy on primary (name = 'Primary',filename = 'd:\dump\primay.mdf'), filegroup FG_User (name = 'FG_User_1',filename = 'd:\dump\fg_user_1.ndf')for attach
For 2005, the command will be exactly the same, with the exception that instead of for attach, specify for attach_rebuild_log.
You are now free to expand, move or change the log file as you require.
ok, dettaching the database is an offline operation, but you do get to resize the log. There is also the option of 'pausing' the sql service to allow connections to drop off one by one, or 'alter database <mydb> set offline with rollback after n'
-- This Blog is provided 'as-is'