Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Detaching a database

  1. #1
    Join Date
    Dec 2003
    Posts
    181

    Detaching a database

    I want to move the log files of a database to another drive to get more performance on our sql server. I'm about to detach the database but need to know if I need to mark the Update statistics prior to detach.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Not necessary.

  3. #3
    Join Date
    Dec 2003
    Posts
    181
    Done. Now what's the best way to see the improvements of moving the ldf files in Performance monitor. Which one do I need to monitor?

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You can compare the Disk Queue length, writes/sec, reads/sec for the disk.

  5. #5
    Join Date
    Dec 2003
    Posts
    181
    I'm running the monitor right now. Do you also suggest defraging the server, I don't remember running since we started using this server in 2001.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    I hardly do that myself as my servers are dedicated to sql server and data file/log file drives don't anything except sql files. In that case there is no fragmentation on those disks.

    Your situation may be different.

  7. #7
    Join Date
    Dec 2003
    Posts
    181
    I've asked around about the server and what other functions it did in the past before I got here but they said its been dedicated as a sql server and nothing else. in that case i don't need to defrag it as well. i'll take their word for it. tks.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    You may need check table fragmentation with 'dbcc showcontig' instead, defrag them by rebuilding clustered index if necessary.

  9. #9
    Join Date
    Dec 2003
    Posts
    181
    I'll try to run this now. What would be an acceptable Scan Density..I've read that anything below 90% will benefit from defragmentation.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Should as close to 100% as possible.

  11. #11
    Join Date
    Dec 2003
    Posts
    181
    I can see a lot of the tables from master and tempdb to have low scan density 12-15%, is this by design?

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Don't worry system dbs.

  13. #13
    Join Date
    Dec 2003
    Posts
    181
    so i just need to worry on the results from the company databases then?

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    That's right.

  15. #15
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Master and msdb tables fragmentation percentage will be higher because of their size. If a table only has two pages and if they are in non contiguous location you gor 50% fragmentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •