June 27, 2012 at 9:13 am
Hi,
I have a Database of 6 GB in simple mode. the ldf file is 20MB. There is 1% free space.
When I backup the database the size op the .bak file is 130 GB.
There is only one backup in the file. Confirmed with "Restore headeronly from disk = 'DB.bak'.
Please advice what can cause the large backup file.
Thanks
June 27, 2012 at 9:21 am
If you backup to a new backup device or new file, do you still get 130GB in the backup file?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 12:36 pm
Yes, it is still the same size
June 27, 2012 at 12:57 pm
You absolutely sure there's only one data file of 6GB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2012 at 1:04 pm
Will you post the results of this query
Declare @DBName varchar(128)
Set @DBName = 'YourDBHere'
Select db_name(database_id) as DBName,name as LogicalName,physical_name,type_desc as FileType
,Size*8/1024 as FileSizeMB
From sys.master_files
Where db_name(database_id) = @DBName
Just replace YourDBHere with the name of the database in question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 2:18 pm
Thank you to look in to this problem.
Here you can find the results
pleinplus_restylePleinplusS:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pleinplus_restyle.mdfROWS6428
pleinplus_restylePleinplus_logT:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\pleinplus_restyle_log.ldfLOG19
pleinplus_restylesysft_zoektekstS:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\zoektekstplepl_restyleFULLTEXT0
June 27, 2012 at 2:41 pm
Try this query for the backup history and post the results
DECLARE @DBName varchar(128)
SET @DBName = 'YourDBNameHere'
Select a.database_name,a.backup_start_date
,BackupPath = b.physical_device_name
,BackupSizeMB = a.backup_size/1024/1024
,CompressedBackMB = a.compressed_backup_size/1024/1024
From msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
Where a.type = 'D'
And a.backup_start_date > GETDATE()-7
And b.physical_device_name not like '{%'
AND a.database_name = @DBName
Order By a.database_name,a.backup_start_date
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 3:42 pm
This may be why...
pleinplus_restyle sysft_zoektekst S:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\zoektekstplepl_restyle FULLTEXT 0
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2012 at 1:07 am
I removed ",CompressedBackMB = a.compressed_backup_size/1024/1024"
because of the following message:
"
Msg 207, Level 16, State 1, Line 7
Invalid column name 'compressed_backup_size'.
"
pleinplus_restyle2012-06-22 03:27:51.000\\10.10.10.10\dbdump\pleinplus_restyle_backup_201206220134.bak129690.64794921875
pleinplus_restyle2012-06-23 03:26:16.000\\10.10.10.10\dbdump\pleinplus_restyle_backup_201206230135.bak129709.89697265625
pleinplus_restyle2012-06-24 02:37:54.000\\10.10.10.10\dbdump\pleinplus_restyle_backup_201206240134.bak129709.32763671875
pleinplus_restyle2012-06-25 03:23:31.000\\10.10.10.10\dbdump\pleinplus_restyle_backup_201206250135.bak129710.51611328125
pleinplus_restyle2012-06-26 03:27:40.000\\10.10.10.10\dbdump\pleinplus_restyle_backup_201206260136.bak129716.01806640625
pleinplus_restyle2012-06-27 03:26:03.000\\10.10.10.10\dbdump\pleinplus_restyle_backup_201206270136.bak129721.14794921875
June 28, 2012 at 7:56 am
What would be the problem with "FULLTEXT 0"
June 28, 2012 at 10:03 am
It appears you have fulltext enabled. That will add space to your database. Check the filesystem where the fulltext is stored and see how large it is.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 28, 2012 at 2:02 pm
In reply to a PM - check this directory where your fulltext is being stored. Check the size in that directory.
S:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\zoektekstplepl_restyle
Fulltext will add to the size of your backup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 28, 2012 at 2:07 pm
Hi,
Sorry, I didn't saw that there was already a next page :doze:
The full text directory is 120 GB. Found it 🙂
Can we shrink these files or are there other solutions ?
Thanks
June 29, 2012 at 6:45 am
Is it possible to take a backup without the index ?
June 29, 2012 at 7:25 am
vrsdesign (6/28/2012)
What would be the problem with "FULLTEXT 0"
besides the fact it is all hidden now inside SLQ server so you got to play CSI to figure em out, they resouce govern like crap & they just adds complex ambiguity that seldom is even used correctly by tardoDev monkeys wanting to jack up their resume's.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply