Newbie ?? Where''s the data??

  • I have been working in Access for years.  I have just installed SqlServer 2005 and am moving the data from all the Access tables into my Sql Server database.

    I can see the database via microsoft explorer but as I import the data none of the sql files ever change in size and the date modified field never changes.

    I do a query and can find my newly imported data but I worry about where it is.  Wandering around in cyberspace.  Stuck in some temporary table waiting for me to do something which I don't know I should do?

    I am sorry that this is a silly question for those of you experienced with SQLServer 2005 and I hope to be there with youy some day. 

    Where I work I am "it" and so must learn on my own. 

     

  • Which SQL files are you looking at?

    Typically a databases files (.mdf and .ldf - and .ndf if you have filegroups) are located by default in

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • In the SQL 2005 Server Management Studio, right-click>properties your database, choose the 'files' page, and check the 'path' column. This should be were your database physical files reside.

  • Thanks both of you.  I actually know where the physical files are.  What troubles me is that after updating the file size and the date modified don't change. 

    In Access when you write a record to a table that date in Explorer gets updated.  And typically the file gets bigger. 

    But neither the .mdf or the .ldf files ever change. 

  • You should be able to see the file sizes increase. Try running this command (shows you physical file sizes) before and after you load the data:

    select size from sys.database_files

  • I did a little test by updating some data - saw no change in the file.

    So I bounced the sql server service and then I could see that all files had been updated.

    I would suggest that it has something to do with SQL Servers Lazy writing technology.

    Someone with a better understanding might be able to clarify.

    The only files I see getting updated regularly are the tempdb ones.


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • Thank you.  That's the part that worries me.  Is my stuff floating around in some temp db waiting for me to do something?  And if so, what? 

    Note - this is about a 20 gig database with over 10 million records and I have only ported over a tiny bit.  I don't want to proceed if I'm doing something wrong. 

     

     

  • No, I don't think you're doing anything wrong.

    I've just had a look at one of our client production servers and can see activity in terms of file dates having been changed. This is not happening all the time but appears to be at different intervals.

    I would suggest that if you are concerned about losing data, you implement a backup strategy that includes regular transaction log backups, so that you feel you have the ability to recover the data if necessary.

    I imagine if you were to transfer significantly larger amounts of data you would start to see database growth. Maybe using a batched approach might allow you to monitor progress as far as the files are concerned.

    I'm pretty sure this is standard behaviour for SQL Server


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply