SQL Server Tacklebox- P21: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 4 Managing data growth Figure Physical Disk performance object in Perfmon. With all monitoring systems a go I am ready to load up a heap table called book_list that I created in the All_Books_Ever_Read database. The file has approximately 58 thousand records so I m going to use the BCP batch file technique see Listing in Chapter 3 to iterate through the file 50 times and load million records into the database. Now it is time to begin the load. A quick peek at Perfmon see Figure shows the current absence of activity prior to executing a hefty query. PhysicalDisk _Total b Disk Time Avg. Disk Queue Length Current Disk Queue Length 0 Figure Perfmon low disk activity. Executing Load . now Please don t turn or create the next page . Sorry I could not resist the Sesame Street reference to The Monster at the End of This Book. In fact the load proceeds with little fanfare. Imagine this is being done in the middle of the afternoon perhaps after a big lunch or worse early in the AM DBA M most likely before your second sip of coffee with you blissfully unaware of what s unfolding on one of your servers. Figure shows the BCP bulk insert process running. 100 4 Managing data growth Figure BCPing data into the All_Books_Ever_Read database. You can see that the batch process ran 50 times at an average of seconds a run with a total load time of roughly 2 minutes. Not bad for million records. Now for the bad news Figure shows how much growth can be directly attributed to the load process. Name I SQLLogs i SQLTempDB _J Size Type_ File Folder File Folder 3 276 800 KB SQL Server Databa 3 480 448 KB SQL Server Databa. Figure Log file growth loading millions of records into table. NOTE For comparison in a test I ran without ever having backed up the database the data file grew to over 3 GB but the log file grew only to 150 MB. 101 4 Managing .
