Database Size - Confusion 2

[Borvik]

Sql 2005

I'm a little confused when looking at how much space my database is using.

According to the management studio, when I right click on the database and go to properties, it reports the size at 256046.19 MB (250.04511 GB).

I used the following SQL to give me a complete list of all the table sizes in this database:

CREATE TABLE #tempSpace(
  [name] nvarchar(255), 
  [rows] char(11), 
  reserved varchar(18), 
  data varchar(18), 
  index_size varchar(18), 
  unused varchar(18) )

DECLARE @tableSql varchar(255)
DECLARE tableSpaceCur CURSOR FOR
  SELECT 'INSERT INTO #tempSpace exec sp_spaceused '+TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN tableSpaceCur
FETCH NEXT FROM tableSpaceCur INTO @tableSql
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  PRINT @tableSql
  EXEC(@tableSql)
  FETCH NEXT FROM tableSpaceCur INTO @tableSql
END
DEALLOCATE tableSpaceCur
SELECT * FROM #tempSpace
DROP TABLE #tempSpace

When I converted all the sizes to numbers (everything is in KB), I added everything up and used the following to calculate GB: x/1024/1024. This resulted in (leaving out the unused space column): 18.34009552 GB. I looked up any full text catalogs I had (there were two), and their sizes were 706 MB and 0 MB. Adding that into the total still leaves only around 20GB.

What is with the big difference 250 GB vs 20 GB?
Can anybody help me find the missing space?

 

[gmmastros]

How big is your log file?

sp_helpfile

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

If your log file is huge, you need to check your database recovery mode - is it in FULL RECOVERY or SIMPLE Recovery mode? If it is in FULL mode, you then need to check if you are doing Transaction Log backups. If your Log file is huge and your mode is FULL, you most likely aren't doing log backups.

There can be other reasons for large log files, but not doing log backups while in FULL mode is the most common.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

more reading regarding log files.

http://blogs.lessthandot.com/index....SQLServerAdmin/do-not-truncate-your-ldf-files

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Borvik]

Log file size is: 242803904 KB (>230 GB).

I read that blog - interesting about truncating the log files (don't do it).

However, the process that is so increasing the log files is an import into temporary (not really temporary as in #) tables, that are then renamed to the real tables. If the import process is interrupted we can just run it again and the real data was never touched.

Is there a way to disable logging on the database for the duration of the import, and then turn it on again when it's completed (or ready for the rename)?

 

[gmmastros]

I wouldn't recommend you mess around with disabling the logging operations because it would disable it for everything, not just one table.

Seems to me that if the data fits in 20 gigs and the log is 200 gigs, that you are not properly backing up your log file. My suggestion is:

Modify your backup procedure so that the log file is backed up. Then, shrink the log file to something reasonably small, but still has some wiggle room. The next time you import data, the log file may need to grow, which is ok. Then, when you backup your log file again, the space will be cleared and eventually the log file will end up at an appropriate size. It may be that the log file ends up at 10 gigs or so. If you regularly back up the log file, the space within the file will be cleared and it should stay at a reasonable size.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Borvik]

Oh, it's being backed up all right - Symantec backup exec keeps reporting larger sizes. But perhaps the Symantec backup isn't triggering the clearing of space - in which case how do I set that up in the management studio?

 

[Borvik]

I think I found it - I'll let you know if I didn't.

 

[gmmastros]

Right click your database.
click Tasks
Click Backup
In the backup type, choose "Transaction Log"

Choose other options that are right for you (for example, backup destination).
Click OK

In my opinion, it's best to NOT rely on 3rd party backups. Instead, you should use SQL Server to create backups of the databases, and then allow symantec to make a backup of the file created from SQL Server (a backup of the backup, just to get it off the machine).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Borvik]

Yea, just after I post - I find it (maybe I should just anticipate that to get some things done quicker).

Oh, we do both - SQL Server backups, and 3rd party backups which backup both (at least I think so).

 

[markros]

When you move to SQL Server 2008, you may find this blog about using compression option, quick useful

http://blogs.lessthandot.com/index....testing-backup-compression-in-sql-server-2008

 

[Borvik]

Thanks guys, I've setup the scheduled backup job to also do the log files (yes I was using FULL mode) - so it should keep it manageable.

Also managed to shrink it down for today and all seems to be ok.

Cheers.

 

[Borvik]

Interesting about the compression option for 2008 - very interesting. Thanks.