Remote DBA Services: Database Shrinking
It is a well-known fact that sooner or later each business dependent on IT-infrastructure faces with “Low disk memory” problem. There are plenty of ways to clean the disk’s space, using standard OS services. But neither of them can be used for cleaning or shrinking data on SQL Server. The problem can stuck the overall business processes though and lead to unplanned delay and high costs.
In this article we will show how to shrink a database using standard SQL Server options.
First of all it is needed to detect what exactly allocates the space and how much memory is used. The most popular way is performing a sql-command: execsp_helpdbmydb This command will return the following parameters: files names, files groups, size, etc.
Example:
.png)
Another useful command:
exec master.sys.sp_MSForEachDB 'exec ?.sys.sp_spaceused'
This command will return the following parameters for each db: database_name, database_size, unallocated space.If GUI using is preferable, analyze the disk usage is MS SQL Server Management Studio using standard reports. To invoke the report, the database should be selected, then Reports->Standard reports->Disk usage.
After that a database backup should be created.
Once the backup is done, use the following command:
-- USE mydbGODBCC SHRINKDATABASE(N'mydb' )GO
The command will shrink the size of the data and log files in the specified database.
Unused indexes can also cause the problem. The best way to see the allocated memory for indexes storing is sp_indexes procedure.
Example:
This example returns all index information from the Employees table of the Victoria database on the MELBOURNE database server.
EXEC sp_indexes @table_server = ‘MELBOURNE’,@table_name = 'Employees',@table_catalog = 'Victoria',@is_unique = 0
Drop all the unused indexes after the very careful analyze.
To sum up, the general methodology is following:
- Analyze the physical and logical model base
- Check the distribution of physical objects for files
- Investigate the growth of files
- Identify unused space, and make a prediction of growth
Next:
- Make a decision of troubleshooting and cleaning of old unnecessary data
- After that rectify the issue, clean the data and shrink the files if free space in these files cannot be used by the server
Please note, that this task is very risky and we strongly recommend to use order DB Administrator’s services for this purpose.
Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.
MoreSubject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:
MoreIn our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.
MoreSubscribe to our blog
Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.
Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:
Discuss your project with us