UPDATE Statistics

I've just truncated a very big Transaction log file of a database and I now want to manually run UPDATE STATISTICS on all the tables of the database to improve performance

1) Is it OK to manually run UPDATE STATISTICS if the database itself has AUTO UPDATE/AUTO CREATE STATISTICS on/selected in the Properties/Options of the database? Or does AUTO UPDATE need to be disabled when I manually run UPDATE STATISTICS?

2) Can I run it with users using the database? Is it better to wait till no user is logged in?

3) Do I need to take a backup of the database before updating the statistics?  or is it   safe for data and not backup is required?

Thank you


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) yes, you can do this at any time
2) you can run this while users on the database, however if the database is really big, you might prefer to plan it for the evening hours
3) no need to backup first

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I agree with AngelIII

Yes, you can start a manual update.
We have a job set to run at midnight to update statistics every night. We can have many users so I avoid running it during the day. It may slow down the system.
It is safe for data. No backup required.
deepakeeAuthor Commented:
Thank you for your replies.

One more question. If I want to update statistics for all the tables of a database, is there a quicker way than manually run UPDATE STATISITCS table_name for each table? Or UPDATE STATISITCS table_name for each table is the only way?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
deepakeeAuthor Commented:
Is  EXEC SP_UPDATESTATS safe for data and no backup is also required before running it?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it is safe for data, as it only reads the data from the tables (part of it), it only updates the statistics records for the tables.

You should not need a backup before running it, while I usually run the backup AFTER the statistics run
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.