Extra files just in case? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Extra files just in case?

Author

Topic

sureshot
Yak Posting Veteran

72 Posts

Posted - 2006-08-01 : 22:19:51

I need to run a big one-time column type conversion on a rather large database. I fear that I may run out of room so I'd like to setup extra files on another disk for data and logs. However, I only have access to slower disks so I'd rather not have these extra files used unless they are needed and SQLs general policy seems to load balance the files. Is there any way to set it up to not use the extra files until the main ones are out of space?

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 04:01:28

How about:


SELECT *
INTO RemoteServer.RemoteTempDataase.dbo.TempTable
FROM MyTable

DROP TABLE MyTable

CREATE TABLE MyTable ... with new column structure

INSERT INTO MyTable
SELECT TOP 10000 T1.* -- use column list with conversion if necessary
FROM RemoteServer.RemoteTempDataase.dbo.TempTable AS T1
     LEFT OUTER JOIN MyTable AS T2
         ON T2.MyPK = T1.MyPK
WHERE T2.MyPK IS NULL -- New records only
-- Loop round until no records left

You can resume after disk full / shrink / backup-TLogs / etc. to replenish records until done.

Kristen

sureshot
Yak Posting Veteran

72 Posts

Posted - 2006-08-02 : 10:44:58

The conversion takes about 5 hours overnight so I can't wait until morning to see that it ran out of space and I need to take action.

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 11:02:43

In that case I think my approach would work well. You just need to make sure that the TLog is being backed up often enough to stop it growing, and that the restructuring of the data is in a LOOP - if you do it all in one transaction it will HAVE to grab enough space for a rollback of the whole 9 yards.

I would add

WAITFOR DELAY '000:00:05'

as the last statement in the loop, so that there is a delay to allow the backup etc. to run.

Kristen

sureshot
Yak Posting Veteran

72 Posts

Posted - 2006-08-03 : 07:38:22

I see. I'll give a shot. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources