mdf,ldf and bak file size increases day by day.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

mdf,ldf and bak file size increases day by day.

Author

Topic

shilpi
Starting Member

7 Posts

Posted - 2008-08-12 : 04:49:15

I had a database on client side where 1 month ago i had implemented transactional replication. as a result the ldf file size increased about double (3GB).Because of this our software became slow.Therefore i deleted the replication.

Still the ldf file size did not decreased.I tried to shrinked the database to 2MB but it didn't shrinked.

Then i searched on net and found that i need to schedule the full backup and transactional backup.And i then scheduled a full bakup which takes place at 8 pm and transactional backup which takes place at every half an hour.

Now the problem still remains the same.
ldf file is 3GB
mdf file is 1GB
bak file is more than 4GB

Please help me and suggest me what to do.

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 05:03:41

Hi
as 8 pm is it overiting existing file ?

Regards,
Hitesh Soni

shilpi
Starting Member

7 Posts

Posted - 2008-08-12 : 05:06:37

No its not overwriting but making a new bak file.

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 05:09:55

now take backup of log and take then take full backup @ new location
i.e.
Backup log DBName to Disk = 'Location'
then
Backup Database DBName to disk = 'Location'
Just put new location here
then check

Regards,
Hitesh Soni

shilpi
Starting Member

7 Posts

Posted - 2008-08-12 : 05:37:51

DO i have to take both log backup and full bakup on same location

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 05:40:54

frist log backup and then DB full backup threw TSQL Command and location should be new please

Regards,
Hitesh Soni

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 05:44:41

use INIT at the and of Full backup Query

Regards,
Hitesh Soni

shilpi
Starting Member

7 Posts

Posted - 2008-08-12 : 06:09:25

I did that but there is no effect.Size of file increased.

ldf 4GB
bak 5GB

quote:
Originally posted by hitesh6221

frist log backup and then DB full backup threw TSQL Command and location should be new please

Regards,
Hitesh Soni

shilpi
Starting Member

7 Posts

Posted - 2008-08-12 : 06:11:41

How to write query with INIT appended.

quote:
Originally posted by hitesh6221

use INIT at the and of Full backup Query

Regards,
Hitesh Soni

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 06:26:11

backup database DBNMame to disk = 'Location' with init

Regards,
Hitesh Soni

shilpi
Starting Member

7 Posts

Posted - 2008-08-12 : 06:38:23

With init also didnt worked

quote:
Originally posted by hitesh6221

backup database DBNMame to disk = 'Location' with init

Regards,
Hitesh Soni

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 07:02:05

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/39e00ebe-c9b7-4d53-90bd-15de2015fd34.htm
in books online
Please check.

Regards,
Hitesh Soni

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 08:20:55

Thats not big size. Make sure you take log backup frequently.

Sundaresan
Starting Member

28 Posts

Posted - 2008-08-12 : 09:42:59

Hi,

After backuing up the log file
run
In Query Analyser

backup log <DB NAME> with truncate_only;

Then try shrinking the Log file size of the DB.
It will work fine.

Sundaresan.R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 13:37:24

quote:
Originally posted by Sundaresan

Hi,

After backuing up the log file
run
In Query Analyser

backup log <DB NAME> with truncate_only;

Then try shrinking the Log file size of the DB.
It will work fine.

Sundaresan.R

This will break log chain.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-12 : 23:11:54

>> I tried to shrinked the database to 2MB but it didn't shrinked.

Why want to shrink it in first place? Doesn't have enough disk space on the server?

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-13 : 00:06:39

quote:
Originally posted by shilpi

With init also didnt worked

quote:
Originally posted by hitesh6221

backup database DBNMame to disk = 'Location' with init

Regards,
Hitesh Soni

Can U please tell me the recovery model of your DB

Regards,
Hitesh Soni

shilpi
Starting Member

7 Posts

Posted - 2008-08-19 : 06:02:30

quote:
Originally posted by hitesh6221

quote:
Originally posted by shilpi

With init also didnt worked
Recovery model is full Recovery.

quote:
Originally posted by hitesh6221

backup database DBNMame to disk = 'Location' with init

Regards,
Hitesh Soni

Can U please tell me the recovery model of your DB

Regards,
Hitesh Soni

TejasShah
Starting Member

1 Post

Posted - 2008-10-16 : 10:34:24

Hi,

i faced the same issue on my Production server.
and I got the solution.

Check your Database Replication status by

select name, log_reuse_wait_desc from sys.databases

if it displays the REPLICATION, it means you need to set it to 0 by updating sys.databases

Let me know if problem still persists

Tejas Shah

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-16 : 12:15:54

quote:
Originally posted by TejasShah

Hi,

i faced the same issue on my Production server.
and I got the solution.

Check your Database Replication status by

select name, log_reuse_wait_desc from sys.databases

if it displays the REPLICATION, it means you need to set it to 0 by updating sys.databases

Let me know if problem still persists

Tejas Shah

Why you need to update sys.databases?

SANDIP BHANGE
Starting Member

9 Posts

Posted - 2008-11-24 : 03:57:18

To schedule a database backup operation by using SQL Server Management Studio in SQL Server 2005, follow these steps:
Start SQL Server Management Studio.
In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.
Click Connect.
In Object Explorer, expand Databases.
Right-click the database that you want to back up, click Tasks, and then click Back Up.
In the Back Up Database - DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.
In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.
In the Script list, click Script Action to Job.
In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.

Note In the Job Step Properties - 1 dialog box, you can see the backup command.
Under Select a page, click Schedules, and then click New.
In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.

Note If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.
Click OK two times.

You receive the following message:
The backup of database 'DatabaseName' completed successfully.
Note To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this, the SQL Server Agent service must be running.

sandip b

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources