September 23, 2016 at 11:12 am
Hi all,
My C:\ drive is almost full, with only 7Gb remaining.
I want to assign more space to it, and to do so I need to empty my D:\ drive.
The purpose is to move everything back to D:\ when C:\ is enlarged
When I try to move the directory with a SQL Server file in it, I get the message that the move cannot be done due to an open file.
I get the same message when I try to move the directory with a SSIS file in it.
How can I close this open file?
BTW: both directories use the same database.
Maybe a 2nd question, with a maybe related topic.
When I start my computer, it takes forever for it to complete the start up. It takes ± 20 mins to be ready.
Can this be related to this open file?
It is a long lasting problem, now that I have to move these directories it got me thinking this open file may be the cause.
When I look in task manager the CPU action is 2% andmemoryuse is about 50%
The light at the front of the computer however keeps burning, and the computer is very slow to respond.
Is it possible to close it every time I close SQL Server (and open it when SQL Server is started)?
Thanks for the help
Hein
September 23, 2016 at 11:24 am
The files are open by SQL server.
If you want to move these, as well as other files, you will need to stop the SQL Server service on your PC.
As far as the long boot time, it may be caused by SQL. Instead of automatically starting it, you could set it to manually start. When you need it, start the service.
You should also adjust your memory settings that SQL can use by changing the Max Server memory setting.
Google sp_configure, that should be your starting point.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 23, 2016 at 11:54 am
Hi Michael,
Thank you for your reply.
It was very helpful. Using the << SQL Server Configuration Manager >> I stopped SQL Server from running.
After that I managed to move the 2 directories to another drive, and delete the ones on D:
Your other suggestion about << As far as the long boot time, it may be caused by SQL. Instead of automatically starting it, you could set it to manually start. When you need it, start the service. >>
Is there a way to use, let's say, an icon on the desktop that performs this task?
Hein
September 23, 2016 at 12:10 pm
You could create a link to Services.
You could create a batch file that will start the services, and another to stop the services.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 23, 2016 at 12:18 pm
Thanks Michael,
In the meantime I tried some things.
I right clicked the SQL Server configuaration manager in the startmenu.
I saw that it is possible to pin this programm to the taskbar.
I did and now it is not a lot of work to start and stop SQL Server.
The most "difficult thing" is not to forget to stop SQL Server when I stop working and shut down my computer.
Thanks again for your advice
Hein
September 23, 2016 at 12:22 pm
You do not have to stop it prior to shutting down your computer.
It's smart enough!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 23, 2016 at 12:29 pm
If it's not a system db, you can move db files by specifying a new location for the file, setting the db offline, physically moving the file(s), and finally setting the db back online. No need to stop SQL at all. All other dbs continue to function normally.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 23, 2016 at 12:46 pm
The long boot time may be because all your databases are in the FULL Recovery Model, no backups have ever been taken, the log files have gotten huge, and they have a bazillion VLFs that need to be formatted because you're still using the default of 1MB with 10% growth. And, no... instant file initialization won't help much when it comes to the transaction log files.
If I were you, I'd get someone that knows a fair bit about SQL Server to do a look-see to find and fix these problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2016 at 1:14 pm
Hi Scott and Jeff,
Thanks for your suggestions.
Maybe some answer to your suggestions:
I don't believe my databse is in full recovery mode, I turned it off. My database is a copy from an mySQL database. Everytime I refill the tables in the databse (I only use 1 database for speedskating) I delete all existing rows, and refill.
The database itself is 4.2Gb, the only log file is 3.8Gb.
Indeed, because of the former stated use, no backups have evr been made of this database.
Also there is only 1 logfile (VLF?)
I started my database with an initial size of 1 Gb, growing with 10% when needed
I agree Jeff that asking an experience DBA to look and give advise is a very well placed remark. It's just that ... I don't know anybody that could help me.
And placing an advertisment and ask for help and get some unfamiliair is asking for more trouble.
In the meantime I set SQL Server to start manually instead of autmatically (with SQL Server config mgr)
For now: I have learned a lot, have a lot more learning to do and think / hope that I can manage this problem
Thanks again to all
Hein
September 23, 2016 at 6:10 pm
Post deleted... I don't know what the heck I was thinking when I wrote it but it was totally incorrect.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2016 at 6:35 pm
SpeedSkaterFan (9/23/2016)
Hi Scott and Jeff,Thanks for your suggestions.
Maybe some answer to your suggestions:
I don't believe my databse is in full recovery mode, I turned it off. My database is a copy from an mySQL database. Everytime I refill the tables in the databse (I only use 1 database for speedskating) I delete all existing rows, and refill.
The database itself is 4.2Gb, the only log file is 3.8Gb.
Indeed, because of the former stated use, no backups have evr been made of this database.
Also there is only 1 logfile (VLF?)
I started my database with an initial size of 1 Gb, growing with 10% when needed
I agree Jeff that asking an experience DBA to look and give advise is a very well placed remark. It's just that ... I don't know anybody that could help me.
And placing an advertisment and ask for help and get some unfamiliair is asking for more trouble.
In the meantime I set SQL Server to start manually instead of autmatically (with SQL Server config mgr)
For now: I have learned a lot, have a lot more learning to do and think / hope that I can manage this problem
Thanks again to all
Hein
My apologies. I don't know what I was thinking on my previous post (maybe a bad hard-boiled egg :-)) but it was totally wrong. None of what I cited is going to be the problem because the files already exist and it's not a "restore" situation.
When I login to my laptop as me, I end up with a similar amount of disk and network activity and it takes about 10 minutes to settle down, although it's still usable during that time. If I login as my test user, most of that disk thrashing just doesn't occur. I might be because I login as a system admin and the test user is a mostly unprived user. It might also be because I have way too many linked icons on my desktop that the system verifies when I login.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2016 at 7:36 am
SpeedSkaterFan (9/23/2016)
The database itself is 4.2Gb, the only log file is 3.8Gb.
Focusing in on this, are you saying that the MDF file is 4.2 GB and the LDF file is 3.8 GB? Or are you using the database properties dialog and the whole database is 4.2 GB and 3.8 GB of it is logs?
This is an important difference. If you've never done a log backup, SQL Server is never going to reuse any of the space in the log file, so it's just going to continue to grow. I'd take a log backup or, if you don't need the ability to restore to a point in time, put the database into the simple recovery model.
SpeedSkaterFan (9/23/2016)
And placing an advertisment and ask for help and get some unfamiliair is asking for more trouble.
I wouldn't do this either. Besides, just thing about how much you'll learn by researching backups and log files on your own. 🙂
If you'd like a good place to start, check out the stairway series on this site at http://www.sqlservercentral.com/stairway/73776/.
There are plenty of other things that could be going on with your laptop that aren't SQL Server. It could be nearly anything. I try to be careful about the junk I have running on my machine and am somewhat miserly about installing software. Of course, with Windows 10 doing whatever it wants whenever it wants, who knows anymore.
September 24, 2016 at 10:57 am
Hi Ed,
Thanks for your contribution, appreciate it!
When I look in Windows Explorer I see that the .mdf file = 4.2Gb and the .ldf file is 3.8Gb
I have no need for repairing the database, or to undo an insert.
My situation: I have a database that I use for speedskating times.
For me, the original data comes from a (mySQL) database that gets undated through internet and a synchronize option.
When this synchro is done I start SSIS and use this to 1st delete all data in a table, and refill it with the renewed mySQL data.
This is the data I work with. I don't have to do any deleting, changing or inserting.
If something goes wrong (which so far hasn't occured) i just use SSIS to renew the data.
Reading Michel's advice yesterday, and after taking a good look at the contents of SQL Server config mgr, not only did I set SQL Server to a manual start, but also SSAS and SSRS.
The startup time dropped from 20 to 30 mins to 3! 😀
As far as your comment on asking somebody to help: I like the learning experience. Always have and I hope to learn a lot more.
My disadvantage is that I don't know all the posibilities there are. If I find out what's possible there are (like Michel's hint), then I can search for the answer.
Luckily for me, this site provides me with lots of info.
And yes, I know the stairway series. It is a welcome source of info. The items about Indexing eg where very helpful.
Maybe somebody should make a book of the complete series.
Hein
September 24, 2016 at 1:58 pm
SpeedSkaterFan (9/24/2016)
Reading Michel's advice yesterday, and after taking a good look at the contents of SQL Server config mgr, not only did I set SQL Server to a manual start, but also SSAS and SSRS.The startup time dropped from 20 to 30 mins to 3! 😀
Hein
Oh MY! That's, of course, an incredible difference. I've never seen never mind eve heard of such a thing before. I have no idea off the top of my head what the cause could be in your particular case but that's just crazy. If you ever learn what the cause of this is, I'd sure like to know.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply