1

I am a SharePoint admin and need to copy the log data in database to another temp database for analysis. The data is stored in a view ([WSS_Logging].[dbo].[RequestUsage]). There is a column [LogTime] on the view and any row older than 14 days will be discarded.

I would like to setup a daily schedule job to copy all records one day before to a temp database. I estimate there will be 1GB data daily. May I have your suggestion the best way to achieve this? If possible, please introduce method to check against the copying have not lost any row during the copy, too.

SQL Server is not my professional area. Any help is appreciated. Thanks.

Improve this question
4
  • Does the view have a clustered index? If not, then the data isn't stored there...
    – Aaron Bertrand
    Commented Apr 24, 2014 at 2:37
  • I am not sure. Can I simply copy the view data to a table? My only concern is the copying operation should not lasting too long...
    – Mark
    Commented Apr 24, 2014 at 2:50
  • 1
    You absolutely can copy the view returned by the data to a table but no matter what you do you're talking about copying 1GB of data every day. That will take as long as it takes pretty much, so the best mitigation you can use is to schedule the copy to happen at a quiet time of day. If you are happy to take potentially dirty data then you could use the nolock hint to reduce the impact on other users but that brings with it it's own dangers and should not be used lightly.
    – Steve Pettifer
    Commented Apr 24, 2014 at 10:13
  • Is your temporary database on the same server/instance? If so I'd consider if it's possible to analyze data where it is. The server would be doing pretty much the same workload but you would save the i/o overhead of copying.
    – Sir Swears-a-lot
    Commented Nov 24, 2014 at 1:23

2 Answers 2

Reset to default
1

Moving 1GB is not a trivial operation, though not a scary one either. A simple INSERT .. SELECT would have too much impact on the running server. Batching so only n-thousand rows are moved at a time would be better. 'n' can be tweaked to the idiosycracies of your particular system. Better still would be a dedicated ETL package. You'll need a SQL Server developer to help you with that, most likely. If it is important that you never miss a row be sure to build reconciliation into whatever solution you adopt.

Be careful using a datetime to determine whether a row should be copied or not. Times have to rounded to the resolution of the column holding them. It is therefore possible that a new row is rounded down and missed by the next extraction. It sounds like you will be copying data that is 13 days old so will not be at risk.

Improve this answer
0

I would suggest you to use BCP OUT and BULK INSERT with native mode. Alternatively, you can use SSIS as well - to create a package and schedule it as per your need.

BCP OUT and BULK INSERT with native mode - will be extremely fast.

You can use my script found HERE (test it and modify as per your need).

Also, read up on some tips to improve performance when you are loading data at The Data Loading Performance Guide -- It covers some trace flags for minimal logging, SSIS performance tweaks, etc.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.