December 29, 2003 at 2:36 pm
Hello,
Can someone refer me to information on how to determine the best data and log file size for a new database?
Once the database is build it will go through a data migration process from old legacy data.
I have been building the data file at 2GB and the log file at 500MB. The migration works fine. When finished, the data file is still at 2GB and the log file is at 1.2GB. During the previous data migration testing, I was using the following code to speed it up:
DBCC SHRINKFILE ( database_name_Log , TRUNCATEONLY )
BACKUP LOG database_name WITH TRUNCATE_ONLY
This did help speed up the loading of the data when using NT Server/SQL Server 7.0. Close to the end of the development of our new system, we brought in 2000 Server/SQL Server 2000. The above code slowed down the data migration on the 2000 Server by about 3 hours. Since my only test of the data migration without the above code was a restart (database and tables built, table records deleted and then reloaded) and not from the beginning (Empty database built as mentioned above, tables build and then loaded) I don’t know how big of a log file I will end up with when the data migration is run without the log truncation. I don’t even know if this matters. We’re using a combination of DTS and VBScript running under cscript to migrate the data. The data migration will only run one time.
Even though I’ve been using SQL Server for 2 years, I’m still very new at this. I’m Serving as an ASP/SQL Server Developer and DBA.
My concern is that I will not build the database using the most optimum size and it will affect the performance of the system.
Any and all help will be appreciated.
Thanks in advance for your time.
Steve
December 30, 2003 at 1:21 am
- which Recovery-model did you specify on your sql2000 db ?
Maybe switching to bulk-logged may help out.
Edited by - alzdba on 12/30/2003 01:24:52 AM
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 31, 2003 at 9:16 am
Hi alzdba,
Thanks for the reply. I do not know how to set the Resovery model. My real question was in the first sentence of this posting. The rest was information on how we are loading the data and how the log file grew, but the data file did not.
Thanks again.
January 1, 2004 at 3:24 pm
Additional Information:
I created a new DB with 2000MB for the .mdf and 1000MB for the .ldf. I left the default settings at 10% automatic growth with no restriction for growth.
The Data Migration I mentioned in my fist posting is complete. The files are now 2000MB for the .mdf and 2594MB for the .ldf.
I found out how to set the Recovery Model. Currently it is set to "Full." Going forward, what can I expect to see if it is set to "Bulk-Logged?"
Now that the Data Migration is complete and we have a complete backup, should I Truncate the Log file? When this is done, how much is actually Truncated in the Log file? Is there way to use a parameter to specify a date and time to truncate from. For that matter, what is really truncated? My lack of DBA knowledge is really showing itself here. Then again, DBA's are not made over night.
For anyone that replies, thanks in advance for your time.
Steve
Nashua, NH
January 2, 2004 at 12:00 am
Check http://support.microsoft.com/default.aspx?scid=kb;en-us;272318 (sql2000)
http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD (SQL7.0)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 2, 2004 at 3:46 am
If you refer to DBCC SHRINKFLE help
it says
TRUNCATEONLY : Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data.
However, this does not by anymeans clear existing log. so you have to reverse your commands.
BACKUP LOG database_name WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( database_name_Log , TRUNCATEONLY )
The first 1 will clear tran log and 2nd will return free space to OS (hence reducing file size)
- Amit
January 6, 2004 at 1:57 pm
Thanks so much for this info. I will study it.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply