February 16, 2010 at 1:50 am
Hi,
I have done some powershell scripts in the past, but have not done anything with SQL Server and Powershell other than issuing queries. I am looking for ideas as to how I could do the following:
1). Stop SQL Services.
2). Run a disk defrag.
3). Start SQL Services.
The added complication is that I can only run the defrag in a maintenance window, so it has to complete by or be stopped at a certain time.
Has anyone attempted this from powershell?
Thanks.
February 18, 2010 at 3:27 pm
I would not recommend automating this process unless you or some other qualified person is going to be around to monitor it and ensure that SQL stops and starts okay. If the script does not work correctly and no one is there to monitor it, you could easily run outside of your outage window and it could not be noticed until you come in to check on it.
Stopping and starting the services should be easy enough, but I do not know of any cmdlet that will run a defrag, so you will have to run the command-line application defrag.exe to acheive that.
You can use the stop-service and start-service cmdlets to stop and start the sql services. I would suggest running two scripts through task scheduler. One to stop the services and run the defrag, and then another towards the end of your maintenance window to check to see if the defrag is still running. If it is, have it stop the defrag and start your sql services.
Joie Andrew
"Since 1982"
February 18, 2010 at 4:29 pm
disk defraggers such as diskkeeper can run with SQL up.
---------------------------------------------------------------------
February 19, 2010 at 9:28 am
I believe tools like DiskKeeper are a better solution to this. Also keep in mind that file fragmentation is only half the potential issue, you can easily have database fragmentation that defragging the file will do nothing about.
CEWII
February 19, 2010 at 9:34 am
If you manage your file size and grow discretely, you don't need to do this often. Diskeeper works, but I've done this once a quarter or less with the built in tool by shutting down SQL for a few hours and letting it run.
Elliot has a great point. Most of the frag issues are internal fragmentation inside the SQL files. Not the files being spread out, but the data inside the file not being contiguous. For this you need to check and rebuild your clustered indexes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply