SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 C drive running out of space due to large database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asp__developer
Posting Yak Master

108 Posts

Posted - 07/19/2013 :  09:57:42  Show Profile  Reply with Quote
My c drive is running out of space and literally has no space left since the size of my database has reached maximum size.

what to do ? I read about shrinking database but it is recommended since at many places a few people are saying that shrinking database is not a good practice and?

how to solve since my data is very important ?

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/19/2013 :  10:44:28  Show Profile  Reply with Quote
In general, shrinking the data files is not a good idea for all the reasons that you may have read. Even if you shrink, aren't you going to run into the same problem in a day or in a week or whatever?

If you have space on another drive, you can move the data and log files to that drive. http://msdn.microsoft.com/en-us/library/ms189133.aspx
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/19/2013 :  10:52:27  Show Profile  Reply with Quote
What is there is only c drive on the server and c drive is full ? what to do in this situation ?

Based on the readings of shrinking databases, it seems like a bad idea and it will slow down the performance.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/19/2013 :  11:04:53  Show Profile  Reply with Quote
The thing is, even if you were to shrink the data file, all it can do is release free space within the file. If the file is mostly used then shrinking won't do you any good. So first find if you are going to gain anything from shrinking. See here for how to find how much free space is available: http://www.mssqltips.com/sqlservertip/1805/different-ways-to-determine-free-space-for-sql-server-databases-and-database-files/

If you do have free space, and if it will make a difference, I guess you have to shrink it. But it will fragment the indexes and cause performance issues. I assume you have read articles by Paul Randal on this topic.

If at all possible, I would get the powers that be to get you another hard disk. Also, is this a dedicated SQL Server? If it is not, what else is taking up the disk space?
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/19/2013 :  11:41:45  Show Profile  Reply with Quote
It is just this one database that is taking all the space.

We do have another drive, could you please help me or guide me how I can move the database to another drive if this option is better than shrinking ?

I have seen the article but what is the command to move the database ? can you please give me an example how to do that in steps ?

Also, then the database won't be in c:/program files ? sql server ? 90 / data ? it will be in different drive ? will is cause any problem in future in backing up, maintenance etc ?

Based on the article: http://msdn.microsoft.com/en-us/library/ms345483.aspx

1) If I move my database to d drive, my instance and everything will remain same right ?

2) In this command
ALTER DATABASE database_name SET OFFLINE;


I just need to replace "database_name" with my database name ?

3) In this command
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );


"database_name" = my database name

let us say, new path is: D:/MyDataFolder/

How I will mention the path in syntax in place of 'new_path\os_file_name'?

what is os_file name ?

Moving database will keep the name of the database same right ?

3) Will moving database changes everything ? If my application is running on same machine that is using the same so in code the connectionstring is mention that links to the database. Changing drive / movie database - do I need to change anything else too ?

Edited by - asp__developer on 07/19/2013 12:08:34
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/19/2013 :  13:25:37  Show Profile  Reply with Quote
Couple of things that you want to do to avoid the situation where you took the database offline and then can't bring it back.

1. Take a full backup of the database and keep it safely somewhere.
2. Create a small test database, and go through this process to make sure you can move the files and bring it back online successfully.

Run this command - it tells you where the physical files are located currently. When you specify the new names, you should specify in the same format. It is the physical files that you are moving, not the logical names.

-- Replace MyTestDatabase with your database name
SELECT name AS LogicalName, physical_name FROM sys.master_files WHERE database_id = DB_ID('MyTestDatabase');


Connection strings etc. would remain the same. Logically the database remains the same. It is the physical files that are moving, so anything to do with physical files would be affected.

Again, I cannot reiterate how important it is that a) you take a full backup of the database and b) practise doing this on a test database.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
769 Posts

Posted - 07/19/2013 :  13:51:22  Show Profile  Reply with Quote
Are you sure the data file for the database is the issue? Could it possibly be the log file has grown out of control and filled the drive?

Is the database set to full recovery model? If yes, do you run frequent transaction log backups?
Also check your backups (your are taking backups - right?). How many backups do you keep - are you removing the old backup files?

Ideally, you should not have your database files on the C:\ drive. You should always create additional drives SQL Server data files, log files, etc... But, until you understand exactly what is causing the issues - just moving the files over to a new drive may only push the issue down the road...
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/19/2013 :  14:06:32  Show Profile  Reply with Quote
quote:
Originally posted by James K

Couple of things that you want to do to avoid the situation where you took the database offline and then can't bring it back.

1. Take a full backup of the database and keep it safely somewhere.
2. Create a small test database, and go through this process to make sure you can move the files and bring it back online successfully.

Run this command - it tells you where the physical files are located currently. When you specify the new names, you should specify in the same format. It is the physical files that you are moving, not the logical names.

-- Replace MyTestDatabase with your database name
SELECT name AS LogicalName, physical_name FROM sys.master_files WHERE database_id = DB_ID('MyTestDatabase');


Connection strings etc. would remain the same. Logically the database remains the same. It is the physical files that are moving, so anything to do with physical files would be affected.

Again, I cannot reiterate how important it is that a) you take a full backup of the database and b) practise doing this on a test database.



Thank you for the advise - I will keep these things in mind.

I ran the command on test database and I got the result:


LogicalName	physical_name
Test_DB	c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_DB.mdf
Test_DB_log	c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_DB_log.ldf



Could you please answer these questions ?

Based on the article: http://msdn.microsoft.com/en-us/library/ms345483.aspx

1) If I move my database to d drive, my instance and everything will remain same right ?

2) In this command
ALTER DATABASE database_name SET OFFLINE;


I just need to replace "database_name" with my database name ?

3) In this command
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );


"database_name" = my database name

let us say, new path is: D:/MyDataFolder/

How I will mention the path in syntax in place of 'new_path\os_file_name'?

what is os_file name ?

Moving database will keep the name of the database same right ?

Edited by - asp__developer on 07/19/2013 14:10:46
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/19/2013 :  15:15:32  Show Profile  Reply with Quote
1. Yes, if you only move the database files, your instance name, database name and everything else as seen by business clients would remain unchanged.

2. Yes. database_name is a placeholder for your actual database name. See my example below.

3. It is backward slash, but you would provide the path exactly like you do it in Windows. Again, see my example below.

The script below is a complete script. You can copy and paste it to an SSMS window and run it step by step to see what it is doing. Your database name will not be changed. It is the physical file location that is changing.
-- 1. create a test database
USE master 
GO

CREATE DATABASE MyTestDatabase;
GO

-- 2. see where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO
--MyTestDatabase	D:\Sql_Data\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	L:\Sql_Log\MyTestDatabase_log.LDF	ONLINE

-- 3. set the database offline.
ALTER DATABASE MyTestDatabase SET OFFLINE;
GO
-- 4. now go to windows exporer and move the file manually to the new location.
-- in my case I am moving it from D:\Sql_Data\MyTestDatabase.mdf to 
-- D:\Temp\MyTestDatabase.mdf

-- 5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO

-- 6. See where SQL Server will look for the database files when it comes online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

-- 7. Set the database back online.
ALTER DATABASE MyTestDatabase SET ONLINE;
GO

-- 8. See where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

--MyTestDatabase	D:\Temp\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	L:\Sql_Log\MyTestDatabase_log.LDF	ONLINE

-- 9. Cleanup. You DONT WANT TO DO THIS WITH YOUR REAL DATABASE ;)
DROP DATABASE MyTestDatabase
GO
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/19/2013 :  15:38:33  Show Profile  Reply with Quote
Great - thank you so much for your help.

I will post back with results if everything goes smoothly.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/22/2013 :  12:23:22  Show Profile  Reply with Quote
On the step I am getting error that database is offline so can't do anything ? why ?

5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/22/2013 :  13:24:23  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

On the step I am getting error that database is offline so can't do anything ? why ?

5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO

Not sure why you are getting that error message. I ran the test script on SQL 2008 R2 and on SQL 2012 and it works correctly. What version of SQL Server are you using? Can you post the exact text of the error message?
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/22/2013 :  15:26:43  Show Profile  Reply with Quote
I am using SQL Server Msg Std 2012

So I just ran the command and somehow it worked:

ALTER DATABASE Move_DB MODIFY FILE ( NAME = Move_DB, FILENAME = 'C:\Move_DB.mdf' );
GO


I received the message saying:
The file "Move_DB" has been modified in the system catalog. The new path will be used the next time the database is started.


But then I see a triangle with the database (like error sign) when I bring back the database online.

I ran the command:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'Move_DB');



Which gave me results as :

Move_DB	C:\Move_DB.ldf	ONLINE
Move_DB_log	C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Move_DB_log.ldf	ONLINE


so it seems like it only moved mdf ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/22/2013 :  15:41:42  Show Profile  Reply with Quote
I was only showing you the example of moving the database file. If you want to move the log file also, do a similar thing for the log file - see 5b and 5c in my example below. Pay particular attention to the logical name and file locations. If you don't give the correct names, SQL Server will get confused and you won't be able to bring the database online.
-- 1. create a test database
USE master 
GO

CREATE DATABASE MyTestDatabase;
GO

-- 2. see where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO
--MyTestDatabase	D:\Sql_Data\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	L:\Sql_Log\MyTestDatabase_log.LDF	ONLINE

-- 3. set the database offline.
ALTER DATABASE MyTestDatabase SET OFFLINE;
GO
-- 4. now go to windows exporer and move the file manually to the new location.
-- in my case I am moving it from D:\Sql_Data\MyTestDatabase.mdf to 
-- D:\Temp\MyTestDatabase.mdf

-- 5. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase, FILENAME = 'D:\Temp\MyTestDatabase.mdf' );
GO

-- 5b. now go to windows exporer and move the LOG file manually to the new location.
-- in my case I am moving it from L:\Sql_Log\MyTestDatabase_log.LDF to 
-- D:\Temp\MyTestDatabase_log.LDF

-- 5c. Tell SQL Server that the physical file has moved.
ALTER DATABASE MyTestDatabase MODIFY FILE ( NAME = MyTestDatabase_log, FILENAME = 'D:\Temp\MyTestDatabase_log.LDF' );
GO

-- 6. See where SQL Server will look for the database files when it comes online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

-- 7. Set the database back online.
ALTER DATABASE MyTestDatabase SET ONLINE;
GO

-- 8. See where the files are now.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyTestDatabase');
GO

--MyTestDatabase	D:\Temp\MyTestDatabase.mdf	ONLINE
--MyTestDatabase_log	D:\Temp\MyTestDatabase_log.LDF'	ONLINE

-- 9. Cleanup. You DONT WANT TO DO THIS WITH YOUR REAL DATABASE ;)
DROP DATABASE MyTestDatabase
GO
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 07/22/2013 :  15:43:08  Show Profile  Reply with Quote
Ok thank you very much for the help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000