Shrink Database Files

SQL Allow you to shrink data and log file. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.
We will discussed here some ways to shrink database files.

Before we move to shrink file using queries, first we need to get names of files which are attached to the database.
Below is the query which is used to retrieve logical name of mdf and ldf files.

Get Name of mdf and lfd file

Syntax:

USE Database_Name
GO
EXEC sp_helpfile
GO


Shrink database file using queries

By using this query you will be find name of files. No we will move to use this file name into the query to shrink those files.

Syntax:

Use Database_Name
DBCC SHRINKFILE (FileNameToShrink' , 1)

SHRINKFILE requires two parameter. First is the name of file to shrink and other is size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Shrink database files through management studio

  1. Log in to the Microsoft SQL 2000 Server as an administrator.
  2. Open SQL Enterprise Manager.
  3. Right-click the database that VirtualCenter is using.
  4. Click Properties.
  5. Click the Options Tab.
  6. Set the Recovery Model to Simple as follows:

  1. Click OK.
  2. Right-click on the database again.
  3. Click All Tasks ->Shrink Database.
  4. On the Shrink Database window, click Files.
  5. Select the transaction log from the database file list. This appears in the list as databasename_Log as follows:
The space used versus the space allocated displays. In general after you set the recovery model to Simple you see the majority of the space in the transaction log released.
  1. Ensure that the Compress pages and then truncate free space from end of file radio button is selected. 
  2. Click OK to shrink the log.
  3. Click OK, to perform a shrink the database, and close the wizard. 
Space is now freed on the server.  If there is more than one transaction log for the database, perform the above steps for each of the logs.


Comments

Post a Comment

Popular posts from this blog

Connecting Remote server with Local Server

Image
Introduction: Linked server is powerful functionality provided by SQL Server. We can create object of remote server on local server and use that object to perform operations on remote server. To explain this topic let's consider a scenario Scenario:   Local server having number of databases. We need to take backup of local databases and restored this database into the remote server. For this scenario we will follow certain steps: Step 1 : Create object of Remote Server Add remote server object in local server Syntax:     sp_addlinkedserver @server = N'LinkServer' ,     @srvproduct = N' ' ,     @provider = N'SQLNCLI' ,     @datasrc = N'Server Name ,     @catalog = N'Database Name' Here we have created object of remote server i.e. LinkServer . By using this object you can perform Select, Update functionality on the same. Step 2 : Grant Permission Next Step...
Read more

MDX Error handling tricks

Error List: Duplication of column value(column is not primary key): This issue occur due to NULL records. You have to handle NULL records for particular column. Below are the some properties which you need to set for the attribute: On dim property set: UnknownMember = Visible On attribute property:Key column Expand -> Null Processing-> handle in your way.
Read more

JSON_Automated_stored_procedure

Image
What is JSON: JSON stands for  J ava S cript  O bject  N otation, and is a way to store information in an organized, easy-to-access manner, "self-describing" and easy to understand. It gives us a human-readable collection of data that we can access in a really logical manner. Why we need JSON: JSON is a lightweight data-interchange format and it is language independent. Many sites are sharing data using JSON in addition to RSS feeds nowadays, and with good reason: JSON feeds can be loaded asynchronously much more easily than XML/RSS. Format of JSON string: JSON Example { "employees" :[     { "firstName" : "John" ,   "lastName" : "Doe" },     { "firstName" : "Anna" ,   "lastName" : "Smith" },     { "firstName" : "Peter" ,   "lastName" : "Jones" }             ] } You can compa...
Read more