[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to reduce the size of  mdf,ndf file

Posted on 2006-05-04
10
Medium Priority
?
770 Views
Last Modified: 2008-01-09
hi

                In My database one primary Data File and two secondary data file
                DBAudit - Primary data file - it takes 4 MB
                 
                The secondary data file DBAudit
                 DBAudit_data_2.ndf  - it occupy 1.GB
                 DBAudit_data_3,ndf    it occupy 1.GB

I'm running now out of space

how rduce the above data file size
please give suggestion for me very urgent

urs

mk_mur
0
Comment
Question by:mk_mur
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16605254
USE

DBCC SHRINKFILE (filename, value)
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 375 total points
ID: 16605262
Shrinks the size of the specified data file or log file for the related database.

Syntax
DBCC SHRINKFILE
    ( { file_name | file_id }
        { [ , target_size ]
            | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
        }
    )
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16605273
note that shrinkfile will only work if there is free space inside the files...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mk_mur
ID: 16605531
hi
          any other method  for reduce and move the data file

thanks
mk_mur
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16605588
dbcc shrinkdatabase
0
 
LVL 25

Expert Comment

by:SStory
ID: 16606719
not without archiving the historical data to another database.
0
 
LVL 4

Expert Comment

by:Sowmya_K
ID: 16614546
You can shrink databases using Enterprise Manager
here are the steps:
1. Expand a server group, and then expand a server.
2. Expand Databases, right-click the database to shrink, point to All Tasks, and then click Shrink Database.
3. To specify how much to shrink the database, select from these options:
For Maximum free space in files after shrinking, enter the amount of free space you want left in the database after shrinking. Use the Database Size, Space free value as a guideline.
4. Select Move pages to beginning of file before shrinking to cause the freed file space to be retained in the database files, and pages containing data to be moved to the beginning of the database files.
5.Click Schedule to create or change the frequency or time when the database is automatically shrunk.
6.Click Shrink files if you want to shrink individual database files.

Other option is to use the " Shrink" method
The Shrink method attempts to reduce the size of a referenced operating system file, or attempts to reduce the size of all operating system files maintaining the referenced Microsoft SQL Server 2000 database.
syntax:  object.Shrink( NewSize , Truncate )
see this link for more information on shrink http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_4eqs.asp







0
 
LVL 4

Expert Comment

by:Sowmya_K
ID: 16614597
u can also set the automatic shrinking option by using sp_dboption

sp_dboption [ [ @dbname = ] 'database' ]
    [ , [ @optname = ] 'option_name' ]
    [ , [ @optvalue = ] 'value' ]
use the autoshrink option: When true, the database files are candidates for automatic periodic shrinking.  
0
 
LVL 14

Expert Comment

by:profya
ID: 16651519
You can use either Enterprise manager or T-SQL to shrink the database. But before doing this you need to change database recovery model to Simple. You can do that via Enterprise Manager/<Target Database>/Properties/Options. Then you can revert to the recovery model you were using (Full for example).
You can make a full database backup before doing database shrinking.

0
 
LVL 14

Expert Comment

by:profya
ID: 16729409
Where are you mk_mur. Please feed me back about my post.
Of course you can move the database after reducing its size using attach/detach.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question