Hello All,
I have My databse on SQL Server at the Web with an space allocation of 800
MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On Uploading t
he Data in the Database. This has caused the maximum size allocated
to my Database exceed, which disallows me for further Uploading of data. To
Come out of the Problem I opted for taking the regular backup of the databas
e which would truncarte the Log fle, But still I cant decrease the
maximum size of the Log file since almost 300 MB is used wile doing transact
ion for the 500 MB datafiles. I also tried deleting the old/unused data fro
m the datafile , but this did not resulted in the decrease of size of
the datafiles. is there any way I can decrease the maximum space allocated t
o the database. I can afford to deleate certain History data which is of no
use to me.
Regards
JaiHi,
1. Perform the Transaction log backup ( BACKUP LOG DBNAME to
disk='c:\backup\dbname.TRN')
2. Shrink the database (Refer Books online for DBCC SHRINKDATABASE and DBCC
SHRINKFILE)
3. Before doing the step 2, Better make the database to restricted mode
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
DBCC SHRINKDATABASE oR DBCC SHRINKFILE
Have a look into the below article on shrinking the Transaction log file,
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Jai" <jai.s@.sagainfotech.com> wrote in message
news:4805D811-29D2-4973-ABD8-51CD36F9A1EB@.microsoft.com...
> Hello All,
> I have My databse on SQL Server at the Web with an space allocation of
800 MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On
Uploading the Data in the Database. This has caused the maximum size
allocated
> to my Database exceed, which disallows me for further Uploading of data.
To Come out of the Problem I opted for taking the regular backup of the
database which would truncarte the Log fle, But still I cant decrease the
> maximum size of the Log file since almost 300 MB is used wile doing
transaction for the 500 MB datafiles. I also tried deleting the old/unused
data from the datafile , but this did not resulted in the decrease of size
of
> the datafiles. is there any way I can decrease the maximum space allocated
to the database. I can afford to deleate certain History data which is of no
use to me.
>
> Regards
> Jai|||Thanks for the reply..
Will you explain me that even if I delete substantial records from the data
base..why I did not get any decrease in the size of the mdf file ?
Regards,
Jai
-- Hari Prasad wrote: --
Hi,
1. Perform the Transaction log backup ( BACKUP LOG DBNAME to
disk='c:\backup\dbname.TRN')
2. Shrink the database (Refer Books online for DBCC SHRINKDATABASE and DBCC
SHRINKFILE)
3. Before doing the step 2, Better make the database to restricted mode
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
DBCC SHRINKDATABASE oR DBCC SHRINKFILE
Have a look into the below article on shrinking the Transaction log file,
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Jai" <jai.s@.sagainfotech.com> wrote in message
news:4805D811-29D2-4973-ABD8-51CD36F9A1EB@.microsoft.com...
> Hello All,
800 MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On
Uploading the Data in the Database. This has caused the maximum size
allocated
> to my Database exceed, which disallows me for further Uploading of data.
To Come out of the Problem I opted for taking the regular backup of the
database which would truncarte the Log fle, But still I cant decrease the
> maximum size of the Log file since almost 300 MB is used wile doing
transaction for the 500 MB datafiles. I also tried deleting the old/unused
data from the datafile , but this did not resulted in the decrease of size
of
> the datafiles. is there any way I can decrease the maximum space allocated
to the database. I can afford to deleate certain History data which is of no
use to me.
> Jai
