This may help you to solve your issue:
Database size maintenance is among the "boring" tasks that DBAs have to perform. Even though storage space is becoming cheaper each day, disk drives still have limits. If you allow your databases to grow without ever managing their size you're very likely to get in trouble. Regardless of how large your disk drives are, once your database and log files grow large enough, not managing them closely will cause your pager to go off frequently.
So why is the database size an issue? Can't you configure SQL Server 2000 to grow and shrink files as it pleases? You can, but that doesn't necessarily mean that you should. It's easy to configure your database files to grow automatically, but once your file fills up the disk it's sitting on you're in trouble; your database users won't be able to initiate any transactions until you allocate more space to the database. On the other hand, if you don't check database files for unused space, SQL Server might be using considerably more disk space than it has to.
The above paragraph may get you thinking about turning on the AUTOSHRINK database option. Do yourself a huge favor and forget that idea. Turning on AUTOSHRINK in a production environment can degrade system performance significantly. The only time you might want to use AUTOSHRINK is on your development server(s), where optimal performance is not as necessary.
Allowing SQL Server to grow data and log files automatically is generally a good idea; however, you should keep in mind that allocating extra disk space comes with considerable overhead. If you notice that database files keep growing daily you might wish to increase the growth percentage or amount of megabytes added to the file size each time the database grows.
The bottom line is that database and log files should be monitored and managed with care. You can allocate additional space to the database using the ALTER DATABASE command. Both data and log files can be shrunk using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands. The latter allows you to shrink individual files, whereas the former shrinks all database files (if you have more than one) in parallel. Unless I have a small database to manage I prefer using DBCC SHRINKFILE since it allows more control by specifying which file to shrink. The syntax of DBCC SHRINKFILE is as follows:
DBCC SHRINKFILE ({fileid | 'filename'}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
As shown above, you may provide a file identifier (found in sysfiles system table) or file name you wish to shrink. In addition you can specify the compress size and one of the shrinking options. If you omit the compress_size parameter, DBCC SHRINKFILE will attempt to reduce the file to its minimum size. The NOTRUNCATE option moves data pages within the file but does not release free space to the operating system. TRUNCATEONLY does the exact opposite -- it releases free space at the end of the file, but does not re-shuffle the data pages. EMPTYFILE option is for special occasions when you wish to move all data to a different file and drop the data file. Feel free to get additional information on DBCC SHRINKFILE from SQL Server online documentation.
Please be forewarned that shrinking a large data file (one that is several gigabytes or larger) can generate much system overhead and might take a dozens of minutes to complete.
Managing log files is somewhat different from management of data files. The difference is due to the architecture of the transaction log. Regardless of how many log files you add to your database, it is still considered to be a single physical log; therefore you cannot backup individual transaction log files. On the logical level, the transaction log is made up of multiple Virtual Log Files (VLF). Transaction logs may contain transactions that have been committed as well as those that have not. Committed transactions are saved to the disk each time the CHECKPOINT command is executed.
Whether you can shrink the transaction log depends on whether the last virtual log file contains active transactions. You can examine the output of an undocumented DBCC command DBCC LOGINFO, to see if VLF's contain active transactions. If you pass no parameters to DBCC LOGINFO, it will execute in the context of the current database. Alternatively you can pass the database name OR database identifier as a parameter. For example:
DBCC LOGINFO('pubs')
Sample results:
FileID FileSize StartOffset FSeqNo Status Parity CreateLSN
------ ------- ----------- ------ ------ ------ ---------
2 253952 8192 909780 0 64 0
2 253952 262144 909779 0 64 0
2 253952 516096 909778 0 128 0
2 278528 770048 909777 0 128 0
2 262144 1048576 0 0 0 909788000000397000000
2 262144 1310720 0 0 0 909788000000457000000
2 262144 1572864 0 0 0 909789000000019000000
2 262144 1835008 0 0 0 909790000000017000000
2 262144 2097152 0 0 0 909791000000039000000
2 262144 2359296 0 0 0 909792000000035000000
7 458752 48300032 909901 2 64 909884000000410000000
7 327680 48758784 909894 2 64 909886000000400000000
7 327680 49086464 909893 2 64 909886000000400000000
7 327680 49414144 909892 2 64 909886000000400000000
7 524288 49741824 909891 2 64 909886000000400000000
This sample shows that the transaction log consists of two files: file identifiers found in the sysfiles system table for log files are 2 and 7. The transaction log contains 15 virtual log files (total number of rows in the output) of which 5 contain active transactions -- this is true for VLF's that have 2 in the status column of the output. Note also that virtual log files containing the active transactions are at the bottom of the output. Until the status of these VLF's changes to 0 you cannot shrink the transaction log. The status will change when transactions are saved to the disk.
Once the status of a virtual log file changes to 0 you can run DBCC SHRINKFILE against each physical log file (in this case against files 2 and 7) to reduce their size. For example:
DBCC SHRINKFILE (2)
Results:
Dbid fileid currentsize minimumsize usedpages estimatedpages
---- ------ ----------- ----------- --------- --------------
8 2 128 128 128 128
This output lets us know that the file size has been reduced to its minimum.