how can i reduce the size of the database?

I am using SQL server 2000 and its size is increasing like a crazy.. is there any way to reduce the size by deleting any temp data or logs?

Have a look at the database files, which one are gwoing extremly, the data files or the log files ? if the logfiles are getting bigger you should consider shrking them from time to time.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What is growing, your data files or your transaction log file? Are you running in Full Recovery mode and not doing transaction log backups?

If your data files are growing, you can try defragmenting or rebuilding your indexes. If you have a lot of index fragmentation, this can free up a lot of space. You can also try shrinking your data and log files like this:

DBCC SHRINKFILE (N'YourFileName_dat' , 0, TRUNCATEONLY)

http://glennberrysqlperformance.spaces.live.com

|||

Read the below mentioned links... You need to change your backup policy... take the transaction log backup regularly... Do not shirnk database just like that... every database requires some extra space for daily activities... Yes you can shrink Transaction log provided the first action after the shrinking should be Full Backup of your database. Which you truncate and shrink the Transaction Log the Backup chain breaks... so be aware that you should take full backup as soon as possible...

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19637

Madhu

|||

review ur backup policy...truncate transactional logs when u take a full backup....

i wont recommend -- regular -- shrinking of data files...its only goin to make it slower and wont be of significant help mostly...

how can i reduce the size of the database?

I am using SQL server 2000 and its size is increasing like a crazy.. is there any way to reduce the size by deleting any temp data or logs?

Have a look at the database files, which one are gwoing extremly, the data files or the log files ? if the logfiles are getting bigger you should consider shrking them from time to time.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What is growing, your data files or your transaction log file? Are you running in Full Recovery mode and not doing transaction log backups?

If your data files are growing, you can try defragmenting or rebuilding your indexes. If you have a lot of index fragmentation, this can free up a lot of space. You can also try shrinking your data and log files like this:

DBCC SHRINKFILE (N'YourFileName_dat' , 0, TRUNCATEONLY)

http://glennberrysqlperformance.spaces.live.com

|||

Read the below mentioned links... You need to change your backup policy... take the transaction log backup regularly... Do not shirnk database just like that... every database requires some extra space for daily activities... Yes you can shrink Transaction log provided the first action after the shrinking should be Full Backup of your database. Which you truncate and shrink the Transaction Log the Backup chain breaks... so be aware that you should take full backup as soon as possible...

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19637

Madhu

|||

review ur backup policy...truncate transactional logs when u take a full backup....

i wont recommend -- regular -- shrinking of data files...its only goin to make it slower and wont be of significant help mostly...

sql

how can i reduce the size of the database?

I am using SQL server 2000 and its size is increasing like a crazy.. is there any way to reduce the size by deleting any temp data or logs?

Have a look at the database files, which one are gwoing extremly, the data files or the log files ? if the logfiles are getting bigger you should consider shrking them from time to time.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What is growing, your data files or your transaction log file? Are you running in Full Recovery mode and not doing transaction log backups?

If your data files are growing, you can try defragmenting or rebuilding your indexes. If you have a lot of index fragmentation, this can free up a lot of space. You can also try shrinking your data and log files like this:

DBCC SHRINKFILE (N'YourFileName_dat' , 0, TRUNCATEONLY)

http://glennberrysqlperformance.spaces.live.com

|||

Read the below mentioned links... You need to change your backup policy... take the transaction log backup regularly... Do not shirnk database just like that... every database requires some extra space for daily activities... Yes you can shrink Transaction log provided the first action after the shrinking should be Full Backup of your database. Which you truncate and shrink the Transaction Log the Backup chain breaks... so be aware that you should take full backup as soon as possible...

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19637

Madhu

|||

review ur backup policy...truncate transactional logs when u take a full backup....

i wont recommend -- regular -- shrinking of data files...its only goin to make it slower and wont be of significant help mostly...

How Can I reduce my transacion log file?

I Have a Database with a transaction log file 31 Gb, and I have problem to
backup it.
I want decresea it but don't remember How.
LorenaHi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model before doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateon
ly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Lorena" <Lorena@.discussions.microsoft.com> wrote in message
news:6B7189C4-2904-4000-BD28-1A89C8BA9866@.microsoft.com...
> I Have a Database with a transaction log file 31 Gb, and I have problem
to backup it.
> I want decresea it but don't remember How.
> Lorena

How Can I reduce my transacion log file?

I Have a Database with a transaction log file 31 Gb, and I have problem to backup it.
I want decresea it but don't remember How.
Lorena
Hi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model before doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateonly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Lorena" <Lorena@.discussions.microsoft.com> wrote in message
news:6B7189C4-2904-4000-BD28-1A89C8BA9866@.microsoft.com...
> I Have a Database with a transaction log file 31 Gb, and I have problem
to backup it.
> I want decresea it but don't remember How.
> Lorena

How can I reduce My Database 1G to 700MB....

HI...
I want to make Backup cd(700MB).. but My Database is more Bic 1G over.
How can I reduce My Database less than 700MB.
I using MS SQL 2000 Server...
Hi,
Pls. look my reply to your previous post
Thanks
Hari
MCDBA
"Basic SQL SERVER User" <z9813027@.daum.net> wrote in message
news:CB4B5DCA-9F09-4438-B00F-D4B7C602DA01@.microsoft.com...
> HI...
> I want to make Backup cd(700MB).. but My Database is more Bic 1G over.
> How can I reduce My Database less than 700MB.
> I using MS SQL 2000 Server...

How can I reduce My Database 1G to 700MB....

HI...
I want to make Backup cd(700MB).. but My Database is more Bic 1G over.
How can I reduce My Database less than 700MB.
I using MS SQL 2000 Server...Hi,
Pls. look my reply to your previous post
Thanks
Hari
MCDBA
"Basic SQL SERVER User" <z9813027@.daum.net> wrote in message
news:CB4B5DCA-9F09-4438-B00F-D4B7C602DA01@.microsoft.com...
> HI...
> I want to make Backup cd(700MB).. but My Database is more Bic 1G over.
> How can I reduce My Database less than 700MB.
> I using MS SQL 2000 Server...sql

How can I reduce My Database 1G to 700MB....

HI...
I want to make Backup cd(700MB).. but My Database is more Bic 1G over
How can I reduce My Database less than 700MB.
I using MS SQL 2000 Server...Hi,
Pls. look my reply to your previous post
--
Thanks
Hari
MCDBA
"Basic SQL SERVER User" <z9813027@.daum.net> wrote in message
news:CB4B5DCA-9F09-4438-B00F-D4B7C602DA01@.microsoft.com...
> HI...
> I want to make Backup cd(700MB).. but My Database is more Bic 1G over.
> How can I reduce My Database less than 700MB.
> I using MS SQL 2000 Server...