Database Size Issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Size Issues

Author

Topic

WillG
Starting Member

3 Posts

Posted - 2012-08-15 : 08:35:10

Hi

We had a problem where one of our servers crashed and our database was restored from a .bak file.

Since the restore the Database size has been increasing at a much higher rate than it was before the restore.

I have looked at the size of database using: sp_spaceused and the result was:

Reserved: 13260512 KB
Data: 3621616 KB
Index_size: 571096 KB
Unused: 9067800 KB

As you can see most of the size of the database is from unused space.

The indexes are set to rebuild every week and the free space per page percentage is set to 10%.

Also every night as a part of the Maintenance plans the option to 'Remove unused space from database files' is run with the options, Shrink Database when it grows beyond 50MB and set to 10% space to remain after Shrink.

How can i get rid of the unused space to bring the database size down as we are rapidly running our of disk space due to the backups etc.

Any help greatly appreciated.

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-15 : 21:59:15

Shrink database and reorganize file

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-16 : 00:39:39

On Maintenance Plan task "Shrink Database Task", there is an option as following, choose option 2 to release unused space.
1. Retain freed space in database files
2. Return freed space to operating system

Let us know if it done and does not reduce in database size.
Or try following command and compare the result.

DBCC SHRINKDATABASE(N'YourDatabaseName', 10 )

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

WillG
Starting Member

3 Posts

Posted - 2012-08-16 : 03:48:24

Hi Komkrit

Thanks for your reply. It in SQL 2000 (should have added that sorry) so the Shrink options are just:

Remove unused space from database files - Which is ticked with the following sub options:

Shrink database when it grown beyond: 50MB
Amount of free space to remain after shrink: 10%

I cant try the DBCC SHRINKDATABASE(N'YourDatabaseName', 10 ) until later on this evening when the server is not being used but i will try that and let you know the result. Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-16 : 15:34:28

It is important to understand why it is growing. Is temporary data being written then deleted? This implies that the space is required. In those circumstances , you could be creating more of a problem.
On the other hand , if in analysing you see that the unused space is never used - for example - it was a one-off delete, then issuing a SHRINKDATABASE is good value

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

WillG
Starting Member

3 Posts

Posted - 2012-08-17 : 05:01:42

Hi

Komkrit i tried what you suggested running DBCC SHRINKDATABASE(N'YourDatabaseName', 10 ) however this had no effect on the unused space.

Jack - Thanks for your reply, how would i go about analysing if the space is getting used or not? Are then any specific commands to run? (Sorry i am new to SQL administration, kind of got lumbered with when other staff left).

Cheers
G

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-17 : 09:18:07

A few different options 1) you could monitor the DMLs executed against the db - particuarly focusing on INSERT and DELETE statements , which are most likely linked to increased | decreased usage. Also , for any ETL jobs that might have BULK jobs. Typical places you coul look include : SQL Server Agent and SSIS
2) You could SHRINK down the db and see how much it grows

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-18 : 00:53:22

To G,

Thanks for information.

In my case, I have ever seen a case of database unused space could not be shrinked.
It is from a database have number of inconsistence allocation unit.
Check by running this command(be awared, it takes long time, let it run until completely)
------------
USE <yourdatabasename>
DBCC CHECKDB WITH TABLOCK
------------

If any error found(red text), disconnect all sessions and put database to single-user mode.
Try this command

------------------
DBCC CHECKDB (<yourdatabasename>, REPAIR_ALLOW_DATA_LOSS)
------------------

Have a nice day
Komkrit

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-18 : 01:04:26

To G,

Refer to msdn website --> http://msdn.microsoft.com/en-us/library/ms190488.aspx
It just be cleary that we have to run this command sequentially to
- re-organize page
- and release unused data

DBCC SHRINKDATABASE (<yourdatabasename>, NOTRUNCATE)
DBCC SHRINKDATABASE (<yourdatabasename>, TRUNCATEONLY)

Good Luck

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources