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.
| Author |
Topic |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2007-04-12 : 20:20:43
|
| DB data file size: 200GBgiant table A: 100GBSometimes we've had to do ALTER TABLE statements on giant table A such as converting from int to float that ballooned to using 200+ GB of transaction log to make a copy of the table with the new field type, copy the data in, rename and delete all in a transaction. So we ended up with a 250 GB log file that sits around being 98% empty most of the time as we do log file backups every 3 hours. This seems like a waste of SAN space, however there are times when we do need to do a conversion and we need the space available so it stays. Using a local hard drive for extra log space I would think would severely slow down the conversion. So how do most people handle this. Perhaps have a temp drive tray plugged in just for conversions shared by the data center? Any other suggestions?I'm thinking we really only need the log file to be big enough to do all the index maintenance day to day and only grow it large when we know downtime and a conversion will be going on. Growing a log file to 250GB is time consuming in itself. No instant initialization of log files in SQL 2005. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-04-12 : 22:15:49
|
| instant initialization has nothing to do with sql server. it has to do with windows, and the account requesting the disk space for file. if sql service account is local admin, then you'll get instant initialization.as for the log filling up when u do this, i would just manually backup the log after. if u are saying u want log on smaller partition, then can create filegroup. how often do u modify table structure? |
 |
|
|
|
|
|
Transaction Log Size and SAN Utilitzation