August 15, 2008 at 5:25 am
I'v got very large database file more then 2TB. I can not shrinking or make file size smaller. SQL 2005 gives me different kind of errors.
One of them is - "MODIFY FILE failed. Size is greater than MAXSIZE".
Is it possible to shrink or reside- make it smaller database file with 2.4TB size? If yes , how? Thanks.
August 15, 2008 at 7:23 am
If the file does not have free space you cannot shrink it. Does the file have free space? Is it a database file or a log file?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 7:32 am
It is in my question "very large database[/u] file more then 2TB"
August 15, 2008 at 7:46 am
fixfox8 (8/15/2008)
It is in my question "very large database[/u] file more then 2TB"
I have learned in my close to 3000 posts on this site, that the reason someone has posted something is because sometimes they don't know what they are asking so a simple question of clarification can help me better answer a question.
You still did not answer my question about free space. In general you do not want to shrink files (database or logs) in SQL Server because they have gotten that big because they need to be that big. If this is a test or development database where the DB has grown beyond the actual size needed then you could shrink it. Have you run DBCC ShrinkDatabase?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 7:54 am
fixfox8 (8/15/2008)
It is in my question "very large database[/u] file more then 2TB"
Is it a data file (.mdf or .ndf) or a log file (.ldf)
What's the output of the following query? (run in the database in question)
select name, max_size, vfs.size_on_disk_bytes/1048576 AS SizeInMB
from sys.database_files df inner join sys.dm_io_virtual_file_stats(db_id(), NULL) vfs on df.file_id = vfs.file_id
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 7:58 am
I have tryed DBCC ShrinkDatabase (filename, filesize), but it gave me previous error. By the way the database is in the SIMPLE recovery model.
As I know if we use this DBCC ShrinkDatabase (filename, filesize), then whatever is more then filesize should go to the other files (There are six of them for the database). Thanks.
August 15, 2008 at 8:06 am
[/quote]Is it a data file (.mdf or .ndf) or a log file (.ldf)[/quote]
It is a data file .mdf. I can not run right now that query, becaus I am running something else. Thanks.
August 15, 2008 at 8:15 am
fixfox8 (8/15/2008)
I can not run right now that query, becaus I am running something else. Thanks.
When you can please post the results. I have a suspicion I know what's wrong, but want to confirm.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 8:20 am
fixfox8 (8/15/2008)
I have tryed DBCC ShrinkDatabase (filename, filesize), but it gave me previous error. By the way the database is in the SIMPLE recovery model.As I know if we use this DBCC ShrinkDatabase (filename, filesize), then whatever is more then filesize should go to the other files (There are six of them for the database). Thanks.
DBCC ShrinkDatabase does not move data off a file to another file. DBCC ShrinkFile using EMPTYFILE does move the data to other files in the same filegroup. From BOL:
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
...
D. Emptying a file
The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.
Copy Code
USE AdventureWorks;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- Empty the data file.
DBCC SHRINKFILE ('Test1data', EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks
REMOVE FILE Test1data;
GO
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 8:37 am
Jack Corbett (8/15/2008)
fixfox8 (8/15/2008)
I have tryed DBCC ShrinkDatabase (filename, filesize), but it gave me previous error. By the way the database is in the SIMPLE recovery model.As I know if we use this DBCC ShrinkDatabase (filename, filesize), then whatever is more then filesize should go to the other files (There are six of them for the database). Thanks.
DBCC ShrinkDatabase does not move data off a file to another file. DBCC ShrinkFile using EMPTYFILE does move the data to other files in the same filegroup. From BOL:
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
...
D. Emptying a file
The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.
Copy Code
USE AdventureWorks;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- Empty the data file.
DBCC SHRINKFILE ('Test1data', EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks
REMOVE FILE Test1data;
GO
Thanks. You are right DBCC ShrinkDatabase does not move data off a file to another file. If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. But anyway it gives me an error immediately. Microsoft declares that data file can be much more then 2TB, but I get error "MODIFY FILE failed. Size is greater than MAXSIZE" or something similar. Cheers.
August 15, 2008 at 8:53 am
GilaMonster (8/15/2008)
fixfox8 (8/15/2008)
I can not run right now that query, becaus I am running something else. Thanks.When you can please post the results. I have a suspicion I know what's wrong, but want to confirm.
I could manage to run it and this is the result:
master-14
mastlog-10
Thanks
August 15, 2008 at 8:56 am
GilaMonster (8/15/2008)
fixfox8 (8/15/2008)
I can not run right now that query, becaus I am running something else. Thanks.When you can please post the results. I have a suspicion I know what's wrong, but want to confirm.
The result:
master-14
mastlog-10
August 15, 2008 at 9:06 am
That's for the master database. Please run the query in the database that you are trying to shrink.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 9:14 am
GilaMonster (8/15/2008)
That's for the master database. Please run the query in the database that you are trying to shrink.
Sorry:
Sop -1 2367438
Sop_log128000001
Sop_11920000001000000
Sop_21920000001000000
Sop_31920000001000000
Sop_4192000000512000
August 15, 2008 at 9:23 am
Ok. Which one are you trying to shrink and what size are you trying to shrink it to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply