Increased Backup size for unknown reason

Does any one know what could be making the size of a backup get so big in SQL server?

I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.

Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures... Now all of a sudden my database backup is 7 times larger.

What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?

What is the log file about? Mine is huge? Is there a way to reset it or clear it?

any help would be great.

Thank you,

AlecIf you're sure you're not making any changes are you adding/appending to the backup rather than overwriting?

Incorrect syntax near the keyword in.

Hi

i want to copy tables from sql to access
i gave

SELECT Persons.* INTO Persons IN 'Backup.mdb'FROM Persons
 The error is Incorrect syntax near the keyword 'in'
any one reply me

I think we are going to need more information, but I am pretty sure you could use OPENDATASOURCE for this:
INSERT INTO
OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','DataSource="c:\test.mdb"; User ID=Admin;Password=' )...Persons(column1,column2)
SELECT * FROM Persons
(seehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9334 for reference)

Incorrect syntax for backup

Hi All,
I have the following issue.
I have upgraded MS SQL Server 6.5 to 7.0.
I also have SQL Server 2000.
I have registered the SQL 7.0 server in the SQL 2000 server group.
When I run the following backup command in the query analyzer, that
runs ok with all the databases in SQL 2000 but not in the SQL 7.0.
Backup database abc to disk='\\NTDR1\DBBack\abc.dat' WITH INIT
I get the following error
Incorrect syntax near the keyword 'database'.
I have checked the permission and I am loggin as sa.
Could anyone please help me why I am unable to run the above command
for the database upgraded from SQL 6.5 to SQL 7.0, whereas the same
command works fine for SQL 2000.
I do appreciate your help.
Thanks a million in advance.
Best regards,
mamunRun this
Exec sp_dbcmptlevel 'DB NAME'
Does it say 65 ?
If it does change it to
Exec sp_dbcmptlevel 'DB NAME',70
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0310071109.53289600@.posting.google.com...
> Hi All,
> I have the following issue.
> I have upgraded MS SQL Server 6.5 to 7.0.
> I also have SQL Server 2000.
> I have registered the SQL 7.0 server in the SQL 2000 server group.
> When I run the following backup command in the query analyzer, that
> runs ok with all the databases in SQL 2000 but not in the SQL 7.0.
> Backup database abc to disk='\\NTDR1\DBBack\abc.dat' WITH INIT
> I get the following error
> Incorrect syntax near the keyword 'database'.
> I have checked the permission and I am loggin as sa.
>
> Could anyone please help me why I am unable to run the above command
> for the database upgraded from SQL 6.5 to SQL 7.0, whereas the same
> command works fine for SQL 2000.
>
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0310071109.53289600@.posting.google.com...
> I have the following issue.
> I have upgraded MS SQL Server 6.5 to 7.0.
> I also have SQL Server 2000.
> I have registered the SQL 7.0 server in the SQL 2000 server group.
> When I run the following backup command in the query analyzer, that
> runs ok with all the databases in SQL 2000 but not in the SQL 7.0.
> Backup database abc to disk='\\NTDR1\DBBack\abc.dat' WITH INIT
> I get the following error
> Incorrect syntax near the keyword 'database'.
> I have checked the permission and I am loggin as sa.
>
> Could anyone please help me why I am unable to run the above command
> for the database upgraded from SQL 6.5 to SQL 7.0, whereas the same
> command works fine for SQL 2000.
>
The MSSQLServer service must be running under the context of a service
account that has update permissions on your target UNC.
http://support.microsoft.com/default.aspx?scid=kb;en-us;207187&Product=sql2k
Steve
Steve

inconsistent tran log

I have this problem with:
I made komplet backup of database before reinstalation SQL server. After that reinstalation a made backup of tran log..
Now I'm not able to load the tran log on the komplet database.
Server returns this mesasage:
The log in this backup set cannot be aplied beacuse it is on a recovery path inconsistent with the database.

Please, dont you someone know, how could I repair this...
Thanks for any advice..If you already RESTORE the database with RECOVERY option then you can't apply/load any transaction log after that. You must use the NORECOVERY option when restoring the DB, then applying the transaction log (TLOG) - one by one - until the last TLOG. On the last restore of TLOG, you use the RECOVERY option to open the database for user connection.|||Originally posted by SVT
If you already RESTORE the database with RECOVERY option then you can't apply/load any transaction log after that. You must use the NORECOVERY option when restoring the DB, then applying the transaction log (TLOG) - one by one - until the last TLOG. On the last restore of TLOG, you use the RECOVERY option to open the database for user connection.

Yes I know, I did it by this way...
I have new information about that pls read the topic "restore database" posting by me...

thanks

Inconsistent SP performance on different Servers

One of my developers recently installed a backup of the production database onto his test site. His test server has the same configuration as the production server.

One of the Stored Procedures that is called takes 1:45 to run on his machine, but only 2 seconds on the production server. This same SP takes only 2 seconds on my development database.

The SP is called iteratively, up to 10 times... to run against 10 separate fields. Depending on a value for a parameter called @.CriteriaClassID, depends on which portion of the SP runs.

The significant difference in processing time in itself is baffling (since the servers are same specs / configuration, as far as I can tell, and the data is identical, since he has a backup of the most recent production data).

But more baffling: if, in his data, I switch the values from field 1 to field 2, and vice versa, his results take 2 seconds (switching the values in field 1 to field 2 switches the value in @.CriteriaClassID which is passed through to this SP).

It's exactly the same SP; the only difference is that field 1 is processed first, field 2 second, field 3 third etc. On the production site and my development site, it doesn't make a difference in the order they are processed. On his machine it does.

Any ideas? I though perhaps his Indexes were corrupted in the rebuild, but we ran a SQL Server maintenance schedule to clean it up, and no improvement.

This is the SP, if it is of any help:

CREATE procedure [dbo].[st_pull_model_data] @.ModelID as integer, @.CriteriaID as integer
as

declare @.ClientID as integer, @.CriteriaClassId as char(1)

/*Procedure to pull data from org_model_data and postalcode_model_data for modeling and media analysis */
/*Need to have table #temp_data created outside of SP with fields org_id and zip_code */
/*This procedure is used by SP st_model_data */

If @.CriteriaID is not null
begin

set @.CriteriaClassId = (Select model_criteria_type from model_criteria where model_criteria_id = @.CriteriaID)
if @.CriteriaClassID = 'G' -- changes client_id from specific to general, if General is required.
begin
set @.ClientID = 0
end
else
begin
set @.ClientID = (Select client_id from model where model_id = @.ModelID)
end

If @.CriteriaClassId in ('G','P')
Begin
update #temp_data
set data1 = postal_criteria_value
from #temp_data t
left outer join
(select postalcode, postal_criteria_value
from postalcode_model_data pmd
join model_org_trade_area mota on mota.zip_code = pmd.postalcode
join model_org mo on mo.model_org_id = mota.model_org_id
where model_criteria_id = @.CriteriaID
and client_id = @.ClientID
and mo.model_id = @.ModelID) as PMD
on PMD.postalcode = t.zip_code
end
else
Begin
update #temp_data
set data1 = org_criteria_value
from #temp_data t
left outer join
(select distinct postalcode, org_criteria_value, omd.org_id
from org_model_data omd
join org o on o.org_id = omd.org_id
join model_org_trade_area mota on mota.zip_code = omd.postalcode
join model_org mo on mo.model_org_id = mota.model_org_id and mo.org_id = o.org_id
where model_criteria_id = @.CriteriaID and o.client_id = @.ClientID and mo.model_id = @.ModelID) as OMD
on OMD.postalcode = t.zip_code and omd.org_id = t.org_id
end
endJust a thought on something to try...

Declare two local variables that are similar to the two passed in parameters, copy the parameters to these local variables at the top of your sporc and then use the local variables only within the code.

If this has the effect of solving your issue, you have a "parameter sniffing" issue.|||One other thought, is his tempdb on the same physical disk as the database data files? It won't explain the whole performance problem, but it will explain a part.|||Chopin,

Thanks a ton. The inclusion of the two local variables (copying over the SP variables) solved the problem for performance.

Another lesson learned.|||In that case does that mean backup restore do not restore query plans for the stored proc in the database? Any idea?|||NO ... backup does not save the procedure cache.

include .mdf, .ndf, and .ldf files in exclusion list

Hi,
In the past I have always included .mdf, .ndf, and .ldf files in the
exclusion list for
both Antivirus and backup agents.
This message is primarily in regards to an open file backup agent.
I now work at a place that has an open file back agent.
Prior to my arriving the previous dba sucessfully tested a backup to tape
and restore using this agent.
Then DR plan was written to make use of this paradigm.
Fast forward 6 months
New product in production, many open file errors on .mdf, .ndf, and .ldf
files causing entrire backup streams to be delayed and eventually failing.
Immediately resolved when file extensions are added to exlusion list.
Does my old school thought process (exclude the files) lack a basis in the
new landscape involving SQL 2K5 and new backup agents?
Can anyone point to a MS document that recommends this as best practise?
I have seen Microsoft KB article 309422 regarding anit virus files.
Any input apprciated.
Hi Joe
I would exclude the files from any filter agent as this can lead to
performance problems, therefore it would be true regardless of SQL server
version. You will need to look at the SLA and find out what level of recovery
is required and how that could be met.
John
"Joe" wrote:

> Hi,
> In the past I have always included .mdf, .ndf, and .ldf files in the
> exclusion list for
> both Antivirus and backup agents.
> This message is primarily in regards to an open file backup agent.
> I now work at a place that has an open file back agent.
> Prior to my arriving the previous dba sucessfully tested a backup to tape
> and restore using this agent.
> Then DR plan was written to make use of this paradigm.
> Fast forward 6 months
> New product in production, many open file errors on .mdf, .ndf, and .ldf
> files causing entrire backup streams to be delayed and eventually failing.
> Immediately resolved when file extensions are added to exlusion list.
> Does my old school thought process (exclude the files) lack a basis in the
> new landscape involving SQL 2K5 and new backup agents?
> Can anyone point to a MS document that recommends this as best practise?
> I have seen Microsoft KB article 309422 regarding anit virus files.
> Any input apprciated.

include .mdf, .ndf, and .ldf files in exclusion list

Hi,
In the past I have always included .mdf, .ndf, and .ldf files in the
exclusion list for
both Antivirus and backup agents.
This message is primarily in regards to an open file backup agent.
I now work at a place that has an open file back agent.
Prior to my arriving the previous dba sucessfully tested a backup to tape
and restore using this agent.
Then DR plan was written to make use of this paradigm.
Fast forward 6 months
New product in production, many open file errors on .mdf, .ndf, and .ldf
files causing entrire backup streams to be delayed and eventually failing.
Immediately resolved when file extensions are added to exlusion list.
Does my old school thought process (exclude the files) lack a basis in the
new landscape involving SQL 2K5 and new backup agents?
Can anyone point to a MS document that recommends this as best practise?
I have seen Microsoft KB article 309422 regarding anit virus files.
Any input apprciated.Hi Joe
I would exclude the files from any filter agent as this can lead to
performance problems, therefore it would be true regardless of SQL server
version. You will need to look at the SLA and find out what level of recover
y
is required and how that could be met.
John
"Joe" wrote:

> Hi,
> In the past I have always included .mdf, .ndf, and .ldf files in the
> exclusion list for
> both Antivirus and backup agents.
> This message is primarily in regards to an open file backup agent.
> I now work at a place that has an open file back agent.
> Prior to my arriving the previous dba sucessfully tested a backup to tape
> and restore using this agent.
> Then DR plan was written to make use of this paradigm.
> Fast forward 6 months
> New product in production, many open file errors on .mdf, .ndf, and .ldf
> files causing entrire backup streams to be delayed and eventually failing.
> Immediately resolved when file extensions are added to exlusion list.
> Does my old school thought process (exclude the files) lack a basis in the
> new landscape involving SQL 2K5 and new backup agents?
> Can anyone point to a MS document that recommends this as best practise?
> I have seen Microsoft KB article 309422 regarding anit virus files.
> Any input apprciated.