Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Default Database Drive Almost Full


Default Database Drive Almost Full

Author
Message
bill 49652
bill 49652
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
I have a client running a security badge app that originally installed SQL2005 on a 12GB C: Drive with Windows Server 2003. There is a 487GB D: partition. I am not a SQL Admin, but from what I read I thought it would be pretty easy, but I may be missing a simple step.

I created a D:\MSSQL\DATA folder and assigned the same User Permissions as the default C: data location. Using the Studio Management Console I changed the Default Data Location to the new location on D:. I then stopped the security application.

I thought at this point I would just have to Detach the databases, copy them to the new location and Attach them, but I can't Detach the main security program database.

Do I need to run any utilities to check DB integrity before I attempt to detach the DB's?
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1244 Visits: 3427
That is because there is still a user connected to that database.

Change the default database that *You* are logging into SSMS with, and then log back into SQL. Verify that no users are connected by using sp_who2 command.

Once no users, or processes are connected to that database. You should be able to detach it. If that fails. Create a Full backup. Drop the database, re-create the same database in the other location, and then restore from the backup that you just took.

You do not have to have the same directory structure. You can store the databases in any directory that you like. I actually store mine in a directory of the same name, under a parent of "SQL Server 2008 Databases". I also store the log file(s) on a separate spindle. And then store the backups on a totally separate box.

Andrew SQLDBA
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42504 Visits: 18877
I think Andrew is correct. If you have that database selected in EM, or a query window open, there's a shared lock on the database.

Select master, the right click your database and select detach (or run a script from master).

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
bill 49652
bill 49652
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
In theory what I'm attempting should work aside from my non-familiarity handling the db's right?

Do I have to detach and move all the databases one-by-one including the SQL2005 default db's. I'm not clear whether the Database and Transaction locations are for user-created db's or all.
Adiga
Adiga
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 21012
You may move the user databases using Detach and Attach method. Detach the database, move the files to new the drive using explorer and attach database. You can do it through GUI or you may use the script as in this article.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
balaji.ganga
balaji.ganga
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 656
Hi,
We cannot move a resource database straightway right. We can change the path of this database by using property --> advanced option. then only we can able to move this database..

Is it right?

Regards
Balaji.G
Adiga
Adiga
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 21012
Resource database cannot be moved. You cannot change the location of that database using SSMS since it is not listed there. Please refer this article on how to move System Databases.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search