We help IT Professionals succeed at work.

DB file size keep increase but not much data in(SQL2005)

turbot_yu
turbot_yu used Ask the Experts™
on
The database file is about 1G, after backup about 600M.

But there is not much data in it. Is there any unuseful info in it?

I may want to clean the DB.

BTW, it has been backuped and restored for many times.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Run this script:

create table #temp (table_name varchar(100),record_count int);

insert into #temp
exec sp_msforeachtable 'select ''?'', count(*) from ? '

select * from #temp

It would list the table with records count and find whether you have valid data in those tables are not and delete accordingly..

Author

Commented:
All the tables not have much records.

Is it possible too many logs there, thanks.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
It depends based upon the column size or record size of your table..
If you tables have columns with datatypes such as varchar(max), nvarchar(max) or any columns with larger size can occupy or contribute to your database size

And delete records from table as required..

Author

Commented:
The max size table only have several hundreds records.

I defined all the tables and columns. Though there are some columns in varchar(max)...

But the whole DB file should not be so big.

Any suggestion, thanks.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> But the whole DB file should not be so big.

Ok, Should have asked you more clearly..
Do you mean your mdf and ldf file sizes or your backup file..

Author

Commented:
Both the mdf and ldf, also the backup file.

All the data should not beyond 100M.

The database file has been backuped, copied and restored on different computer for many times.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> The database file has been backuped, copied and restored on different computer for many times.

That's not a problem..
Having a database around 1GB is normal and need not worry about if you have disk space limitations..
Else you can ignore that..

Another option would be to SHRINK your MDF and LDF file sizes, but I would not recommend it since database would grow to a higher value again once it is operational..

Author

Commented:
It is need to transfer over internet, so smaller the better.

Also, it just need to keep some basic data in it, I need to clean it up as new one. All the other info are no use.

Commented:
See ho much unused space you have - right click on the DB -> Tasks -> Shrink -> Files .
If you want - shrink the log and or data files.
To prevent the Transaction Log from growing - perform schedueled Transaction Log backups ( I run one every hour or so on one of my DBs)

Author

Commented:
May I know the Shrink delete the unuseful info or just compress them. It may need to clean it.

If perform schedueled Transaction log backups, is all the log still there or some be dropped. Just want to learn how it make the log from growing, thanks.
Commented:
I do not compress them , I just delete .

As for the transaction log -
While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.

If you want to restore the database you will need the Daily full backup and the hourly tranaction log backups since .
After my database daily Full backup - I delete the previos transaction log backups - they are not needed any more.  

.
For the hourly transaction log backups I prepared a Maintainance task
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> May I know the Shrink delete the unuseful info or just compress them. It may need to clean it.

Even if you shrink Database Files, it would again grow and hence won't recommend to Shrink and Compress it.
Deleting records from the tables is the recommended step to do now..

Author

Commented:
Actually after I mount the DB into a new PC, I want to clean all the data and logs, just leave some basic data in it. The data size should be very small.

I tried to use BACKUP LOG xx TO DISK = 'c:\xx\BackupLog', but the BackupLog is only 100k. Is there any other way to backup the log.

I also tried to shrink the files, but there is no change on the actual file size, though it said about 10% free space for the database and log file.

I learned from some web that the DB size cannot smaller than its original size. After I mount the DB, I check the DB file and log file sizes is about 700M and 1G. It may because I backup, copied and restore it for many many times.

Any suggestion, thanks.
Narender GakkaAWS / DevOps / Cloud Consultant

Commented:

Commented:
If your required data from the "old" database is relatively small and not too many rows - try preparing an SQL scrupt from the database and choose the option to script the data from the tables you need.
This will give you a clean and thin copy of the required data with the design you need .
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> but the BackupLog is only 100k. Is there any other way to backup the log.

No need to take Log backup for your objective..
Just taking a full backup, restoring it in your new server and deleting records would do.
But you need to decide upon which records to delete.
Try this Script : --INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7  http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7  http://www.support.microsoft.com/kb/256650  
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles

-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT


-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***

SELECT  @LogicalFileName = '<Databse_LOG>',  -- Use sp_helpfile to identify the logical file name that you want to shrink.
        @MaxMinutes = 10,               -- Limit on time allowed to wrap log.
        @NewSize = 305                  -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
  FROM sysfiles
  WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
  FROM sysfiles
  WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
  (DummyColumn char (8000) not null)


-- Wrap log and truncate it.
DECLARE @Counter   INT,
        @StartTime DATETIME,
        @TruncLog  VARCHAR(255)
SELECT  @StartTime = GETDATE(),
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- the log has not shrunk    
      AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
  BEGIN -- Outer loop.
    SELECT @Counter = 0
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
      BEGIN -- update
        INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
        DELETE DummyTrans
        SELECT @Counter = @Counter + 1
      END   -- update
    EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
  END   -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
  FROM sysfiles
  WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF

Author

Commented:
I tried to set
SELECT  @LogicalFileName = 'C:\SC\sc_1.ldf',

But it said
In sys.database_files cannot find database 'sc' file 'C:\SC\sc_1.ldf'. the file does not exist or been deleted.
Top Expert 2012

Commented:
>>the file does not exist or been deleted.<<
You need to read up on the difference between logical and physical filename.  They are not the same.

Author

Commented:
Thanks, after I change to SC, now it works.

After run it, the DB file got 100M smaller.

Now I change the time to 60 and still running. Is it 60mins or 60 seconds.

Author

Commented:
After it run over for a long time, the DB and log file size does not change.

So I searched and use the command below. It make the log file size to 1M.
   Use xx
   go
   Checkpoint

   DBCC SHRINKFILE('xx_log', 1)
   BACKUP LOG sc WITH TRUNCATE_ONLY DBCC SHRINKFILE('xx_log', 1)

But the DB file size still 600M, after I make a DB backup, the backup file size is still 600M.

Any suggestion?
Top Expert 2012

Commented:
>>But the DB file size still 600M, after I make a DB backup, the backup file size is still 600M.<<
That is because you did this:
DBCC SHRINKFILE('xx_log', 1)

Which is the Transaction Log and not the Data file.  Perhaps if you did the same for the Data file you may be lucky.

Incidentally doing DBCC SHRINKFILE on either data or Transaction Log is a very bad idea.  But whatever works for you ...

Author

Commented:
I tried to use the same for the DB as below.
   Use sc
   go
   Checkpoint

   DBCC SHRINKFILE('SC', 1)
   BACKUP LOG sc WITH TRUNCATE_ONLY DBCC SHRINKFILE('SC', 1)

But when run to the last SQL, it give error as
DBCC SHRINKFILE: File ID 1 of database ID 6 was skipped because the file size was changed in the middle of shrink operation.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

May you give any suggestion, thanks.
Top Expert 2012

Commented:
>>BACKUP LOG sc WITH TRUNCATE_ONLY DBCC SHRINKFILE('SC', 1)<<
Why are you doing this?  I thought we had got beyond shrinking the Transaction Log and we were focusing on the Data file?

Perhaps it is time to leave well alone or contract a reliable and dependable DBA...