Any idea on what happen to our database.

[xirosen]

Hi,, i am a newbie in SQL server.. we have a database in our office.. I notice that since 2004 - 2008 our database growth was about 19GB, but since jan. 2009 to present our database grows from 19GB to 52GB... is there's something wrong happening in our database's configuration?

Can anyone pls share an idea..

Thanks..

Sorry for my english..

[urtrivedi]

Generally log file occupies more space in sql server 2000, I dont know about later version. You need to shrink your database. Some time shirnk option does not help much. try following steps. Keep backup of whole database before doing anything.

HOW TO DELETE/REDUCE LOG FILE SIZE IN SQL SERVER 2000
HOW TO DELETE / REDUCE / SHRINK SQL SERVER 2000 DATABASE LOG FILE SIZE

I was devleoping SNGPL Remmittence Reporting system during my job @ NIFT. After about 4 months we noticed that SQL Server 2000 log file size of reporting system was above 35 GB which was of no use. So, we tried to shrink log file size using the recommened methods but no result. Then i developed the following strategy which reduced the log file(LDF) and took it back to just 512KB-1MB.

1. Detach your target database and copy its MDF and LDF file from actual location to some different location. (e.g. SQL Server 2000 installed at C:\Program Files\Microsoft SQL Server\ stores its data files in C:\Program Files\Microsoft SQL Server\MSSQL\Data\)
2. Create a new database with the same name and same MDF and LDF files.
3. Stop sql server, delete the new MDF and LDF file and copy the original MDF (the file you moved to some different location in step 1) to this location.
4. Restart SQL Server and open Query Analyzer to run following scripts. Select Database "master" from the drop down in query analyzer.
5. Now your database will be marked "suspect" by SQL Server
6. Execute the following script to allow adhoc updates etc.
sp_configure "allow updates",1
GO
RECONFIGURE WITH OVERRIDE
GO
7. Update the sysdatabases to update to Emergency mode using following statement. (Replace dbname with your target Database Name)
update sysdatabases set status=32768 where name ='dbname'
8. Restart sql server. now the database will be in emergency mode

9. Now execute the undocumented DBCC to create a log file (Note: Replace the dbname with your target Database Name and log file name according to your requirement)

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')

10. Execute sp_resetstatus <dbname> (Replace <dbname> with your target Database Name)

11. Restart SQL server and see the database is online. Now, you can use your database as you were using before deleting log file.

[xirosen]

i have notice also in our database when i execute "sp_spaceused" that a big allocation for unused space were retrieved.. this is the result of the query..

database_name database_size unallocated_space
devDB 52808.63 MB 4263.15 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
49574504 KB 6858280 KB 2001712 KB 40714512 KB

Can we reuse the unused space?

[urtrivedi]

I am not sure about that option. You just look the file property from windows explorer, ie size of MDF file and size of LDF file. I guess size of LDF will be very large in your case.

[xirosen]

i have check the property of the LDF & MDF file..
the size of MDF is 53939968kb
and the sife of the LDF is 136064kb only...