One of the applications in our organisation used an sql database on a consolidated SQL server (meaning, there are more databases on the server, owned by other applications). At installation time of the new SQL 2008 server R2, I wasn't contacted, so the initial databases were created without correct forethought.
Best practice in my opinion:
- Drive for system
- drive for databases
- drive for logs
- drive for backups
BUT then you should setup the server to automatically place new databases divided over the drives mentioned above.
Another thing you should make sure, is to remove default SQL folder names like: MSSQL10_50.MSSQLSERVER\MSSQL. Make it consistent over all drives: either in root of the drive, or in a specific instance folder.
Now I had to move a 110GB (yes really) Log file, from my database drive, to the log file drive. Which in itself takes time to move, but the steps also aren't without risk.
Shutdown application so the database isn't used anymore.
Take down the database on the server.
Detach database.
Move the LDF to the appropriate drive.
Reattach the database with the correct drive location for log files.
When you want to shrink a database log of 110GB, which is what you SHOULD do when the actual DB is only 1.5GB, make sure you change the backup type to Simple and the initial size for the log to be as small as possible (also check irratic max growth rates).
Currently I have all the stuff in backup and shrunk the files to manageable files.
Geen opmerkingen:
Een reactie posten