March 16, 2009 at 10:57 am
Hi
I have a database , created before I got this job an these days I've made a backup, and the backup size is 22 Gb. When I archived the backup, the archive size in 3 Gb. Is it normal to be such a big difference?
My first guess is that the indexes are fragmented and old and...who knows what...
Is my guess a right one? If not where should I take a look to see what might be the problem ? because the database is prety big and I wanted to see if there are many nvarchar columns(second guess) and I don't think that's a good start.
I'm waiting any sugestion
Thanks
March 16, 2009 at 11:32 am
Hi
The size difference between the backup and the archive is okay. The SQL Server backup files are not archived and many information are text/int so the compression is quiet good. (In SQL Server 2008 it will be possible to create archived backups.).
The question if NVARCHAR fields are a good decision or not depends on its content.
Greets
Flo
March 17, 2009 at 2:23 am
Ok
Thanks for the reply and another question.
If the difference is biger ,backup 20 Gb and the archive 500 Mb, is this still normal?
I was thinking to search the character columns and see if there is wasted space,char(n) - too big n, though as far as I know this happens only in char, and not on varchar, where the ocupied space is only the size of the string. I hope I will find smth here. I accept any other suggestion...:D
March 17, 2009 at 2:31 am
CHAR data type is not needed in most cases. (I don't even know any reason - if anybody knows please tell me).
The difference between 20gb and 500mb is very huge. This may depend on the CHAR data types but its also possible that you may shrink your database (especially the log file).
Right click on the database -> "Standard Reports" -> "Disk usage". If there is a huge space is marked as "unused" you can shrink your database:
DBCC SHRINKDATABASE(YourDbName)
Greets
Flo
March 17, 2009 at 3:15 am
I'm a bit stupid but I have some difficulties. To find Standard Reports I have to conect to Reporting Services? Because I connect to Database Engine, and I don't seem to find them:D, and , I don't know why,I cannot conect to Reporting Services...
Sorry for the stupid question...
March 17, 2009 at 4:05 am
Which database version and edition are you using?
If you are not sure go to a SQL window and execute:
PRINT @@VERSION
Greets
Flo
March 17, 2009 at 4:14 am
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
March 17, 2009 at 4:19 am
Quiet strange... This reports should be available on each database node (not on server or tables)...
If you can't find it execute this:
execute sp_spaceused
This will also show you the database size and the unallocated space.
Greets
Flo
March 17, 2009 at 4:26 am
Ok man
Thanks for all your advices
A last question : the shrinkdb has any side effects?
March 17, 2009 at 4:31 am
You should use SHRINKDATABASE instead of SHRINKDB.
Side effects:
* Because the database usually becomes shrinked to minimum size it will become slow for the next file extend within the next some statements. After that extend all should work fine again (no further performance issues)
* This might lock the database since execution. You should only execute within a maintenance window (especially on huge databases)
If only the transaction log is too big you might have a look to "DBCC SHRINKFILE". For the file name use:
SELECT * FROM sys.database_files
Have a nice day 🙂
Flo
March 17, 2009 at 4:36 am
Thanks again man
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply